Menu
▾
▴
[Astlinux-commits] SF.net SVN: astlinux:[5988] branches/1.0/package/webinterface/altweb
|
From: <abe...@us...> - 2013-03-12 18:08:46
|
Revision: 5988
http://astlinux.svn.sourceforge.net/astlinux/?rev=5988&view=rev
Author: abelbeck
Date: 2013-03-12 18:08:36 +0000 (Tue, 12 Mar 2013)
Log Message:
-----------
web interface, various tweaks...
1) add default view for cdr-sqlite3 if /mnt/kd/cdr-sqlite3/master.db exists
2) fix view bug in phpliteadmin: https://code.google.com/p/phpliteadmin/issues/detail?id=191
3) hopefully final tweak to the sqldata.sql standard template
4) document changes in SQL-Data Tab topic info
Modified Paths:
--------------
branches/1.0/package/webinterface/altweb/admin/phpliteadmin.php
branches/1.0/package/webinterface/altweb/admin/prefs.php
branches/1.0/package/webinterface/altweb/admin/sqldata.php
branches/1.0/package/webinterface/altweb/common/sqldata.sql
branches/1.0/package/webinterface/altweb/common/topics.info
Modified: branches/1.0/package/webinterface/altweb/admin/phpliteadmin.php
===================================================================
--- branches/1.0/package/webinterface/altweb/admin/phpliteadmin.php 2013-03-11 22:01:15 UTC (rev 5987)
+++ branches/1.0/package/webinterface/altweb/admin/phpliteadmin.php 2013-03-12 18:08:36 UTC (rev 5988)
@@ -92,7 +92,7 @@
}
function mydate($value)
{
- return date("g:ia n/j/y", intval($value));
+ return date("H:i n/j/y", intval($value));
}
function myreplace($value)
{
@@ -557,7 +557,8 @@
//get the last modified time of database
public function getDate()
{
- return date("g:ia \o\\n F j, Y", filemtime($this->data["path"]));
+ //AstLinux//
+ return date("M d H:i:s T Y", filemtime($this->data["path"]));
}
//get number of affected rows from last query
@@ -2988,6 +2989,8 @@
//row actions
/////////////////////////////////////////////// view row
case "row_view":
+ $is_view = isset($_GET['view']) ? '&view=1' : '';
+
if(!isset($_POST['startRow']))
$_POST['startRow'] = 0;
@@ -3020,14 +3023,14 @@
if($_POST['startRow']>0)
{
echo "<div style='float:left; overflow:hidden;'>";
- echo "<form action='".PAGE."?action=row_view&table=".urlencode($_GET['table'])."' method='post'>";
+ echo "<form action='".PAGE."?action=row_view&table=".urlencode($_GET['table']).$is_view."' method='post'>";
echo "<input type='hidden' name='startRow' value='0'/>";
echo "<input type='hidden' name='numRows' value='".$_SESSION[COOKIENAME.'numRows']."'/> ";
echo "<input type='submit' value='←←' name='previous' class='btn'/> ";
echo "</form>";
echo "</div>";
echo "<div style='float:left; overflow:hidden; margin-right:20px;'>";
- echo "<form action='".PAGE."?action=row_view&table=".urlencode($_GET['table'])."' method='post'>";
+ echo "<form action='".PAGE."?action=row_view&table=".urlencode($_GET['table']).$is_view."' method='post'>";
echo "<input type='hidden' name='startRow' value='".intval($_POST['startRow']-$_SESSION[COOKIENAME.'numRows'])."'/>";
echo "<input type='hidden' name='numRows' value='".$_SESSION[COOKIENAME.'numRows']."'/> ";
echo "<input type='submit' value='←' name='previous_full' class='btn'/> ";
@@ -3037,7 +3040,7 @@
//show certain number buttons
echo "<div style='float:left; overflow:hidden;'>";
- echo "<form action='".PAGE."?action=row_view&table=".urlencode($_GET['table'])."' method='post'>";
+ echo "<form action='".PAGE."?action=row_view&table=".urlencode($_GET['table']).$is_view."' method='post'>";
echo "<input type='submit' value='Show : ' name='show' class='btn'/> ";
echo "<input type='text' name='numRows' style='width:50px;' value='".$_SESSION[COOKIENAME.'numRows']."'/> ";
echo "row(s) starting from record # ";
@@ -3065,14 +3068,14 @@
if(intval($_POST['startRow']+$_SESSION[COOKIENAME.'numRows'])<$rowCount)
{
echo "<div style='float:left; overflow:hidden; margin-left:20px; '>";
- echo "<form action='".PAGE."?action=row_view&table=".urlencode($_GET['table'])."' method='post'>";
+ echo "<form action='".PAGE."?action=row_view&table=".urlencode($_GET['table']).$is_view."' method='post'>";
echo "<input type='hidden' name='startRow' value='".intval($_POST['startRow']+$_SESSION[COOKIENAME.'numRows'])."'/>";
echo "<input type='hidden' name='numRows' value='".$_SESSION[COOKIENAME.'numRows']."'/> ";
echo "<input type='submit' value='→' name='next' class='btn'/> ";
echo "</form>";
echo "</div>";
echo "<div style='float:left; overflow:hidden;'>";
- echo "<form action='".PAGE."?action=row_view&table=".urlencode($_GET['table'])."' method='post'>";
+ echo "<form action='".PAGE."?action=row_view&table=".urlencode($_GET['table']).$is_view."' method='post'>";
echo "<input type='hidden' name='startRow' value='".intval($rowCount-$remainder)."'/>";
echo "<input type='hidden' name='numRows' value='".$_SESSION[COOKIENAME.'numRows']."'/> ";
echo "<input type='submit' value='→→' name='next_full' class='btn'/> ";
@@ -3126,7 +3129,7 @@
if(isset($_GET['view']))
{
- echo "'".htmlencode($_GET['table'])."' is a view, which means it is a SELECT statement treated as a read-only table. You may not edit or insert records. <a href='http://en.wikipedia.org/wiki/View_(database)' target='_blank'>http://en.wikipedia.org/wiki/View_(database)</a>";
+ echo "'".htmlencode($_GET['table'])."' is a view, which means it is a SELECT statement treated as a read-only table. You may not edit or insert records.";
echo "<br/><br/>";
}
@@ -3136,7 +3139,7 @@
if(!isset($_SESSION[COOKIENAME.'viewtype']) || $_SESSION[COOKIENAME.'viewtype']=="table")
{
- echo "<form action='".PAGE."?action=row_editordelete&table=".urlencode($table)."' method='post' name='checkForm'>";
+ echo "<form action='".PAGE."?action=row_editordelete&table=".urlencode($table).$is_view."' method='post' name='checkForm'>";
echo "<table border='0' cellpadding='2' cellspacing='1' class='viewTable'>";
echo "<tr>";
if(!isset($_GET['view']))
@@ -3298,7 +3301,7 @@
<div id="chart_div" style="float:left;">If you can read this, it means the chart could not be generated. The data you are trying to view may not be appropriate for a chart.</div>
<?php
echo "<fieldset style='float:right; text-align:center;' id='chartsettingsbox'><legend><b>Chart Settings</b></legend>";
- echo "<form action='".PAGE."?action=row_view&table=".urlencode($_GET['table'])."' method='post'>";
+ echo "<form action='".PAGE."?action=row_view&table=".urlencode($_GET['table']).$is_view."' method='post'>";
echo "Chart Type: <select name='charttype'>";
echo "<option value='bar'";
if($_SESSION[COOKIENAME.'charttype']=="bar")
Modified: branches/1.0/package/webinterface/altweb/admin/prefs.php
===================================================================
--- branches/1.0/package/webinterface/altweb/admin/prefs.php 2013-03-11 22:01:15 UTC (rev 5987)
+++ branches/1.0/package/webinterface/altweb/admin/prefs.php 2013-03-12 18:08:36 UTC (rev 5988)
@@ -273,6 +273,10 @@
$value = 'sqldata_create_schema = no';
fwrite($fp, $value."\n");
}
+ if (! isset($_POST['sqldata_create_cdr_view'])) {
+ $value = 'sqldata_create_cdr_view = no';
+ fwrite($fp, $value."\n");
+ }
if (isset($_POST['users_hide_pass'])) {
$value = 'users_voicemail_hide_pass = yes';
@@ -852,8 +856,12 @@
putHtml('</td></tr>');
putHtml('<tr class="dtrow1"><td style="text-align: right;">');
$sel = (getPREFdef($global_prefs, 'sqldata_create_schema') !== 'no') ? ' checked="checked"' : '';
- putHtml('<input type="checkbox" value="sqldata_create_schema" name="sqldata_create_schema"'.$sel.' /></td><td colspan="5">Create default SIP & Phone SQL Schema if not defined</td></tr>');
+ putHtml('<input type="checkbox" value="sqldata_create_schema" name="sqldata_create_schema"'.$sel.' /></td><td colspan="5">Create SIP & Phone standard SQL schema</td></tr>');
+ putHtml('<tr class="dtrow1"><td style="text-align: right;">');
+ $sel = (getPREFdef($global_prefs, 'sqldata_create_cdr_view') !== 'no') ? ' checked="checked"' : '';
+ putHtml('<input type="checkbox" value="sqldata_create_cdr_view" name="sqldata_create_cdr_view"'.$sel.' /></td><td colspan="5">Create CDR SQLite3 standard view if database exists</td></tr>');
+
putHtml('<tr class="dtrow0"><td colspan="6"> </td></tr>');
putHtml('<tr class="dtrow0"><td class="dialogText" style="text-align: left;" colspan="6">');
Modified: branches/1.0/package/webinterface/altweb/admin/sqldata.php
===================================================================
--- branches/1.0/package/webinterface/altweb/admin/sqldata.php 2013-03-11 22:01:15 UTC (rev 5987)
+++ branches/1.0/package/webinterface/altweb/admin/sqldata.php 2013-03-12 18:08:36 UTC (rev 5988)
@@ -51,6 +51,23 @@
}
}
$pdo_db = NULL;
+
+ if (is_file('/mnt/kd/cdr-sqlite3/master.db') && getPREFdef($global_prefs, 'sqldata_create_cdr_view') !== 'no') {
+ $pdo_db = new PDO("sqlite:/mnt/kd/cdr-sqlite3/master.db");
+ $pdo_db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
+
+ // List all views
+ $view_arr = array();
+ $sql = "SELECT name FROM sqlite_master WHERE type='view'";
+ foreach ($pdo_db->query($sql) as $row) {
+ $view_arr[] = $row['name'];
+ }
+ if (! in_array('standard', $view_arr)) {
+ $sql = "CREATE VIEW IF NOT EXISTS 'standard' AS SELECT calldate, clid, dstchannel, dcontext, billsec, userfield FROM cdr ORDER BY calldate DESC";
+ $pdo_db->exec($sql);
+ }
+ $pdo_db = NULL;
+ }
} catch (PDOException $e) {
return($e->getMessage());
}
Modified: branches/1.0/package/webinterface/altweb/common/sqldata.sql
===================================================================
--- branches/1.0/package/webinterface/altweb/common/sqldata.sql 2013-03-11 22:01:15 UTC (rev 5987)
+++ branches/1.0/package/webinterface/altweb/common/sqldata.sql 2013-03-12 18:08:36 UTC (rev 5988)
@@ -1,6 +1,6 @@
CREATE TABLE IF NOT EXISTS 'sip_users' (
- 'userid' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+ 'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
'sipuser' TEXT NOT NULL,
'lastname' TEXT,
'firstname' TEXT,
@@ -16,42 +16,44 @@
);
CREATE TABLE IF NOT EXISTS 'out_context' (
- 'out_contextid' INTEGER PRIMARY KEY NOT NULL,
- 'outgoing_context' TEXT NOT NULL,
- 'out_cx_description' TEXT
+ 'id' INTEGER PRIMARY KEY NOT NULL,
+ 'context' TEXT NOT NULL,
+ 'description' TEXT
);
CREATE TABLE IF NOT EXISTS 'ip_phones' (
- 'phone_id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
- 'phone_type' TEXT,
- 'phone_fw' TEXT,
- 'phone_ip' TEXT,
- 'phone_mac' TEXT,
- 'userid' INTEGER
+ 'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+ 'type' TEXT,
+ 'firmware' TEXT,
+ 'hostname' TEXT,
+ 'ipv4' TEXT,
+ 'ipv6' TEXT,
+ 'mac' TEXT,
+ 'sipuser_id' INTEGER
);
-INSERT OR IGNORE INTO "out_context" ("out_contextid","outgoing_context","out_cx_description")
+INSERT OR IGNORE INTO "out_context" ("id","context","description")
VALUES ('0','emergency','Emergency calls only')
;
-INSERT OR IGNORE INTO "out_context" ("out_contextid","outgoing_context","out_cx_description")
+INSERT OR IGNORE INTO "out_context" ("id","context","description")
VALUES ('1','local_outgoing','Local calls only')
;
-INSERT OR IGNORE INTO "out_context" ("out_contextid","outgoing_context","out_cx_description")
+INSERT OR IGNORE INTO "out_context" ("id","context","description")
VALUES ('2','local_outgoing','Unused1')
;
-INSERT OR IGNORE INTO "out_context" ("out_contextid","outgoing_context","out_cx_description")
+INSERT OR IGNORE INTO "out_context" ("id","context","description")
VALUES ('3','national_outgoing','National calls')
;
-INSERT OR IGNORE INTO "out_context" ("out_contextid","outgoing_context","out_cx_description")
+INSERT OR IGNORE INTO "out_context" ("id","context","description")
VALUES ('4','national_outgoing','Unused2')
;
-INSERT OR IGNORE INTO "out_context" ("out_contextid","outgoing_context","out_cx_description")
+INSERT OR IGNORE INTO "out_context" ("id","context","description")
VALUES ('5','national_outgoing','Unused3')
;
-INSERT OR IGNORE INTO "out_context" ("out_contextid","outgoing_context","out_cx_description")
+INSERT OR IGNORE INTO "out_context" ("id","context","description")
VALUES ('6','national_outgoing','Unused4')
;
-INSERT OR IGNORE INTO "out_context" ("out_contextid","outgoing_context","out_cx_description")
+INSERT OR IGNORE INTO "out_context" ("id","context","description")
VALUES ('7','international_outgoing','International calls')
;
Modified: branches/1.0/package/webinterface/altweb/common/topics.info
===================================================================
--- branches/1.0/package/webinterface/altweb/common/topics.info 2013-03-11 22:01:15 UTC (rev 5987)
+++ branches/1.0/package/webinterface/altweb/common/topics.info 2013-03-12 18:08:36 UTC (rev 5988)
@@ -291,11 +291,15 @@
The SQL-Data Tab adds a web dialog to edit the SQLite3 database (added in AstLinux 1.1.1).
The database exists in "/mnt/kd/asterisk-odbc.sqlite3".
-The Prefs tab contains a relevant option under "SQL-Data Tab Options:".
+The Prefs tab contains relevant options under "SQL-Data Tab Options:".
-The "Create default SIP & Phone SQL Schema if not defined" (checked by default),
-automatically creates the default table structure for the SQLite3 database.
+The "Create SIP & Phone standard SQL schema" (checked by default),
+automatically creates the standard table structure for the SQLite3 database.
+The "Create CDR SQLite3 standard view if database exists" (checked by default),
+automatically creates the standard view for the CDR SQLite3 database located
+at "/mnt/kd/cdr-sqlite3/master.db" if it exists.
+
You can access the database via Asterisk with user defined "func_odbc" functions, where
you can define customized database queries and more. A quite universal example for an entry
in the "func_odbc.conf" would be:
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
