Zählen von Voicemails

mc.mauser · Apr 21st 2023

Hallo,

gibt es die möglichkeit abzufragen wieviel Voicemails Benutzer X gerade hat ?

Gruß
Robert

FabianZ · Apr 24th 2023

Ich nehme an, du willst spezifisch wissen wie viele neue Voicemails?

In der Datenbank ist das über mehrere Tabellen verteilt.

Wenn du es also via Datenbank machst:

SQL

SELECT id FROM public.cdrsummary WHERE cdraccountid=1000 AND hasvoicemail=true;

Liefert eine Liste von id's aller CDR-Entries mit Voicemails für den User 1000 (AccountID nicht LoginID)

Danach must du mit diesen ID's zur nächsten Tabelle

SQL

SELECT idcdrvoicemail FROM public.cdrsummarytovoicemail WHERE idcdr = 2179;

Dann erhälst du die id's der Voicemails, diese musst du dann mit

SQL

SELECT id, voicemailend, duration, file, mailbox, exten	FROM public.cdrvoicemail WHERE id=246;

Danach erhälst du die Voicemail

246 1680177315000 2 "/var/spool/asterisk/voicemail/default/132/INBOX/msg00003" "132" "210"

Und alle neuen Voicemails befinden sich aktuell in der INBOX

Es gibt sicher einen weg diese Selektionen in eine einzige Query zu kombinieren, aber da kenne ich mich zu wenig aus.

Alterantiv kannst du mit Java Code

Code

        VoiceboxHandler VOB = (VoiceboxHandler)context.provider().fetch(VoiceboxHandler.class);
        VoicemailUserList VBs = VOB.getVoicemailBoxesForAccountId(STARFACE_ACCOUNT); //Hole alle Voicemailboxen, auf die dieser Benutzer zugriff hat.
        for(Account2VoicemailuserBean Entry: VBs.getList())
        {
            log.trace(Entry.getMailbox() +" <==> " + Voicemailno + " " + Entry.getMailbox().equals(Voicemailno));
            if(Entry.getMailbox().equals(Voicemailno)) //Wenn es die korrekte *9[Voicemailno] ist
            {
            
                log.debug("Found the correct Voicemailbox!");
                Voicemailid = ""+Entry.getId();
                break;
            }
        }

        if(Voicemailid.isEmpty()) //Wenn die Voicemailbox nicht gefunden wurde
        {
            log.error("The Voicemailbox: "+ Voicemailno +" does not exist for User: " + STARFACE_ACCOUNT);
            return;
        }
        
        
        VoicemailListBusinessObject VBO = (VoicemailListBusinessObject)context.provider().fetch(VoicemailListBusinessObject.class);
        Date D = new Date();
        
        //Eine Liste aller Voicemailboxen der letzten 365 Tage vom "Neu" anfordern
        VoicemailList VMList = VBO.getVoicemailList(STARFACE_ACCOUNT, D , DateUtils.addDays(D, -365), "",  VoicemailListEntryFolder.INBOX , VoicemailListEntryProperties.callDescription, OrderDirection.ASCENDING, 0, Integer.MAX_VALUE);
            log.debug("Searching trough Voicemails. Total of:" + VMList.getEntries().size()); //Totale Anzahl voicemailboxen

Display More

MfG

Fabian

mc.mauser · Apr 26th 2023

Danke, damit werde ich mal experimentieren

FabianZ · Apr 27th 2023

Ich habe mich ein bischen mit LEFT-Join beschäftigt.

Folgende Query liefert alle Voicemails für die AccountID 1000.

SQL

SELECT cdr.*, cdr2vm.*, cdrvm.* FROM public.cdrsummary cdr
LEFT join cdrsummarytovoicemail  cdr2vm ON cdr.id=cdr2vm.idcdr
LEFT join cdrvoicemail cdrvm ON cdr2vm.idcdrvoicemail = cdrvm.id
WHERE cdr.cdraccountid=1000 AND hasvoicemail=true

Beispieloutput:

Code

"id","callid","callleguuid","cdraccountid","calleraccountid","callercallerid","callernumber","callername","calledaccountid","calledcallerid","callednumber","calledname","serviceid","starttime","ringingtime","linktime","callresulttime","callresult","callresultcausedby","lineid","linename","dialednumber","callbacknumber","answeredelsewhere","incoming","answered","hasvoicemail","hasmonitor","hasfax","deleted","privatecall","callbacknumberextern","duration","comment","commentauthor","commentmodified","calledback","calledbackauthor","calledbackmodified","answeredbyusername","idcdr","idcdrvoicemail","id-2","voicemailend","duration-2","file","mailbox","exten"
2179,1944,"voicemailbox clone 1000",1000,1029,"200 200 200","200","200 200",1254,"BC  210","210","BC ",23,1680177307163,NULL,1680177308488,1680177315558,"CONNECTED",NULL,NULL,"","210","200","",True,True,True,False,False,False,False,False,7,NULL,NULL,NULL,False,NULL,NULL,"",2179,246,246,1680177315000,2,"/var/spool/asterisk/voicemail/default/132/INBOX/msg00003","132","210"
2192,1945,"voicemailbox clone 1000",1000,1029,"200 200 200","200","200 200",1254,"BC  210","210","BC ",23,1680177539711,NULL,1680177541009,1680177547361,"CONNECTED",NULL,NULL,"","210","200","",True,True,True,False,False,False,False,False,6,NULL,NULL,NULL,False,NULL,NULL,"",2192,247,247,1680177547000,1,"/var/spool/asterisk/voicemail/default/132/INBOX/msg00004","132","210"

MfG

Fabian

Zählen von Voicemails

Participate now!

Users Viewing This Thread