HI,
I have been down this road so many times now
To the point I actually wrote a routine that extracts the data from Postgres
into a MS SQL Server, where its much more accessible.
easier to write reports and can be posted into other applications via XML.
As you know the reports for 3CX are dreadful and to be honest
they always have been, even in v14 you have to extract to a csv that has page breaks !
That's just lazy coding, there is no logical reason to do this.
The only reason you export to CSV is to import to something else.
As far as I know, I may be wrong, there is no intention of using MS SQL Server.
Any way solution time;
version 9, 10 &11 you hook into Postgres via a SQL ODBC driver and pull
the data out and push into MS SQL, It also gives you the chance to clean it up as well.
Options for v14 onwards
the design of the now table(s), yes there are now 3 of them
all begin with cl_
and you need all of them linked together to make any sense of it.
have changed, I have worked out how they all link, however
there seems to be an issue with 64bit and 32bit ODBC SQL drivers
and converting data from one to the other, still working on this.
It centres around that Postgres puts a Time Zone stamp in a date time storage
which will not import directly, you have to 'massage' the date data to make it work.
The other option for v14 is to use the CDR file, this actually can be quite
useful, but yet again there seems to be some lazy coding, after every line
there seems to be a double line break, making importing almost impossible
also the id of the call has had 'call ' stuck in front of it for some reason,
which makes it difficult to import into an 'INT'
However there are ways round both of these issues (Quite easy to be honest)
Unfortunately the data includes every call between queues and queues
to every phone, you only actually need about 20% of the data that's exported.
You then run a routine that strips out this 80% and you are left
with the call data in MS SQL,
I have added a correct call billing system as well, working out costing's
for 0844, 0845, 0330, 01, 02 07 mobile etc based on the route it takes out
this gives a very accurate cost analysis, its just an inner join to the
call table in MS SQL so its immediate and displays it on a secure web page.
The reason they use Postgres is because its free,
MS SQL would create licensing complications,
Even if you use SQL Express, you would need
to be competent writing DTS Jobs that the Agent would normally execute.
You can, as I have, run this on a Windows schedule, which works very well.
On another note I also converted this via Lame to MP3 to aid storage
and back up transfer, which helped a lot, I stored by day rather than by extension per month
This means its easy to get to a call if you know the day it was taken.
If you need help with SQL or the conversion let me know
Hope this helps
