app_mysql está marcado para ser removido e não deve ser usado em novos planos de discagem
Use a função REALTIME e func_odbc.
PS Não há como encontrar erro sem ver o seu plano de discagem. Muito provavelmente é o nome da variável
Estou usando o asterisco 11.9.0 e criei um dialplan. Meu problema é que a cada hora executo uma operação sql funcionando bem mas a linha mesmo = > n, mysql (limpar $ {resultid}) está dando um erro Identificador 2, identificador_ tipo 2 não encontrado na lista de identificadores Eu verifiquei minhas tabelas no banco de dados também verifiquei a minha sintaxe no dialplan, mas não há tal erro eu posso encontrar. Meu plano de discagem
[from-pstn]
exten => _X.,1,NoOp(<<===Call Starts Here===>)
exten => _X.,n,NoOp(<<===IVR DEVELOPED BY DEETYA SOFT===>>)
;Set mysql user password
same => n,Set(__mysql_user=abc)
same => n,Set(__mysql_pwd=****)
same => n,Set(__mysql_db=ivr)
;Check is direct or dtmf
same => n,mysql(Connect connid localhost ${mysql_user} ${mysql_pwd} ${mysql_db})
same => n,mysql(Query resultid ${connid} select account_id,routing,tone_type from account_did where 'did'='${CALLERID(dnid)}' and 'active'='1')
same => n,MYSQL(Fetch fetchid ${resultid} account_id routing tone_type)
same => n,mysql(clear ${resultid})
same => n,set(__ACCOUNT_ID=${account_id})
same => n,set(__ROUTING=${routing})
same => n,set(__TONETYPE=${tone_type})
same => n,GotoIf($["${ACCOUNT_ID}" = ""]?callend:start)
same => n(callend),Hangup(<<===========callend==========>>)
same => n(start),NoOp(<<============ACCOUNT ID = ${ACCOUNT_ID}=============>>)
same => n,NoOp(<<============ROUTING = ${routing}=============>>)
same => n,set(__FROM=${CDR(clid)})
same => n,set(__DID=${CDR(dst)})
same => n,set(__START=${CDR(start)})
same => n,set(__UNIQUE_ID=${CDR(uniqueid)})
same => n,GotoIf($["${routing}" = "direct"]?direct_routing:dtmf_routing)
same => n(direct_routing),Goto(direct_routing,${CALLERID(dnid)},1)
same => n(dtmf_routing),Goto(dtmf_routing,s,1)
same => n,Hangup(<<===========No route hangup==========>>)
[direct_routing]
exten => _X.,1,NoOp(<<=============Start direct routing===============>>)
same => n,MYSQL(Query resultid ${connid} INSERT INTO 'call_info' ('account_id','from','did','start','uniqueid') VALUES (${ACCOUNT_ID},'${FROM}','${DID}','${START}','${UNIQUE_ID}'))
same => n,MYSQL(Query resultid ${connid} INSERT INTO 'cust_sms' ('account_id','from','unique_id','call_status','DID','ANS_TIME') VALUES (${ACCOUNT_ID},'${FROM}','${UNIQUEID}',0,'${DID}','${START}'))
;same => n,MYSQL(Query resultid ${connid} INSERT INTO 'cust_sms'('account_id','from','unique_id','STATUS','DID','ANS_TIME') VALUES (${ACCOUNT_ID},'${FROM}','${UNIQUEID}',0,'${DID}','${START}'));;;;;;D;;;;;;
same => n,mysql(clear ${resultid})
same => n,Answer()
same => n,Set(__TONEFILE=${IF($[ ${TONETYPE} = file]?/var/www/html/tone/tone_${DID}:/var/www/html/new_welcome)})
same => n,Playback(${TONEFILE});
same => n,GOTO(dr_ivr,start,1)
same => n,Hangup(<<===========Direct routing hangup==========>>)
same => n,MYSQL(Connect connid deetya abc **** ivr)
same => n,mysql(Query resultid ${connid} UPDATE call_info SET 'end'="${END}" where 'uniqueid'='${UNIQUE_ID}')
same => n,mysql(clear ${resultid})
;endcall
exten => h,1,Goto(dr_ivr_endcall,h,1)
[dr_ivr]
exten => start,1,noop(######START######)
same => n,mysql(Query resultid ${connid} SELECT count(*),number,call_info.user_id FROM 'call_info' JOIN users ON users.id=call_info.user_id JOIN direct_routing_flow ON call_info.user_id = direct_routing_flow.user_id WHERE call_info.'account_id' =${ACCOUNT_ID} AND 'from' = ${FROM} AND users.active=1 order by call_info.id desc limit 1)
;same => n,mysql(Query resultid ${connid} SELECT count(*),number,call_info.user_id FROM 'call_info' JOIN users ON users.id=call_info.user_id JOIN direct_routing_flow ON call_info.user_id = direct_routing_flow.user_id WHERE call_info.'account_id' =${ACCOUNT_ID} AND 'from' = ${FROM} AND users.active=1 order by call_info.id desc limit 1);;;DDDDDD
same => n,MYSQL(Fetch fetchid ${resultid} count number user_id)
same => n,mysql(clear ${resultid})
same => n,GotoIf($[${count} < 1]?startroundrobin)
same => n,set(__NUMBER=${number})
same => n,set(__USER_ID=${user_id})
same => n,mysql(Query resultid ${connid} UPDATE call_info SET user_id='${USER_ID}','to'='${NUMBER}' where 'uniqueid'='${UNIQUE_ID}')
same => n,MYSQL(Query resultid ${connid} UPDATE cust_sms SET 'to' = '${NUMBER}' WHERE 'UNIQUE_ID'='${UNIQUE_ID}');;;;;D;;;;
same => n,NooP(##########################---------Current Number is --[${NUMBER}]-----${UNIQUE_ID}------------------###############)
same => n,Set(CALLERID(all)=120${DID})
same => n,dial(DAHDI/g0/0${NUMBER},20,mM(ANSWEREDED))
;Check Round Robin executive number
same => n(startroundrobin),set(counter=1)
same => n,mysql(Query resultid ${connid} select count(*) from direct_routing_flow as drf join users ON users.id = drf.user_id where drf.did='${DID}' and users.active = 1 and drf.account_id =${ACCOUNT_ID})
same => n,MYSQL(Fetch fetchid ${resultid} count)
same => n,mysql(clear ${resultid})
same => n,set(__COUNT=${count})
same => n,mysql(Query resultid ${connid} select last from direct_routing_flow as drf join users ON users.id = drf.user_id where drf.did='${DID}' and users.active = 1 order by last desc)
same => n,MYSQL(Fetch fetchid ${resultid} last)
same => n,mysql(clear ${resultid})
same => n,set(__LAST_RANK=${last})
same => n,Set(i=${IF($[ ${LAST_RANK} >= ${COUNT}]?1:$[${LAST_RANK} + 1])})
same => n,While($[${i} <= ${COUNT}])
same => n,mysql(Query resultid ${connid} SELECT number,user_id FROM (SELECT direct_routing_flow.*,@i := @i + 1 as rank FROM 'direct_routing_flow' JOIN users ON users.id = direct_routing_flow.user_id JOIN (select @i := 0 ) temp WHERE direct_routing_flow.did='${DID}' and users.active='1' and direct_routing_flow.'account_id' =${ACCOUNT_ID} ORDER BY priority asc) temp1 where rank=${i});;;DDDDDD
;same => n,mysql(Query resultid ${connid} SELECT number,user_id FROM (SELECT direct_routing_flow.*,@i := @i + 1 as rank FROM 'direct_routing_flow' JOIN users ON users.id = direct_routing_flow.user_id JOIN (select @i := 0 ) temp WHERE direct_routing_flow.did='${DID}' and users.active='1' and direct_routing_flow.'account_id' =${ACCOUNT_ID} ORDER BY priority asc) temp1 where rank=${i});;;DDDDDD
same => n,MYSQL(Fetch fetchid ${resultid} number user_id)
same => n,mysql(clear ${resultid})
same => n,set(__NUMBER=${number})
same => n,set(__USER_ID=${user_id})
;Update last dial
same => n,mysql(Query resultid ${connid} UPDATE direct_routing_flow SET last='1' where 'did'='${DID}')
same => n,mysql(Query resultid ${connid} UPDATE direct_routing_flow SET last='${i}' where 'did'='${DID}' and 'user_id'='${USER_ID}')
same => n,mysql(Query resultid ${connid} UPDATE call_info SET user_id='${USER_ID}','to'='${NUMBER}' where 'uniqueid'='${UNIQUE_ID}')
same => n,MYSQL(Query resultid ${connid} UPDATE cust_sms SET 'to' = '${NUMBER}' WHERE 'UNIQUE_ID'='${UNIQUE_ID}');;;;;D;;;;
same => n,NooP(##########################---------Current Number is --[${NUMBER}]-----${UNIQUE_ID}------------------###############)
same => n,Set(CALLERID(all)=120${DID})
same => n,dial(DAHDI/g0/0${NUMBER},20,mM(ANSWEREDED))
same => n,NooP(<<=================DISPOSIOTION -- ${CDR(disposition)} ===================>)
same => n,Set(i=$[${i} + 1])
same => n,EndWhile()
;endcall
exten => h,1,Goto(dr_ivr_endcall,h,1)
[macro-ANSWEREDED]
exten => s,1,noop(CALL_ANSWERED ==============${UNIQUE_ID}==================)
exten => s,n,set(__DISPOSITION=${CDR(disposition)})
exten => s,n,set(__ANSWER=${CDR(answer)})
exten => s,n,set(__DISPOSITION=${CDR(disposition)})
exten => s,n,mysql(Connect connid localhost ${mysql_user} ${mysql_pwd} ${mysql_db})
exten => s,n,mysql(Query resultid ${connid} UPDATE call_info SET 'user_id'='${USER_ID}','to'='${NUMBER}', 'answer'='${ANSWER}','disposition'='${DISPOSITION}' where 'uniqueid'='${UNIQUE_ID}')
exten => s,n,MYSQL(Query resultid ${connid} UPDATE cust_sms SET 'to' = '${NUMBER}', 'ANS_TIME'='${ANSWER}','call_status'='${DISPOSITION}' WHERE 'UNIQUE_ID'='${UNIQUE_ID}');;;;;D;;;;
exten => s,n,mysql(clear ${resultid})
exten => s,n,Mixmonitor(/var/www/html/recordings/answered/${DID}_${FROM}_${ANSWER}.wav)
[dr_ivr_endcall]
exten => h,1,noop(###### END CALL ######)
exten => h,n,set(__END=${CDR(end)})
exten => h,n,set(__DURATION=${CDR(duration)})
exten => h,n,set(__BILLSEC=${CDR(billsec)})
;exten => h,n,set(__DISPOSITION=${CDR(disposition)})
exten => h,n,Set(ETIME=${STRFTIME(${EPOCH},,%Y-%m-%d %H:%M:%S)})
exten => h,n,MYSQL(Query resultid ${connid} UPDATE call_info SET 'end'='${ETIME}','billsec'='${BILLSEC}','duration'='${DURATION}' where 'uniqueid'='${UNIQUE_ID}')
exten => h,n,MYSQL(Query resultid ${connid} UPDATE cust_sms SET 'STATUS'='1' WHERE 'UNIQUE_ID'='${UNIQUE_ID}');;;;;D;;;;
exten => h,n,AGI(sms.php)
exten => h,n,hangup()
A saída do meu asterisco cli é
-- Executing [4862800@direct_routing:3] MYSQL("DAHDI/i1/9717055088-1", "Query resultid 1 INSERT INTO 'cust_sms'('account_id','from','unique_id','STATUS','DID','ANS_TIME') VALUES (1,'9717055088','1401994718.0',0,'4862800','2014-06-06 00:28:38')") in new stack
-- Executing [4862800@direct_routing:4] MYSQL("DAHDI/i1/9717055088-1", "clear 2") in new stack
[Jun 6 00:28:38] WARNING[7088][C-00000000]: app_mysql.c:194 find_identifier: Identifier 2, identifier_type 2 not found in identifier list
[Jun 6 00:28:38] WARNING[7088][C-00000000]: app_mysql.c:520 aMYSQL_clear: Invalid result identifier 2 passed in aMYSQL_clear
-- Executing [4862800@direct_routing:5] Answer("DAHDI/i1/9717055088-1", "") in new stack
-- Executing [4862800@direct_routing:6] Set("DAHDI/i1/9717055088-1", "__TONEFILE=/var/www/html/new_welcome") in new stack
-- Executing [4862800@direct_routing:7] Playback("DAHDI/i1/9717055088-1", "/var/www/html/new_welcome") in new stack
-- <DAHDI/i1/9717055088-1> Playing '/var/www/html/new_welcome.slin' (language 'yes')
-- Executing [4862800@direct_routing:8] Goto("DAHDI/i1/9717055088-1", "dr_ivr,start,1") in new stack
-- Goto (dr_ivr,start,1)
-- Executing [start@dr_ivr:1] NoOp("DAHDI/i1/9717055088-1", "######START######") in new stack
-- Executing [start@dr_ivr:2] MYSQL("DAHDI/i1/9717055088-1", "Query resultid 1 SELECT count(*") in new stack
[Jun 6 00:28:42] WARNING[7088][C-00000000]: app_mysql.c:405 aMYSQL_query: aMYSQL_query: mysql_query failed. Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
-- Executing [start@dr_ivr:3] MYSQL("DAHDI/i1/9717055088-1", "Fetch fetchid 2 count number user_id") in new stack
[Jun 6 00:28:42] WARNING[7088][C-00000000]: app_mysql.c:194 find_identifier: Identifier 2, identifier_type 2 not found in identifier list
[Jun 6 00:28:42] WARNING[7088][C-00000000]: app_mysql.c:503 aMYSQL_fetch: aMYSQL_fetch: Invalid result identifier 2 passed
-- Executing [start@dr_ivr:4] MYSQL("DAHDI/i1/9717055088-1", "clear 2") in new stack
[Jun 6 00:28:42] WARNING[7088][C-00000000]: app_mysql.c:194 find_identifier: Identifier 2, identifier_type 2 not found in identifier list
[Jun 6 00:28:42] WARNING[7088][C-00000000]: app_mysql.c:520 aMYSQL_clear: Invalid result identifier 2 passed in aMYSQL_clear
[Jun 6 00:28:42] WARNING[7088][C-00000000]: ast_expr2.fl:470 ast_yyerror: ast_yyerror(): syntax error: syntax error, unexpected '<', expecting $end; Input:
< 1
^
[Jun 6 00:28:42] WARNING[7088][C-00000000]: ast_expr2.fl:474 ast_yyerror: If you have questions, please refer to https://wiki.asterisk.org/wiki/display/AST/Channel+Variables
Não consigo encontrar o erro
Qualquer ajuda seria apreciada.