#include "func_odbc.d/*.conf"
[general]
single_db_connection=no
;
; func_odbc.conf
;
; Each context is a separately defined function.  By convention, all
; functions are entirely uppercase, so the defined contexts should also
; be all-uppercase, but there is nothing that enforces this.  All functions
; are case-sensitive, however.
;
; For substitution, you have ${ARG1}, ${ARG2} ... ${ARGn}
; for the arguments to each SQL statement.
;
; In addition, for write statements, you have ${VAL1}, ${VAL2} ... ${VALn}
; parsed, just like arguments, for the values.  In addition, if you want the
; whole value, never mind the parsing, you can get that with ${VALUE}.
;
;
; If you have data which may potentially contain single ticks, you may wish
; to use the dialplan function SQL_ESC() to escape the data prior to its
; inclusion in the SQL statement.
;
;
; The following options are available in this configuration file:
;
; readhandle   A comma-separated list of DSNs (from res_odbc.conf) to use when
;              executing the readsql statement.  Each DSN is tried, in
;              succession, until the statement succeeds.  You may specify up to
;              5 DSNs per function class.  If not specified, it will default to
;              the value of writehandle or dsn, if specified.
; writehandle  A comma-separated list of DSNs (from res_odbc.conf) to use when
;              executing the writesql statement.  The same rules apply as to
;              readhandle.  "dsn" is a synonym for "writehandle".
; readsql      The statement to execute when reading from the function class.
; writesql     The statement to execute when writing to the function class.
; insertsql    The statement to execute when writing to the function class
;              succeeds, but initially indicates that 0 rows were affected.
; prefix       Normally, all function classes are prefixed with "ODBC" to keep
;              them uniquely named.  You may choose to change this prefix, which
;              may be useful to segregate a collection of certain function
;              classes from others.
; escapecommas This option may be used to turn off the default behavior of
;              escaping commas which occur within a field.  If commas are
;              escaped (the default behavior), then fields containing commas
;              will be treated as a single value when assigning to ARRAY() or
;              HASH().  If commas are not escaped, then values will be separated
;              at the comma within fields.  Please note that turning this option
;              off is incompatible with the functionality of HASH().
; synopsis     Appears in the synopsis field for the command
;              'core show function <function name>'
; mode         This option may be set to 'multirow' to allow the function
;              specified to return more than a single row.  However, this
;              changes the way that func_odbc normally works.  Instead of the
;              invocation of the function returning a row, it returns an opaque
;              ID, which may be passed to ODBC_FETCH() to return each row in
;              turn.  ODBC_FETCH_STATUS returns SUCCESS or FAILURE, to indicate
;              whether any results were stored, and you should call ODBC_Finish
;              on the ID to clean up any remaining results when you are done
;              with the query.  Also, the variable ODBCROWS is set initially,
;              which may be used in an iterative fashion to return each row in
;              the result.
;              Please note that multirow queries are isolated to the channel,
;              and rows may not be fetched outside of the channel where the
;              query was initially performed.  Additionally, as the results are
;              associated with a channel, mode=multirow is incompatible with
;              the global space.
; rowlimit     Rowlimit limits the total number of rows which can be stored for
;              that query.  For mode=multirow, otherwise, func_odbc will
;              attempt to store all rows in the resultset, up to the maximum
;              amount of memory.  In normal mode, rowlimit can be set to allow
;              additional rows to be fetched, rather than just the first one.
;              These additional rows can be returned by using the name of the
;              function which was called to retrieve the first row as an
;              argument to ODBC_FETCH().


; ODBC_SQL - Allow an SQL statement to be built entirely in the dialplan
;[SQL]
;dsn=mysql1
;readsql=${ARG1}

; ODBC_ANTIGF - A blacklist.
;[ANTIGF]
;dsn=mysql1,mysql2   ; Use mysql1 as the primary handle, but fall back to mysql2
                    ; if mysql1 is down.  Supports up to 5 comma-separated
                    ; DSNs.  "dsn" may also be specified as "readhandle" and
                    ; "writehandle", if it is important to separate reads and
                    ; writes to different databases.
;readsql=SELECT COUNT(*) FROM exgirlfriends WHERE callerid='${SQL_ESC(${ARG1})}'
;syntax=<callerid>
;synopsis=Check if a specified callerid is contained in the ex-gf database

; ODBC_PRESENCE - Retrieve and update presence
;[PRESENCE]
;dsn=mysql1
;readsql=SELECT location FROM presence WHERE id='${SQL_ESC(${ARG1})}'
;writesql=UPDATE presence SET location='${SQL_ESC(${VAL1})}' WHERE id='${SQL_ESC(${ARG1})}'

;[READ_EXTENSIONS]
;dsn=asterisk1,asterisk2
;synopsis=Retrieve the extension data
;readsql=SELECT * FROM ex_extensions where ex_id='${ARG1}'

[DEVICEINFO_FROM_NUMBER]
dsn=asterisk1,asterisk2
synopsis=Retrieve the device for an internal call, parameters: <tenant> <number>
readsql=SELECT ex_tech,if(coalesce(sipfriends.regseconds,0)>coalesce(sipregs.regseconds,0),sipfriends.regseconds,sipregs.regseconds) as regseconds,if(coalesce(sipfriends.regseconds,0)>coalesce(sipregs.regseconds,0),sipfriends.regserver,sipregs.regserver) as regserver,sipfriends.name,ex_id,ex_dialtimeout,ex_trunk,if(coalesce(sipfriends.regseconds,0)>coalesce(sipregs.regseconds,0),sipfriends.lastms,sipregs.lastms) as lastms,host,ex_unconditionalstatus,ex_onbusystatus,ex_onofflinestatus,ex_onnoanswerstatus,ex_onconditionstatus,ex_oncondition_id,ex_dnd,ex_rp_id,ex_number,ex_txvolume,ex_rxvolume,ex_notifymissingemail,ex_fmfmstatus,ex_fmfmnumber,ex_fmfmdialtimeout,ex_fmfmcallerid,ex_fmfmnumprefix,ex_fmfmnameprefix,ex_fmfmdialmethod,ex_fmfmconfirmmessage_id,ex_fmfmconfirm,ex_fmfmholdmessage_id,ex_trunkdid,ex_callwaiting,ex_autoanswer,ex_spygroups,ex_inboundblockcid,ex_inboundblockcidname FROM ex_extensions,sipfriends,te_tenants,sipregs WHERE sipfriends.name=sipregs.name and ex_te_id='${ARG1}' and sipfriends.te_id='${ARG1}' and te_tenants.te_id='${ARG1}' and ( ex_number='${ARG2}' or sipfriends.name='${ARG2}' or concat(ex_number,te_code)='${ARG2}' ) and ex_tech='SIP' and ex_tech_id=sipfriends.id order by regseconds desc,regserver limit 1

[CUSTOMINFO_FROM_NUMBER]
dsn=asterisk1,asterisk2
synopsis=Retrieve the custom extension settings, parameters: <tenant> <number>
readsql=SELECT ex_id,ex_te_id,ex_name,ex_tech,ex_number,ex_trunk,ex_tech_id,ex_cidnum,ex_cidname,ex_blockcid,ex_prefix,ex_regexprefix,ex_fmfmstatus,ex_fmfmnumber,ex_fmfmdialtimeout,ex_fmfmdialmethod,ex_fmfmcallerid,ex_fmfmnumprefix,ex_fmfmnameprefix,ex_recording,ex_dialtimeout,ex_unconditionalstatus,ex_onbusystatus,ex_onofflinestatus,ex_onnoanswerstatus,ex_onconditionstatus,ex_oncondition_id,ex_callallowed,ex_destallowregex,ex_rp_id,ex_dnd,ex_emergencycidnum,ex_emailrecording,ex_fmfmconfirm,ex_fmfmconfirmmessage_id,ex_minemailrecording,ex_trunkcidoverride,ex_trunkemergencycidoverride,ex_trunkcidsource,ex_fmfmholdmessage_id,ex_notifymissingemail,ex_txvolume,ex_rxvolume,ex_mu_id,ex_faxgateway,ex_faxalert,ex_trunkdid,ex_fmfmdelay,ex_abusedetection,ex_callwaiting,ex_costlimits,ex_dailycostlimit,ex_dailycostlimitdom,ex_dailycostlimitint,ex_routecostlimit,ex_dailycostwarning,ex_dailycostwarningdom,ex_dailycostwarningint,ex_cl_id,ce_id,ce_te_id,ce_peername,ce_dialoption,ce_auth_type,ce_pr_id,ce_auth_parameter,ce_usecid,te_alertemail,te_cl_id,te_payment_type,te_rp_id,te_billingcode,te_disabled,te_prefixbillingcode,te_allowonnet,te_allowanycalleridusage,te_allowinonnet,te_billonnet,te_onnetdigitstoadd,te_onnetdigitstoremove,te_ca_rp_id,te_defaultrrid,te_sms_rp_id,te_ca_cl_id,te_costlimits,te_dailytenantcostlimit,te_monthlytenantcostlimit,te_routecostlimit,te_cr_no_id,te_ca_no_id,te_allowanycalleridforemerg,te_abusedetection,te_expirationdate,te_alertamount,te_dailycostlimitdom,te_dailycostlimitint,te_dailytenantcostlimitdom,te_dailytenantcostlimitint,te_monthlytenantcostlimitdom,te_monthlytenantcostlimitint,te_dailycostwarningdom,te_dailycostwarningint,te_dailytenantcostwarningdom,te_dailytenantcostwarningint,te_monthlytenantcostwarningdom,te_monthlytenantcostwarningint,te_uploadrecordingatcallend,te_dailytenantcostwarning,te_monthlytenantcostwarning,te_dailycostwarning,ex_inboundblockcid,ex_inboundblockcidname FROM ex_extensions,ce_customextensions,te_tenants WHERE ex_te_id='${ARG1}' and ce_te_id='${ARG1}' and te_tenants.te_id='${ARG1}' and ( ex_number='${ARG2}' or ce_peername='${ARG2}' or concat(ex_number,te_code)='${ARG2}' ) and ex_tech='CUSTOM' and ex_tech_id=ce_id

[CUSTOMINFO_FROM_ID]
dsn=asterisk1,asterisk2
synopsis=Retrieve the custom extension settings, parameters: <id>
readsql=SELECT ex_id,ex_te_id,ex_name,ex_tech,ex_number,ex_trunk,ex_tech_id,ex_cidnum,ex_cidname,ex_blockcid,ex_prefix,ex_regexprefix,ex_fmfmstatus,ex_fmfmnumber,ex_fmfmdialtimeout,ex_fmfmdialmethod,ex_fmfmcallerid,ex_fmfmnumprefix,ex_fmfmnameprefix,ex_recording,ex_dialtimeout,ex_unconditionalstatus,ex_onbusystatus,ex_onofflinestatus,ex_onnoanswerstatus,ex_onconditionstatus,ex_oncondition_id,ex_callallowed,ex_destallowregex,ex_rp_id,ex_dnd,ex_emergencycidnum,ex_emailrecording,ex_fmfmconfirm,ex_fmfmconfirmmessage_id,ex_minemailrecording,ex_trunkcidoverride,ex_trunkemergencycidoverride,ex_trunkcidsource,ex_fmfmholdmessage_id,ex_notifymissingemail,ex_txvolume,ex_rxvolume,ex_mu_id,ex_faxgateway,ex_faxalert,ex_trunkdid,ex_fmfmdelay,ex_abusedetection,ex_callwaiting,ex_autoanswer,ex_costlimits,ex_dailycostlimit,ex_dailycostlimitdom,ex_dailycostlimitint,ex_routecostlimit,ex_dailycostwarning,ex_dailycostwarningdom,ex_dailycostwarningint,ex_cl_id,ce_id,ce_te_id,ce_peername,ce_dialoption,ce_auth_type,ce_usecid,ex_inboundblockcid,ex_inboundblockcidname FROM ex_extensions,ce_customextensions WHERE ex_te_id=ce_te_id and ex_id='${ARG1}' and ex_tech='CUSTOM' and ex_tech_id=ce_id

[VIRTUALINFO_FROM_NUMBER]
dsn=asterisk1,asterisk2
synopsis=Retrieve the virtual extension settings, parameters: <tenant> <number>
readsql=SELECT ex_id,ex_te_id,ex_name,ex_tech,ex_number,ex_trunk,ex_tech_id,ex_cidnum,ex_cidname,ex_blockcid,ex_prefix,ex_regexprefix,ex_fmfmstatus,ex_fmfmnumber,ex_fmfmdialtimeout,ex_fmfmdialmethod,ex_fmfmcallerid,ex_fmfmnumprefix,ex_fmfmnameprefix,ex_recording,ex_dialtimeout,ex_unconditionalstatus,ex_onbusystatus,ex_onofflinestatus,ex_onnoanswerstatus,ex_onconditionstatus,ex_oncondition_id,ex_callallowed,ex_destallowregex,ex_rp_id,ex_dnd,ex_emergencycidnum,ex_emailrecording,ex_fmfmconfirm,ex_fmfmconfirmmessage_id,ex_minemailrecording,ex_trunkcidoverride,ex_trunkemergencycidoverride,ex_trunkcidsource,ex_fmfmholdmessage_id,ex_notifymissingemail,ex_txvolume,ex_rxvolume,ex_mu_id,ex_faxgateway,ex_faxalert,ex_trunkdid,ex_fmfmdelay,ex_abusedetection,ex_callwaiting,ex_costlimits,ex_dailycostlimit,ex_dailycostlimitdom,ex_dailycostlimitint,ex_routecostlimit,ex_dailycostwarning,ex_dailycostwarningdom,ex_dailycostwarningint,ex_cl_id,ve_id,ve_te_id,ve_name,ve_callerid,ve_securitypin,te_alertemail,te_cl_id,te_payment_type,te_rp_id,te_billingcode,te_disabled,te_prefixbillingcode,te_allowonnet,te_allowanycalleridusage,te_allowinonnet,te_billonnet,te_onnetdigitstoadd,te_onnetdigitstoremove,te_ca_rp_id,te_defaultrrid,te_sms_rp_id,te_ca_cl_id,te_costlimits,te_dailytenantcostlimit,te_monthlytenantcostlimit,te_routecostlimit,te_cr_no_id,te_ca_no_id,te_allowanycalleridforemerg,te_abusedetection,te_expirationdate,te_alertamount,te_dailycostlimitdom,te_dailycostlimitint,te_dailytenantcostlimitdom,te_dailytenantcostlimitint,te_monthlytenantcostlimitdom,te_monthlytenantcostlimitint,te_dailycostwarningdom,te_dailycostwarningint,te_dailytenantcostwarningdom,te_dailytenantcostwarningint,te_monthlytenantcostwarningdom,te_monthlytenantcostwarningint,te_uploadrecordingatcallend,te_dailytenantcostwarning,te_monthlytenantcostwarning,te_dailycostwarning,ex_inboundblockcid,ex_inboundblockcidname FROM ex_extensions,ve_virtualextensions,te_tenants WHERE ex_te_id='${ARG1}' and ve_te_id='${ARG1}' and te_tenants.te_id='${ARG1}' and ( ex_number='${ARG2}' or ve_name='${ARG2}' or concat(ex_number,te_code)='${ARG2}' ) and ex_tech='VIRTUAL' and ex_tech_id=ve_id
;readsql=SELECT * FROM ex_extensions,ve_virtualextensions,te_tenants WHERE ex_te_id='${ARG1}' and ve_te_id='${ARG1}' and te_tenants.te_id='${ARG1}' and ( ex_number='${ARG2}' or ve_name='${ARG2}' or concat(ex_number,te_code)='${ARG2}' ) and ex_tech='VIRTUAL' and ex_tech_id=ve_id

[PJSIPDEVICEINFO_FROM_NUMBER]
dsn=asterisk1,asterisk2
synopsis=Retrieve the pjsip extension settings, parameters: <tenant> <number>
readsql=SELECT ps_contacts.reg_server as reg_server,ps_endpoints.id,ex_id,ex_te_id,ex_name,ex_tech,ex_number,ex_trunk,ex_tech_id,ex_cidnum,ex_cidname,ex_blockcid,ex_prefix,ex_regexprefix,ex_fmfmstatus,ex_fmfmnumber,ex_fmfmdialtimeout,ex_fmfmdialmethod,ex_fmfmcallerid,ex_fmfmnumprefix,ex_fmfmnameprefix,ex_recording,ex_dialtimeout,ex_unconditionalstatus,ex_onbusystatus,ex_onofflinestatus,ex_onnoanswerstatus,ex_onconditionstatus,ex_oncondition_id,ex_callallowed,ex_destallowregex,ex_rp_id,ex_dnd,ex_emergencycidnum,ex_emailrecording,ex_fmfmconfirm,ex_fmfmconfirmmessage_id,ex_minemailrecording,ex_trunkcidoverride,ex_trunkemergencycidoverride,ex_trunkcidsource,ex_fmfmholdmessage_id,ex_notifymissingemail,ex_txvolume,ex_rxvolume,ex_mu_id,ex_faxgateway,ex_faxalert,ex_trunkdid,ex_fmfmdelay,ex_abusedetection,ex_callwaiting,ex_autoanswer,ex_costlimits,ex_dailycostlimit,ex_dailycostlimitdom,ex_dailycostlimitint,ex_routecostlimit,ex_dailycostwarning,ex_dailycostwarningdom,ex_dailycostwarningint,ex_cl_id,ps_endpoints.id as id,te_alertemail,te_cl_id,te_payment_type,te_rp_id,te_billingcode,te_disabled,te_prefixbillingcode,te_allowonnet,te_allowanycalleridusage,te_allowinonnet,te_billonnet,te_onnetdigitstoadd,te_onnetdigitstoremove,te_ca_rp_id,te_defaultrrid,te_sms_rp_id,te_ca_cl_id,te_costlimits,te_dailytenantcostlimit,te_monthlytenantcostlimit,te_routecostlimit,te_cr_no_id,te_ca_no_id,te_allowanycalleridforemerg,te_abusedetection,te_expirationdate,te_alertamount,te_dailycostlimitdom,te_dailycostlimitint,te_dailytenantcostlimitdom,te_dailytenantcostlimitint,te_monthlytenantcostlimitdom,te_monthlytenantcostlimitint,te_dailycostwarningdom,te_dailycostwarningint,te_dailytenantcostwarningdom,te_dailytenantcostwarningint,te_monthlytenantcostwarningdom,te_monthlytenantcostwarningint,te_uploadrecordingatcallend,te_dailytenantcostwarning,te_monthlytenantcostwarning,te_dailycostwarning,ex_inboundblockcid,ex_inboundblockcidname FROM ex_extensions,te_tenants,ps_endpoints left outer join ps_contacts on ps_contacts.endpoint=ps_endpoints.id WHERE ex_te_id='${ARG1}' and ps_endpoints.te_id='${ARG1}' and te_tenants.te_id='${ARG1}' and ( ex_number='${ARG2}' or ps_endpoints.id='${ARG2}' or concat(ex_number,te_code)='${ARG2}' ) and ex_tech='PJSIP' and ex_tech_id=tech_id

[PJSIPDEVICEINFO_FROM_ID]
dsn=asterisk1,asterisk2
synopsis=Retrieve the pjsip extension settings, parameters: <id>
readsql=SELECT ex_id,ex_te_id,ex_name,ex_tech,ex_number,ex_trunk,ex_tech_id,ex_cidnum,ex_cidname,ex_blockcid,ex_prefix,ex_regexprefix,ex_fmfmstatus,ex_fmfmnumber,ex_fmfmdialtimeout,ex_fmfmdialmethod,ex_fmfmcallerid,ex_fmfmnumprefix,ex_fmfmnameprefix,ex_recording,ex_dialtimeout,ex_unconditionalstatus,ex_onbusystatus,ex_onofflinestatus,ex_onnoanswerstatus,ex_onconditionstatus,ex_oncondition_id,ex_callallowed,ex_destallowregex,ex_rp_id,ex_dnd,ex_emergencycidnum,ex_emailrecording,ex_fmfmconfirm,ex_fmfmconfirmmessage_id,ex_minemailrecording,ex_trunkcidoverride,ex_trunkemergencycidoverride,ex_trunkcidsource,ex_fmfmholdmessage_id,ex_notifymissingemail,ex_txvolume,ex_rxvolume,ex_mu_id,ex_faxgateway,ex_faxalert,ex_trunkdid,ex_fmfmdelay,ex_abusedetection,ex_callwaiting,ex_costlimits,ex_autoanswer,ex_dailycostlimit,ex_dailycostlimitdom,ex_dailycostlimitint,ex_routecostlimit,ex_dailycostwarning,ex_dailycostwarningdom,ex_dailycostwarningint,ex_cl_id,te_alertemail,te_cl_id,te_payment_type,te_rp_id,te_billingcode,te_disabled,te_prefixbillingcode,te_allowonnet,te_allowanycalleridusage,te_allowinonnet,te_billonnet,te_onnetdigitstoadd,te_onnetdigitstoremove,te_ca_rp_id,te_defaultrrid,te_sms_rp_id,te_ca_cl_id,te_costlimits,te_dailytenantcostlimit,te_monthlytenantcostlimit,te_routecostlimit,te_cr_no_id,te_ca_no_id,te_allowanycalleridforemerg,te_abusedetection,te_expirationdate,te_alertamount,te_dailycostlimitdom,te_dailycostlimitint,te_dailytenantcostlimitdom,te_dailytenantcostlimitint,te_monthlytenantcostlimitdom,te_monthlytenantcostlimitint,te_dailycostwarningdom,te_dailycostwarningint,te_dailytenantcostwarningdom,te_dailytenantcostwarningint,te_monthlytenantcostwarningdom,ps_endpoints.id,te_monthlytenantcostwarningint,te_uploadrecordingatcallend,te_dailytenantcostwarning,te_monthlytenantcostwarning,te_dailycostwarning,ex_inboundblockcid,ex_inboundblockcidname FROM ex_extensions,ps_endpoints,te_tenants WHERE ex_te_id=te_tenants.te_id and ex_id='${ARG1}' and ex_tech='PJSIP' and ex_tech_id=tech_id

[PJSIPDEVICEINFO_FROM_TECHID]
dsn=asterisk1,asterisk2
synopsis=Retrieve the pjsip endpoint settings, parameters: <id>
readsql=SELECT id FROM ps_endpoints WHERE tech_id='${ARG1}'

[GET_DISTINCT_CONTACTS_MULTIROW]
dsn=asterisk1,asterisk2
synopsis=Get the servers on which there is an extension registered
readsql=SELECT distinct reg_server from ps_contacts where endpoint='${ARG1}'
Mode=multirow

[VIRTUALINFO_FROM_ID]
dsn=asterisk1,asterisk2
synopsis=Retrieve the virtual extension settings, parameters: <id>
readsql=SELECT ex_id,ex_te_id,ex_name,ex_tech,ex_number,ex_trunk,ex_tech_id,ex_cidnum,ex_cidname,ex_blockcid,ex_prefix,ex_regexprefix,ex_fmfmstatus,ex_fmfmnumber,ex_fmfmdialtimeout,ex_fmfmdialmethod,ex_fmfmcallerid,ex_fmfmnumprefix,ex_fmfmnameprefix,ex_recording,ex_dialtimeout,ex_unconditionalstatus,ex_onbusystatus,ex_onofflinestatus,ex_onnoanswerstatus,ex_onconditionstatus,ex_oncondition_id,ex_callallowed,ex_destallowregex,ex_rp_id,ex_dnd,ex_emergencycidnum,ex_emailrecording,ex_fmfmconfirm,ex_fmfmconfirmmessage_id,ex_minemailrecording,ex_trunkcidoverride,ex_trunkemergencycidoverride,ex_trunkcidsource,ex_fmfmholdmessage_id,ex_notifymissingemail,ex_txvolume,ex_rxvolume,ex_mu_id,ex_faxgateway,ex_faxalert,ex_trunkdid,ex_fmfmdelay,ex_abusedetection,ex_callwaiting,ex_autoanswer,ex_costlimits,ex_dailycostlimit,ex_dailycostlimitdom,ex_dailycostlimitint,ex_routecostlimit,ex_dailycostwarning,ex_dailycostwarningdom,ex_dailycostwarningint,ex_cl_id,ve_id,ve_te_id,ve_name,ve_callerid,ve_securitypin,ex_inboundblockcid,ex_inboundblockcidname FROM ex_extensions,ve_virtualextensions WHERE ex_te_id=ve_te_id and ex_id='${ARG1}' and ex_tech='VIRTUAL' and ex_tech_id=ve_id

[VIRTUALINFO_FROM_REALPEER]
dsn=asterisk1,asterisk2
synopsis=Retrieve the virtual extension data, parameters: <tenant> <realpeer>
readsql=SELECT ex_id,ex_te_id,ex_name,ex_tech,ex_number,ex_trunk,ex_tech_id,ex_cidnum,ex_cidname,ex_blockcid,ex_prefix,ex_regexprefix,ex_fmfmstatus,ex_fmfmnumber,ex_fmfmdialtimeout,ex_fmfmdialmethod,ex_fmfmcallerid,ex_fmfmnumprefix,ex_fmfmnameprefix,ex_recording,ex_dialtimeout,ex_unconditionalstatus,ex_onbusystatus,ex_onofflinestatus,ex_onnoanswerstatus,ex_onconditionstatus,ex_oncondition_id,ex_callallowed,ex_destallowregex,ex_rp_id,ex_dnd,ex_emergencycidnum,ex_emailrecording,ex_fmfmconfirm,ex_fmfmconfirmmessage_id,ex_minemailrecording,ex_trunkcidoverride,ex_trunkemergencycidoverride,ex_trunkcidsource,ex_fmfmholdmessage_id,ex_notifymissingemail,ex_txvolume,ex_rxvolume,ex_mu_id,ex_faxgateway,ex_faxalert,ex_trunkdid,ex_fmfmdelay,ex_abusedetection,ex_callwaiting,ex_costlimits,ex_dailycostlimit,ex_dailycostlimitdom,ex_dailycostlimitint,ex_routecostlimit,ex_dailycostwarning,ex_dailycostwarningdom,ex_dailycostwarningint,ex_cl_id,ve_id,ve_te_id,ve_name,ve_callerid,ve_securitypin,te_alertemail,te_cl_id,te_payment_type,te_rp_id,te_billingcode,te_disabled,te_prefixbillingcode,te_allowonnet,te_allowanycalleridusage,te_allowinonnet,te_billonnet,te_onnetdigitstoadd,te_onnetdigitstoremove,te_ca_rp_id,te_defaultrrid,te_sms_rp_id,te_ca_cl_id,te_costlimits,te_dailytenantcostlimit,te_monthlytenantcostlimit,te_routecostlimit,te_cr_no_id,te_ca_no_id,te_allowanycalleridforemerg,te_abusedetection,te_expirationdate,te_alertamount,te_dailycostlimitdom,te_dailycostlimitint,te_dailytenantcostlimitdom,te_dailytenantcostlimitint,te_monthlytenantcostlimitdom,te_monthlytenantcostlimitint,te_dailycostwarningdom,te_dailycostwarningint,te_dailytenantcostwarningdom,te_dailytenantcostwarningint,te_monthlytenantcostwarningdom,te_monthlytenantcostwarningint,te_uploadrecordingatcallend,te_dailytenantcostwarning,te_monthlytenantcostwarning,te_dailycostwarning,vi_te_id,vi_ve_id,vi_ex_id,vi_name,ex_inboundblockcid,ex_inboundblockcidname FROM ex_extensions,ve_virtualextensions,te_tenants,vi_virtualextensionitems WHERE ex_te_id='${ARG1}' and vi_te_id='${ARG1}' and vi_name='${ARG2}' and ve_te_id='${ARG1}' and te_tenants.te_id='${ARG1}' and ex_tech='VIRTUAL' and ex_tech_id=ve_id and vi_ve_id=ve_id

[VIRTUALINFO_FROM_VEID]
dsn=asterisk1,asterisk2
synopsis=Retrieve the virtual extension settings, parameters: <ve_id>
readsql=SELECT * FROM ex_extensions,ve_virtualextensions WHERE ex_te_id=ve_te_id and ve_id='${ARG1}' and ex_tech='VIRTUAL' and ex_tech_id=ve_id

[COUNT_VIRTUALEXTENSION_ITEMS]
dsn=asterisk1,asterisk2
synopsis=Count the number of virtual extension items, parameter:<ve_id>
readsql=SELECT count(*) FROM vi_virtualextensionitems where vi_ve_id='${ARG1}'

[GET_VIRTUALEXTENSION_N_ITEM]
dsn=asterisk1,asterisk2
synopsis=Get the N virtual extension item
readsql=SELECT * FROM vi_virtualextensionitems where vi_ve_id='${ARG1}' order by vi_name limit ${ARG2},1

[GET_VIRTUALEXTENSION_INFO_MULTIROW]
dsn=asterisk1,asterisk2
synopsis=Get the virtual extension items
readsql=SELECT * from vi_virtualextensionitems where vi_ve_id='${ARG1}'
Mode=multirow

[GET_VIRTUALEXTENSIONITEM_INFO_MULTIROW]
dsn=asterisk1,asterisk2
synopsis=Get the virtual extension items
readsql=SELECT * from vi_virtualextensionitems where vi_name='${ARG1}'
Mode=multirow

[GET_QUEUE_INFO_MULTIROW]
dsn=asterisk1,asterisk2
synopsis=Get the queues for a tenant
readsql=SELECT * from qu_queues where qu_te_id='${ARG1}'
Mode=multirow

[GET_ALLOWED_QUEUE_INFO_MULTIROW]
dsn=asterisk1,asterisk2
synopsis=Get the queues for a tenant, only for allowed extension
readsql=SELECT * from qu_queues,aq_allowed_queue_member where qu_te_id='${ARG1}' and aq_queue_name=qu_id and aq_member_device='${ARG2}' 
Mode=multirow

[COPY_ALLOWED_QUEUE_MEMBER]
dsn=asterisk1,asterisk2
synopsis=Copy the data from the aq_allowed_queue_member to queue_member table
writesql=INSERT IGNORE INTO queue_member(membername,queue_name,interface,state_interface,penalty,member_device) SELECT aq_membername,aq_queue_name,aq_interface,aq_state_interface,aq_penalty,aq_member_device from aq_allowed_queue_member where aq_uniqueid='${ARG1}'

[GET_AGENT_FROM_QUEUE_MULTIROW]
dsn=asterisk1,asterisk2
synopsis=Get the queues for a tenant
readsql=SELECT member_device from qu_queues,queue_member where qu_te_id='${ARG1}' and queue_name='${ARG2}' and qu_id='${ARG2}'
Mode=multirow

[DEVICENUMBER_FROM_ID]
dsn=asterisk1,asterisk2
synopsis=Retrieve the extension number from id, parameters: <id>
readsql=SELECT ex_number FROM ex_extensions WHERE ex_id='${ARG1}'

[DEVICEID_FROM_NUMBER]
dsn=asterisk1,asterisk2
synopsis=Retrieve the extension id from number, parameters: <tenantid>, <number>
readsql=SELECT ex_id FROM ex_extensions WHERE ex_number='${ARG2}' and ex_te_id='${ARG1}'

[DEVICETECH_FROM_NUMBER]
dsn=asterisk1,asterisk2
synopsis=Retrieve the extension id from number, parameters: <tenantid>, <number>
readsql=SELECT ex_tech FROM ex_extensions WHERE ex_number='${ARG2}' and ex_te_id='${ARG1}'

[DEVICETECH_FROM_ID]
dsn=asterisk1,asterisk2
synopsis=Retrieve the extension tech from id, parameters: <id>
readsql=SELECT ex_tech FROM ex_extensions WHERE ex_id='${ARG1}'

[DEVICEINFO_GROUP_RINGING]
dsn=asterisk1,asterisk2
synopsis=Retrieve the device for a ringing extension, parameters: <tenant> <peer calling>
readsql=SELECT b.ex_id FROM ex_extensions a,sipfriends af,st_states,ex_extensions b,sipfriends bf,pg_pickupgroups,cg_callgroups WHERE pg_te_id='${ARG1}' and cg_te_id='${ARG1}' and pg_value=cg_value and pg_ex_id=a.ex_id and cg_ex_id=b.ex_id and a.ex_te_id='${ARG1}' and b.ex_te_id='${ARG1}' and af.name='${ARG2}' and a.ex_tech='SIP' and b.ex_tech='SIP' and a.ex_tech_id=af.id and b.ex_tech_id=bf.id and st_state in ('RINGING','RINGINUSE') and bf.name=st_extension       union SELECT b.ex_id from ex_extensions a,sipfriends af,st_states,ex_extensions b,ve_virtualextensions bf,pg_pickupgroups,cg_callgroups WHERE pg_te_id='${ARG1}' and cg_te_id='${ARG1}' and pg_value=cg_value and pg_ex_id=a.ex_id and cg_ex_id=b.ex_id and a.ex_te_id='${ARG1}' and b.ex_te_id='${ARG1}' and af.name='${ARG2}' and a.ex_tech='SIP' and b.ex_tech='VIRTUAL' and a.ex_tech_id=af.id and b.ex_tech_id=bf.ve_id and st_state in ('RINGING','RINGINUSE') and bf.ve_name=st_extension      union SELECT b.ex_id from ex_extensions a,ve_virtualextensions af,st_states,ex_extensions b,ve_virtualextensions bf,pg_pickupgroups,cg_callgroups WHERE pg_te_id='${ARG1}' and cg_te_id='${ARG1}' and pg_value=cg_value and pg_ex_id=a.ex_id and cg_ex_id=b.ex_id and a.ex_te_id='${ARG1}' and b.ex_te_id='${ARG1}' and af.ve_name='${ARG2}' and a.ex_tech='VIRTUAL' and b.ex_tech='VIRTUAL' and a.ex_tech_id=af.ve_id and b.ex_tech_id=bf.ve_id and st_state in ('RINGING','RINGINUSE') and bf.ve_name=st_extension           union SELECT b.ex_id FROM ex_extensions a,ve_virtualextensions af,st_states,ex_extensions b,sipfriends bf,pg_pickupgroups,cg_callgroups WHERE pg_te_id='${ARG1}' and cg_te_id='${ARG1}' and pg_value=cg_value and pg_ex_id=a.ex_id and cg_ex_id=b.ex_id and a.ex_te_id='${ARG1}' and b.ex_te_id='${ARG1}' and af.ve_name='${ARG2}' and a.ex_tech='VIRTUAL' and b.ex_tech='SIP' and a.ex_tech_id=af.ve_id and b.ex_tech_id=bf.id and st_state in ('RINGING','RINGINUSE') and bf.name=st_extension          union SELECT b.ex_id from ex_extensions a,sipfriends af,st_states,ex_extensions b,ps_endpoints bf,pg_pickupgroups,cg_callgroups WHERE pg_te_id='${ARG1}' and cg_te_id='${ARG1}' and pg_value=cg_value and pg_ex_id=a.ex_id and cg_ex_id=b.ex_id and a.ex_te_id='${ARG1}' and b.ex_te_id='${ARG1}' and af.name='${ARG2}' and a.ex_tech='SIP' and b.ex_tech='PJSIP' and a.ex_tech_id=af.id and b.ex_tech_id=bf.tech_id and st_state in ('RINGING','RINGINUSE') and bf.id=st_extension      union SELECT b.ex_id from ex_extensions a,ps_endpoints af,st_states,ex_extensions b,ps_endpoints bf,pg_pickupgroups,cg_callgroups WHERE pg_te_id='${ARG1}' and cg_te_id='${ARG1}' and pg_value=cg_value and pg_ex_id=a.ex_id and cg_ex_id=b.ex_id and a.ex_te_id='${ARG1}' and b.ex_te_id='${ARG1}' and af.id='${ARG2}' and a.ex_tech='PJSIP' and b.ex_tech='PJSIP' and a.ex_tech_id=af.tech_id and b.ex_tech_id=bf.tech_id and st_state in ('RINGING','RINGINUSE') and bf.id=st_extension           union SELECT b.ex_id FROM ex_extensions a,ps_endpoints af,st_states,ex_extensions b,sipfriends bf,pg_pickupgroups,cg_callgroups WHERE pg_te_id='${ARG1}' and cg_te_id='${ARG1}' and pg_value=cg_value and pg_ex_id=a.ex_id and cg_ex_id=b.ex_id and a.ex_te_id='${ARG1}' and b.ex_te_id='${ARG1}' and af.id='${ARG2}' and a.ex_tech='PJSIP' and b.ex_tech='SIP' and a.ex_tech_id=af.tech_id and b.ex_tech_id=bf.id and st_state in ('RINGING','RINGINUSE') and bf.name=st_extension      union SELECT b.ex_id from ex_extensions a,ps_endpoints af,st_states,ex_extensions b,ve_virtualextensions bf,pg_pickupgroups,cg_callgroups WHERE pg_te_id='${ARG1}' and cg_te_id='${ARG1}' and pg_value=cg_value and pg_ex_id=a.ex_id and cg_ex_id=b.ex_id and a.ex_te_id='${ARG1}' and b.ex_te_id='${ARG1}' and af.id='${ARG2}' and a.ex_tech='PJSIP' and b.ex_tech='VIRTUAL' and a.ex_tech_id=af.tech_id and b.ex_tech_id=bf.ve_id and st_state in ('RINGING','RINGINUSE') and bf.ve_name=st_extension    union SELECT b.ex_id FROM ex_extensions a,ve_virtualextensions af,st_states,ex_extensions b,ps_endpoints bf,pg_pickupgroups,cg_callgroups WHERE pg_te_id='${ARG1}' and cg_te_id='${ARG1}' and pg_value=cg_value and pg_ex_id=a.ex_id and cg_ex_id=b.ex_id and a.ex_te_id='${ARG1}' and b.ex_te_id='${ARG1}' and af.ve_name='${ARG2}' and a.ex_tech='VIRTUAL' and b.ex_tech='PJSIP' and a.ex_tech_id=af.ve_id and b.ex_tech_id=bf.tech_id and st_state in ('RINGING','RINGINUSE') and bf.id=st_extension      union SELECT b.ex_id FROM ex_extensions a,ce_customextensions af,st_states,ex_extensions b,ce_customextensions bf,pg_pickupgroups,cg_callgroups WHERE pg_te_id='${ARG1}' and cg_te_id='${ARG1}' and pg_value=cg_value and pg_ex_id=a.ex_id and cg_ex_id=b.ex_id and a.ex_te_id='${ARG1}' and b.ex_te_id='${ARG1}' and af.ce_peername='${ARG2}' and a.ex_tech='CUSTOM' and b.ex_tech='CUSTOM' and a.ex_tech_id=af.ce_id and b.ex_tech_id=bf.ce_id and st_state in ('RINGING','RINGINUSE') and bf.ce_peername=st_extension       union SELECT b.ex_id from ex_extensions a,sipfriends af,st_states,ex_extensions b,ce_customextensions bf,pg_pickupgroups,cg_callgroups WHERE pg_te_id='${ARG1}' and cg_te_id='${ARG1}' and pg_value=cg_value and pg_ex_id=a.ex_id and cg_ex_id=b.ex_id and a.ex_te_id='${ARG1}' and b.ex_te_id='${ARG1}' and af.name='${ARG2}' and a.ex_tech='SIP' and b.ex_tech='CUSTOM' and a.ex_tech_id=af.id and b.ex_tech_id=bf.ce_id and st_state in ('RINGING','RINGINUSE') and bf.ce_peername=st_extension    union SELECT b.ex_id FROM ex_extensions a,ce_customextensions af,st_states,ex_extensions b,sipfriends bf,pg_pickupgroups,cg_callgroups WHERE pg_te_id='${ARG1}' and cg_te_id='${ARG1}' and pg_value=cg_value and pg_ex_id=a.ex_id and cg_ex_id=b.ex_id and a.ex_te_id='${ARG1}' and b.ex_te_id='${ARG1}' and af.ce_peername='${ARG2}' and a.ex_tech='CUSTOM' and b.ex_tech='SIP' and a.ex_tech_id=af.ce_id and b.ex_tech_id=bf.id and st_state in ('RINGING','RINGINUSE') and bf.name=st_extension      union SELECT b.ex_id FROM ex_extensions a,ce_customextensions af,st_states,ex_extensions b,ps_endpoints bf,pg_pickupgroups,cg_callgroups WHERE pg_te_id='${ARG1}' and cg_te_id='${ARG1}' and pg_value=cg_value and pg_ex_id=a.ex_id and cg_ex_id=b.ex_id and a.ex_te_id='${ARG1}' and b.ex_te_id='${ARG1}' and af.ce_peername='${ARG2}' and a.ex_tech='CUSTOM' and b.ex_tech='PJSIP' and a.ex_tech_id=af.ce_id and b.ex_tech_id=bf.tech_id and st_state in ('RINGING','RINGINUSE') and bf.id=st_extension      union SELECT b.ex_id FROM ex_extensions a,ps_endpoints af,st_states,ex_extensions b,ce_customextensions bf,pg_pickupgroups,cg_callgroups WHERE pg_te_id='${ARG1}' and cg_te_id='${ARG1}' and pg_value=cg_value and pg_ex_id=a.ex_id and cg_ex_id=b.ex_id and a.ex_te_id='${ARG1}' and b.ex_te_id='${ARG1}' and af.id='${ARG2}' and a.ex_tech='PJSIP' and b.ex_tech='CUSTOM' and a.ex_tech_id=af.tech_id and b.ex_tech_id=bf.ce_id and st_state in ('RINGING','RINGINUSE') and bf.ce_peername=st_extension              union SELECT b.ex_id FROM ex_extensions a,ce_customextensions af,st_states,ex_extensions b,ve_virtualextensions bf,pg_pickupgroups,cg_callgroups WHERE pg_te_id='${ARG1}' and cg_te_id='${ARG1}' and pg_value=cg_value and pg_ex_id=a.ex_id and cg_ex_id=b.ex_id and a.ex_te_id='${ARG1}' and b.ex_te_id='${ARG1}' and af.ce_peername='${ARG2}' and a.ex_tech='CUSTOM' and b.ex_tech='VIRTUAL' and a.ex_tech_id=af.ce_id and b.ex_tech_id=bf.ve_id and st_state in ('RINGING','RINGINUSE') and bf.ve_name=st_extension      union SELECT b.ex_id FROM ex_extensions a,ve_virtualextensions af,st_states,ex_extensions b,ce_customextensions bf,pg_pickupgroups,cg_callgroups WHERE pg_te_id='${ARG1}' and cg_te_id='${ARG1}' and pg_value=cg_value and pg_ex_id=a.ex_id and cg_ex_id=b.ex_id and a.ex_te_id='${ARG1}' and b.ex_te_id='${ARG1}' and af.ve_id='${ARG2}' and a.ex_tech='VIRTUAL' and b.ex_tech='CUSTOM' and a.ex_tech_id=af.ve_id and b.ex_tech_id=bf.ce_id and st_state in ('RINGING','RINGINUSE') and bf.ce_peername=st_extension        

[TENANTID_FROM_ACCOUNTCODE]
dsn=asterisk1,asterisk2
synopsis=Retrieve the tenant id from the account code, parameters: <accountcode>
readsql=SELECT te_id FROM te_tenants where te_code='${ARG1}'

[TENANTID_FROM_PEERNAME]
dsn=asterisk1,asterisk2
synopsis=Retrieve the tenant id from the peername, parameters: <peername>
readsql=SELECT te_id FROM sipfriends where name='${ARG1}'

[ACCOUNTCODE_FROM_TENANTID]
dsn=asterisk1,asterisk2
synopsis=Retrieve the account code from the tenant id, parameters: <tenant id>
readsql=SELECT te_code FROM te_tenants where te_id='${ARG1}'

[CHANNELSLIMIT_FROM_TENANTID]
dsn=asterisk1,asterisk2
synopsis=Retrieve the max number of channels allowed for tenant id, parameters: <tenant id>
readsql=SELECT te_maxchannels FROM te_tenants where te_id='${ARG1}'

[ANYCHANNELSLIMIT_FROM_TENANTID]
dsn=asterisk1,asterisk2
synopsis=Retrieve the max number of channels allowed for tenant id, parameters: <tenant id>
readsql=SELECT te_maxanychannels FROM te_tenants where te_id='${ARG1}'

[DEVICEINFO_FROM_ID]
dsn=asterisk1,asterisk2
synopsis=Retrieve the device for a call arriving from another pbx
readsql=SELECT ex_tech,if(coalesce(sipfriends.regseconds,0)>coalesce(sipregs.regseconds,0),sipfriends.regserver,sipregs.regserver) as regserver,sipfriends.name,ex_number,ex_dialtimeout,ex_trunk,if(coalesce(sipfriends.regseconds,0)>coalesce(sipregs.regseconds,0),sipfriends.lastms,sipregs.lastms) as lastms,host,ex_unconditionalstatus,ex_onbusystatus,ex_onofflinestatus,ex_onnoanswerstatus,ex_dnd,ex_rp_id,ex_id,if(coalesce(sipfriends.regseconds,0)>coalesce(sipregs.regseconds,0),sipfriends.regseconds,sipregs.regseconds) as regseconds FROM ex_extensions,sipfriends,sipregs WHERE sipfriends.name=sipregs.name and ex_id='${ARG1}' and ex_tech='SIP' and ex_tech_id=sipfriends.id order by regseconds desc limit 1

[DIDINFO_FROM_DID]
dsn=asterisk1,asterisk2
synopsis=Retrieve the tenant id from the DID, parameters: <did> <trunkprefix> <internationalprefix> <faxoutprefix>
readsql=SELECT di_id,di_te_id,di_country,di_area,di_number,di_recording,di_didprefix,di_fax,di_faxstationid,di_faxheaderinfo,di_faxprotocol,di_fax_email,di_fax_store,di_didnameprefix,di_unconditionalstatus,di_maxchannels,di_cnam,di_allowemergency,di_ibid,di_emailrecording,di_notifyunassigned,di_minemailrecording,di_dp_id,di_storeandforwardnumber,concat(di_country,di_area,di_number) as normalized,1 as level,te_allowonnet,te_onnetdigitstoadd,te_onnetdigitstoremove,di_smsemail,di_comment,te_code,te_billingtype,di_notifymaxchannels,di_faxprinturl FROM di_dids left outer join te_tenants on di_te_id=te_id where ( concat(di_country,di_area,di_number)='${ARG1}' or concat('${ARG4}',di_country,di_area,di_number)='${ARG1}' or concat(di_area,di_number)='${ARG1}' or concat('${ARG4}',di_area,di_number)='${ARG1}' or concat('${ARG2}',di_area,di_number)='${ARG1}' or concat('${ARG4}${ARG2}',di_area,di_number)='${ARG1}' or concat('${ARG3}',di_country,di_area,di_number)='${ARG1}' or concat('${ARG4}${ARG3}',di_country,di_area,di_number)='${ARG1}' or concat('+',di_country,di_area,di_number)='${ARG1}') union SELECT di_id,di_te_id,di_country,di_area,di_number,di_recording,di_didprefix,di_fax,di_faxstationid,di_faxheaderinfo,di_faxprotocol,di_fax_email,di_fax_store,di_didnameprefix,di_unconditionalstatus,di_maxchannels,di_cnam,di_allowemergency,di_ibid,di_emailrecording,di_notifyunassigned,di_minemailrecording,di_dp_id,di_storeandforwardnumber,concat(di_country,di_area,di_number) as normalized,2 as level,te_allowonnet,te_onnetdigitstoadd,te_onnetdigitstoremove,di_smsemail,di_comment,te_code,te_billingtype,di_notifymaxchannels,di_faxprinturl FROM di_dids left outer join te_tenants on di_te_id=te_id where di_number='${ARG1}' union select di_id,di_te_id,di_country,di_area,di_number,di_recording,di_didprefix,di_fax,di_faxstationid,di_faxheaderinfo,di_faxprotocol,di_fax_email,di_fax_store,di_didnameprefix,di_unconditionalstatus,di_maxchannels,di_cnam,di_allowemergency,di_ibid,di_emailrecording,di_notifyunassigned,di_minemailrecording,di_dp_id,di_storeandforwardnumber,'${ARG1}' as normalized,3 as level,te_allowonnet,te_onnetdigitstoadd,te_onnetdigitstoremove,di_smsemail,di_comment,te_code,te_billingtype,di_notifymaxchannels,di_faxprinturl FROM di_dids left outer join te_tenants on di_te_id=te_id where ( '${ARG1}' regexp(concat('^',di_country,di_area,di_number,'$')) or '${ARG1}' regexp(concat('^',di_area,di_number,'$')) or '${ARG1}' regexp(concat('^${ARG2}',di_area,di_number,'$')) or '${ARG1}' regexp(concat('^${ARG3}',di_country,di_area,di_number,'$')) or '${ARG1}' regexp(concat('^+',di_country,di_area,di_number,'$')) ) order by level ASC limit 1

[DIDINFO_FROM_DIDID]
dsn=asterisk1,asterisk2
synopsis=Retrieve the DID info, parameters: <didid>
readsql=SELECT * from di_dids where di_id='${ARG1}'

[GET_ONNETSETTING]
dsn=asterisk1,asterisk2
synopsis=Retrieve the tenant onnet status from the ID
readsql=SELECT te_allowonnet FROM te_tenants WHERE te_id='${ARG1}'

[GET_FAXSTATUS]
dsn=asterisk1,asterisk2
synopsis=Retrieve the fax status
readsql=SELECT fa_status FROM fa_faxes WHERE fa_id='${ARG1}'

[GET_INONNETSETTING]
dsn=asterisk1,asterisk2
synopsis=Retrieve the tenant IN onnet status from the ID
readsql=SELECT te_allowinonnet FROM te_tenants WHERE te_id='${ARG1}'

[GET_ANYCALLERIDUSAGESETTING]
dsn=asterisk1,asterisk2
synopsis=Retrieve the tenant allow callerid usage from the ID
readsql=SELECT te_allowanycalleridusage FROM te_tenants WHERE te_id='${ARG1}'

[TENANTINFO_FROM_ID]
dsn=asterisk1,asterisk2
synopsis=Retrieve the tenant code from the ID
readsql=SELECT te_code FROM te_tenants WHERE te_id='${ARG1}'

[COUNT_DEST]
dsn=asterisk1,asterisk2
synopsis=Count the destinations for an entry
readsql=SELECT count(*) FROM de_destinations where de_type_src='${ARG1}' and de_type_id_src='${ARG2}'

[COUNT_VMMESSAGES]
dsn=asterisk1,asterisk2
synopsis=Count the voicemail messages
readsql=SELECT count(*) FROM voicemail_messages where msgnum>=0 and mailboxuser='${ARG1}' and mailboxcontext='${ARG2}' 

[COUNT_VMMESSAGES_DIR]
dsn=asterisk1,asterisk2
synopsis=Count the voicemail messages in directory
readsql=SELECT count(*) FROM voicemail_messages where msgnum>=0 and dir='${ARG1}'

[COUNT_READ_VMMESSAGES]
dsn=asterisk1,asterisk2
synopsis=Count the number of read voicemails in a vm box
readsql=select count(*) from voicemail_messages where msgnum>=0 and mailboxuser='${ARG1}' and mailboxcontext='${ARG2}' and dir ='/var/spool/asterisk/voicemail/${ARG2}/${ARG1}/Old'

[DELETE_VMMESSAGES]
dsn=asterisk1,asterisk2
synopsis=Delete the voicemail messages
writesql=DELETE FROM voicemail_messages where msgnum>=0 and mailboxuser='${ARG1}' and mailboxcontext='${ARG2}'

[DELETE_READ_VMMESSAGES]
dsn=asterisk1,asterisk2
synopsis=Delete the read voicemail messages
writesql=DELETE FROM voicemail_messages where msgnum>=0 and mailboxuser='${ARG1}' and mailboxcontext='${ARG2}' and dir='/var/spool/asterisk/voicemail/${ARG2}/${ARG1}/Old'

[GET_DESTINATION]
dsn=asterisk1,asterisk2
synopsis=Get the nth destination
readsql=SELECT de_type_dst,de_type_id_dst FROM de_destinations where de_type_src='${ARG1}' and de_type_id_src='${ARG2}' and de_ord='${ARG3}'

[GET_DESTINATION_MULTIROW]
dsn=asterisk1,asterisk2
synopsis=Get the destinations
readsql=SELECT de_type_dst,de_type_id_dst FROM de_destinations where de_type_src='${ARG1}' and de_type_id_src='${ARG2}' order by de_ord desc
Mode=multirow

[MEDIAFILE_MD5_FROM_ID]
dsn=asterisk1,asterisk2
synopsis=Retrieve the md5 of a media file from the ID
readsql=SELECT me_md5 FROM me_mediafiles WHERE me_id='${ARG1}'

[GET_CONDITION]
dsn=asterisk1,asterisk2
synopsis=Get the condition parameters
readsql=SELECT co_type,co_timezone,co_param1,co_param2,co_param3,co_param4,co_param5,co_param6,co_param7,co_param8,co_param9,co_param10,co_param11,co_param12 FROM co_conditions where co_id='${ARG1}'

[GET_SETTING]
dsn=asterisk1,asterisk2
synopsis=Get the setting
readsql=SELECT se_value FROM se_settings where se_te_id='${ARG1}' and se_code='${ARG2}'

[GET_FULL_SETTING]
dsn=asterisk1,asterisk2
synopsis=Get the setting trying the tenant first
readsql=SELECT se_value FROM se_settings where se_te_id in ('${ARG1}',-1) and se_code='${ARG2}' and se_value<>'' order by se_te_id desc limit 1

[GET_PEEREXTEN]
dsn=asterisk1,asterisk2
synopsis=Get the extension number from the peer
readsql=SELECT ex_number from ex_extensions,sipfriends where ex_tech='SIP' and ex_tech_id=id and name='${ARG1}' union select ex_number from ex_extensions,ve_virtualextensions where ex_tech='VIRTUAL' and ex_tech_id=ve_id and ve_name='${ARG1}' union select ex_number from ex_extensions,ps_endpoints where ex_tech='PJSIP' and ex_tech_id=tech_id and id='${ARG1}' union select ex_number from ex_extensions,ce_customextensions where ex_tech='CUSTOM' and ex_tech_id=ce_id and ce_peername='${ARG1}'

[GET_FULL_PEEREXTEN]
dsn=asterisk1,asterisk2
synopsis=Get the extension name from the peer
readsql=SELECT ex_extensions.ex_name from ex_extensions,sipfriends where ex_tech='SIP' and ex_tech_id=id and name='${ARG1}' union select ex_extensions.ex_name from ex_extensions,ve_virtualextensions where ex_tech='VIRTUAL' and ex_tech_id=ve_id and ve_name='${ARG1}' union select ex_name from ex_extensions,ps_endpoints where ex_tech='PJSIP' and ex_tech_id=tech_id and id='${ARG1}' union select ex_name from ex_extensions,ce_customextensions where ex_tech='CUSTOM' and ex_tech_id=ce_id and ce_peername='${ARG1}'

[GET_PEERNAME_FROM_NUMBER]
dsn=asterisk1,asterisk2
synopsis=Get the peername from extension number
readsql=SELECT name from ex_extensions,sipfriends where ex_tech='SIP' and ex_tech_id=id and ex_number='${ARG1}' and ex_te_id='${ARG2}' union select ve_name as name from ex_extensions,ve_virtualextensions where ex_tech='VIRTUAL' and ex_tech_id=ve_id and ex_number='${ARG1}' and ex_te_id='${ARG2}' union select id as name from ex_extensions,ps_endpoints where ex_tech='PJSIP' and ex_tech_id=tech_id and ex_number='${ARG1}' and ex_te_id='${ARG2}' union select ce_peername as name from ex_extensions,ce_customextensions where ex_tech='CUSTOM' and ex_tech_id=ce_id and ex_number='${ARG1}' and ex_te_id='${ARG2}'

[GET_PEERID]
dsn=asterisk1,asterisk2
synopsis=Get the extension id from the peer
readsql=SELECT ex_id from ex_extensions,sipfriends where ex_tech='SIP' and ex_tech_id=id and name='${ARG1}' union select ex_id from ex_extensions,ve_virtualextensions where ex_tech='VIRTUAL' and ex_tech_id=ve_id and ve_name='${ARG1}' union select ex_id from ex_extensions,ce_customextensions where ex_tech='CUSTOM' and ex_tech_id=ce_id and ce_peername='${ARG1}' union select ex_id from ex_extensions,ps_endpoints where ex_tech='PJSIP' and ex_tech_id=tech_id and id='${ARG1}'

[VOICEMAILINFO_FROM_ID]
dsn=asterisk1,asterisk2
synopsis=Get the voicemail mailbox and context from uniqueid
readsql=SELECT mailbox,context,welcomeoption,language,ivr_id from voicemail where uniqueid='${ARG1}'

[VOICEMAILINFO_FROM_MAILBOX]
dsn=asterisk1,asterisk2
synopsis=Get the voicemail mailbox from mailbox and tenant
readsql=SELECT * from voicemail where mailbox='${ARG1}' and context='${ARG2}'

[VOICEMAILINFO_FROM_EXTEN]
dsn=asterisk1,asterisk2
synopsis=Get the voicemail mailbox and context from extension and tenant id
readsql=SELECT voicemail.mailbox,voicemail.context,voicemail.welcomeoption,voicemail.language from voicemail,ex_extensions,sipfriends where voicemail.te_id='${ARG1}' and ex_te_id='${ARG1}' and ex_tech='SIP' and ex_tech_id=id and ex_number='${ARG2}' and sipfriends.mailbox=concat(voicemail.mailbox,'@',voicemail.context) union select voicemail.mailbox,voicemail.context,voicemail.welcomeoption,voicemail.language from voicemail,ex_extensions,ve_virtualextensions where voicemail.te_id='${ARG1}' and ex_te_id='${ARG1}' and ex_tech='VIRTUAL' and ex_number='${ARG2}' and ex_tech_id=ve_id and ve_mailbox=concat(voicemail.mailbox,'@',voicemail.context) union select voicemail.mailbox,voicemail.context,voicemail.welcomeoption,voicemail.language from voicemail,ex_extensions where voicemail.te_id='${ARG1}' and ex_te_id='${ARG1}' and ex_tech='PJSIP' and ex_number='${ARG2}' and voicemail.mailbox='${ARG2}' union select voicemail.mailbox,voicemail.context,voicemail.welcomeoption,voicemail.language from voicemail,ex_extensions where voicemail.te_id='${ARG1}' and ex_te_id='${ARG1}' and ex_tech='CUSTOM' and ex_number='${ARG2}' and voicemail.mailbox='${ARG2}' 

[IVR_GET_WELCOME_ID]
dsn=asterisk1,asterisk2
synopsis=Get the ivr welcome message of IVR
readsql=SELECT iv_me_id from iv_ivrs where iv_id='${ARG1}'

[IVR_GET_INFO]
dsn=asterisk1,asterisk2
synopsis=Get the ivr info
readsql=SELECT * from iv_ivrs where iv_id='${ARG1}'

[IVR_GET_ID_FROM_NAME]
dsn=asterisk1,asterisk2
synopsis=Get the ivr id
readsql=SELECT iv_id from iv_ivrs where iv_name='${ARG1}' and iv_te_id='${ARG2}'

[QUEUE_GET_ID_FROM_NAME]
dsn=asterisk1,asterisk2
synopsis=Get the queue id
readsql=SELECT qu_id from qu_queues where qu_name='${ARG1}' and qu_te_id='${ARG2}'

[HUNTLIST_GET_ID_FROM_NAME]
dsn=asterisk1,asterisk2
synopsis=Get the huntlist id
readsql=SELECT hu_id from hu_huntlists where hu_name='${ARG1}' and hu_te_id='${ARG2}'

[MEDIAFILE_GET_ID_FROM_NAME]
dsn=asterisk1,asterisk2
synopsis=Get the media id
readsql=SELECT me_id from me_mediafiles where me_name='${ARG1}' and me_te_id='${ARG2}'

[GET_DISA_ID_FROM_NAME]
dsn=asterisk1,asterisk2
synopsis=Get the disa id from the name
readsql=SELECT ds_id from ds_disas where ds_name='${ARG1}' and ds_te_id='${ARG2}'

[GET_FLOW_ID_FROM_NAME]
dsn=asterisk1,asterisk2
synopsis=Get the flow id from the name
readsql=SELECT fl_id from fl_flows where fl_name='${ARG1}' and fl_te_id='${ARG2}'

[GET_FMFM]
dsn=asterisk1,asterisk2
synopsis=Get the FMFM status and number
readsql=SELECT ex_fmfmstatus,ex_fmfmnumber from ex_extensions where ex_id='${ARG1}'

[GET_TENANT_FROM_PEER_ID]
dsn=asterisk1,asterisk2
synopsis=Get the Tenant ID from the ID of the peer
readsql=SELECT ex_te_id from ex_extensions where ex_id='${ARG1}'

[GET_TENANT_FROM_QUEUE_ID]
dsn=asterisk1,asterisk2
synopsis=Get the Tenant ID from the ID of the queue
readsql=SELECT qu_te_id from qu_queues where qu_id='${ARG1}'

[GET_TENANT_FROM_FLOW_ID]
dsn=asterisk1,asterisk2
synopsis=Get the Tenant ID from the ID of the flow
readsql=SELECT fl_te_id from fl_flows where fl_id='${ARG1}'

[GET_TENANT_FROM_CUSTOM_ID]
dsn=asterisk1,asterisk2
synopsis=Get the Tenant ID from the ID of the custom destination
readsql=SELECT cu_te_id from cu_customs where cu_id='${ARG1}'

[GET_TENANT_FROM_EXTERNAL_AGENT_ID]
dsn=asterisk1,asterisk2
synopsis=Get the Tenant ID from the ID of the external agent destination
readsql=SELECT ea_te_id from ea_externalagents where ea_id='${ARG1}'

[GET_TENANT_TIMEZONE]
dsn=asterisk1,asterisk2
synopsis=Get the Tenant Timezone
readsql=SELECT te_timezone from te_tenants where te_id='${ARG1}'

[GET_TENANT_FROM_MEETME_ID]
dsn=asterisk1,asterisk2
synopsis=Get the Tenant ID from the ID of the meetme
readsql=SELECT cr_te_id from cr_conferencerooms where cr_id='${ARG1}'

[GET_HUNTLIST_TYPE]
dsn=asterisk1,asterisk2
synopsis=Get the Hunt List type
readsql=SELECT * from hu_huntlists where hu_id='${ARG1}'

[GET_FEATURE_CODE_ID]
dsn=asterisk1,asterisk2
synopsis=Get the feature id and extension id or dialbyname entered
readsql=SELECT fe_id,ex_number,fe_te_id as tenantid from fe_features,ex_extensions,sipfriends where fe_te_id in ('${ARG1}',0) and ex_te_id='${ARG1}' and ex_tech='SIP' and sipfriends.id=ex_tech_id and (replace(fe_code,'[EXT]',ex_number) in ('${ARG2}','${ARG3}')  OR replace(fe_code,'[EXT]',sipfriends.name)='${ARG2}' OR replace(replace(fe_code,'[EXT]',sipfriends.name),'-','')='${ARG2}' )       union SELECT fe_id,ex_number,fe_te_id as tenantid from fe_features,ex_extensions,ve_virtualextensions where fe_te_id in ('${ARG1}',0) and ex_te_id='${ARG1}' and ex_tech='VIRTUAL' and ve_id=ex_tech_id and (replace(fe_code,'[EXT]',ex_number) in ('${ARG2}','${ARG3}') OR replace(fe_code,'[EXT]',ve_name)='${ARG2}' OR replace(replace(fe_code,'[EXT]',ve_name),'-','')='${ARG2}' )        union SELECT fe_id,ex_number,fe_te_id as tenantid from fe_features,ex_extensions,ps_endpoints where fe_te_id in ('${ARG1}',0) and ex_te_id='${ARG1}' and ex_tech='PJSIP' and tech_id=ex_tech_id and (replace(fe_code,'[EXT]',ex_number) in ('${ARG2}','${ARG3}') OR replace(fe_code,'[EXT]',id)='${ARG2}'  OR replace(replace(fe_code,'[EXT]',id),'-','')='${ARG2}' )       union SELECT fe_id,ex_number,fe_te_id as tenantid from fe_features,ex_extensions,ce_customextensions where fe_te_id in ('${ARG1}',0) and ex_te_id='${ARG1}' and ex_tech='CUSTOM' and ce_id=ex_tech_id and (replace(fe_code,'[EXT]',ex_number) in ('${ARG2}','${ARG3}') OR replace(fe_code,'[EXT]',ce_peername)='${ARG2}' OR replace(replace(fe_code,'[EXT]',ce_peername),'-','')='${ARG2}' )      union SELECT fe_id,substr('${ARG2}',locate('[NUM]',fe_code)),fe_te_id as tenantid from fe_features where fe_te_id in ('${ARG1}',0) and '${ARG2}' like replace(fe_code,'[NUM]','%') union SELECT fe_id,fl_number,fe_te_id as tenantid from fe_features,fl_flows where fe_te_id in ('${ARG1}',0) and fl_te_id='${ARG1}' and replace(fe_code,'[EXT]',fl_number) in ('${ARG2}','${ARG3}') union SELECT fe_id,mailbox,fe_te_id as tenantid from fe_features,voicemail where fe_te_id in ('${ARG1}',0) and te_id='${ARG1}' and replace(fe_code,'[EXT]',mailbox)='${ARG3}' union select fe_id,sn_destnumber,fe_te_id as tenantid from fe_features,sn_shortnumbers where fe_te_id in ('${ARG1}',0) and sn_te_id='${ARG1}' and replace(fe_code,'[EXT]',sn_number)='${ARG2}' order by tenantid desc limit 1 

[GET_SPECIAL_CODE]
dsn=asterisk1,asterisk2
synopsis=Get the code for a special feature
readsql=SELECT sp_code from sp_specials where sp_id='${ARG1}'

[GET_CUSTOM_CODE]
dsn=asterisk1,asterisk2
synopsis=Get the code,param for a custom destination
readsql=SELECT ct_name,cu_param1,cu_param2,cu_param3,cu_param4,cu_param5,cu_param6,cu_param7,cu_param8,cu_param9,cu_param10,cu_param11,cu_param12,cu_param13,cu_param14,cu_param15 from cu_customs,ct_customtypes where cu_ct_id=ct_id and cu_id='${ARG1}'

;[GET_RECORDING_FOR_PEER]
;dsn=asterisk1,asterisk2
;synopsis=Get the always record status of an extension
;readsql=SELECT ex_recording,ex_emailrecording,ex_minemailrecording from ex_extensions,sipfriends where ex_te_id='${ARG1}' and te_id='${ARG1}' and ex_tech='SIP' and ex_tech_id=id and name='${ARG2}' 

;[COUNT_PROVIDERS]
;dsn=asterisk1,asterisk2
;synopsis=Get the number of provider associated to a pbx node
;readsql=SELECT count(*) from ro_routes,no_nodes where ro_no_id=no_id and no_peername='${ARG1}'

;[GET_PROVIDER]
;dsn=asterisk1,asterisk2
;synopsis=Get the nth provider associated to a pbx node
;readsql=SELECT pr_tech,pr_peername from ro_routes,no_nodes,pr_providers where pr_disabled='' and ro_no_id=no_id and no_peername='${ARG1}' and ro_pr_id=pr_id and ro_order='${ARG2}'

[GET_CONFERENCE_ID_FROM_NUMBER]
dsn=asterisk1,asterisk2
synopsis=Get the conference id from number
readsql=SELECT cr_id from cr_conferencerooms,meetme where cr_te_id='${ARG1}' and cr_number='${ARG2}' and cr_bookid=bookid

[GET_CONFERENCE_ID_FROM_PIN]
dsn=asterisk1,asterisk2
synopsis=Get the conference id from pin
readsql=SELECT cr_id from cr_conferencerooms,meetme where cr_te_id='${ARG1}' and ( pin='${ARG2}' or adminpin='${ARG2}' ) and cr_bookid=bookid

[CHECK_CONFERENCEPIN]
dsn=asterisk1,asterisk2
synopsis=Check the conference pin
readsql=SELECT cr_id from cr_conferencerooms,meetme where cr_te_id='${ARG1}' and ( pin='${ARG3}' or adminpin='${ARG3}' ) and cr_id='${ARG2}' and cr_bookid=bookid

[LOG_CONFERENCESTART]
dsn=asterisk1,asterisk2
synopsis=Log the conference start
writesql=INSERT into cl_conferenceroomlogs (cl_te_id,cl_start,cl_end,cl_cr_id,cl_pinused,cl_calleridname,cl_calleridnum,cl_uniqueid) values ('${ARG1}',NOW(),NULL,'${ARG2}','${ARG3}','${ARG4}','${ARG5}','${ARG6}')

[LOG_CONFERENCEEND]
dsn=asterisk1,asterisk2
synopsis=Log the conference end
writesql=UPDATE cl_conferenceroomlogs set cl_end=NOW(), cl_meetmesecs='${VAL1}' where cl_te_id='${ARG1}' and cl_uniqueid='${ARG2}'

[GET_CONFERENCENUMUSERS]
dsn=asterisk1,asterisk2
synopsis=Get the conference number of users
readsql=SELECT members,cr_sendreport from cr_conferencerooms,meetme where cr_te_id='${ARG1}' and cr_id='${ARG2}' and cr_bookid=bookid

[GET_HUNTLIST_ID_FROM_NUMBER]
dsn=asterisk1,asterisk2
synopsis=Get the huntlist id from number
readsql=SELECT hu_id from hu_huntlists where hu_te_id='${ARG1}' and hu_number='${ARG2}'

[GET_QUEUE_ID_FROM_NUMBER]
dsn=asterisk1,asterisk2
synopsis=Get the queue id from number
readsql=SELECT qu_id from qu_queues where qu_te_id='${ARG1}' and qu_number='${ARG2}'

[GET_CONFERENCE_FROM_ID]
dsn=asterisk1,asterisk2
synopsis=Get the conference from id
readsql=SELECT * from cr_conferencerooms,meetme where cr_id='${ARG1}' and cr_bookid=bookid

[SET_CONFERENCE_HOST]
dsn=asterisk1,asterisk2
synopsis=Set the conference server for the conference id
writesql=UPDATE cr_conferencerooms set cr_hosted='${VAL1}' where cr_id='${ARG1}'

[COUNT_DIAL_BY_NAME]
dsn=asterisk1,asterisk2
synopsis=Get the number of dial by name matching peers
readsql=SELECT count(*) from dn_dialbyname where dn_te_id='${ARG1}' and dn_number='${ARG2}'

[GET_DIAL_BY_NAME]
dsn=asterisk1,asterisk2
synopsis=Get the dial extensions by name matching peers
readsql=SELECT dn_type,dn_type_id,ex_name as name,ex_number as number from dn_dialbyname,ex_extensions where dn_type='EXT' and dn_te_id='${ARG1}' and dn_number='${ARG2}' and ex_te_id='${ARG1}' and dn_type_id=ex_id union SELECT dn_type,dn_type_id,fullname as name,mailbox as number from dn_dialbyname,voicemail where dn_type='VOICEMAIL' and dn_te_id='${ARG1}' and dn_number='${ARG2}' and te_id='${ARG1}' and dn_type_id=uniqueid order by name limit ${ARG3},1

;[GET_LOCAL_CIDNUM_CIDNAME_FROM_PEERNAME]
;dsn=asterisk1,asterisk2
;synopsis=Get the callerid num and name for internal calls
;readsql=SELECT ex_number,ex_name,ex_trunk from ex_extensions,sipfriends where ex_tech='SIP' and ex_tech_id=id and name='${ARG1}'

;[GET_OUTBOUND_CIDNUM_CIDNAME_FROM_PEERNAME]
;dsn=asterisk1,asterisk2
;synopsis=Get the callerid num and name for external calls
;readsql=SELECT CASE ex_trunk WHEN 'on' THEN CASE ex_trunkcidoverride WHEN 'none' THEN ex_cidnum WHEN 'name' THEN ex_cidnum ELSE '' END ELSE ex_cidnum END as ex_cidnum,CASE ex_trunk WHEN 'on' THEN CASE ex_trunkcidoverride WHEN 'none' THEN ex_cidname WHEN 'number' THEN ex_cidname ELSE '' END ELSE ex_cidname END as ex_cidname,ex_blockcid from ex_extensions,sipfriends where ex_tech='SIP' and ex_tech_id=id and name='${ARG1}'

[GET_EMERGENCY_OUTBOUND_CIDNUM_FROM_PEERNAME]
dsn=asterisk1,asterisk2
synopsis=Get the callerid num and name for external calls
readsql=SELECT CASE ex_trunk WHEN 'on' THEN CASE ex_trunkemergencycidoverride WHEN 'none' THEN ex_emergencycidnum ELSE '' END ELSE ex_emergencycidnum END as ex_emergencycidnum from ex_extensions,sipfriends where ex_tech='SIP' and ex_tech_id=id and name='${ARG1}' union select ex_emergencycidnum from ex_extensions,ve_virtualextensions where ex_tech='VIRTUAL' and ex_tech_id=ve_id and ve_name='${ARG1}' union select ex_emergencycidnum from ex_extensions,ps_endpoints where ex_tech='PJSIP' and ex_tech_id=tech_id and id='${ARG1}' union select ex_emergencycidnum from ex_extensions,ce_customextensions where ex_tech='CUSTOM' and ex_tech_id=ce_id and ce_peername='${ARG1}' 

;[GET_CALLALLOWED_FROM_PEERNAME]
;dsn=asterisk1,asterisk2
;synopsis=Get the security setting for the peername
;readsql=SELECT ex_callallowed,ex_destallowregex from ex_extensions,sipfriends where ex_tech='SIP' and ex_tech_id=id and name='${ARG1}'

[SET_CALLALLOWED_FROM_PEERNAME]
dsn=asterisk1,asterisk2
synopsis=Set the security setting for the peername
writesql=UPDATE ex_extensions SET ex_callallowed='${VAL1}' where ( ex_tech='SIP' and ex_tech_id in ( SELECT id from sipfriends where name='${ARG1}' ) ) or ( ex_tech='VIRTUAL' and ex_tech_id in ( SELECT ve_id from ve_virtualextensions where ve_name='${ARG1}' ) ) or ( ex_tech='PJSIP' and ex_tech_id in ( SELECT tech_id from ps_endpoints where id='${ARG1}' ) ) or ( ex_tech='CUSTOM' and ex_tech_id in ( SELECT ce_id from ce_customextensions where ce_peername='${ARG1}' ) )  

[GET_OUTBOUND_CIDNUM_CIDNAME_FROM_CALLERID]
dsn=asterisk1,asterisk2
synopsis=Get the callerid num and name for external calls
readsql=SELECT ex_cidnum,ex_cidname,ex_blockcid,ex_rp_id from ex_extensions where ex_number='${ARG1}' and ex_te_id='${ARG2}'

[GET_FMFM_CONFIGURATION]
dsn=asterisk1,asterisk2
synopsis=Get the full FMFM configuration is enabled
readsql=SELECT ex_fmfmnumber,ex_fmfmdialmethod,ex_fmfmcallerid,ex_fmfmnumprefix,ex_fmfmnameprefix,ex_fmfmdialtimeout,ex_fmfmconfirm,ex_fmfmconfirmmessage_id,ex_fmfmholdmessage_id,ex_fmfmdelay from ex_extensions where ex_id='${ARG1}' and ex_fmfmstatus='on'

[GET_PARKING_LOT_INFO_FROM_LOT]
dsn=asterisk1,asterisk2
synopsis=Get the details about the parking lot
readsql=SELECT pk_id,pk_te_id,pk_hosted,pk_start,pk_end from pk_parkinglots where pk_name='${ARG1}'

[GET_PARKING_LOT_INFO_FROM_ID]
dsn=asterisk1,asterisk2
synopsis=Get the details about the parking lot
readsql=SELECT pk_name,pk_te_id,pk_hosted,pk_start,pk_end from pk_parkinglots where pk_id='${ARG1}'

[GET_PARKING_LOT_INFO_FROM_NUMBER]
dsn=asterisk1,asterisk2
synopsis=Get the details about the parking lot searching for number and tenant
readsql=SELECT pk_id,pk_name,pk_hosted from pk_parkinglots where pk_start<='${ARG1}' and pk_end>='${ARG1}' and pk_te_id='${ARG2}'

[SET_PARKING_LOT_HOST]
dsn=asterisk1,asterisk2
synopsis=Set the parking lot server for the parking lot named
writesql=UPDATE pk_parkinglots set pk_hosted='${VAL1}' where pk_name='${ARG1}'

;[GET_PARKINGLOT_FROM_ACCOUNT]
;dsn=asterisk1,asterisk2
;synopsis=Get the id of the peer and the parkinglot associated
;readsql=SELECT ex_id,parkinglot from ex_extensions,sipfriends where ex_tech='SIP' and ex_tech_id=id and name='${ARG1}' union select ex_id,parkinglot from ex_extensions,sipfriends where ex_tech='SIP' and ex_tech_id=id and name='${ARG1}'

;[GET_PARKINGLOT_FROM_PEERID]
;dsn=asterisk1,asterisk2
;synopsis=Get the parkinglot associated to the id
;readsql=SELECT parkinglot from ex_extensions,sipfriends where ex_tech='SIP' and ex_tech_id=id and ex_id='${ARG1}'

[GET_QUEUE_FROM_ID]
dsn=asterisk1,asterisk2
synopsis=Get the info about a queue
readsql=SELECT qu_hosted,qu_alwaysrecord,qu_timeout,qu_parameters,qu_nobodyhome,qu_calltimeout,qu_agent_announce_me_id,qu_name,qu_nofreemember,qu_ivr_id,qu_agent_ag_id,qu_agent_confirmmessage_me_id,qu_agentname,qu_number,qu_allowoptimization,monitor_format,qu_spygroups from qu_queues,queue where qu_id='${ARG1}' and name='${ARG1}'

[SET_QUEUE_HOST]
dsn=asterisk1,asterisk2
synopsis=Set the server for the queue named
writesql=UPDATE qu_queues set qu_hosted='${VAL1}' where qu_id='${ARG1}'

[GET_WEEKTIME_EXCONDITION]
dsn=asterisk1,asterisk2
synopsis=Check if the condition apply for weektime
readsql=SELECT ce_id from ce_conditions_extended where ce_co_id='${ARG1}' and ce_param1='${ARG2}' and ce_param2<='${ARG3}' and ce_param3>'${ARG3}'

[GET_CALENDAR_EXCONDITION]
dsn=asterisk1,asterisk2
synopsis=Check if the condition apply for calendar
readsql=SELECT ce_id from ce_conditions_extended where ce_co_id='${ARG1}' and ce_param1<='${ARG2}' and ce_param2>'${ARG2}'

;[GET_OUTBOUND_ROUTE]
;dsn=asterisk1,asterisk2
;readsql=SELECT pr_id,pr_tech,pr_peername,dl_adddigits,dl_deldigits,pr_header1,pr_header2,pr_header3,null as cost,dl_order,dl_priority,dl_weight,pr_penalty from pr_providers,no_nodes,dl_dialingrules where dl_uselcr='' and pr_disabled='' and dl_pr_id=pr_id and no_id=dl_no_id and no_peername='${ARG1}' and '${ARG2}' regexp(dl_regex) and dl_rp_id='${ARG3}' union SELECT pr_id,pr_tech,pr_peername,dl_adddigits,dl_deldigits,pr_header1,pr_header2,pr_header3, pc_cost as cost,dl_order,dl_priority,dl_weight,pr_penalty from pr_providers,pc_providercosts,no_nodes,dl_dialingrules where dl_uselcr='on' and pr_id=pc_pr_id and pr_disabled='' and dl_pr_id=pr_id and no_id=dl_no_id and no_peername='${ARG1}' and '${ARG2}' regexp(dl_regex) and dl_rp_id='${ARG3}' and pc_id=( select c.pc_id from pc_providercosts c where pr_id=c.pc_pr_id and pc_code='${ARG4}' and '${ARG2}' like concat(c.pc_prefix,'%') order by pc_cost limit 1) order by isnull(cost),cost ASC,dl_order ASC,dl_priority ASC,dl_weight*pr_penalty ASC limit ${ARG5},1

[GET_OUTBOUND_ROUTE]
dsn=asterisk1,asterisk2
readsql=SELECT pr_id,pr_tech,pr_peername,dl_adddigits,dl_deldigits,pr_header1,pr_header2,pr_header3,pr_header4,pr_header5,pr_header6,pr_adddigits,pr_deldigits,null as cost,dl_emergencyroute,dl_usepin,dl_order,dl_priority,dl_weight,pr_penalty,pr_callerpres,pr_smsurl,pr_smspost,pr_smsuser,pr_smspassword,pr_smsprovider,pr_smsprotocol,pr_calleridregex from pr_providers,dl_dialingrules left outer join no_nodes on dl_no_id=no_id where dl_uselcr='' and pr_disabled='' and dl_pr_id=pr_id and ( no_peername='${ARG1}' or dl_no_id='0' ) and '${ARG2}' regexp(dl_regex) and dl_rp_id='${ARG3}' and dl_paused='' union SELECT pr_id,pr_tech,pr_peername,dl_adddigits,dl_deldigits,pr_header1,pr_header2,pr_header3,pr_header4,pr_header5,pr_header6,pr_adddigits,pr_deldigits,pc_cost as cost,dl_emergencyroute,dl_usepin,dl_order,dl_priority,dl_weight,pr_penalty,pr_callerpres,pr_smsurl,pr_smspost,pr_smsuser,pr_smspassword,pr_smsprovider,pr_smsprotocol,pr_calleridregex from pr_providers,pc_providercosts,dl_dialingrules left outer join no_nodes on dl_no_id=no_id where dl_uselcr='on' and pr_id=pc_pr_id and pr_disabled='' and dl_pr_id=pr_id and ( no_peername='${ARG1}' or dl_no_id='0' ) and '${ARG2}' regexp(dl_regex) and dl_rp_id='${ARG3}' and pc_id=( select c.pc_id from pc_providercosts c where pr_id=c.pc_pr_id and pc_code='${ARG4}' and '${ARG2}' like concat(c.pc_prefix,'%') and dl_paused='' order by length(pc_prefix) DESC limit 1) order by isnull(cost),cost ASC,dl_order ASC,dl_priority ASC,dl_weight*pr_penalty ASC limit ${ARG5},1

[GET_BUYRATE]
dsn=asterisk1,asterisk2
readsql=SELECT pc_cost from pc_providercosts where pc_pr_id='${ARG1}' and '${ARG2}' like concat(pc_prefix,'%') order by length(pc_prefix) DESC limit 1

[SET_INCREASE_PENALTY]
dsn=asterisk1,asterisk2
synopsis=Increase the penalty of an outbound server
writesql=UPDATE pr_providers set pr_penalty=pr_penalty+${VAL1} where pr_id='${ARG1}'

[GET_MD5_GREET]
dsn=asterisk1,asterisk2
synopsis=Get the MD5 of the recorded voicemail greeting
readsql=SELECT MD5(recording) from voicemail_messages,te_tenants where dir=concat('/var/spool/asterisk/voicemail/',te_code,'/${ARG2}/greet') and te_id='${ARG1}'

[SET_ADD_QUEUE_MEMBER]
dsn=asterisk1,asterisk2
synopsis=Add a member to a queue 
writesql=INSERT IGNORE INTO queue_member(membername,queue_name,interface,state_interface,member_device,penalty) values ('${ARG1}','${VAL1}','Local/AG-000-NF-${ARG2}@fromotherpbx${ARG6}','Custom:${ARG3}','${ARG3}','${ARG5}')

[SET_ADD_ADQUEUE_MEMBER]
dsn=asterisk1,asterisk2
synopsis=Add a member to a queue 
writesql=INSERT IGNORE INTO queue_member(membername,queue_name,interface,state_interface,member_device,penalty) values ('${ARG1}','${VAL1}','Local/AG-000-AD-${ARG2}@fromotherpbx${ARG6}','Custom:${ARG3}','${ARG3}','${ARG5}')

[SET_ADD_XXQUEUE_MEMBER]
dsn=asterisk1,asterisk2
synopsis=Add a member to a queue 
writesql=INSERT IGNORE INTO queue_member(membername,queue_name,interface,state_interface,member_device,penalty) values ('${ARG1}','${VAL1}','Local/AG-000-${ARG6}-${ARG2}@fromotherpbx${ARG7}','Custom:${ARG3}','${ARG3}','${ARG5}')

[LOG_ADD_QUEUE_MEMBER]
dsn=asterisk1,asterisk2
synopsis=Log login for a member
writesql=INSERT into queue_log(time,callid,queuename,agent,event) select NOW(),'${ARG4}-LOGIN-${VAL1}','${VAL1}','${ARG1}','LOGIN' from qu_queues where qu_id='${VAL1}' and qu_id not in (select queue_name from queue_member where queue_name='${VAL1}' and interface like 'Local/AG%-%-%-${ARG2}@fromotherpbx%' )

[SET_DEL_QUEUE_MEMBER]
dsn=asterisk1,asterisk2
synopsis=Delete a member from a queue
//writesql=DELETE FROM queue_member where queue_name='${VAL1}' and ( interface like 'Local/AG%-%-AD%-${ARG2}@fromotherpbx%' or interface like 'Local/AG%-%-NF%-${ARG2}@fromotherpbx%' )
writesql=DELETE FROM queue_member where queue_name='${VAL1}' and member_device='${ARG3}'

[SET_DEL_EAQUEUE_MEMBER]
dsn=asterisk1,asterisk2
synopsis=Delete an external member from a queue
writesql=DELETE FROM queue_member where queue_name='${VAL1}' and ( interface like 'Local/AG%-%-EA%-${ARG2}@fromotherpbx%' )

[LOG_DEL_QUEUE_MEMBER]
dsn=asterisk1,asterisk2
synopsis=Log login for a member
//writesql=INSERT into queue_log(time,callid,queuename,agent,event) select NOW(),'${ARG4}-LOGOUT-${VAL1}','${VAL1}','${ARG1}','LOGOUT' from queue_member where queue_name='${VAL1}' and interface like 'Local/AG%-%-%-${ARG2}@fromotherpbx%'
writesql=INSERT into queue_log(time,callid,queuename,agent,event) select NOW(),'${ARG4}-LOGOUT-${VAL1}','${VAL1}','${ARG1}','LOGOUT' from queue_member where queue_name='${VAL1}' and member_device='${ARG3}'

[SET_PAUSE_QUEUE_MEMBER]
dsn=asterisk1,asterisk2
synopsis=Pause a member 
writesql=UPDATE queue_member set paused=1 where queue_name='${VAL1}' and interface like 'Local/AG%-%-%-${ARG2}@fromotherpbx%'

[LOG_PAUSE_QUEUE_MEMBER]
dsn=asterisk1,asterisk2
synopsis=Log Pause a member
writesql=INSERT into queue_log(time,callid,queuename,agent,event,data1) select NOW(),'${ARG4}-${VAL1}','${VAL1}','${ARG1}','PAUSE','${ARG5}' from queue_member where queue_name='${VAL1}' and interface like 'Local/AG%-%-%-${ARG2}@fromotherpbx%' and ( paused=0 or paused is null)

[SET_UNPAUSE_QUEUE_MEMBER]
dsn=asterisk1,asterisk2
synopsis=Unpause a member 
writesql=UPDATE queue_member set paused=0 where queue_name='${VAL1}' and interface like 'Local/AG%-%-%-${ARG2}@fromotherpbx%'

[LOG_UNPAUSE_QUEUE_MEMBER]
dsn=asterisk1,asterisk2
synopsis=Log unpause a member
writesql=INSERT into queue_log(time,callid,queuename,agent,event,data1) select NOW(),'${ARG4}-${VAL1}','${VAL1}','${ARG1}','UNPAUSE','${ARG5}' from queue_member where queue_name='${VAL1}' and interface like 'Local/AG%-%-%-${ARG2}@fromotherpbx%' and paused=1

[GET_QUEUE_MEMBER_INTERFACE]
dsn=asterisk1,asterisk2
synopsis=Get the interface for a member in a queue
readsql=select interface from queue_member where queue_name='${ARG1}' and member_device='${ARG2}'

[SET_ENABLE_FMFM]
dsn=asterisk1,asterisk2
synopsis=Enable FMFM
writesql=UPDATE ex_extensions set ex_fmfmstatus='on' where ex_id='${VAL1}'

[SET_DISABLE_FMFM]
dsn=asterisk1,asterisk2
synopsis=Disable FMFM
writesql=UPDATE ex_extensions set ex_fmfmstatus='' where ex_id='${VAL1}'

[GET_DND_STATUS]
dsn=asterisk1,asterisk2
synopsis=Get DND Status
readsql=SELECT ex_dnd from ex_extensions where ex_id='${ARG1}'

[GET_CW_STATUS]
dsn=asterisk1,asterisk2
synopsis=Get DND Status
readsql=SELECT ex_callwaiting from ex_extensions where ex_id='${ARG1}'

[SET_ENABLE_DND]
dsn=asterisk1,asterisk2
synopsis=Enable DND
writesql=UPDATE ex_extensions set ex_dnd='on' where ex_id='${VAL1}'

[SET_DISABLE_DND]
dsn=asterisk1,asterisk2
synopsis=Disable DND
writesql=UPDATE ex_extensions set ex_dnd='' where ex_id='${VAL1}'

[SET_ENABLE_CW]
dsn=asterisk1,asterisk2
synopsis=Enable CW
writesql=UPDATE ex_extensions set ex_callwaiting='yes' where ex_id='${VAL1}'

[SET_DISABLE_CW]
dsn=asterisk1,asterisk2
synopsis=Disable CW
writesql=UPDATE ex_extensions set ex_callwaiting='no' where ex_id='${VAL1}'

[SET_FMFM_NUMBER]
dsn=asterisk1,asterisk2
synopsis=Set FMFM Number
writesql=UPDATE ex_extensions set ex_fmfmnumber='${VAL1}' where ex_id='${ARG1}'

[GET_PAGING_INFO_FROM_NUMBER]
dsn=asterisk1,asterisk2
synopsis=Get the ID of the paging group
readsql=SELECT * from pa_paginggroups where pa_number='${ARG1}' and pa_te_id='${ARG2}'

[GET_PAGING_INFO_FROM_ID]
dsn=asterisk1,asterisk2
synopsis=Get the paging group info
readsql=SELECT * from pa_paginggroups where pa_id='${ARG1}'

;[GET_OUTBOUND_PREFIX_FROM_PEERNAME]
;dsn=asterisk1,asterisk2
;synopsis=Get the prefix and prefix digit for external calls
;readsql=SELECT ex_prefix,ex_regexprefix from ex_extensions,sipfriends where ex_tech='SIP' and ex_tech_id=id and name='${ARG1}' 

[GET_CALLRATE_FROM_TENANT]
dsn=asterisk1,asterisk2
synopsis=Get the callrate and type of payment for the tenant
readsql=SELECT te_cl_id,te_payment_type from te_tenants where te_id='${ARG1}'

[GET_COST]
dsn=asterisk1,asterisk2
synopsis=Get the call cost
readsql=SELECT ROUND(greatest(ra_minimumcost,ra_setup+(ra_cost*ceil(${ARG3}/ra_roundingtime)*ra_roundingtime/60)),ra_roundingcost) as cost,ra_country,ra_network from ra_rates where ra_cl_id='${ARG1}' and '${ARG2}' like concat(ra_prefix,'%') order by length(ra_prefix) DESC limit 1

[GET_INBOUNDCOST]
dsn=asterisk1,asterisk2
synopsis=Get the inbound call cost
readsql=SELECT ROUND(greatest(ib_minimumcost,ib_setup+(ib_cost*ceil(${ARG2}/ib_roundingtime)*ib_roundingtime/60)),ib_roundingcost) as cost from ib_inboundrates where ib_id='${ARG1}'

[GET_BUYCOST]
dsn=asterisk1,asterisk2
synopsis=Get the call provider cost
readsql=SELECT ROUND(greatest(pc_minimumcost,pc_setup+(pc_cost*ceil(${ARG3}/pc_roundingtime)*pc_roundingtime/60)),pc_roundingcost) as cost,pc_country,pc_network from pc_providercosts where pc_pr_id='${ARG1}' and '${ARG2}' like concat(pc_prefix,'%') order by length(pc_prefix) DESC limit 1

[GET_MAXTIME_FOR_CREDIT]
dsn=asterisk1,asterisk2
synopsis=Get the max call duration for the credit
readsql=SELECT CASE WHEN ra_cost=0 and (ra_minimumcost+ra_setup)<=${ARG3} THEN 'NaN' ELSE floor( ( ${ARG3} - ra_setup - ra_minimumcost ) / ra_cost * 60 ) END from ra_rates where ra_cl_id=${ARG1} and '${ARG2}' like concat(ra_prefix,'%') order by length(ra_prefix) DESC limit 1 

[GET_CALLROUTE_COST]
dsn=asterisk1,asterisk2
synopsis=Get the call cost from client rate
readsql=SELECT ra_cost from ra_rates where ra_cl_id=${ARG1} and '${ARG2}' like concat(ra_prefix,'%') order by length(ra_prefix) DESC limit 1

[SET_COST]
dsn=asterisk1,asterisk2
synopsis=Store the cost of the call and other info
writesql=INSERT into cc_callcosts(cc_uniqueid,cc_cdr_sequence,cc_cost,cc_te_id,cc_pr_id,cc_buy,cc_peername,cc_date,cc_type,cc_country,cc_network,cc_international) values ('${ARG1}','${ARG2}','${VAL1}','${ARG3}','${ARG4}','${ARG5}','${ARG6}',NOW(),'${ARG7}','${ARG8}','${ARG9}','${ARG10}')

[SET_CIDUSAGE]
dsn=asterisk1,asterisk2
synopsis=Change the CID Usage
writesql=UPDATE ex_extensions set ex_cidusage='${VAL1}' where ex_id='${ARG1}'

[REMOVE_TEMPORARY_COST]
dsn=asterisk1,asterisk2
synopsis=Remove the temporary cost for pre paid 
writesql=DELETE FROM cc_callcosts where cc_te_id='${ARG1}' and cc_uniqueid='${VAL1}'

[GET_CREDIT]
dsn=asterisk1,asterisk2
synopsis=Get the available credit for a tenant
readsql=SELECT coalesce(sum(amount),0) from ( select sum(bi_amount) as amount from bi_billings where bi_te_id='${ARG1}' union select -sum(cc_cost) as amount from cc_callcosts where cc_te_id='${ARG1}' and cc_bi_id=0 ) a

[GET_ROUTING_PROFILE_FROM_TENANTID]
dsn=asterisk1,asterisk2
synopsis=Get the routing profile for the tenant
readsql=SELECT te_rp_id FROM te_tenants where te_id='${ARG1}'

[GET_ROUTING_PROFILE_FROM_PEERNAME]
dsn=asterisk1,asterisk2
synopsis=Get the routing profile for the tenant
readsql=SELECT ex_rp_id FROM ex_extensions,sipfriends where ex_tech_id=id and name='${ARG1}'

[GET_DISA_FROM_ID]
dsn=asterisk1,asterisk2
synopsis=Retrieve all the DISA info
readsql=SELECT ds_me_id,ds_pin,ds_outbound,ds_blockcid,ds_calleridname,ds_calleridnum FROM ds_disas WHERE ds_id='${ARG1}'

[GET_DEVICE_STATE]
dsn=asterisk1,asterisk2
synopsis=Get the device state
readsql=SELECT st_state from st_states where st_extension='${ARG1}'

[GET_DEVICE_STATE_PEERNAME]
dsn=asterisk1,asterisk2
synopsis=Get the device state
readsql=SELECT st_peername from st_states where st_extension='${ARG1}'

[GET_QUEUE_MESSAGES_ID]
dsn=asterisk1,asterisk2
synopsis=Get the message id for the queue messages
readsql=SELECT qu_youarenext_me_id,qu_thereare_me_id,qu_callswaiting_me_id,qu_thankyou_me_id,qu_holdtime_me_id,qu_agent_announce_me_id from qu_queues where qu_id='${ARG1}'

[GET_QUEUE_MESSAGES_MD5]
dsn=asterisk1,asterisk2
synopsis=Get the message md5 for the queue messages
readsql=SELECT queue_youarenext,queue_thereare,queue_callswaiting,queue_thankyou,periodic_announce,queue_holdtime,announce from queue where name='${ARG1}'

[SET_QUEUE_MESSAGE_MD5]
dsn=asterisk1,asterisk2
synopsis=Set the message md5 for the queue message
writesql=UPDATE queue set queue_youarenext='${ARG1}', queue_thereare='${ARG2}', queue_callswaiting='${ARG3}', queue_thankyou='${ARG4}', periodic_announce='${ARG5}', queue_holdtime='${ARG6}',announce='${ARG7}' where name='${VAL1}'

[FULLPATHMEDIAFILE_MD5_FROM_ID]
dsn=asterisk1,asterisk2
synopsis=Retrieve the full path md5 of a media file from the ID
readsql=SELECT concat('pbx/',me_md5) FROM me_mediafiles WHERE me_id='${ARG1}'

[GET_QUEUE_COUNT_MEMBERS]
dsn=asterisk1,asterisk2
synopsis=Get the number of queue members
readsql=SELECT count(*) from queue_member where queue_name='${ARG1}'

[GET_QUEUE_MEMBER]
dsn=asterisk1,asterisk2
synopsis=Get the member of the queue
readsql=SELECT * from queue_member where queue_name='${ARG1}' and member_device='${ARG2}'

[GET_FLOWS_MULTIROW]
dsn=asterisk1,asterisk2
synopsis=Get the flows
readsql=SELECT * from fl_flows where fl_te_id='${ARG1}'
Mode=multirow

[GET_CECONDITIONS_MULTIROW]
dsn=asterisk1,asterisk2
synopsis=Get the flows
readsql=SELECT * from ce_conditions_extended where ce_co_id='${ARG1}'
Mode=multirow

[GET_QUEUE_MEMBER_MULTIROW]
dsn=asterisk1,asterisk2
synopsis=Get the members of the queue
readsql=SELECT uniqueid,membername,queue_name,interface,state_interface,penalty,paused,member_device from queue_member where queue_name='${ARG1}' order by uniqueid
Mode=multirow

[CHECK_IVR_FEATURECODE]
dsn=asterisk1,asterisk2
synopsis=Check if the feature code is authorized
readsql=SELECT de_id from de_destinations where de_type_src='IVR_FEATURE' and de_type_id_src='${ARG1}' and de_type_dst='FEATURE' and de_type_id_dst='${ARG2}'

[GET_DESTNUMBER]
dsn=asterisk1,asterisk2
synopsis=Check the destination number for a short number
readsql=SELECT sn_destnumber from sn_shortnumbers where sn_te_id='${ARG1}' and sn_number='${ARG2}'

[CLEAN_DESTINATION]
dsn=asterisk1,asterisk2
synopsis=Clean the destination for a type
writesql=delete from de_destinations where de_type_src='${ARG2}' and de_type_id_src='${ARG3}' and de_te_id='${ARG1}'

[SET_DESTINATION]
dsn=asterisk1,asterisk2
synopsis=set the destination for a type
writesql=insert into de_destinations(de_te_id,de_type_src,de_type_id_src,de_type_dst,de_type_id_dst,de_ord) values ('${ARG1}','${ARG2}','${ARG3}','${ARG4}','${ARG5}','${VAL1}')

[GET_CUSTOM_FORWARD_ID]
dsn=asterisk1,asterisk2
synopsis=Get the ID of a custom forward number
readsql=SELECT cu_id from cu_customs,ct_customtypes where cu_ct_id=ct_id and ct_name='FORWARD' and cu_te_id='${ARG1}' and (cu_param1='${ARG2}' or cu_param1 like '${ARG2},%')

[CREATE_CUSTOM_FORWARD_ID]
dsn=asterisk1,asterisk2
synopsis=Set a new custom forward number
writesql=insert into cu_customs(cu_te_id,cu_name,cu_ct_id,cu_param1,cu_param2,cu_param3,cu_param4) select '${ARG1}','${ARG2}',ct_id,'${VAL1}','${VAL2}','${ARG3}','${VAL3}' from ct_customtypes where ct_name='FORWARD'

[DISENABLE_ADDITIONAL_DESTINATION]
dsn=asterisk1,asterisk2
synopsis=Enable or Disable Additional Destination
writesql=update ex_extensions set ${ARG1}='${VAL1}' where ex_id='${ARG2}'

[SET_EXTENSION_VALUE]
dsn=asterisk1,asterisk2
synopsis=Set extension value
writesql=update ex_extensions set ${ARG1}='${VAL1}' where ex_id='${ARG2}'

[SET_DID_UNCONDITIONAL_FORWARD]
dsn=asterisk1,asterisk2
synopsis=Enable or Disable Unconditional Forward of DID
writesql=update di_dids set di_unconditionalstatus='${VAL1}' where di_id='${ARG2}' and di_te_id='${ARG1}'

[GET_DID_UNCONDITIONAL_FORWARD]
dsn=asterisk1,asterisk2
synopsis=Get Unconditional Forward of DID
readsql=select di_unconditionalstatus from di_dids where di_id='${ARG2}' and di_te_id='${ARG1}'

[GET_EXID_USING_CONDUIT]
dsn=asterisk1,asterisk2
synopsis=Get the ID of a peer to call over a tenant conduit
readsql=SELECT ex_id,ex_te_id from ex_extensions,tc_tenantconduits where tc_te_id='${ARG1}' and tc_dst_te_id=ex_te_id and concat(tc_prefix,ex_number)='${ARG2}'

[GET_CRID_USING_CONDUIT]
dsn=asterisk1,asterisk2
synopsis=Get the ID of a conference room to call over a tenant conduit
readsql=SELECT cr_id,cr_te_id from cr_conferencerooms,tc_tenantconduits where tc_te_id='${ARG1}' and tc_dst_te_id=cr_te_id and concat(tc_prefix,cr_number)='${ARG2}'

[GET_TENANT_CONDUIT_FROM_NUMBER]
dsn=asterisk1,asterisk2
synopsis=Get the tenant for a conduit number
readsql=select tc_dst_te_id,tc_prefix,tc_autocallerid,tc_switchtenant from tc_tenantconduits where tc_te_id='${ARG1}' and '${ARG2}' like concat(tc_prefix,'%')

[GET_CONDUIT_PREFIX]
dsn=asterisk1,asterisk2
synopsis=Get the prefix for a tenant conduit
readsql=SELECT tc_prefix from tc_tenantconduits where tc_te_id='${ARG1}' and tc_dst_te_id='${ARG2}'

[SET_WAKEUP_ALARM]
dsn=asterisk1,asterisk2
synopsis=set the alarm for a peer
writesql=insert into al_alarms(al_te_id,al_alarm_type,al_param1,al_param2,al_param3,al_param4) values ('${ARG1}','WAKEUP','${VAL1}','${ARG2}','${ARG3}',NOW())

[GET_NUM_ALARMS]
dsn=asterisk1,asterisk2
synopsis=get the alarm for a peer
readsql=select count(*) from al_alarms where al_te_id='${ARG2}' and al_alarm_type='${ARG1}' and al_param1='${ARG3}'

[GET_WAKEUP_ALARM_N]
dsn=asterisk1,asterisk2
synopsis=get the alarm for a peer
readsql=select al_param2,al_param3 from al_alarms where al_te_id='${ARG1}' and al_alarm_type='WAKEUP' and al_param1='${ARG2}' limit ${ARG3},1

[DELETE_WAKEUP_ALARM]
dsn=asterisk1,asterisk2
synopsis=delete the alarm for a peer
writesql=delete from al_alarms where al_te_id='${ARG1}' and al_alarm_type='WAKEUP' and al_param1='${VAL1}' and al_param2='${ARG2}' and al_param3='${ARG3}'

[GET_NUMBER_IN_BLACKLIST]
dsn=asterisk1,asterisk2
synopsis=Check if a callerid is in blacklist
readsql=select bl_id,bl_reason from bl_blacklists where bl_te_id='${ARG1}' and ( bl_callerid='${ARG2}' or '${ARG2}' like bl_callerid )

[SET_QUEUECALLBACK]
dsn=asterisk1,asterisk2
synopsis=set the callback for the call
writesql=insert into qc_queuecallbacks(qc_te_id,qc_uniqueid,qc_qu_id,qc_callsource) values ('${ARG1}','${VAL1}','${ARG2}','EXITKEY')

[GET_AGISCRIPT]
dsn=asterisk1,asterisk2
synopsis=get the agi script info
readsql=select * from ag_agiscripts where ag_id='${ARG1}' and ag_te_id='${ARG2}'

[COUNT_CALL_LEGS]
dsn=asteriskcdrdb1,asteriskcdrdb2
synopsis=Get the number of call legs for a uniqueid
readsql=select count(*) from cdr where uniqueid='${ARG1}'

[GET_CALL_LEG_DETAIL_N]
dsn=asteriskcdrdb1,asteriskcdrdb2
synopsis=Get the detail for the N call leg
readsql=select * from cdr where uniqueid='${ARG1}' order by ID limit ${ARG2},1

[SET_COST_ID]
dsn=asterisk1,asterisk2
synopsis=Store the cost of the call
writesql=INSERT into cc_callcosts(cc_uniqueid,cc_cost,cc_cdr_id,cc_te_id) values ('${ARG1}','${VAL1}','${ARG2}','${ARG3}')

[GET_CALLERID_MOD]
dsn=asterisk1,asterisk2
readsql=SELECT cr_id,cr_adddigits,cr_deldigits from ca_calleridmods,pr_providers,cr_calleridmodrules where pr_id='${ARG1}' and pr_ca_id=ca_id and cr_ca_id=ca_id and '${ARG2}' regexp(cr_regex) limit 1

[GET_INCALLERID_MOD]
dsn=asterisk1,asterisk2
readsql=SELECT cr_adddigits,cr_deldigits from cr_calleridmodrules where cr_ca_id='${ARG1}' and '${ARG2}' regexp(cr_regex) limit 1

[GET_PHONEBOOK_DESTINATION]
dsn=asterisk1,asterisk2
readsql=select pd_pi_value as destination from pd_phonebookdetails where pd_pe_id = ( select pd_pe_id FROM pd_phonebookdetails,pe_phonebookentries where pd_pe_id=pe_id and pd_pi_id in (select pi_id from pi_phonebookitems where pi_code in ('PHONE1','PHONE2','PHONE3','PHONE4','PHONE5','PHONE6','PHONE7') ) and ( pd_pi_value='${SQL_ESC(${ARG2})}' or pd_pi_value='${SQL_ESC(+${ARG2})}' or pd_pi_value='${SQL_ESC(${ARG5}${ARG2})}' or pd_pi_value='${SQL_ESC(${ARG4}${ARG2})}' or pd_pi_value='${SQL_ESC(+${ARG4}${ARG2})}' or ( '${SQL_ESC(${ARG2})}' REGEXP pd_pi_value and pd_pi_value<>'' )  ) and pe_pb_id='${ARG1}' and pe_te_id='${ARG3}' limit 1 ) and pd_pi_id = ( select pi_id from pi_phonebookitems where pi_code='ROUTING' )

[GET_PHONEBOOK_CALLERIDNAME]
dsn=asterisk1,asterisk2
readsql=select pd_pi_value as calleridname from pd_phonebookdetails where pd_pe_id = ( select pd_pe_id FROM pd_phonebookdetails,pe_phonebookentries where pd_pe_id=pe_id and pd_pi_id in (select pi_id from pi_phonebookitems where pi_code in ('PHONE1','PHONE2','PHONE3','PHONE4','PHONE5','PHONE6','PHONE7') ) and ( pd_pi_value='${SQL_ESC(${ARG2})}' or pd_pi_value='${SQL_ESC(+${ARG2})}' or pd_pi_value='${SQL_ESC(${ARG5}${ARG2})}' or pd_pi_value='${SQL_ESC(${ARG4}${ARG2})}' or pd_pi_value='${SQL_ESC(+${ARG4}${ARG2})}' ) and pe_pb_id='${ARG1}' and pe_te_id='${ARG3}' limit 1 ) and pd_pi_id = ( select pi_id from pi_phonebookitems where pi_code='NAME' )

[CHECK_PHONEBOOK_CALLERIDNUMBER]
dsn=asterisk1,asterisk2
readsql=select pd_id FROM pd_phonebookdetails,pe_phonebookentries where pd_pe_id=pe_id and ( pd_pi_value='${SQL_ESC(${ARG2})}' or pd_pi_value='${SQL_ESC(+${ARG2})}' or pd_pi_value='${SQL_ESC(${ARG5}${ARG2})}' or pd_pi_value='${SQL_ESC(${ARG4}${ARG2})}' or pd_pi_value='${SQL_ESC(+${ARG4}${ARG2})}' ) and pe_pb_id='${ARG1}' and pe_te_id='${ARG3}' limit 1 

[SET_FAXSTATUS]
dsn=asterisk1,asterisk2
synopsis=Set the fax status
writesql=UPDATE fa_faxes set fa_status='${VAL1}' where fa_id='${ARG1}'

[SET_FAXERROR]
dsn=asterisk1,asterisk2
synopsis=Set the fax error
writesql=UPDATE fa_faxes set fa_error='${SQL_ESC(${VAL1})}' where fa_id='${ARG1}'

[INSERT_FAXATTEMPTS]
dsn=asterisk1,asterisk2
synopsis=insert a record for this fax attempt
writesql=INSERT INTO ft_faxattempts (ft_te_id,ft_fa_id, ft_remoteid, ft_date, ft_status, ft_dialstatus, ft_error, ft_attempt, ft_ecm, ft_rate, ft_modem) SELECT fa_te_id,'${ARG1}', fa_remoteid, now(), fa_status, '', fa_error, fa_attempt, '${VAL2}', '${VAL3}','${VAL4}'  FROM fa_faxes WHERE fa_id = '${ARG1}'

[SET_FAXREMOTEID]
dsn=asterisk1,asterisk2
synopsis=Set the fax remote id
writesql=UPDATE fa_faxes set fa_remoteid='${VAL1}' where fa_id='${ARG1}'

[SET_GROUP_COUNT]
dsn=asterisk1,asterisk2
synopsis=Set the count for the group
writesql=INSERT INTO gr_groupcounts(gr_name,gr_server,gr_count) VALUES ('${ARG1}','${ARG2}','${VAL1}') ON DUPLICATE KEY UPDATE gr_count='${VAL1}'

[GET_GROUP_COUNT]
dsn=asterisk1,asterisk2
synopsis=get the count for the group
readsql=SELECT coalesce(sum(gr_count),0) from gr_groupcounts where gr_name='${ARG1}'

[SET_CALL_CAMPAIGN_DIALSTATUS]
dsn=asterisk1,asterisk2
synopsis=Set the dialstatus for campaign call
writesql=UPDATE cn_campaignnumbers set cn_disposition='${VAL1}' where cn_id='${ARG1}'

[DELETE_NUMBER_FROM_CALL_CAMPAIGN]
dsn=asterisk1,asterisk2
synopsis=Delete a number from the call campaign
writesql=DELETE from cn_campaignnumbers where cn_te_id='${ARG1}' and cn_id='${VAL1}'

[SET_CALL_CAMPAIGN_BILLSEC]
dsn=asterisk1,asterisk2
synopsis=Set the billsec for campaign call
writesql=UPDATE cn_campaignnumbers set cn_billsec='${VAL1}' where cn_id='${ARG1}'

[GET_CAMPAIGN_NUMBERDETAIL]
dsn=asterisk1,asterisk2
synopsis=Get the detail for the campaign number dialed
readsql=SELECT * from cn_campaignnumbers where cn_id='${ARG1}'

[SEARCH_CAMPAIGN_NUMBERDETAIL]
dsn=asterisk1,asterisk2
synopsis=Get the detail for the campaign number dialed
readsql=SELECT * from cn_campaignnumbers where cn_te_id='${ARG1}' and cn_ca_id='${ARG2}' and cn_number='${ARG3}'

[GET_CAMPAIGN]
dsn=asterisk1,asterisk2
synopsis=get the campaign details
readsql=SELECT * from ca_campaigns,te_tenants where ca_id='${ARG1}' and ca_te_id=te_id

[GET_STATUS_FROM_TENANTID]
dsn=asterisk1,asterisk2
synopsis=get the disabled status for a tenant
readsql=SELECT  if( te_disabled = 'on', 'on', if( te_disabled = 'expiration', if( te_expirationdate > NOW( ) , '', 'on' ) , '' ) ) AS disabled from te_tenants where te_id='${ARG1}'

[GET_CNAMCACHE_FROM_NUMBER]
dsn=asterisk1,asterisk2
synopsis=Get the name using the local cnam cache
readsql=SELECT cc_name from cc_cnamecache where cc_number='${ARG1}' and cc_te_id='${ARG2}'

[SET_CNAMCACHE]
dsn=asterisk1,asterisk2
synopsis=Set the name in the local cnam cache
writesql=INSERT IGNORE INTO cc_cnamecache (cc_number,cc_name,cc_datetime,cc_te_id) values ('${ARG1}','${VAL1}',NOW(),'${ARG2}' ) 

[GET_BILLINGCODE_FROM_TENANTID]
dsn=asterisk1,asterisk2
synopsis=Get the tenant billing code from tenant id
readsql=SELECT te_billingcode,te_prefixbillingcode from te_tenants where te_id='${ARG1}'

[GET_FLOW_ID_FROM_NUMBER]
dsn=asterisk1,asterisk2
synopsis=Get the flow id from number
readsql=SELECT fl_id from fl_flows,te_tenants where fl_te_id='${ARG1}' and te_id='${ARG1}' and ( fl_number='${SQL_ESC(${ARG2})}' or '${SQL_ESC(${ARG2})}'=concat(fl_number,te_code) or '${SQL_ESC(${ARG2})}'=concat(fl_number,'-',te_code) )

[GET_FLOW_NUMBER_FROM_ID]
dsn=asterisk1,asterisk2
synopsis=Get the flow number from id
readsql=SELECT fl_number from fl_flows where fl_te_id='${ARG1}' and fl_id='${SQL_ESC(${ARG2})}'

[GET_DESTINATION_TYPE_ID_FROM_NUMBER]
dsn=asterisk1,asterisk2
synopsis=Get the type and id from number
readsql=SELECT 'FLOW' as type ,fl_id as id from fl_flows,te_tenants where fl_te_id='${ARG1}' and te_id='${ARG1}' and ( fl_number='${SQL_ESC(${ARG2})}' or '${SQL_ESC(${ARG2})}'=concat(fl_number,te_code) or '${SQL_ESC(${ARG2})}'=concat(fl_number,'-',te_code) ) union SELECT 'EXT' as type,ex_id as id from ex_extensions,te_tenants where ex_te_id='${ARG1}' and te_id='${ARG1}' and ( ex_number='${SQL_ESC(${ARG2})}' or '${SQL_ESC(${ARG2})}'=concat(ex_number,te_code) or '${SQL_ESC(${ARG2})}'=concat(ex_number,'-',te_code) or '${SQL_ESC(${ARG2})}'=concat(ex_number,'_',te_code) ) union SELECT 'HUNTLIST' as type,hu_id as id from hu_huntlists,te_tenants where hu_te_id='${ARG1}' and te_id='${ARG1}' and ( hu_number='${SQL_ESC(${ARG2})}' or '${SQL_ESC(${ARG2})}'=concat(hu_number,te_code) or '${SQL_ESC(${ARG2})}'=concat(hu_number,'-',te_code) ) union SELECT 'CONFERENCE' as type,cr_id as id from cr_conferencerooms,te_tenants where cr_te_id='${ARG1}' and te_id='${ARG1}' and ( cr_number='${SQL_ESC(${ARG2})}' or '${SQL_ESC(${ARG2})}'=concat(cr_number,te_code) or '${SQL_ESC(${ARG2})}'=concat(cr_number,'-',te_code) )

[GET_INBOUND_RATE]
dsn=asterisk1,asterisk2
synopsis=Get the inbound rate from id
readsql=SELECT * from ib_inboundrates where ib_id='${ARG1}'

[COUNT_MULTIPLE_STATE_ALL_STATE_AVAILABLE_NOTIN]
dsn=asterisk1,asterisk2
synopsis=Check if all extension state are of type selected
readsql=SELECT count(*) from ( select ce_id from ce_conditions_extended,ex_extensions,sipfriends,st_states where name=st_extension and ex_dnd='' and st_state not in (${ARG2}) and ce_param1='EXT' and ce_co_id='${ARG1}' and ex_id=ce_param2 and ex_tech_id=id union select ce_id from ce_conditions_extended,fl_flows,st_states where concat('FLOW-',fl_id)=st_extension and st_state not in (${ARG2}) and ce_param1='FLOW' and ce_co_id='${ARG1}' and fl_id=ce_param2 ) a

[COUNT_MULTIPLE_STATE_ALL_NOTIN]
dsn=asterisk1,asterisk2
synopsis=Check if all extension state are of type selected
readsql=SELECT count(*) from ( select ce_id from ce_conditions_extended,ex_extensions,sipfriends left outer join st_states on name=st_extension where ( st_state not in (${ARG2}) or st_state is null ) and ce_param1='EXT' and ex_dnd='' and ce_co_id='${ARG1}' and ex_id=ce_param2 and ex_tech_id=id union select ce_id from ce_conditions_extended,fl_flows left outer join st_states on concat('FLOW-',fl_id)=st_extension where ( st_state not in (${ARG2}) or st_state is null ) and ce_param1='FLOW' and ce_co_id='${ARG1}' and fl_id=ce_param2 ) a

[GET_EXTENSION_DAILY_PAID]
dsn=asterisk1,asterisk2
synopsis=Check how much the current extension has spent today
readsql=SELECT coalesce(sum(cc_cost),0) FROM cc_callcosts where cc_date='${ARG2}' and cc_peername='${ARG1}' and cc_te_id='${ARG3}' and cc_uniqueid not like 'Temporary%'

[GET_TENANT_DAILY_PAID]
dsn=asterisk1,asterisk2
synopsis=Check how much the current tenant has spent today
readsql=SELECT coalesce(sum(cc_cost),0) FROM cc_callcosts where cc_date='${ARG2}' and cc_te_id='${ARG1}' and cc_uniqueid not like 'Temporary%'

[GET_TENANT_MONTHLY_PAID]
dsn=asterisk1,asterisk2
synopsis=Check how much the current tenant has spent this month
readsql=SELECT coalesce(sum(cc_cost),0) FROM cc_callcosts where cc_te_id='${ARG1}' and cc_date in ('${ARG2}-01','${ARG2}-02','${ARG2}-03','${ARG2}-04','${ARG2}-05','${ARG2}-06','${ARG2}-07','${ARG2}-08','${ARG2}-09','${ARG2}-10','${ARG2}-11','${ARG2}-12','${ARG2}-13','${ARG2}-14','${ARG2}-15','${ARG2}-16','${ARG2}-17','${ARG2}-18','${ARG2}-19','${ARG2}-20','${ARG2}-21','${ARG2}-22','${ARG2}-23','${ARG2}-24','${ARG2}-25','${ARG2}-26','${ARG2}-27','${ARG2}-28','${ARG2}-29','${ARG2}-30','${ARG2}-31') and cc_uniqueid not like 'Temporary%'

[GET_EXTENSION_DAILY_PAID_SPLIT]
dsn=asterisk1,asterisk2
synopsis=Check how much the current extension has spent today
readsql=SELECT coalesce(sum(cc_cost),0) FROM cc_callcosts where cc_date='${ARG2}' and cc_peername='${ARG1}' and cc_international='${ARG3}' and cc_te_id='${ARG4}' and cc_uniqueid not like 'Temporary%'

[GET_TENANT_DAILY_PAID_SPLIT]
dsn=asterisk1,asterisk2
synopsis=Check how much the current tenant has spent today
readsql=SELECT coalesce(sum(cc_cost),0) FROM cc_callcosts where cc_date='${ARG2}' and cc_te_id='${ARG1}' and cc_international='${ARG3}' and cc_uniqueid not like 'Temporary%'

[GET_TENANT_MONTHLY_PAID_SPLIT]
dsn=asterisk1,asterisk2
synopsis=Check how much the current tenant has spent this month
readsql=SELECT coalesce(sum(cc_cost),0) FROM cc_callcosts where cc_te_id='${ARG1}' and cc_date in ('${ARG2}-01','${ARG2}-02','${ARG2}-03','${ARG2}-04','${ARG2}-05','${ARG2}-06','${ARG2}-07','${ARG2}-08','${ARG2}-09','${ARG2}-10','${ARG2}-11','${ARG2}-12','${ARG2}-13','${ARG2}-14','${ARG2}-15','${ARG2}-16','${ARG2}-17','${ARG2}-18','${ARG2}-19','${ARG2}-20','${ARG2}-21','${ARG2}-22','${ARG2}-23','${ARG2}-24','${ARG2}-25','${ARG2}-26','${ARG2}-27','${ARG2}-28','${ARG2}-29','${ARG2}-30','${ARG2}-31') and cc_international='${ARG3}' and cc_uniqueid not like 'Temporary%'

[GET_LATEST_CALLERID]
dsn=asteriskcdrdb1,asteriskcdrdb2
synopsis=Get the latest callerid
readsql=SELECT src,start FROM cdr where start>'${ARG2}' and accountcode='${ARG1}' and userfield like '%inbound%' and ( lastdst='${ARG4}' or wherelanded='${ARG4}' or dstchannel like '%${ARG3}%' ) order by start DESC limit 1

[GET_LATEST_DIALED]
dsn=asteriskcdrdb1,asteriskcdrdb2
synopsis=Get the latest dialed number
readsql=SELECT lastdst FROM cdr where start>'${ARG2}' and accountcode='${ARG1}' and userfield like '%outbound%' and realsrc='${ARG3}' order by start DESC limit 1

[BLACKLIST_CALLERID]
dsn=asterisk1,asterisk2
synopsis=Blacklist a callerid
writesql=INSERT IGNORE INTO bl_blacklists (bl_te_id,bl_callerid,bl_inserted,bl_reason) values ('${ARG1}','${VAL1}',NOW(),'Last call') 

[GET_PEERMAILBOX]
dsn=asterisk1,asterisk2
synopsis=Get the mailbox from the peer
readsql=SELECT mailbox from ex_extensions,sipfriends where ex_tech='SIP' and ex_tech_id=id and name='${ARG1}' union select mailboxes from ex_extensions,ps_endpoints where ex_tech='PJSIP' and ex_tech_id=tech_id and id='${ARG1}' union SELECT ve_mailbox from ex_extensions,ve_virtualextensions where ex_tech='VIRTUAL' and ex_tech_id=ve_id and ve_name='${ARG1}'

[GET_CONFERENCE_COST]
dsn=asterisk1,asterisk2
synopsis=Get the cost for the conference usage
readsql=SELECT coalesce(rr_setup+(rr_cost*ceil(cl_meetmesecs/rr_rounding)*rr_rounding/60),0) from  cl_conferenceroomlogs, rr_conferenceroomsrates, cr_conferencerooms where cr_rrid=rr_id and cl_cr_id=cr_id and cl_uniqueid='${ARG1}'

[GET_PROVIDER_FROM_PEERNAME]
dsn=asterisk1,asterisk2
synopsis=Get the trunk info from the trunk name
readsql=SELECT * from pr_providers where pr_peername='${ARG1}'

[SET_FLOWVARIABLE]
dsn=asterisk1,asterisk2
synopsis=Store the variable in the database
writesql=UPDATE fl_flows set fl_value='${VAL1}' where fl_variable_name='${ARG2}' and fl_te_id='${ARG1}'

[ALIGN_FLOWVARIABLE_STATE]
dsn=asterisk1,asterisk2
synopsis=Store the state in the database
writesql=INSERT INTO st_states (st_extension,st_state,st_timestamp) SELECT concat(fl_number,'-',te_code),case fl_value when fl_value_for_unavailable then 'UNAVAILABLE' when fl_value_for_ringing then 'RINGING' when fl_value_for_inuse then 'INUSE' when fl_value_for_notinuse then 'NOT_INUSE' else '' end as st_state,NOW() FROM `fl_flows`,te_tenants WHERE te_id='${ARG1}' and fl_te_id='${ARG1}' and fl_number<>'' and fl_variable_name='${VAL1}'  ON DUPLICATE KEY UPDATE st_state= ( SELECT case fl_value when fl_value_for_unavailable then 'UNAVAILABLE' when fl_value_for_ringing then 'RINGING' when fl_value_for_inuse then 'INUSE' when fl_value_for_notinuse then 'NOT_INUSE' else '' end as st_state FROM `fl_flows` WHERE fl_te_id='${ARG1}' and fl_variable_name='${VAL1}' )

[GET_VARIABLE_FROM_NAME]
dsn=asterisk1,asterisk2
synopsis=Get the variable info from the variable name
readsql=SELECT * from fl_flows where fl_te_id='${ARG1}' and fl_variable_name='${ARG2}'

[GET_VARIABLE_FROM_NUMBER]
dsn=asterisk1,asterisk2
synopsis=Get the variable info from the variable number
readsql=SELECT * from fl_flows where fl_te_id='${ARG1}' and fl_number='${ARG2}'

[SET_QUEUECALLBACK_RESULT]
dsn=asterisk1,asterisk2
synopsis=Set the queue call back result
writesql=UPDATE qc_queuecallbacks set qc_status='${VAL1}' where qc_id='${ARG1}' and qc_status<>'COMPLETED'

[DOUBLE_CHECK_CALLERID]
dsn=asterisk1,asterisk2
synopsis=Return the callerid if one of the allowed
readsql=select 'yes' from dual where '${ARG4}' in ( SELECT case when se_code='NATIONALDID' then concat('${ARG3}',di_area,di_number) when se_code='NATDIDNOTRUNK' then concat(di_area,di_number) when se_code='NATDIDNOTRUNK' then concat(di_area,di_number) when se_code='E164DID' then concat(di_country,di_area,di_number) when se_code='E164PLUSDID' then concat('+',di_country,di_area,di_number) when se_code='E164INTERNATIONAL' then concat ('${ARG2}',di_country,di_area,di_number) else NULL END as number FROM di_dids,se_settings where di_te_id='${ARG1}' and se_te_id=-1 and se_code in ('NATIONALDID','E164DID','E164PLUSDID','E164INTERNATIONAL','NATDIDNOTRUNK') and se_value='on' union select od_number from od_outbounddids where od_te_id='${ARG1}' and od_status='VERIFIED')

[IS_EMERGENCY_NUMBER]
dsn=asterisk1,asterisk2
synopsis=Return the callerid if one of the allowed
readsql=select 'yes' from dual where '${ARG4}' in ( SELECT case when se_code='NATIONALDID' then concat('${ARG3}',di_area,di_number) when se_code='NATDIDNOTRUNK' then concat(di_area,di_number) when se_code='NATDIDNOTRUNK' then concat(di_area,di_number) when se_code='E164DID' then concat(di_country,di_area,di_number) when se_code='E164PLUSDID' then concat('+',di_country,di_area,di_number) when se_code='E164INTERNATIONAL' then concat ('${ARG2}',di_country,di_area,di_number) else NULL END as number FROM di_dids,se_settings where di_te_id='${ARG1}' and se_te_id=-1 and se_code in ('NATIONALDID','E164DID','E164PLUSDID','E164INTERNATIONAL','NATDIDNOTRUNK') and se_value='on' and di_allowemergency='on' )

[GET_FIRST_DID]
dsn=asterisk1,asterisk2
synopsis=Return the first callerid allowed
readsql=SELECT case when se_code='NATIONALDID' then concat('${ARG3}',di_area,di_number) when se_code='NATDIDNOTRUNK' then concat(di_area,di_number) when se_code='E164DID' then concat(di_country,di_area,di_number) when se_code='E164PLUSDID' then concat('+',di_country,di_area,di_number) when se_code='E164INTERNATIONAL' then concat ('${ARG2}',di_country,di_area,di_number) else NULL END as number FROM di_dids,se_settings where di_te_id='${ARG1}' and se_te_id=-1 and se_code in ('NATIONALDID','E164DID','E164PLUSDID','E164INTERNATIONAL','NATDIDNOTRUNK') and se_value='on' order by number limit 1

;[GET_DEFAULT_MOH]
;dsn=asterisk1,asterisk2
;synopsis=Get the default MOH
;readsql=SELECT mu_id from mu_musiconholds where mu_te_id='${ARG1}' and mu_default='on'

[SET_FOLLOWME_MEDIA]
dsn=asterisk1,asterisk2
synopsis=Set the followme media files
writesql=UPDATE followme set ${ARG2}='${VAL1}' where name='${ARG1}'

[VERIFY_OUTBOUND_PIN]
dsn=asterisk1,asterisk2
synopsis=Verifiy outbound pin
readsql=SELECT pc_pin from pc_pincodes where pc_te_id='${ARG1}' and pc_pin='${ARG2}'

[LOCATE_NUMBER]
dsn=asterisk1,asterisk2
synopsis=Locate the type of number
readsql=SELECT ex_tech as tab, ex_id as code,ex_te_id as tenantid from ex_extensions where ex_te_id='${ARG1}' and ex_number='${ARG3}' union SELECT 'MEETME' as tab, cr_id as code,cr_te_id as tenantid from cr_conferencerooms where cr_te_id='${ARG1}' and cr_number='${ARG3}' union SELECT 'QUEUE' as tab, qu_id as code,qu_te_id as tenantid from qu_queues where qu_te_id='${ARG1}' and qu_number='${ARG3}' union SELECT 'HUNTLIST' as tab, hu_id as code,hu_te_id as tenantid from hu_huntlists where hu_te_id='${ARG1}' and hu_number='${ARG3}' union SELECT 'FLOW' as tab, fl_id as code,fl_te_id as tenantid from fl_flows where fl_te_id='${ARG1}' and fl_number='${ARG3}' union SELECT 'FLOW' as tab, fl_id as code,fl_te_id as tenantid from fl_flows,ex_extensions where fl_te_id='${ARG1}' and ex_te_id='${ARG1}' and replace(fl_number,'[EXT]',ex_number)='${ARG3}' union SELECT 'SHORT' as tab, sn_id as code,sn_te_id as tenantid from sn_shortnumbers where sn_te_id='${ARG1}' and sn_number='${ARG2}' union SELECT 'PARKING' as tab, pk_id as code,pk_te_id as tenantid from pk_parkinglots where pk_te_id='${ARG1}' and pk_start<=cast('${ARG3}' as unsigned) and pk_end>=cast('${ARG3}' as unsigned) union SELECT 'PAGING' as tab, pa_id as code,pa_te_id as tenantid from pa_paginggroups where pa_te_id='${ARG1}' and pa_number='${ARG3}' union SELECT 'CONDUIT' as tab, tc_id as code,tc_te_id as tenantid from tc_tenantconduits where tc_te_id='${ARG1}' and '${ARG3}' like concat(tc_prefix,'%') union SELECT 'FEATURE' as tab,fe_id as code,fe_te_id as tenantid from fe_features,ex_extensions,sipfriends where fe_te_id in ('${ARG1}',0) and ex_te_id='${ARG1}' and ex_tech='SIP' and sipfriends.id=ex_tech_id and (replace(fe_code,'[EXT]',ex_number) in ('${ARG3}','${ARG2}') OR replace(fe_code,'[EXT]',sipfriends.name)='${ARG2}' OR replace(replace(fe_code,'[EXT]',sipfriends.name),'-','')='${ARG2}') union SELECT 'FEATURE' as tab, fe_id as code,fe_te_id as tenantid from fe_features,ex_extensions,ve_virtualextensions where fe_te_id in ('${ARG1}',0) and ex_te_id='${ARG1}' and ex_tech='VIRTUAL' and ve_id=ex_tech_id and (replace(fe_code,'[EXT]',ex_number)='${ARG2}' OR replace(fe_code,'[EXT]',ve_name)='${ARG2}' ) union SELECT 'FEATURE' as tab, fe_id as code,fe_te_id as tenantid from fe_features where fe_te_id in ('${ARG1}',0) and '${ARG2}' like replace(fe_code,'[NUM]','%') and replace(fe_code,'[NUM]','')<>'${ARG2}' union SELECT 'FEATURE' as tab, fe_id as code,fe_te_id as tenantid from fe_features,fl_flows where fe_te_id in ('${ARG1}',0) and fl_te_id='${ARG1}' and replace(fe_code,'[EXT]',fl_number) in ('${ARG2}','${ARG3}') union SELECT 'VOICEMAIL' as tab,uniqueid as code,te_id as tenantid from voicemail where te_id='${ARG1}' and mailbox='${ARG3}' and '${ARG3}' not in ( SELECT ex_number from ex_extensions where ex_te_id='${ARG1}' and ex_number='${ARG3}' ) union SELECT 'FEATURE' as tab,fe_id as code,fe_te_id as tenantid from fe_features,voicemail where fe_te_id in ('${ARG1}',0) and te_id='${ARG1}' and (replace(fe_code,'[EXT]',mailbox)='${ARG3}') union SELECT 'FEATURE' as tab,fe_id as code,fe_te_id as tenantid from fe_features,sn_shortnumbers where fe_te_id in ('${ARG1}',0) and sn_te_id='${ARG1}' and replace(fe_code,'[EXT]',sn_number)='${ARG2}' order by tenantid desc limit 1

[GET_LOCAL_FULLCID_FROM_PEERNAME]
dsn=asterisk1,asterisk2
synopsis=Get full callerid and trunk setting
readsql=SELECT CASE ex_trunk WHEN 'on' THEN '' ELSE callerid END as callerid from ex_extensions,sipfriends where ex_tech='SIP' and ex_tech_id=id and name='${ARG1}'

[GET_OUTBOUND_CIDNUM_USING_REGEX_PEERNAME]
dsn=asterisk1,asterisk2
synopsis=Get callerid based on outbound callerid regex
readsql=SELECT rr_number from ex_extensions,sipfriends,rr_calleridregex_rules where ex_tech='SIP' and ex_tech_id=id and name='${ARG1}' and ex_cr_id=rr_cr_id and '${ARG2}' REGEXP rr_regex union SELECT rr_number from ex_extensions,ve_virtualextensions,rr_calleridregex_rules where ex_tech='VIRTUAL' and ex_tech_id=ve_id and ve_name='${ARG1}' and ex_cr_id=rr_cr_id and '${ARG2}' REGEXP rr_regex union SELECT rr_number from ex_extensions,ce_customextensions,rr_calleridregex_rules where ex_tech='CUSTOM' and ex_tech_id=ce_id and ce_peername='${ARG1}' and ex_cr_id=rr_cr_id and '${ARG2}' REGEXP rr_regex 

[GET_SHORTNUMBER_ID_DESTINATION]
dsn=asterisk1,asterisk2
synopsis=Get destination for shortnumber
readsql=SELECT sn_destnumber from sn_shortnumbers where sn_te_id='${ARG1}' and sn_id='${ARG2}'

[GET_EXT_DETAILS_FROM_PEERNAME]
dsn=asterisk1,asterisk2
synopsis=Get all extensions details from peername
readsql=select ex_id,ex_te_id,ex_name,ex_tech,ex_number,ex_trunk,ex_tech_id,ex_cidnum,ex_cidname,ex_blockcid,ex_prefix,ex_regexprefix,ex_fmfmstatus,ex_fmfmnumber,ex_fmfmdialtimeout,ex_fmfmdialmethod,ex_fmfmcallerid,ex_fmfmnumprefix,ex_fmfmnameprefix,ex_recording,ex_dialtimeout,ex_unconditionalstatus,ex_onbusystatus,ex_onofflinestatus,ex_onnoanswerstatus,ex_callallowed,ex_destallowregex,ex_rp_id,ex_dnd,ex_emergencycidnum,ex_emailrecording,ex_fmfmconfirm,ex_fmfmconfirmmessage_id,ex_minemailrecording,ex_trunkcidoverride,ex_trunkemergencycidoverride,ex_trunkcidsource,ex_fmfmholdmessage_id,ex_notifymissingemail,ex_txvolume,ex_rxvolume,ex_mu_id,ex_faxgateway,ex_faxalert,ex_trunkdid,ex_fmfmdelay,ex_abusedetection,ex_callwaiting,ex_costlimits,ex_dailycostlimit,ex_dailycostlimitdom,ex_dailycostlimitint,ex_routecostlimit,ex_dailycostwarning,ex_dailycostwarningdom,ex_dailycostwarningint,ex_cl_id,sipfriends.language as language,ex_fromfilterregex,ex_spygroups from ex_extensions,sipfriends where ex_tech='SIP' and ex_tech_id=id and name='${ARG1}' union select ex_id,ex_te_id,ex_name,ex_tech,ex_number,ex_trunk,ex_tech_id,ex_cidnum,ex_cidname,ex_blockcid,ex_prefix,ex_regexprefix,ex_fmfmstatus,ex_fmfmnumber,ex_fmfmdialtimeout,ex_fmfmdialmethod,ex_fmfmcallerid,ex_fmfmnumprefix,ex_fmfmnameprefix,ex_recording,ex_dialtimeout,ex_unconditionalstatus,ex_onbusystatus,ex_onofflinestatus,ex_onnoanswerstatus,ex_callallowed,ex_destallowregex,ex_rp_id,ex_dnd,ex_emergencycidnum,ex_emailrecording,ex_fmfmconfirm,ex_fmfmconfirmmessage_id,ex_minemailrecording,ex_trunkcidoverride,ex_trunkemergencycidoverride,ex_trunkcidsource,ex_fmfmholdmessage_id,ex_notifymissingemail,ex_txvolume,ex_rxvolume,ex_mu_id,ex_faxgateway,ex_faxalert,ex_trunkdid,ex_fmfmdelay,ex_abusedetection,ex_callwaiting,ex_costlimits,ex_dailycostlimit,ex_dailycostlimitdom,ex_dailycostlimitint,ex_routecostlimit,ex_dailycostwarning,ex_dailycostwarningdom,ex_dailycostwarningint,ex_cl_id,'' as language,ex_fromfilterregex,ex_spygroups from ex_extensions,ve_virtualextensions where ex_tech='VIRTUAL' and ex_tech_id=ve_id and ve_name='${ARG1}' union select ex_id,ex_te_id,ex_name,ex_tech,ex_number,ex_trunk,ex_tech_id,ex_cidnum,ex_cidname,ex_blockcid,ex_prefix,ex_regexprefix,ex_fmfmstatus,ex_fmfmnumber,ex_fmfmdialtimeout,ex_fmfmdialmethod,ex_fmfmcallerid,ex_fmfmnumprefix,ex_fmfmnameprefix,ex_recording,ex_dialtimeout,ex_unconditionalstatus,ex_onbusystatus,ex_onofflinestatus,ex_onnoanswerstatus,ex_callallowed,ex_destallowregex,ex_rp_id,ex_dnd,ex_emergencycidnum,ex_emailrecording,ex_fmfmconfirm,ex_fmfmconfirmmessage_id,ex_minemailrecording,ex_trunkcidoverride,ex_trunkemergencycidoverride,ex_trunkcidsource,ex_fmfmholdmessage_id,ex_notifymissingemail,ex_txvolume,ex_rxvolume,ex_mu_id,ex_faxgateway,ex_faxalert,ex_trunkdid,ex_fmfmdelay,ex_abusedetection,ex_callwaiting,ex_costlimits,ex_dailycostlimit,ex_dailycostlimitdom,ex_dailycostlimitint,ex_routecostlimit,ex_dailycostwarning,ex_dailycostwarningdom,ex_dailycostwarningint,ex_cl_id,ps_endpoints.language as language,ex_fromfilterregex,ex_spygroups from ex_extensions,ps_endpoints where ex_tech='PJSIP' and ex_tech_id=tech_id and id='${ARG1}' union select ex_id,ex_te_id,ex_name,ex_tech,ex_number,ex_trunk,ex_tech_id,ex_cidnum,ex_cidname,ex_blockcid,ex_prefix,ex_regexprefix,ex_fmfmstatus,ex_fmfmnumber,ex_fmfmdialtimeout,ex_fmfmdialmethod,ex_fmfmcallerid,ex_fmfmnumprefix,ex_fmfmnameprefix,ex_recording,ex_dialtimeout,ex_unconditionalstatus,ex_onbusystatus,ex_onofflinestatus,ex_onnoanswerstatus,ex_callallowed,ex_destallowregex,ex_rp_id,ex_dnd,ex_emergencycidnum,ex_emailrecording,ex_fmfmconfirm,ex_fmfmconfirmmessage_id,ex_minemailrecording,ex_trunkcidoverride,ex_trunkemergencycidoverride,ex_trunkcidsource,ex_fmfmholdmessage_id,ex_notifymissingemail,ex_txvolume,ex_rxvolume,ex_mu_id,ex_faxgateway,ex_faxalert,ex_trunkdid,ex_fmfmdelay,ex_abusedetection,ex_callwaiting,ex_costlimits,ex_dailycostlimit,ex_dailycostlimitdom,ex_dailycostlimitint,ex_routecostlimit,ex_dailycostwarning,ex_dailycostwarningdom,ex_dailycostwarningint,ex_cl_id,'' as language,ex_fromfilterregex,ex_spygroups from ex_extensions,ce_customextensions where ex_tech='CUSTOM' and ex_tech_id=ce_id and ce_peername='${ARG1}'

[GET_ALL_TENANT_INFO_FROM_ID]
dsn=asterisk1,asterisk2
synopsis=Get all tenant info from id
readsql=select * from te_tenants where te_id='${ARG1}'

[SET_RESERVATION]
dsn=asterisk1,asterisk2
synopsis=Set the reservation for a call
writesql=INSERT INTO cr_callreservations(cr_te_id,cr_caller,cr_called,cr_datetime) values ('${ARG1}','${ARG2}','${VAL1}',NOW())

[LOG_SMS]
dsn=asterisk1,asterisk2
synopsis=Log the route of a SMS
writesql=INSERT INTO sm_smslogs(sm_te_id,sm_date,sm_direction,sm_sourceraw,sm_destraw,sm_from,sm_to,sm_body,sm_fullresult,sm_response) values ('${ARG1}',NOW(),'${ARG2}','${ARG3}','${ARG4}','${ARG5}','${ARG6}','${SQL_ESC(${ARG7})}','${SQL_ESC(${ARG8})}','${SQL_ESC(${VAL1})}')

[PLAN_SMS]
dsn=asterisk1,asterisk2
synopsis=Log the route of a SMS
writesql=INSERT INTO ss_smstobesent(ss_te_id,ss_submitted,ss_name,ss_messagefrom,ss_messagebody) values ('${ARG1}',NOW(),'${ARG2}','${SQL_ESC(${ARG3})}','${SQL_ESC(${VAL1})}')

[GET_EMAILTEMPLATE]
dsn=asterisk1,asterisk2
synopsis=Get the id for a template
readsql=select et_id from et_emailtemplates where et_te_id in ('${ARG1}',0) and et_type='${ARG2}' and et_active='on' order by et_te_id DESC limit 1

[CREATE_EMAILTEMPLATE]
dsn=asterisk1,asterisk2
synopsis=Create an email template
writesql=INSERT INTO et_emailtemplates (et_te_id,et_name,et_type,et_active,et_sendername,et_senderemail,et_subject,et_text,et_language) values ('${ARG1}','${ARG2}','${ARG3}','${ARG4}','${ARG5}','${ARG6}','${ARG7}','${ARG8}','${ARG9}')

[REMOVE_EXTENSION_FROM_VIRTUALEXTENSION]
dsn=asterisk1,asterisk2
synopsis=Remove an extension from a virtual extension
writesql=delete from vi_virtualextensionitems where vi_te_id='${ARG1}' and vi_ve_id='${ARG2}' and vi_name='${VAL1}'

[REMOVE_ALL_EXTENSIONS_FROM_VIRTUALEXTENSION]
dsn=asterisk1,asterisk2
synopsis=Remove an extension from a virtual extension
writesql=delete from vi_virtualextensionitems where vi_te_id='${ARG1}' and vi_ve_id='${VAL1}'

[ADD_EXTENSION_TO_VIRTUALEXTENSION]
dsn=asterisk1,asterisk2
synopsis=Remove an extension from a virtual extension
writesql=insert into vi_virtualextensionitems (vi_te_id,vi_ve_id,vi_ex_id,vi_name) values ('${ARG1}','${ARG2}','${ARG3}','${VAL1}')

[RESET_CALLERID_TO_VIRTUALEXTENSION_ITEM]
dsn=asterisk1,asterisk2
synopsis=Reset the callerid to virtualextension items
writesql=update sipfriends,ex_extensions set callerid = concat(ex_name,' <',ex_number,'>' ) where ex_autocallerid='on' and id=ex_tech_id and ex_tech='SIP' and name = '${ARG1}' and te_id>0

[ASSIGN_CALLERID_TO_VIRTUALEXTENSION_ITEM]
dsn=asterisk1,asterisk2
synopsis=Assign the callerid to virtualextension items
writesql=update sipfriends,ex_extensions set callerid = (select concat(ex_name,' <',ex_number,'>') from ex_extensions where ex_tech_id='${VAL1}' and ex_tech='VIRTUAL' )  where ex_autocallerid='on' and id=ex_tech_id and ex_tech='SIP' and ex_id = '${ARG1}' and te_id>0

[GET_PEERNAME_FROM_CHANNEL]
dsn=asterisk1,asterisk2
synopsis=Try to get the peername from the channel name
readsql=select name from sipfriends where '${ARG1}' like concat('SIP/',name,'-%')

[RESET_ALL_CAMPAIGNNUMBERS]
dsn=asterisk1,asterisk2
synopsis=Reset all campaign number dialing
writesql=update cn_campaignnumbers set cn_attempts=0, cn_lastattempt='', cn_billsec=0, cn_disposition='' where cn_ca_id='${VAL1}' and cn_te_id='${ARG1}'

[RESET_ALL_NOT_ANSWERED_CAMPAIGNNUMBERS]
dsn=asterisk1,asterisk2
synopsis=Reset all not answered campaign number dialing
writesql=update cn_campaignnumbers set cn_attempts=0, cn_lastattempt='', cn_billsec=0, cn_disposition='' where cn_ca_id='${VAL1}' and cn_te_id='${ARG1}' and cn_disposition<>'ANSWER'

[SET_CAMPAIGN_STATE]
dsn=asterisk1,asterisk2
synopsis=Start a campaign
writesql=update ca_campaigns set ca_state='${VAL1}' where ca_id='${ARG2}' and ca_te_id='${ARG1}'

[GET_CALLRATE]
dsn=asterisk1,asterisk2
synopsis=Get the callrate details
readsql=select * from cl_clientrates where cl_id='${ARG1}'

[ADD_NUMBER_TO_DNC_LIST]
dsn=asterisk1,asterisk2
synopsis=Add a number to a DNC List
writesql=insert into dl_dnc_numbers(dl_te_id,dl_dc_id,dl_number,dl_description) values ('${ARG1}','${ARG2}','${VAL1}','${SQL_ESC(${ARG3})}')

[CHECK_IF_IN_DNC_LIST]
dsn=asterisk1,asterisk2
synopsis=Check if a number is in DNC
readsql=select count(*) from dl_dnc_numbers where dl_te_id in (0,'${ARG1}') and dl_number='${ARG2}' and dl_dc_id='${ARG3}'

[LOG_IVR_ACTION]
dsn=asterisk1,asterisk2
synopsis=Add info about IVR processing
writesql=insert into il_ivr_logs(il_te_id,il_iv_id,il_cdr_start,il_cdr_uniqueid,il_start,il_end,il_option,il_disa,il_action,il_clid,il_firstdst) values ('${ARG1}','${ARG2}','${ARG3}','${ARG4}','${ARG5}','${ARG6}','${ARG7}','${ARG8}','${VAL1}','${SQL_ESC(${ARG9})}','${SQL_ESC(${ARG10})}')

[GET_ALL_LANGUAGE_INFO_FROM_DIRECTORY]
dsn=asterisk1,asterisk2
synopsis=Get all language info
readsql=select * from la_languages where la_directory='${ARG1}'

[LOG_QUEUECALLBACK_REQUEST]
dsn=asterisk1,asterisk2
synopsis=Add a request for callback
writesql=insert into queue_log(time,callid,queuename,event,data2) values(NOW(),'${VAL1}','${ARG1}','QUEUECALLBACK','${ARG2}')

[COUNT_ABUSEDETECTION_SAMECALL]
dsn=asterisk1,asterisk2
synopsis=Count number of identical calls
readsql=select count(*) from ld_lastnumbersdialed where ld_peername='${ARG1}' and ld_number='${ARG2}' and ld_datetime>NOW() - INTERVAL ${ARG3} SECOND

[LOGCALL_FOR_ABUSEDETECTION]
dsn=asterisk1,asterisk2
synopsis=Log an international call dialed
writesql=insert into ld_lastnumbersdialed(ld_peername,ld_number) values ('${ARG1}','${VAL1}')

[RESETLOGCALL_FOR_ABUSEDETECTION]
dsn=asterisk1,asterisk2
synopsis=Reset log for international call dialed
writesql=delete from ld_lastnumbersdialed where ld_peername='${ARG1}'

[GET_EXTERNAL_AGENT]
dsn=asterisk1,asterisk2
synopsis=Get External Agent details
readsql=select * from ea_externalagents where ea_id='${ARG1}'

[GET_EXTERNAL_AGENT_BYNUMBER]
dsn=asterisk1,asterisk2
synopsis=Get External Agent details by number
readsql=select * from ea_externalagents where ea_number='${ARG1}' and ea_te_id='${ARG2}'

[INSERT_EXTERNAL_AGENT]
dsn=asterisk1,asterisk2
synopsis=insert a new external agent
writesql=insert into ea_externalagents(ea_te_id,ea_number) values ('${ARG1}','${VAL1}')

[CHECK_CALLBACK_CALLERID]
dsn=asterisk1,asterisk2
synopsis=Check if the callerid is allowed for callback
readsql=select count(*) from ce_customs_extended where ce_cu_id='${ARG1}' and '${ARG2}' like ce_param1

[GET_SECRET_OUTBOUNDDID_CODE]
dsn=asterisk1,asterisk2
synopsis=Get the secret code for outbound did verification
readsql=select od_secret from od_outbounddids where od_id='${ARG1}' and od_te_id='${ARG2}'

[SET_OUTBOUNDID_STATUS]
dsn=asterisk1,asterisk2
synopsis=Set the status for outbound did verification
writesql=update od_outbounddids set od_status='${VAL1}' where od_id='${ARG1}' and od_te_id='${ARG2}'

[LOG_RESPONSE_PATH]
dsn=asterisk1,asterisk2
synopsis=Set the response path data
writesql=insert into rr_responsepathresponses(rr_te_id,rr_rp_id,rr_uniqueid,rr_type,rr_type_id,rr_type_name,rr_value_name,rr_value) values ('${ARG1}','${ARG2}','${ARG3}','${ARG4}','${ARG5}','${SQL_ESC(${ARG6})}','${SQL_ESC(${ARG7})}','${SQL_ESC(${VAL1})}')

[LOG_CALL_START_SIMPLE]
dsn=asteriskcdrdb1,asteriskcdrdb2
synopsis=Log the start of the call
writesql=insert into sc_simplecdr(sc_te_id,sc_start,sc_direction,sc_calleridnum,sc_calleridname,sc_dialednum,sc_disposition,sc_uniqueid) values ('${ARG1}',NOW(),'${ARG2}','${SQL_ESC(${ARG3})}','${SQL_ESC(${ARG4})}','${SQL_ESC(${ARG5})}','ACTIVE','${VAL1}')

[LOG_CALL_END_SIMPLE]
dsn=asteriskcdrdb1,asteriskcdrdb2
synopsis=Log the end of the call
writesql=update sc_simplecdr set sc_disposition=case sc_disposition when 'ACTIVE' then '${ARG1}' ELSE least('${ARG1}',sc_disposition) END,sc_duration=timestampdiff(SECOND,sc_start,NOW()) where sc_uniqueid='${VAL1}'

[UPDATE_DIR_LOG_CALL_SIMPLE]
dsn=asteriskcdrdb1,asteriskcdrdb2
synopsis=Log an external call
writesql=update sc_simplecdr set sc_direction='${ARG2}' where sc_uniqueid='${VAL1}'

[GET_PARKINGLOT_RETURN_NUMBER]
dsn=asterisk1,asterisk2
synopsis=Get the parking lot return number
readsql=select pr_dialback from pr_parkinglotsreturns where pr_number='${ARG2}' and pr_te_id='${ARG1}'

[SET_BLOCK_CALLERID]
dsn=asterisk1,asterisk2
synopsis=Set the Block Callerid for the extension
writesql=UPDATE ex_extensions SET ex_blockcid='${VAL1}' where ( ex_tech='SIP' and ex_tech_id in ( SELECT id from sipfriends where name='${ARG1}' ) ) or ( ex_tech='VIRTUAL' and ex_tech_id in ( SELECT ve_id from ve_virtualextensions where ve_name='${ARG1}' ) )  or ( ex_tech='CUSTOM' and ex_tech_id in ( SELECT ce_id from ce_customextensions where ce_peername='${ARG1}' ) ) or ( ex_tech='PJSIP' and ex_tech_id in ( SELECT tech_id from ps_endpoints where id='${ARG1}' ) ) 

[SET_ESCALATION_RESPONSE]
dsn=asterisk1,asterisk2
synopsis=Set the Voicemail Escalation Response
writesql=UPDATE vl_voicemaillog SET vl_response='${VAL1}' where vl_id='${ARG1}'

[LOCATE_CUSTOMEXTENSION]
dsn=asterisk1,asterisk2
synopsis=Set the Voicemail Escalation Response
readsql=select ex_number,ex_name,ex_te_id,te_code,ce_peername from ex_extensions,ce_customextensions,te_tenants,cp_customextensionproviders where ex_te_id=te_id and ex_tech='CUSTOM' and ex_tech_id=ce_id and ex_id=cp_ex_id and ex_te_id=ce_te_id and cp_pr_id='${ARG1}' and ce_auth_type='CALLERID' and ce_auth_parameter='${SQL_ESC(${ARG2})}'

[GET_MD5_EX_DBN]
dsn=asterisk1,asterisk2
synopsis=Get the media file for Dial by name
readsql=select me_md5 from me_mediafiles where me_id in ( select ex_dn_me_id from ex_extensions where ex_te_id='${ARG1}' and ex_id='${ARG2}' )

[GET_EXTENSION_DN_ME_ID]
dsn=asterisk1,asterisk2
synopsis=Get the media file id for Dial by name from extension
readsql=select ex_dn_me_id from ex_extensions where ex_id='${ARG1}'

[SET_EXTENSION_DN_ME_ID]
dsn=asterisk1,asterisk2
synopsis=Set the media file id for Dial by name from extension
writesql=update ex_extensions set ex_dn_me_id='${VAL1}' where ex_id='${ARG1}'

[SET_EXTENSION_UNCONDITIONAL]
dsn=asterisk1,asterisk2
synopsis=Set the unconditional flag for extension
writesql=update ex_extensions set ex_unconditionalstatus='${VAL1}' where ex_id='${ARG1}'

