Bug 166662 - www/asterisk-stat - pgsql select substring() problem
Summary: www/asterisk-stat - pgsql select substring() problem
Status: Closed FIXED
Alias: None
Product: Ports & Packages
Classification: Unclassified
Component: Individual Port(s) (show other bugs)
Version: Latest
Hardware: Any Any
: Normal Affects Only Me
Assignee: Michael Scheidell
Depends on:
Reported: 2012-04-05 09:20 UTC by vess
Modified: 2012-06-09 12:20 UTC (History)
0 users

See Also:

file.diff (606 bytes, patch)
2012-04-05 09:20 UTC, vess
no flags Details | Diff
www_askrisk-stat.patch (1.62 KB, patch)
2012-05-26 12:57 UTC, Michael Scheidell
no flags Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description vess 2012-04-05 09:20:08 UTC

While using statustics page for 'cdr report', 'RED TOTAL BAR TABLE' at the bottom not displayed. 

Here is part of  /var/log/message:

Apr  5 10:22:37 server postgres[34881]: [2-1] ERROR:  function pg_catalog.substring(timestamp with time zone, integer, integer) does not exist at character 8
Apr  5 10:22:37 server postgres[34881]: [2-2] HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
Apr  5 10:22:37 server postgres[34881]: [2-3] STATEMENT:  SELECT substring(calldate,1,10) AS day, sum(duration) AS calltime, count(*) as nbcall FROM cdr WHERE (calldate) >= ('2012-01-01') AND (calldate) <= ('2012-03-31 23:59:59') GROUP BY substring(calldate,1,10)

Fix: patch file attached

Patch attached with submission follows:

Choose any period and click search.
Comment 1 vess 2012-04-05 09:55:28 UTC
Even better - optimized query with order:

$QUERY = "SELECT cast(calldate as date) AS day, sum(duration) AS
calltime, count(*) as nbcall FROM cdr WHERE ".$FG_TABLE_CLAUSE." GROUP
BY cast(calldate as date) order by 1 desc"; //extract(DAY from calldate)
Comment 2 Mark Linimon freebsd_committer 2012-04-05 09:56:54 UTC
Responsible Changed
From-To: freebsd-www->freebsd-ports-bugs

ports PR.  The 'www' category is for problems with the FreeBSD.org 
Comment 3 Michael Scheidell freebsd_committer 2012-05-01 21:51:53 UTC
I am assuming you didn't get a copy of this, due to the original 
submitters mistaken category.

Please check this patch, and decide if you want to approve it.
I would suggest a PORTREVISION bump (at least), and if this patch is 
needed, send it upstream?

Michael Scheidell, CTO
 >*| * SECNAP Network Security Corporation
d: +1.561.948.2259
w: http://people.freebsd.org/~scheidell
Comment 4 Michael Scheidell freebsd_committer 2012-05-26 12:39:26 UTC
Responsible Changed
From-To: freebsd-ports-bugs->scheidell

I'll take it. Maintainer feedback timeout.
Comment 5 Michael Scheidell freebsd_committer 2012-05-26 12:57:04 UTC
For audit, could everyone please test the attached patch? 

(curl 'http://www.freebsd.org/cgi/query-pr.cgi?pr=ports%2F166662&getpatch=2' | patch -EuNI)

Veselin: to avoid confusion, this appears to apply your second QUERY.
jfkimura:  do you approve this patch?

(note, I did bump PORTREVISION, this will force a rebuild for mysql as well as pgsql users, but it looks like this is the 
safest thing to do)

Michael Scheidell, CTO
SECNAP Network Security Corporation

This email has been scanned and certified safe by SpammerTrap(r). 
For Information please see http://www.spammertrap.com/
Comment 6 Michael Scheidell freebsd_committer 2012-05-27 11:13:40 UTC
State Changed
From-To: open->feedback

Ask for maintainer approval.
Comment 7 Michael Scheidell freebsd_committer 2012-06-07 23:00:42 UTC
State Changed
From-To: feedback->closed

No response. Don't know what submitter or maintainer wants to do. 
Closeing pr.
Comment 8 dfilter service freebsd_committer 2012-06-09 12:11:00 UTC
scheidell    2012-06-09 11:10:50 UTC

  FreeBSD ports repository

  Modified files:
    www/asterisk-stat    Makefile 
  Added files:
    www/asterisk-stat/files pgsql__patch-call.log.php 
  - Fix pgsql select substring() problem
  - pet portlint (gd.4 to gd.[4-9] in LIB_DEPENDS)
  PR:             ports/166662
  Submitted by:   Veselin Slavov <vess@slavof.net>
  Reviewed by:    scheidell@ (me)
  Approved by:    F. Kimura <jfkimura@yahoo.co.jp> (maintainer)
  Revision  Changes    Path
  1.15      +3 -2      ports/www/asterisk-stat/Makefile
  1.1       +9 -0      ports/www/asterisk-stat/files/pgsql__patch-call.log.php (new)
cvs-all@freebsd.org mailing list
To unsubscribe, send any mail to "cvs-all-unsubscribe@freebsd.org"
Comment 9 Michael Scheidell freebsd_committer 2012-06-09 12:11:22 UTC
-------- Original Message --------
Subject: 	Re: ports/166662: www/asterisk-stat - pgsql select substring() 
Date: 	Sat, 9 Jun 2012 16:04:48 +0900
From: 	F. Kimura <jfkimura@yahoo.co.jp>
Reply-To: 	F. Kimura <jfkimura@yahoo.co.jp>
To: 	scheidell@FreeBSD.org <scheidell@FreeBSD.org>, vess@slavof.net 

I was not using e-mail for a month. Sorry. If possible, I want to accept this PR.

// Fumihiko Kimura