From 7d5306ea268033b92cac8496c2d5efdb16b9f6c6 Mon Sep 17 00:00:00 2001
From: ftimme <ft@falkotimme.com>
Date: Fri, 28 Jun 2013 14:37:02 -0400
Subject: [PATCH] - Lists can now be created from more than one database table (including sorting and searching) // needs more testing!
---
interface/web/dns/dns_a_list.php | 2
interface/web/sites/web_vhost_subdomain_list.php | 4
interface/web/client/client_template_list.php | 2
interface/web/mail/mail_domain_catchall_list.php | 2
interface/web/mail/lib/lang/de_spamfilter_users.lng | 2
interface/web/mail/mail_domain_list.php | 2
interface/web/client/client_circle_list.php | 2
interface/web/client/reseller_list.php | 4
interface/web/sites/user_quota_stats.php | 4
interface/web/client/lib/lang/de_client_template_list.lng | 1
interface/web/monitor/datalog_list.php | 4
interface/web/monitor/log_list.php | 2
interface/web/sites/list/aps_installedpackages.list.php | 18 +
interface/web/mail/mail_user_stats.php | 80 ++++++++++
interface/web/dns/dns_soa_list.php | 4
interface/web/sites/shell_user_list.php | 2
interface/web/dns/dns_template_list.php | 4
interface/web/sites/web_subdomain_list.php | 4
interface/web/mail/lib/lang/en_spamfilter_users.lng | 2
interface/web/sites/database_user_list.php | 2
interface/web/sites/web_domain_list.php | 4
interface/web/client/client_list.php | 4
interface/lib/classes/listform_actions.inc.php | 144 ++++-------------
interface/web/client/domain_list.php | 2
interface/web/client/lib/lang/en_client_template_list.lng | 1
interface/web/vm/openvz_ip_list.php | 2
interface/web/admin/server_ip_list.php | 2
interface/web/mailuser/mail_user_filter_list.php | 2
interface/web/mail/mail_user_list.php | 2
interface/lib/classes/listform.inc.php | 5
interface/web/sites/database_list.php | 2
interface/web/admin/users_list.php | 2
interface/web/mail/mail_blacklist_list.php | 2
interface/web/sites/ftp_user_list.php | 2
interface/web/sites/webdav_user_list.php | 2
interface/web/sites/web_aliasdomain_list.php | 4
interface/web/help/support_message_list.php | 2
interface/web/mail/mail_aliasdomain_list.php | 2
interface/web/mail/spamfilter_blacklist_list.php | 2
interface/web/admin/server_php_list.php | 2
interface/web/mail/mail_forward_list.php | 2
interface/web/help/faq_list.php | 2
interface/web/mail/mail_whitelist_list.php | 2
interface/web/mail/mail_alias_list.php | 2
interface/web/sites/web_sites_stats.php | 90 +++++++++++
interface/web/dns/dns_slave_list.php | 4
interface/web/mail/spamfilter_whitelist_list.php | 2
interface/web/sites/aps_availablepackages_list.php | 6
48 files changed, 281 insertions(+), 166 deletions(-)
diff --git a/interface/lib/classes/listform.inc.php b/interface/lib/classes/listform.inc.php
index a57f8e5..b3a59e9 100644
--- a/interface/lib/classes/listform.inc.php
+++ b/interface/lib/classes/listform.inc.php
@@ -180,9 +180,10 @@
if(@is_array($this->listDef['item'])) {
foreach($this->listDef['item'] as $i) {
$field = $i['field'];
+ $table = $i['table'];
// if($_REQUEST[$search_prefix.$field] != '') $sql_where .= " $field ".$i["op"]." '".$i["prefix"].$_REQUEST[$search_prefix.$field].$i["suffix"]."' and";
if(isset($_SESSION['search'][$list_name][$search_prefix.$field]) && $_SESSION['search'][$list_name][$search_prefix.$field] != ''){
- $sql_where .= " $field ".$i['op']." '".$app->db->quote($i['prefix'].$_SESSION['search'][$list_name][$search_prefix.$field].$i['suffix'])."' and";
+ $sql_where .= " ".($table != ''? $table.'.' : $this->listDef['table'].'.')."$field ".$i['op']." '".$app->db->quote($i['prefix'].$_SESSION['search'][$list_name][$search_prefix.$field].$i['suffix'])."' and";
}
}
}
@@ -216,7 +217,7 @@
if($this->searchChanged == 1) $_SESSION['search'][$list_name]['page'] = 0;
$sql_von = $app->functions->intval($_SESSION['search'][$list_name]['page'] * $records_per_page);
- $record_count = $app->db->queryOneRecord("SELECT count(*) AS anzahl FROM $table WHERE $sql_where");
+ $record_count = $app->db->queryOneRecord("SELECT count(*) AS anzahl FROM $table".($app->listform->listDef['additional_tables'] != ''? ','.$app->listform->listDef['additional_tables'] : '')." WHERE $sql_where");
$pages = $app->functions->intval(($record_count['anzahl'] - 1) / $records_per_page);
diff --git a/interface/lib/classes/listform_actions.inc.php b/interface/lib/classes/listform_actions.inc.php
index aef3371..39225df 100644
--- a/interface/lib/classes/listform_actions.inc.php
+++ b/interface/lib/classes/listform_actions.inc.php
@@ -59,28 +59,42 @@
//* Manipulate order by for sorting / Every list has a stored value
//* Against notice error
- if(!isset($_SESSION['search'][$app->listform->listDef["name"].$app->listform->listDef['table']]['order'])){
- $_SESSION['search'][$app->listform->listDef["name"].$app->listform->listDef['table']]['order'] = '';
+ if(!isset($_SESSION['search'][$_SESSION['s']['module']['name'].$app->listform->listDef["name"].$app->listform->listDef['table']]['order'])){
+ $_SESSION['search'][$_SESSION['s']['module']['name'].$app->listform->listDef["name"].$app->listform->listDef['table']]['order'] = '';
}
if(!empty($_GET['orderby'])){
$order = str_replace('tbl_col_','',$_GET['orderby']);
+
//* Check the css class submited value
if (preg_match("/^[a-z\_]{1,}$/",$order)) {
- if($_SESSION['search'][$app->listform->listDef["name"].$app->listform->listDef['table']]['order'] == $order){
- $_SESSION['search'][$app->listform->listDef["name"].$app->listform->listDef['table']]['order'] = $order.' DESC';
+
+ // prepend correct table
+ $prepend_table = $app->listform->listDef['table'];
+ if(trim($app->listform->listDef['additional_tables']) != '' && is_array($app->listform->listDef['item']) && count($app->listform->listDef['item']) > 0) {
+ foreach($app->listform->listDef['item'] as $field) {
+ if($field['field'] == $order && $field['table'] != ''){
+ $prepend_table = $field['table'];
+ break;
+ }
+ }
+ }
+ $order = $prepend_table.'.'.$order;
+
+ if($_SESSION['search'][$_SESSION['s']['module']['name'].$app->listform->listDef["name"].$app->listform->listDef['table']]['order'] == $order){
+ $_SESSION['search'][$_SESSION['s']['module']['name'].$app->listform->listDef["name"].$app->listform->listDef['table']]['order'] = $order.' DESC';
} else {
- $_SESSION['search'][$app->listform->listDef["name"].$app->listform->listDef['table']]['order'] = $order;
+ $_SESSION['search'][$_SESSION['s']['module']['name'].$app->listform->listDef["name"].$app->listform->listDef['table']]['order'] = $order;
}
}
}
// If a manuel oder by like customers isset the sorting will be infront
- if(!empty($_SESSION['search'][$app->listform->listDef["name"].$app->listform->listDef['table']]['order'])){
+ if(!empty($_SESSION['search'][$_SESSION['s']['module']['name'].$app->listform->listDef["name"].$app->listform->listDef['table']]['order'])){
if(empty($this->SQLOrderBy)){
- $this->SQLOrderBy = "ORDER BY ".$_SESSION['search'][$app->listform->listDef["name"].$app->listform->listDef['table']]['order'];
+ $this->SQLOrderBy = "ORDER BY ".$_SESSION['search'][$_SESSION['s']['module']['name'].$app->listform->listDef["name"].$app->listform->listDef['table']]['order'];
} else {
- $this->SQLOrderBy = str_replace("ORDER BY ","ORDER BY ".$_SESSION['search'][$app->listform->listDef["name"].$app->listform->listDef['table']]['order'].', ',$this->SQLOrderBy);
+ $this->SQLOrderBy = str_replace("ORDER BY ","ORDER BY ".$_SESSION['search'][$_SESSION['s']['module']['name'].$app->listform->listDef["name"].$app->listform->listDef['table']]['order'].', ',$this->SQLOrderBy);
}
}
@@ -133,7 +147,7 @@
return $rec;
}
- private function getQueryString() {
+ public function getQueryString() {
global $app;
$sql_where = '';
@@ -151,6 +165,7 @@
}
$sql_where = $app->listform->getSearchSQL($sql_where);
+ if($app->listform->listDef['join_sql']) $sql_where .= ' AND '.$app->listform->listDef['join_sql'];
$app->tpl->setVar($app->listform->searchValues);
$order_by_sql = $this->SQLOrderBy;
@@ -161,109 +176,26 @@
$extselect = '';
$join = '';
- if(!empty($_SESSION['search'][$app->listform->listDef["name"].$app->listform->listDef['table']]['order'])){
- $order = str_replace(' DESC','',$_SESSION['search'][$app->listform->listDef["name"].$app->listform->listDef['table']]['order']);
- if($order == 'server_id' && $app->listform->listDef['table'] != 'server'){
- $join .= ' LEFT JOIN server as s ON '.$app->listform->listDef['table'].'.server_id = s.server_id ';
- $order_by_sql = str_replace('server_id','s.server_name',$order_by_sql);
- } elseif($order == 'client_id' && $app->listform->listDef['table'] != 'client'){
- $join .= ' LEFT JOIN client as c ON '.$app->listform->listDef['table'].'.client_id = c.client_id ';
- $order_by_sql = str_replace('client_id','c.contact_name',$order_by_sql);
- } elseif($order == 'parent_domain_id'){
- $join .= ' LEFT JOIN web_domain as wd ON '.$app->listform->listDef['table'].'.parent_domain_id = wd.domain_id ';
- //$order_by_sql = str_replace(' domain', ' '.$app->listform->listDef['table'].'.domain',$order_by_sql);
- //$order_by_sql = str_replace('parent_domain_id','wd.domain',$order_by_sql);
- $order_by_sql = preg_replace('@( |,|^)(domain)( |,|$)@', '$1'.$app->listform->listDef['table'].'.$2$3', $order_by_sql);
- $order_by_sql = preg_replace('@( |,|^)(parent_domain_id)( |,|$)@', '$1wd.domain$3', $order_by_sql);
-
- //$sql_where = str_replace('type',$app->listform->listDef['table'].'.type',$sql_where);
- //$sql_where = str_replace(' domain',' '.$app->listform->listDef['table'].'.domain',$sql_where);
- $sql_where = preg_replace('@( |,|^)(type)( |,|$)@', '$1'.$app->listform->listDef['table'].'.$2$3', $sql_where);
- $sql_where = preg_replace('@( |,|^)(domain)( |,|$)@', '$1'.$app->listform->listDef['table'].'.$2$3', $sql_where);
- } elseif($order == 'sys_groupid'){
- $join .= ' LEFT JOIN sys_group as sg ON '.$app->listform->listDef['table'].'.sys_groupid = sg.groupid ';
- $order_by_sql = str_replace('sys_groupid','sg.name',$order_by_sql);
- } elseif($order == 'rid'){
- $join .= ' LEFT JOIN spamfilter_users as su ON '.$app->listform->listDef['table'].'.rid = su.id ';
- $order_by_sql = str_replace('rid','su.email',$order_by_sql);
- } elseif($order == 'policy_id'){
- $join .= ' LEFT JOIN spamfilter_policy as sp ON '.$app->listform->listDef['table'].'.policy_id = sp.id ';
- $order_by_sql = str_replace('policy_id','sp.policy_name',$order_by_sql);
- } elseif($order == 'web_folder_id'){
- $join .= ' LEFT JOIN web_folder as wf ON '.$app->listform->listDef['table'].'.web_folder_id = wf.web_folder_id ';
- $order_by_sql = str_replace('web_folder_id','wf.path',$order_by_sql);
- } elseif($order == 'ostemplate_id' && $app->listform->listDef['table'] != 'openvz_ostemplate'){
- $join .= ' LEFT JOIN openvz_ostemplate as oo ON '.$app->listform->listDef['table'].'.ostemplate_id = oo.ostemplate_id ';
- $order_by_sql = str_replace('ostemplate_id','oo.template_name',$order_by_sql);
- } elseif($order == 'template_id' && $app->listform->listDef['table'] != 'openvz_template'){
- $join .= ' LEFT JOIN openvz_template as ot ON '.$app->listform->listDef['table'].'.template_id = ot.template_id ';
- $order_by_sql = str_replace('template_id','ot.template_name',$order_by_sql);
- } elseif($order == 'sender_id' && $app->listform->listDef['table'] != 'sys_user'){
- $join .= ' LEFT JOIN sys_user as su ON '.$app->listform->listDef['table'].'.sender_id = su.userid ';
- $order_by_sql = str_replace('sender_id','su.username',$order_by_sql);
- } elseif($order == 'web_traffic_last_month'){
- $tmp_year = date('Y',mktime(0, 0, 0, date("m")-1, date("d"), date("Y")));
- $tmp_month = date('m',mktime(0, 0, 0, date("m")-1, date("d"), date("Y")));
- $extselect .= ', SUM(wt.traffic_bytes) as calctraffic';
- $join .= ' INNER JOIN web_traffic as wt ON '.$app->listform->listDef['table'].'.domain = wt.hostname ';
- $sql_where .= " AND YEAR(wt.traffic_date) = '$tmp_year' AND MONTH(wt.traffic_date) = '$tmp_month'";
- $order_by_sql = str_replace('web_traffic_last_month','calctraffic',$order_by_sql);
- $order_by_sql = "GROUP BY domain ".$order_by_sql;
- } elseif($order == 'web_traffic_this_month'){
- $tmp_year = date('Y');
- $tmp_month = date('m');
- $extselect .= ', SUM(wt.traffic_bytes) as calctraffic';
- $join .= ' INNER JOIN web_traffic as wt ON '.$app->listform->listDef['table'].'.domain = wt.hostname ';
- $sql_where .= " AND YEAR(wt.traffic_date) = '$tmp_year' AND MONTH(wt.traffic_date) = '$tmp_month'";
- $order_by_sql = str_replace('web_traffic_this_month','calctraffic',$order_by_sql);
- $order_by_sql = "GROUP BY domain ".$order_by_sql;
- } elseif($order == 'web_traffic_last_year'){
- $tmp_year = date('Y',mktime(0, 0, 0, date("m")-1, date("d"), date("Y")));
- $extselect .= ', SUM(wt.traffic_bytes) as calctraffic';
- $join .= ' INNER JOIN web_traffic as wt ON '.$app->listform->listDef['table'].'.domain = wt.hostname ';
- $sql_where .= " AND YEAR(wt.traffic_date) = '$tmp_year'";
- $order_by_sql = str_replace('web_traffic_last_year','calctraffic',$order_by_sql);
- $order_by_sql = "GROUP BY domain ".$order_by_sql;
- } elseif($order == 'web_traffic_this_year'){
- $tmp_year = date('Y');
- $extselect .= ', SUM(wt.traffic_bytes) as calctraffic';
- $join .= ' INNER JOIN web_traffic as wt ON '.$app->listform->listDef['table'].'.domain = wt.hostname ';
- $sql_where .= " AND YEAR(wt.traffic_date) = '$tmp_year'";
- $order_by_sql = str_replace('web_traffic_this_year','calctraffic',$order_by_sql);
- $order_by_sql = "GROUP BY domain ".$order_by_sql;
- } elseif($order == 'mail_traffic_last_month'){
- $tmp_date = date('Y-m',mktime(0, 0, 0, date("m")-1, date("d"), date("Y")));
- $join .= ' INNER JOIN mail_traffic as mt ON '.$app->listform->listDef['table'].'.mailuser_id = mt.mailuser_id ';
- $sql_where .= " AND mt.month like '$tmp_date%'";
- $order_by_sql = str_replace('mail_traffic_last_month','traffic',$order_by_sql);
- } elseif($order == 'mail_traffic_this_month'){
- $tmp_date = date('Y-m');
- $join .= ' INNER JOIN mail_traffic as mt ON '.$app->listform->listDef['table'].'.mailuser_id = mt.mailuser_id ';
- $sql_where .= " AND mt.month like '$tmp_date%'";
- $order_by_sql = str_replace('mail_traffic_this_month','traffic',$order_by_sql);
- } elseif($order == 'mail_traffic_last_year'){
- $tmp_date = date('Y',mktime(0, 0, 0, date("m")-1, date("d"), date("Y")));
- $extselect .= ', SUM(mt.traffic) as calctraffic';
- $join .= ' INNER JOIN mail_traffic as mt ON '.$app->listform->listDef['table'].'.mailuser_id = mt.mailuser_id ';
- $sql_where .= " AND mt.month like '$tmp_date%'";;
- $order_by_sql = str_replace('mail_traffic_last_year','calctraffic',$order_by_sql);
- $order_by_sql = "GROUP BY mailuser_id ".$order_by_sql;
- } elseif($order == 'mail_traffic_this_year'){
- $tmp_date = date('Y');
- $extselect .= ', SUM(mt.traffic) as calctraffic';
- $join .= ' INNER JOIN mail_traffic as mt ON '.$app->listform->listDef['table'].'.mailuser_id = mt.mailuser_id ';
- $sql_where .= " AND mt.month like '$tmp_date%'";
- $order_by_sql = str_replace('mail_traffic_this_year','calctraffic',$order_by_sql);
- $order_by_sql = "GROUP BY mailuser_id ".$order_by_sql;
- }
- }
if($this->SQLExtSelect != '') {
if(substr($this->SQLExtSelect,0,1) != ',') $this->SQLExtSelect = ','.$this->SQLExtSelect;
$extselect .= $this->SQLExtSelect;
}
+
+ $table_selects = array();
+ $table_selects[] = trim($app->listform->listDef['table']).'.*';
+ $app->listform->listDef['additional_tables'] = trim($app->listform->listDef['additional_tables']);
+ if($app->listform->listDef['additional_tables'] != ''){
+ $additional_tables = explode(',', $app->listform->listDef['additional_tables']);
+ foreach($additional_tables as $additional_table){
+ $table_selects[] = trim($additional_table).'.*';
+ }
+ }
+ $select = implode(', ', $table_selects);
- return 'SELECT '.$app->listform->listDef['table'].'.*'.$extselect.' FROM '.$app->listform->listDef['table']."$join WHERE $sql_where $order_by_sql $limit_sql";
+ $sql = 'SELECT '.$select.$extselect.' FROM '.$app->listform->listDef['table'].($app->listform->listDef['additional_tables'] != ''? ','.$app->listform->listDef['additional_tables'] : '')."$join WHERE $sql_where $order_by_sql $limit_sql";
+ //echo $sql;
+ return $sql;
}
diff --git a/interface/web/admin/server_ip_list.php b/interface/web/admin/server_ip_list.php
index 6c98269..939d41f 100644
--- a/interface/web/admin/server_ip_list.php
+++ b/interface/web/admin/server_ip_list.php
@@ -46,7 +46,7 @@
$app->uses('listform_actions');
-$app->listform_actions->SQLOrderBy = "ORDER BY server_id, ip_address";
+$app->listform_actions->SQLOrderBy = "ORDER BY server_ip.server_id, server_ip.ip_address";
$app->listform_actions->onLoad();
diff --git a/interface/web/admin/server_php_list.php b/interface/web/admin/server_php_list.php
index 86a7050..bcd323b 100644
--- a/interface/web/admin/server_php_list.php
+++ b/interface/web/admin/server_php_list.php
@@ -46,7 +46,7 @@
$app->uses('listform_actions');
-$app->listform_actions->SQLOrderBy = "ORDER BY server_id, name";
+$app->listform_actions->SQLOrderBy = "ORDER BY server_php.server_id, server_php.name";
$app->listform_actions->onLoad();
diff --git a/interface/web/admin/users_list.php b/interface/web/admin/users_list.php
index ae0bca5..412d4fa 100644
--- a/interface/web/admin/users_list.php
+++ b/interface/web/admin/users_list.php
@@ -45,7 +45,7 @@
$app->auth->check_module_permissions('admin');
$app->uses('listform_actions');
-$app->listform_actions->SQLOrderBy = 'ORDER BY username';
+$app->listform_actions->SQLOrderBy = 'ORDER BY sys_user.username';
$app->listform_actions->onLoad();
diff --git a/interface/web/client/client_circle_list.php b/interface/web/client/client_circle_list.php
index 935fbee..f37da38 100644
--- a/interface/web/client/client_circle_list.php
+++ b/interface/web/client/client_circle_list.php
@@ -17,7 +17,7 @@
$app->uses('listform_actions');
-$app->listform_actions->SQLOrderBy = 'ORDER BY circle_name, circle_id';
+$app->listform_actions->SQLOrderBy = 'ORDER BY client_circle.circle_name, client_circle.circle_id';
$app->listform_actions->onLoad();
diff --git a/interface/web/client/client_list.php b/interface/web/client/client_list.php
index 3eff65b..35258b8 100644
--- a/interface/web/client/client_list.php
+++ b/interface/web/client/client_list.php
@@ -17,8 +17,8 @@
$app->uses('listform_actions');
-$app->listform_actions->SQLOrderBy = 'ORDER BY company_name, contact_name, client_id';
-$app->listform_actions->SQLExtWhere = "limit_client = 0";
+$app->listform_actions->SQLOrderBy = 'ORDER BY client.company_name, client.contact_name, client.client_id';
+$app->listform_actions->SQLExtWhere = "client.limit_client = 0";
$app->listform_actions->SQLExtSelect = ', client.country as countryiso';
$app->listform_actions->onLoad();
diff --git a/interface/web/client/client_template_list.php b/interface/web/client/client_template_list.php
index f0d1752..bdfe1d1 100644
--- a/interface/web/client/client_template_list.php
+++ b/interface/web/client/client_template_list.php
@@ -44,6 +44,6 @@
if(!$_SESSION["s"]["user"]["typ"] == 'admin') die('Client-Templates are only for Admins.');
$app->uses('listform_actions');
-$app->listform_actions->SQLOrderBy = 'ORDER BY template_name';
+$app->listform_actions->SQLOrderBy = 'ORDER BY client_template.template_name';
$app->listform_actions->onLoad();
?>
diff --git a/interface/web/client/domain_list.php b/interface/web/client/domain_list.php
index 14308ed..e4ef725 100644
--- a/interface/web/client/domain_list.php
+++ b/interface/web/client/domain_list.php
@@ -46,7 +46,7 @@
$app->uses('listform_actions');
-$app->listform_actions->SQLOrderBy = 'ORDER BY domain';
+$app->listform_actions->SQLOrderBy = 'ORDER BY domain.domain';
$app->listform_actions->onLoad();
?>
\ No newline at end of file
diff --git a/interface/web/client/lib/lang/de_client_template_list.lng b/interface/web/client/lib/lang/de_client_template_list.lng
index 98195fe..552035f 100644
--- a/interface/web/client/lib/lang/de_client_template_list.lng
+++ b/interface/web/client/lib/lang/de_client_template_list.lng
@@ -2,4 +2,5 @@
$wb['list_head_txt'] = 'Kundenvorlagen';
$wb['template_type_txt'] = 'Typ';
$wb['template_name_txt'] = 'Vorlagenname';
+$wb['template_id_txt'] = 'Template ID';
?>
diff --git a/interface/web/client/lib/lang/en_client_template_list.lng b/interface/web/client/lib/lang/en_client_template_list.lng
index ac52186..ce1f9bd 100644
--- a/interface/web/client/lib/lang/en_client_template_list.lng
+++ b/interface/web/client/lib/lang/en_client_template_list.lng
@@ -2,4 +2,5 @@
$wb["list_head_txt"] = 'Client-Templates';
$wb["template_type_txt"] = 'Type';
$wb["template_name_txt"] = 'Template name';
+$wb['template_id_txt'] = 'Template ID';
?>
diff --git a/interface/web/client/reseller_list.php b/interface/web/client/reseller_list.php
index fa819e6..fd8fca0 100644
--- a/interface/web/client/reseller_list.php
+++ b/interface/web/client/reseller_list.php
@@ -47,8 +47,8 @@
$app->uses('listform_actions');
-$app->listform_actions->SQLOrderBy = 'ORDER BY company_name, contact_name, client_id';
-$app->listform_actions->SQLExtWhere = "(limit_client > 0 or limit_client = -1)";
+$app->listform_actions->SQLOrderBy = 'ORDER BY client.company_name, client.contact_name, client.client_id';
+$app->listform_actions->SQLExtWhere = "(client.limit_client > 0 or client.limit_client = -1)";
$app->listform_actions->SQLExtSelect = ', client.country as countryiso';
$app->listform_actions->onLoad();
diff --git a/interface/web/dns/dns_a_list.php b/interface/web/dns/dns_a_list.php
index c01aec6..afe76b6 100644
--- a/interface/web/dns/dns_a_list.php
+++ b/interface/web/dns/dns_a_list.php
@@ -16,7 +16,7 @@
$app->auth->check_module_permissions('dns');
$app->uses('listform_actions');
-$app->listform_actions->SQLExtWhere = "type = 'A'";
+$app->listform_actions->SQLExtWhere = "dns_rr.type = 'A'";
$app->listform_actions->onLoad();
diff --git a/interface/web/dns/dns_slave_list.php b/interface/web/dns/dns_slave_list.php
index bc66641..fe78ca2 100644
--- a/interface/web/dns/dns_slave_list.php
+++ b/interface/web/dns/dns_slave_list.php
@@ -16,9 +16,9 @@
$app->auth->check_module_permissions('dns');
$app->uses('listform_actions');
-// $app->listform_actions->SQLExtWhere = "access = 'REJECT'";
+// $app->listform_actions->SQLExtWhere = "dns_slave.access = 'REJECT'";
-$app->listform_actions->SQLOrderBy = 'ORDER BY origin';
+$app->listform_actions->SQLOrderBy = 'ORDER BY dns_slave.origin';
$app->listform_actions->onLoad();
diff --git a/interface/web/dns/dns_soa_list.php b/interface/web/dns/dns_soa_list.php
index d2851f7..27604fb 100644
--- a/interface/web/dns/dns_soa_list.php
+++ b/interface/web/dns/dns_soa_list.php
@@ -16,9 +16,9 @@
$app->auth->check_module_permissions('dns');
$app->uses('listform_actions');
-// $app->listform_actions->SQLExtWhere = "access = 'REJECT'";
+// $app->listform_actions->SQLExtWhere = "dns_soa.access = 'REJECT'";
-$app->listform_actions->SQLOrderBy = 'ORDER BY origin';
+$app->listform_actions->SQLOrderBy = 'ORDER BY dns_soa.origin';
$app->listform_actions->onLoad();
diff --git a/interface/web/dns/dns_template_list.php b/interface/web/dns/dns_template_list.php
index 37648a1..acad5d3 100644
--- a/interface/web/dns/dns_template_list.php
+++ b/interface/web/dns/dns_template_list.php
@@ -16,9 +16,9 @@
$app->auth->check_module_permissions('dns');
$app->uses('listform_actions');
-// $app->listform_actions->SQLExtWhere = "access = 'REJECT'";
+// $app->listform_actions->SQLExtWhere = "dns_template.access = 'REJECT'";
-$app->listform_actions->SQLOrderBy = 'ORDER BY name';
+$app->listform_actions->SQLOrderBy = 'ORDER BY dns_template.name';
$app->listform_actions->onLoad();
diff --git a/interface/web/help/faq_list.php b/interface/web/help/faq_list.php
index df69273..79e9b6f 100644
--- a/interface/web/help/faq_list.php
+++ b/interface/web/help/faq_list.php
@@ -26,7 +26,7 @@
$res = $app->db->queryOneRecord("SELECT MIN(hfs_id) AS min_id FROM help_faq_sections");
$hf_section = $res['min_id'];
}
-$app->listform_actions->SQLExtWhere = "hf_section = $hf_section";
+$app->listform_actions->SQLExtWhere = "help_faq.hf_section = $hf_section";
if($hf_section) $res = $app->db->queryOneRecord("SELECT hfs_name FROM help_faq_sections WHERE hfs_id=$hf_section");
diff --git a/interface/web/help/support_message_list.php b/interface/web/help/support_message_list.php
index dcf779e..b2ac8c0 100644
--- a/interface/web/help/support_message_list.php
+++ b/interface/web/help/support_message_list.php
@@ -12,7 +12,7 @@
$app->uses('listform_actions');
//* Optional limit
-$app->listform_actions->SQLExtWhere = "recipient_id = ".$_SESSION['s']['user']['userid'];
+$app->listform_actions->SQLExtWhere = "support_message.recipient_id = ".$_SESSION['s']['user']['userid'];
//* Start the form rendering and action ahndling
$app->listform_actions->onLoad();
diff --git a/interface/web/mail/lib/lang/de_spamfilter_users.lng b/interface/web/mail/lib/lang/de_spamfilter_users.lng
index d31d0e1..b7f6cd4 100644
--- a/interface/web/mail/lib/lang/de_spamfilter_users.lng
+++ b/interface/web/mail/lib/lang/de_spamfilter_users.lng
@@ -5,4 +5,6 @@
$wb['email_txt'] = 'E-Mail (Zeichenkette)';
$wb['fullname_txt'] = 'Name';
$wb['local_txt'] = 'Lokal';
+$wb['email_error_notempty'] = 'Die E-Mail-Adresse darf nicht leer sein.';
+$wb['fullname_error_notempty'] = 'Der Name darf nicht leer sein.';
?>
diff --git a/interface/web/mail/lib/lang/en_spamfilter_users.lng b/interface/web/mail/lib/lang/en_spamfilter_users.lng
index 78b346c..38d51ae 100644
--- a/interface/web/mail/lib/lang/en_spamfilter_users.lng
+++ b/interface/web/mail/lib/lang/en_spamfilter_users.lng
@@ -5,4 +5,6 @@
$wb["email_txt"] = 'Email (Pattern)';
$wb["fullname_txt"] = 'Name';
$wb["local_txt"] = 'Local';
+$wb['email_error_notempty'] = 'The email address must not be empty.';
+$wb['fullname_error_notempty'] = 'The name must not be empty.';
?>
\ No newline at end of file
diff --git a/interface/web/mail/mail_alias_list.php b/interface/web/mail/mail_alias_list.php
index 1ee6ad6..6921894 100644
--- a/interface/web/mail/mail_alias_list.php
+++ b/interface/web/mail/mail_alias_list.php
@@ -18,7 +18,7 @@
$app->uses('listform_actions');
// Limit the results to alias domains
-$app->listform_actions->SQLExtWhere = "type = 'alias'";
+$app->listform_actions->SQLExtWhere = "mail_forwarding.type = 'alias'";
$app->listform_actions->onLoad();
diff --git a/interface/web/mail/mail_aliasdomain_list.php b/interface/web/mail/mail_aliasdomain_list.php
index 8998afa..396aa61 100644
--- a/interface/web/mail/mail_aliasdomain_list.php
+++ b/interface/web/mail/mail_aliasdomain_list.php
@@ -18,7 +18,7 @@
$app->uses('listform_actions');
// Limit the results to alias domains
-$app->listform_actions->SQLExtWhere = "type = 'aliasdomain'";
+$app->listform_actions->SQLExtWhere = "mail_forwarding.type = 'aliasdomain'";
$app->listform_actions->onLoad();
diff --git a/interface/web/mail/mail_blacklist_list.php b/interface/web/mail/mail_blacklist_list.php
index 000afd1..9c118b3 100644
--- a/interface/web/mail/mail_blacklist_list.php
+++ b/interface/web/mail/mail_blacklist_list.php
@@ -16,7 +16,7 @@
$app->auth->check_module_permissions('mail');
$app->uses('listform_actions');
-$app->listform_actions->SQLExtWhere = "access = 'REJECT'";
+$app->listform_actions->SQLExtWhere = "mail_access.access = 'REJECT'";
$app->listform_actions->onLoad();
diff --git a/interface/web/mail/mail_domain_catchall_list.php b/interface/web/mail/mail_domain_catchall_list.php
index e07495f..a703a17 100644
--- a/interface/web/mail/mail_domain_catchall_list.php
+++ b/interface/web/mail/mail_domain_catchall_list.php
@@ -18,7 +18,7 @@
$app->uses('listform_actions');
// Limit the results to alias domains
-$app->listform_actions->SQLExtWhere = "type = 'catchall'";
+$app->listform_actions->SQLExtWhere = "mail_forwarding.type = 'catchall'";
$app->listform_actions->onLoad();
diff --git a/interface/web/mail/mail_domain_list.php b/interface/web/mail/mail_domain_list.php
index d07855c..cad6506 100644
--- a/interface/web/mail/mail_domain_list.php
+++ b/interface/web/mail/mail_domain_list.php
@@ -21,7 +21,7 @@
// Limit the results to alias domains
// $app->listform_actions->SQLExtWhere = "type = 'local'";
-$app->listform_actions->SQLOrderBy = 'ORDER BY domain';
+$app->listform_actions->SQLOrderBy = 'ORDER BY mail_domain.domain';
$app->listform_actions->onLoad();
diff --git a/interface/web/mail/mail_forward_list.php b/interface/web/mail/mail_forward_list.php
index 40c8244..4fd4790 100644
--- a/interface/web/mail/mail_forward_list.php
+++ b/interface/web/mail/mail_forward_list.php
@@ -18,7 +18,7 @@
$app->uses('listform_actions');
// Limit the results to alias domains
-$app->listform_actions->SQLExtWhere = "type = 'forward'";
+$app->listform_actions->SQLExtWhere = "mail_forwarding.type = 'forward'";
$app->listform_actions->onLoad();
diff --git a/interface/web/mail/mail_user_list.php b/interface/web/mail/mail_user_list.php
index fcbbaa7..7f93e2b 100644
--- a/interface/web/mail/mail_user_list.php
+++ b/interface/web/mail/mail_user_list.php
@@ -44,7 +44,7 @@
}
$list = new list_action;
-$list->SQLOrderBy = 'ORDER BY email';
+$list->SQLOrderBy = 'ORDER BY mail_user.email';
$list->onLoad();
diff --git a/interface/web/mail/mail_user_stats.php b/interface/web/mail/mail_user_stats.php
index 36c7da5..65b179d 100644
--- a/interface/web/mail/mail_user_stats.php
+++ b/interface/web/mail/mail_user_stats.php
@@ -54,6 +54,86 @@
$rec['id'] = $rec[$this->idx_key];
return $rec;
}
+
+ function getQueryString() {
+ global $app;
+ $sql_where = '';
+
+ //* Generate the search sql
+ if($app->listform->listDef['auth'] != 'no') {
+ if($_SESSION['s']['user']['typ'] == "admin") {
+ $sql_where = '';
+ } else {
+ $sql_where = $app->tform->getAuthSQL('r', $app->listform->listDef['table']).' and';
+ //$sql_where = $app->tform->getAuthSQL('r').' and';
+ }
+ }
+ if($this->SQLExtWhere != '') {
+ $sql_where .= ' '.$this->SQLExtWhere.' and';
+ }
+
+ $sql_where = $app->listform->getSearchSQL($sql_where);
+ if($app->listform->listDef['join_sql']) $sql_where .= ' AND '.$app->listform->listDef['join_sql'];
+ $app->tpl->setVar($app->listform->searchValues);
+
+ $order_by_sql = $this->SQLOrderBy;
+
+ //* Generate SQL for paging
+ $limit_sql = $app->listform->getPagingSQL($sql_where);
+ $app->tpl->setVar('paging',$app->listform->pagingHTML);
+
+ $extselect = '';
+ $join = '';
+
+ if(!empty($_SESSION['search'][$_SESSION['s']['module']['name'].$app->listform->listDef["name"].$app->listform->listDef['table']]['order'])){
+ $order = str_replace(' DESC','',$_SESSION['search'][$_SESSION['s']['module']['name'].$app->listform->listDef["name"].$app->listform->listDef['table']]['order']);
+ list($tmp_table, $order) = explode('.', $order);
+ if($order == 'mail_traffic_last_month'){
+ $tmp_date = date('Y-m',mktime(0, 0, 0, date("m")-1, date("d"), date("Y")));
+ $join .= ' INNER JOIN mail_traffic as mt ON '.$app->listform->listDef['table'].'.mailuser_id = mt.mailuser_id ';
+ $sql_where .= " AND mt.month like '$tmp_date%'";
+ $order_by_sql = str_replace($app->listform->listDef['table'].'.mail_traffic_last_month','traffic',$order_by_sql);
+ } elseif($order == 'mail_traffic_this_month'){
+ $tmp_date = date('Y-m');
+ $join .= ' INNER JOIN mail_traffic as mt ON '.$app->listform->listDef['table'].'.mailuser_id = mt.mailuser_id ';
+ $sql_where .= " AND mt.month like '$tmp_date%'";
+ $order_by_sql = str_replace($app->listform->listDef['table'].'.mail_traffic_this_month','traffic',$order_by_sql);
+ } elseif($order == 'mail_traffic_last_year'){
+ $tmp_date = date('Y',mktime(0, 0, 0, date("m")-1, date("d"), date("Y")));
+ $extselect .= ', SUM(mt.traffic) as calctraffic';
+ $join .= ' INNER JOIN mail_traffic as mt ON '.$app->listform->listDef['table'].'.mailuser_id = mt.mailuser_id ';
+ $sql_where .= " AND mt.month like '$tmp_date%'";;
+ $order_by_sql = str_replace($app->listform->listDef['table'].'.mail_traffic_last_year','calctraffic',$order_by_sql);
+ $order_by_sql = "GROUP BY mailuser_id ".$order_by_sql;
+ } elseif($order == 'mail_traffic_this_year'){
+ $tmp_date = date('Y');
+ $extselect .= ', SUM(mt.traffic) as calctraffic';
+ $join .= ' INNER JOIN mail_traffic as mt ON '.$app->listform->listDef['table'].'.mailuser_id = mt.mailuser_id ';
+ $sql_where .= " AND mt.month like '$tmp_date%'";
+ $order_by_sql = str_replace($app->listform->listDef['table'].'.mail_traffic_this_year','calctraffic',$order_by_sql);
+ $order_by_sql = "GROUP BY mailuser_id ".$order_by_sql;
+ }
+ }
+
+ if($this->SQLExtSelect != '') {
+ if(substr($this->SQLExtSelect,0,1) != ',') $this->SQLExtSelect = ','.$this->SQLExtSelect;
+ $extselect .= $this->SQLExtSelect;
+ }
+
+ $table_selects = array();
+ $table_selects[] = trim($app->listform->listDef['table']).'.*';
+ $app->listform->listDef['additional_tables'] = trim($app->listform->listDef['additional_tables']);
+ if($app->listform->listDef['additional_tables'] != ''){
+ $additional_tables = explode(',', $app->listform->listDef['additional_tables']);
+ foreach($additional_tables as $additional_table){
+ $table_selects[] = trim($additional_table).'.*';
+ }
+ }
+ $select = implode(', ', $table_selects);
+
+ $sql = 'SELECT '.$select.$extselect.' FROM '.$app->listform->listDef['table'].($app->listform->listDef['additional_tables'] != ''? ','.$app->listform->listDef['additional_tables'] : '')."$join WHERE $sql_where $order_by_sql $limit_sql";
+ return $sql;
+ }
}
$list = new list_action;
diff --git a/interface/web/mail/mail_whitelist_list.php b/interface/web/mail/mail_whitelist_list.php
index 6a5358e..a4c39a9 100644
--- a/interface/web/mail/mail_whitelist_list.php
+++ b/interface/web/mail/mail_whitelist_list.php
@@ -16,7 +16,7 @@
$app->auth->check_module_permissions('mail');
$app->uses('listform_actions');
-$app->listform_actions->SQLExtWhere = "access = 'OK'";
+$app->listform_actions->SQLExtWhere = "mail_access.access = 'OK'";
$app->listform_actions->onLoad();
diff --git a/interface/web/mail/spamfilter_blacklist_list.php b/interface/web/mail/spamfilter_blacklist_list.php
index c2cc411..087e533 100644
--- a/interface/web/mail/spamfilter_blacklist_list.php
+++ b/interface/web/mail/spamfilter_blacklist_list.php
@@ -16,7 +16,7 @@
$app->auth->check_module_permissions('mail');
$app->uses('listform_actions');
-$app->listform_actions->SQLExtWhere = "wb = 'B'";
+$app->listform_actions->SQLExtWhere = "spamfilter_wblist.wb = 'B'";
$app->listform_actions->onLoad();
diff --git a/interface/web/mail/spamfilter_whitelist_list.php b/interface/web/mail/spamfilter_whitelist_list.php
index 12c622a..ffb0fc9 100644
--- a/interface/web/mail/spamfilter_whitelist_list.php
+++ b/interface/web/mail/spamfilter_whitelist_list.php
@@ -16,7 +16,7 @@
$app->auth->check_module_permissions('mail');
$app->uses('listform_actions');
-$app->listform_actions->SQLExtWhere = "wb = 'W'";
+$app->listform_actions->SQLExtWhere = "spamfilter_wblist.wb = 'W'";
$app->listform_actions->onLoad();
diff --git a/interface/web/mailuser/mail_user_filter_list.php b/interface/web/mailuser/mail_user_filter_list.php
index 43d7eec..d896aa5 100644
--- a/interface/web/mailuser/mail_user_filter_list.php
+++ b/interface/web/mailuser/mail_user_filter_list.php
@@ -24,7 +24,7 @@
$list = new list_action;
-$list->SQLExtWhere = "mailuser_id = ".$_SESSION['s']['user']['mailuser_id'];
+$list->SQLExtWhere = "mail_user_filter.mailuser_id = ".$_SESSION['s']['user']['mailuser_id'];
$list->onLoad();
diff --git a/interface/web/monitor/datalog_list.php b/interface/web/monitor/datalog_list.php
index 6f4766e..dd5bfb3 100644
--- a/interface/web/monitor/datalog_list.php
+++ b/interface/web/monitor/datalog_list.php
@@ -50,13 +50,13 @@
$sql = '(';
foreach($servers as $sv) {
- $sql .= " (datalog_id > ".$sv['updated']." AND sys_datalog.server_id = ".$sv['server_id'].") OR ";
+ $sql .= " (sys_datalog.datalog_id > ".$sv['updated']." AND sys_datalog.server_id = ".$sv['server_id'].") OR ";
}
$sql = substr($sql,0,-4);
$sql .= ')';
$app->listform_actions->SQLExtWhere = $sql;
-$app->listform_actions->SQLOrderBy = "ORDER BY tstamp DESC, datalog_id DESC";
+$app->listform_actions->SQLOrderBy = "ORDER BY sys_datalog.tstamp DESC, sys_datalog.datalog_id DESC";
$app->listform_actions->onLoad();
diff --git a/interface/web/monitor/log_list.php b/interface/web/monitor/log_list.php
index d8304ad..2d5edb2 100644
--- a/interface/web/monitor/log_list.php
+++ b/interface/web/monitor/log_list.php
@@ -47,7 +47,7 @@
$app->uses('listform_actions');
//$app->listform_actions->SQLExtWhere = "wb = 'W'";
-$app->listform_actions->SQLOrderBy = "ORDER BY tstamp DESC, syslog_id DESC";
+$app->listform_actions->SQLOrderBy = "ORDER BY sys_log.tstamp DESC, sys_log.syslog_id DESC";
$app->listform_actions->onLoad();
diff --git a/interface/web/sites/aps_availablepackages_list.php b/interface/web/sites/aps_availablepackages_list.php
index 3e3b83b..0966df9 100644
--- a/interface/web/sites/aps_availablepackages_list.php
+++ b/interface/web/sites/aps_availablepackages_list.php
@@ -42,10 +42,10 @@
// Load needed classes
$app->uses('tpl,listform_actions');
-$app->listform_actions->SQLOrderBy = 'ORDER BY name, version';
+$app->listform_actions->SQLOrderBy = 'ORDER BY aps_packages.name, aps_packages.version';
// Show only unlocked packages to clients and (un-)lockable packages to admins
-if($_SESSION['s']['user']['typ'] != 'admin') $app->listform_actions->SQLExtWhere = 'package_status = '.PACKAGE_ENABLED;
-else $app->listform_actions->SQLExtWhere = '(package_status = '.PACKAGE_ENABLED.' OR package_status = '.PACKAGE_LOCKED.')';
+if($_SESSION['s']['user']['typ'] != 'admin') $app->listform_actions->SQLExtWhere = 'aps_packages.package_status = '.PACKAGE_ENABLED;
+else $app->listform_actions->SQLExtWhere = '(aps_packages.package_status = '.PACKAGE_ENABLED.' OR aps_packages.package_status = '.PACKAGE_LOCKED.')';
// Get package amount
$pkg_count = $app->db->queryOneRecord("SELECT COUNT(*) FROM aps_packages");
diff --git a/interface/web/sites/database_list.php b/interface/web/sites/database_list.php
index dfe11be..42c2005 100644
--- a/interface/web/sites/database_list.php
+++ b/interface/web/sites/database_list.php
@@ -67,7 +67,7 @@
}
$list = new list_action;
-$list->SQLOrderBy = 'ORDER BY database_name';
+$list->SQLOrderBy = 'ORDER BY web_database.database_name';
$list->onLoad();
diff --git a/interface/web/sites/database_user_list.php b/interface/web/sites/database_user_list.php
index 52a48e4..84c9535 100644
--- a/interface/web/sites/database_user_list.php
+++ b/interface/web/sites/database_user_list.php
@@ -58,7 +58,7 @@
}
$list = new list_action;
-$list->SQLOrderBy = 'ORDER BY database_user';
+$list->SQLOrderBy = 'ORDER BY web_database_user.database_user';
$list->onLoad();
diff --git a/interface/web/sites/ftp_user_list.php b/interface/web/sites/ftp_user_list.php
index 250efcf..d5b648c 100644
--- a/interface/web/sites/ftp_user_list.php
+++ b/interface/web/sites/ftp_user_list.php
@@ -68,7 +68,7 @@
}
$list = new list_action;
-$list->SQLOrderBy = 'ORDER BY username';
+$list->SQLOrderBy = 'ORDER BY ftp_user.username';
$list->onLoad();
diff --git a/interface/web/sites/list/aps_installedpackages.list.php b/interface/web/sites/list/aps_installedpackages.list.php
index 2b2f32e..2340824 100644
--- a/interface/web/sites/list/aps_installedpackages.list.php
+++ b/interface/web/sites/list/aps_installedpackages.list.php
@@ -29,8 +29,15 @@
*/
$liste['name'] = 'aps_instances'; // Name of the list
-$liste['table'] = 'aps_instances,aps_packages'; // Database table
+$liste['table'] = 'aps_instances'; // Database table
$liste['table_idx'] = 'id'; // Table index
+
+// if multiple tables are involved, list the additional tables here (comma separated)
+$liste["additional_tables"] = "aps_packages";
+
+// if multiple tables are involved, specify sql to join these tables
+$liste["join_sql"] = " aps_instances.package_id = aps_packages.id";
+
$liste["search_prefix"] = 'search_'; // Search field prefix
$liste['records_per_page'] = 15; // Records per page
$liste['file'] = 'aps_installedpackages_list.php'; // Script file for this list
@@ -47,7 +54,8 @@
'prefix' => '%',
'suffix' => '%',
'width' => '',
- 'value' => '');
+ 'value' => '',
+ 'table' => 'aps_packages');
$liste["item"][] = array('field' => 'version',
'datatype' => 'VARCHAR',
@@ -56,7 +64,8 @@
'prefix' => '%',
'suffix' => '%',
'width' => '',
- 'value' => '');
+ 'value' => '',
+ 'table' => 'aps_packages');
/*
$liste["item"][] = array('field' => 'customer_id',
@@ -79,5 +88,6 @@
'value' => array(INSTANCE_INSTALL => $app->lng('Installation_task'),
INSTANCE_ERROR => $app->lng('Installation_error'),
INSTANCE_SUCCESS => $app->lng('Installation_success'),
- INSTANCE_REMOVE => $app->lng('Installation_remove')));
+ INSTANCE_REMOVE => $app->lng('Installation_remove')),
+ 'table' => 'aps_instances');
?>
\ No newline at end of file
diff --git a/interface/web/sites/shell_user_list.php b/interface/web/sites/shell_user_list.php
index 3307d63..cba130c 100644
--- a/interface/web/sites/shell_user_list.php
+++ b/interface/web/sites/shell_user_list.php
@@ -49,7 +49,7 @@
// Limit the results to alias domains
//$app->listform_actions->SQLExtWhere = "type = 'subdomain'";
-$app->listform_actions->SQLOrderBy = 'ORDER BY username';
+$app->listform_actions->SQLOrderBy = 'ORDER BY shell_user.username';
$app->listform_actions->onLoad();
diff --git a/interface/web/sites/user_quota_stats.php b/interface/web/sites/user_quota_stats.php
index 56cc02c..c00f893 100644
--- a/interface/web/sites/user_quota_stats.php
+++ b/interface/web/sites/user_quota_stats.php
@@ -95,8 +95,8 @@
}
$list = new list_action;
-$list->SQLExtWhere = "type = 'vhost'";
-$list->SQLOrderBy = 'ORDER BY domain';
+$list->SQLExtWhere = "web_domain.type = 'vhost'";
+$list->SQLOrderBy = 'ORDER BY web_domain.domain';
$list->onLoad();
diff --git a/interface/web/sites/web_aliasdomain_list.php b/interface/web/sites/web_aliasdomain_list.php
index ef4a181..c5e63be 100644
--- a/interface/web/sites/web_aliasdomain_list.php
+++ b/interface/web/sites/web_aliasdomain_list.php
@@ -47,8 +47,8 @@
$app->uses('listform_actions');
// Limit the results to alias domains
-$app->listform_actions->SQLExtWhere = "type = 'alias'";
-$app->listform_actions->SQLOrderBy = 'ORDER BY domain';
+$app->listform_actions->SQLExtWhere = "web_domain.type = 'alias'";
+$app->listform_actions->SQLOrderBy = 'ORDER BY web_domain.domain';
$app->listform_actions->onLoad();
diff --git a/interface/web/sites/web_domain_list.php b/interface/web/sites/web_domain_list.php
index feae9c8..d9c5066 100644
--- a/interface/web/sites/web_domain_list.php
+++ b/interface/web/sites/web_domain_list.php
@@ -52,8 +52,8 @@
}
$list = new list_action;
-$list->SQLExtWhere = "type = 'vhost' AND parent_domain_id = '0'";
-$list->SQLOrderBy = 'ORDER BY domain';
+$list->SQLExtWhere = "web_domain.type = 'vhost' AND web_domain.parent_domain_id = '0'";
+$list->SQLOrderBy = 'ORDER BY web_domain.domain';
$list->onLoad();
?>
\ No newline at end of file
diff --git a/interface/web/sites/web_sites_stats.php b/interface/web/sites/web_sites_stats.php
index 3a01069..6249b68 100644
--- a/interface/web/sites/web_sites_stats.php
+++ b/interface/web/sites/web_sites_stats.php
@@ -79,11 +79,97 @@
$app->tpl_defaults();
$app->tpl->pparse();
}
+
+ function getQueryString() {
+ global $app;
+ $sql_where = '';
+
+ //* Generate the search sql
+ if($app->listform->listDef['auth'] != 'no') {
+ if($_SESSION['s']['user']['typ'] == "admin") {
+ $sql_where = '';
+ } else {
+ $sql_where = $app->tform->getAuthSQL('r', $app->listform->listDef['table']).' and';
+ //$sql_where = $app->tform->getAuthSQL('r').' and';
+ }
+ }
+ if($this->SQLExtWhere != '') {
+ $sql_where .= ' '.$this->SQLExtWhere.' and';
+ }
+
+ $sql_where = $app->listform->getSearchSQL($sql_where);
+ if($app->listform->listDef['join_sql']) $sql_where .= ' AND '.$app->listform->listDef['join_sql'];
+ $app->tpl->setVar($app->listform->searchValues);
+
+ $order_by_sql = $this->SQLOrderBy;
+
+ //* Generate SQL for paging
+ $limit_sql = $app->listform->getPagingSQL($sql_where);
+ $app->tpl->setVar('paging',$app->listform->pagingHTML);
+
+ $extselect = '';
+ $join = '';
+
+ if(!empty($_SESSION['search'][$_SESSION['s']['module']['name'].$app->listform->listDef["name"].$app->listform->listDef['table']]['order'])){
+ $order = str_replace(' DESC','',$_SESSION['search'][$_SESSION['s']['module']['name'].$app->listform->listDef["name"].$app->listform->listDef['table']]['order']);
+ list($tmp_table, $order) = explode('.', $order);
+ if($order == 'web_traffic_last_month'){
+ $tmp_year = date('Y',mktime(0, 0, 0, date("m")-1, date("d"), date("Y")));
+ $tmp_month = date('m',mktime(0, 0, 0, date("m")-1, date("d"), date("Y")));
+ $extselect .= ', SUM(wt.traffic_bytes) as calctraffic';
+ $join .= ' INNER JOIN web_traffic as wt ON '.$app->listform->listDef['table'].'.domain = wt.hostname ';
+ $sql_where .= " AND YEAR(wt.traffic_date) = '$tmp_year' AND MONTH(wt.traffic_date) = '$tmp_month'";
+ $order_by_sql = str_replace($app->listform->listDef['table'].'.web_traffic_last_month','calctraffic',$order_by_sql);
+ $order_by_sql = "GROUP BY domain ".$order_by_sql;
+ } elseif($order == 'web_traffic_this_month'){
+ $tmp_year = date('Y');
+ $tmp_month = date('m');
+ $extselect .= ', SUM(wt.traffic_bytes) as calctraffic';
+ $join .= ' INNER JOIN web_traffic as wt ON '.$app->listform->listDef['table'].'.domain = wt.hostname ';
+ $sql_where .= " AND YEAR(wt.traffic_date) = '$tmp_year' AND MONTH(wt.traffic_date) = '$tmp_month'";
+ $order_by_sql = str_replace($app->listform->listDef['table'].'.web_traffic_this_month','calctraffic',$order_by_sql);
+ $order_by_sql = "GROUP BY domain ".$order_by_sql;
+ } elseif($order == 'web_traffic_last_year'){
+ $tmp_year = date('Y',mktime(0, 0, 0, date("m")-1, date("d"), date("Y")));
+ $extselect .= ', SUM(wt.traffic_bytes) as calctraffic';
+ $join .= ' INNER JOIN web_traffic as wt ON '.$app->listform->listDef['table'].'.domain = wt.hostname ';
+ $sql_where .= " AND YEAR(wt.traffic_date) = '$tmp_year'";
+ $order_by_sql = str_replace($app->listform->listDef['table'].'.web_traffic_last_year','calctraffic',$order_by_sql);
+ $order_by_sql = "GROUP BY domain ".$order_by_sql;
+ } elseif($order == 'web_traffic_this_year'){
+ $tmp_year = date('Y');
+ $extselect .= ', SUM(wt.traffic_bytes) as calctraffic';
+ $join .= ' INNER JOIN web_traffic as wt ON '.$app->listform->listDef['table'].'.domain = wt.hostname ';
+ $sql_where .= " AND YEAR(wt.traffic_date) = '$tmp_year'";
+ $order_by_sql = str_replace($app->listform->listDef['table'].'.web_traffic_this_year','calctraffic',$order_by_sql);
+ $order_by_sql = "GROUP BY domain ".$order_by_sql;
+ }
+ }
+
+ if($this->SQLExtSelect != '') {
+ if(substr($this->SQLExtSelect,0,1) != ',') $this->SQLExtSelect = ','.$this->SQLExtSelect;
+ $extselect .= $this->SQLExtSelect;
+ }
+
+ $table_selects = array();
+ $table_selects[] = trim($app->listform->listDef['table']).'.*';
+ $app->listform->listDef['additional_tables'] = trim($app->listform->listDef['additional_tables']);
+ if($app->listform->listDef['additional_tables'] != ''){
+ $additional_tables = explode(',', $app->listform->listDef['additional_tables']);
+ foreach($additional_tables as $additional_table){
+ $table_selects[] = trim($additional_table).'.*';
+ }
+ }
+ $select = implode(', ', $table_selects);
+
+ $sql = 'SELECT '.$select.$extselect.' FROM '.$app->listform->listDef['table'].($app->listform->listDef['additional_tables'] != ''? ','.$app->listform->listDef['additional_tables'] : '')."$join WHERE $sql_where $order_by_sql $limit_sql";
+ return $sql;
+ }
}
$list = new list_action;
-$list->SQLExtWhere = "(type = 'vhost' or type = 'vhostsubdomain')";
-$list->SQLOrderBy = 'ORDER BY domain';
+$list->SQLExtWhere = "(web_domain.type = 'vhost' or web_domain.type = 'vhostsubdomain')";
+$list->SQLOrderBy = 'ORDER BY web_domain.domain';
$list->onLoad();
diff --git a/interface/web/sites/web_subdomain_list.php b/interface/web/sites/web_subdomain_list.php
index f5feaf7..f14d7b3 100644
--- a/interface/web/sites/web_subdomain_list.php
+++ b/interface/web/sites/web_subdomain_list.php
@@ -47,8 +47,8 @@
$app->uses('listform_actions');
// Limit the results to alias domains
-$app->listform_actions->SQLExtWhere = "type = 'subdomain'";
-$app->listform_actions->SQLOrderBy = 'ORDER BY domain';
+$app->listform_actions->SQLExtWhere = "web_domain.type = 'subdomain'";
+$app->listform_actions->SQLOrderBy = 'ORDER BY web_domain.domain';
$app->listform_actions->onLoad();
diff --git a/interface/web/sites/web_vhost_subdomain_list.php b/interface/web/sites/web_vhost_subdomain_list.php
index 8c48339..0677a93 100644
--- a/interface/web/sites/web_vhost_subdomain_list.php
+++ b/interface/web/sites/web_vhost_subdomain_list.php
@@ -47,8 +47,8 @@
$app->uses('listform_actions');
// Limit the results to alias domains
-$app->listform_actions->SQLExtWhere = "type = 'vhostsubdomain'";
-$app->listform_actions->SQLOrderBy = 'ORDER BY domain';
+$app->listform_actions->SQLExtWhere = "web_domain.type = 'vhostsubdomain'";
+$app->listform_actions->SQLOrderBy = 'ORDER BY web_domain.domain';
$app->listform_actions->onLoad();
diff --git a/interface/web/sites/webdav_user_list.php b/interface/web/sites/webdav_user_list.php
index cc4532c..b94157c 100644
--- a/interface/web/sites/webdav_user_list.php
+++ b/interface/web/sites/webdav_user_list.php
@@ -44,7 +44,7 @@
$app->auth->check_module_permissions('sites');
$app->uses('listform_actions');
-$app->listform_actions->SQLOrderBy = 'ORDER BY username';
+$app->listform_actions->SQLOrderBy = 'ORDER BY webdav_user.username';
$app->listform_actions->onLoad();
diff --git a/interface/web/vm/openvz_ip_list.php b/interface/web/vm/openvz_ip_list.php
index 8892efc..804d60f 100644
--- a/interface/web/vm/openvz_ip_list.php
+++ b/interface/web/vm/openvz_ip_list.php
@@ -48,7 +48,7 @@
$app->uses('listform_actions');
// $app->listform_actions->SQLExtWhere = "limit_client = 0";
-$app->listform_actions->SQLOrderBy = 'ORDER BY server_id,ip_address';
+$app->listform_actions->SQLOrderBy = 'ORDER BY openvz_ip.server_id, openvz_ip.ip_address';
$app->listform_actions->onLoad();
--
Gitblit v1.9.1