// Escapes special characters in the txt, so it is safe to place returned string
// to Query method.
func (my *Conn) Escape(txt string) string {
return mysql.Escape(my, txt)
}
func getAgents() map[string]JSONAgentInfo {
output := make(map[string]JSONAgentInfo)
var callerids string = ""
cidrows, cidres, err := dbConn.Query("SELECT callerid FROM vicidial_auto_calls")
checkErr(err)
for _, cidrow := range cidrows {
callerids += cidrow.Str(cidres.Map("callerid")) + "|"
}
rows, res, err := dbConn.Query(`
SELECT
extension,
vicidial_live_agents.user,
conf_exten,
vicidial_live_agents.status,
vicidial_live_agents.server_ip,
UNIX_TIMESTAMP(last_call_time) as last_call_time,
UNIX_TIMESTAMP(last_call_finish) as last_call_finish,
call_server_ip,
vicidial_live_agents.campaign_id,
vicidial_users.user_group,
vicidial_users.full_name,
vicidial_live_agents.comments,
vicidial_live_agents.calls_today,
vicidial_live_agents.callerid,
lead_id,
UNIX_TIMESTAMP(last_state_change) as last_state_change,
on_hook_agent,
ring_callerid,
agent_log_id
FROM
vicidial_live_agents,
vicidial_users
WHERE
vicidial_live_agents.user = vicidial_users.user
`)
checkErr(err)
for _, row := range rows {
agent := JSONAgentInfo{}
agent.Status = row.Str(res.Map("status"))
if row.Str(res.Map("on_hook_agent")) == "Y" {
agent.Status = "RING"
}
// 3-way check
if row.Int(res.Map("lead_id")) != 0 {
threeRows, _, err := dbConn.Query(`
SELECT
UNIX_TIMESTAMP(last_call_time)
FROM
vicidial_live_agents
WHERE
lead_id = '%d'
AND
status = 'INCALL'
ORDER BY UNIX_TIMESTAMP(last_call_time) DESC
`, row.Int(res.Map("lead_id")))
checkErr(err)
if len(threeRows) > 1 {
agent.Status = "3-WAY"
}
}
pause_code := ""
epoch_sec := 0
if isTrue, _ := regexp.MatchString("(?i)READY|PAUSED", row.Str(res.Map("status"))); isTrue {
epoch_sec = row.Int(res.Map("last_state_change"))
if row.Int(res.Map("lead_id")) > 0 {
agent.Status = "DISPO"
} else {
subRows, subRes, err := dbConn.Query("SELECT sub_status FROM vicidial_agent_log WHERE agent_log_id >= '%s' AND user = '******' ORDER BY agent_log_id DESC LIMIT 1", mysql.Escape(dbConn, row.Str(res.Map("agent_log_id"))), mysql.Escape(dbConn, row.Str(res.Map("user"))))
checkErr(err)
if len(subRows) == 1 {
pause_code = subRows[0].Str(subRes.Map("sub_status"))
}
}
} else {
epoch_sec = row.Int(res.Map("last_call_time"))
}
if isTrue, _ := regexp.MatchString("(?i)INCALL", row.Str(res.Map("status"))); isTrue {
incallRows, incallRes, err := dbConn.Query("SELECT UNIX_TIMESTAMP(parked_time) AS pt FROM parked_channels WHERE channel_group = '%s'", mysql.Escape(dbConn, row.Str(res.Map("callerid"))))
checkErr(err)
if len(incallRows) > 0 {
agent.Status = "PARK"
epoch_sec = incallRows[0].Int(incallRes.Map("pt"))
} else {
if isTrue2, _ := regexp.MatchString(regexp.QuoteMeta(row.Str(res.Map("callerid"))+"|"), callerids); !isTrue2 {
epoch_sec = row.Int(res.Map("last_state_change"))
agent.Status = "DEAD"
}
}
}
seconds := (int(time.Now().Unix()) - epoch_sec)
switch agent.Status {
case "DISPO":
agent.Colour = "8e44ad"
case "QUEUE":
agent.Colour = "9b59b6"
case "INCALL":
if row.Str(res.Map("comments")) == "INBOUND" {
agent.Colour = "446CB3"
} else {
// Outbound call.
agent.Colour = "22A7F0"
agent.Status = "OUTBND"
}
case "PARK":
agent.Colour = "E87E04"
case "DEAD":
agent.Colour = "004D86"
agent.Status = "GONE"
case "3-WAY":
agent.Colour = "1abc9c"
case "RING":
agent.Colour = "16a085"
case "PAUSED":
switch pause_code {
case "TLAUTH":
agent.Colour = "D2527F"
agent.Status = "TLAUTH"
case "LUNCH":
agent.Colour = "EB974E"
agent.Status = "LUNCH"
case "BREAK":
agent.Colour = "96281B"
agent.Status = "BREAK"
case "COMBR":
agent.Colour = "F5D76E"
agent.Status = "COMFORT"
case "TRADEV":
agent.Colour = "E08283"
agent.Status = "TRADEV"
break
case "LOGIN":
agent.Colour = "000000"
agent.Status = "LOGIN"
default:
if seconds > 300 {
agent.Colour = "C0392B"
} else {
agent.Colour = "F22613"
}
}
case "CLOSER":
agent.Status = "READY (C)"
agent.Colour = "27ae60"
case "READY":
agent.Colour = "27ae60"
default:
agent.Colour = "D2BEAA"
}
agent.User = row.Str(res.Map("user"))
agent.Conf_exten = row.Str(res.Map("conf_exten"))
agent.Seconds = seconds
agent.Campaign_id = row.Str(res.Map("campaign_id"))
agent.User_group = row.Str(res.Map("user_group"))
agent.Full_name = row.Str(res.Map("full_name"))
agent.Calls_today = row.Int(res.Map("calls_today"))
agent.Lead_id = row.Int(res.Map("lead_id"))
output[row.Str(res.Map("extension"))] = agent
}
return output
}
func getQueues() map[string]JSONQueueInfo {
output := make(map[string]JSONQueueInfo)
start_time := int(time.Now().Unix())
output["global"] = getGlobalQueue(start_time)
campaignRows, campaignRes, err := dbConn.Query(`
SELECT
campaign_id,
campaign_name,
closer_campaigns
FROM
vicidial_campaigns
WHERE
active='Y'
`)
checkErr(err)
for _, campaignRow := range campaignRows {
queue := JSONQueueInfo{}
queue.Name = campaignRow.Str(campaignRes.Map("campaign_name"))
inbound_list := s.Split(mysql.Escape(dbConn, s.TrimSpace(s.Replace(campaignRow.Str(campaignRes.Map("closer_campaigns")), "-", "", -1))), " ")
callRows, callRes, err := dbConn.Query(`
SELECT
status,
campaign_id,
phone_number,
server_ip,
UNIX_TIMESTAMP(call_time) as call_time,
call_type,
queue_priority,
agent_only
FROM
vicidial_auto_calls
WHERE
status IN ('LIVE')
AND
call_type='IN'
AND
campaign_id IN ('` + s.Join(inbound_list, "', '") + `')
ORDER BY
call_time ASC
`)
checkErr(err)
if len(callRows) > 0 {
queue.Number = len(callRows)
queue.Wait_time = (start_time - callRows[0].Int(callRes.Map("call_time")))
} else {
queue.Number = 0
queue.Wait_time = 0
}
queue.Number_colour = number_colour(queue.Number)
queue.Wait_time_colour = wait_time_colour(queue.Wait_time)
tFormat := "2006-01-02"
tNow := time.Now()
tBegin := tNow.Format(tFormat) + " 00:00:00"
tEnd := tNow.Format(tFormat) + " 23:59:59"
var slaTotal float64 = 0
slaTotalRows, slaTotalRes, err := dbConn.Query(`
SELECT
COUNT(*) as 'total'
FROM
vicidial_closer_log
WHERE
status NOT IN ( 'INCALL', 'AFTHRS' )
AND
call_date BETWEEN '%s' AND '%s'
AND
campaign_id IN ('`+s.Join(inbound_list, "', '")+`')
`, mysql.Escape(dbConn, tBegin), mysql.Escape(dbConn, tEnd))
checkErr(err)
if len(slaTotalRows) > 0 {
slaTotal = float64(slaTotalRows[0].Int(slaTotalRes.Map("total")))
} else {
slaTotal = 0
}
if slaTotal > 0 {
slaMetRows, slaMetRes, err := dbConn.Query(`
SELECT
COUNT(*) as 'total'
FROM
vicidial_closer_log
WHERE
status NOT IN ( 'INCALL', 'AFTHRS' )
AND
call_date BETWEEN '%s' AND '%s'
AND
queue_seconds < %d
AND
campaign_id IN ('`+s.Join(inbound_list, "', '")+`')
`, mysql.Escape(dbConn, tBegin), mysql.Escape(dbConn, tEnd), slaSeconds)
checkErr(err)
if len(slaMetRows) > 0 {
queue.Met_SLA_Percentage = Round(((float64(slaMetRows[0].Int(slaMetRes.Map("total"))) / slaTotal) * 100), .5, 0)
} else {
queue.Met_SLA_Percentage = 0
}
slaDropRows, slaDropRes, err := dbConn.Query(`
SELECT
COUNT(*) as 'total'
FROM
vicidial_closer_log
WHERE
status = 'DROP'
AND
call_date BETWEEN '%s' AND '%s'
AND
campaign_id IN ('`+s.Join(inbound_list, "', '")+`')
`, mysql.Escape(dbConn, tBegin), mysql.Escape(dbConn, tEnd))
checkErr(err)
if len(slaDropRows) > 0 {
queue.Drop_SLA_Percentage = Round(((float64(slaDropRows[0].Int(slaDropRes.Map("total"))) / slaTotal) * 100), .5, 0)
} else {
queue.Drop_SLA_Percentage = 0
}
} else {
queue.Met_SLA_Percentage = 100
queue.Drop_SLA_Percentage = 0
}
queue.Met_SLA_Colour = sla_met_colour(queue.Met_SLA_Percentage)
queue.Drop_SLA_Colour = sla_drop_colour(queue.Drop_SLA_Percentage)
output[campaignRow.Str(campaignRes.Map("campaign_id"))] = queue
}
return output
}
func getGlobalQueue(start_time int) JSONQueueInfo {
globalRows, globalRes, err := dbConn.Query(`
SELECT
status,
campaign_id,
phone_number,
server_ip,
UNIX_TIMESTAMP(call_time) as call_time,
call_type,
queue_priority,
agent_only
FROM
vicidial_auto_calls
WHERE
status IN ('LIVE')
AND
call_type='IN'
ORDER BY
call_time ASC
`)
checkErr(err)
globalQueue := JSONQueueInfo{}
globalQueue.Name = "Global Queue Name"
if len(globalRows) > 0 {
globalQueue.Number = len(globalRows)
globalQueue.Wait_time = (start_time - globalRows[0].Int(globalRes.Map("call_time")))
} else {
globalQueue.Number = 0
globalQueue.Wait_time = 0
}
globalQueue.Number_colour = number_colour(globalQueue.Number)
globalQueue.Wait_time_colour = wait_time_colour(globalQueue.Wait_time)
tFormat := "2006-01-02"
tNow := time.Now()
tBegin := tNow.Format(tFormat) + " 00:00:00"
tEnd := tNow.Format(tFormat) + " 23:59:59"
var slaTotal float64 = 0
slaTotalRows, slaTotalRes, err := dbConn.Query(`
SELECT
COUNT(*) as 'total'
FROM
vicidial_closer_log
WHERE
status NOT IN ( 'INCALL', 'AFTHRS' )
AND
call_date BETWEEN '%s' AND '%s'
`, mysql.Escape(dbConn, tBegin), mysql.Escape(dbConn, tEnd))
checkErr(err)
if len(slaTotalRows) > 0 {
slaTotal = float64(slaTotalRows[0].Int(slaTotalRes.Map("total")))
} else {
slaTotal = 0
}
if slaTotal > 0 {
slaMetRows, slaMetRes, err := dbConn.Query(`
SELECT
COUNT(*) as 'total'
FROM
vicidial_closer_log
WHERE
status NOT IN ( 'INCALL', 'AFTHRS' )
AND
call_date BETWEEN '%s' AND '%s'
AND
queue_seconds < %d
`, mysql.Escape(dbConn, tBegin), mysql.Escape(dbConn, tEnd), slaSeconds)
checkErr(err)
if len(slaMetRows) > 0 {
globalQueue.Met_SLA_Percentage = Round(((float64(slaMetRows[0].Int(slaMetRes.Map("total"))) / slaTotal) * 100), .5, 0)
} else {
globalQueue.Met_SLA_Percentage = 0
}
slaDropRows, slaDropRes, err := dbConn.Query(`
SELECT
COUNT(*) as 'total'
FROM
vicidial_closer_log
WHERE
status = 'DROP'
AND
call_date BETWEEN '%s' AND '%s'
`, mysql.Escape(dbConn, tBegin), mysql.Escape(dbConn, tEnd))
checkErr(err)
if len(slaDropRows) > 0 {
globalQueue.Drop_SLA_Percentage = Round(((float64(slaDropRows[0].Int(slaDropRes.Map("total"))) / slaTotal) * 100), .5, 0)
} else {
globalQueue.Drop_SLA_Percentage = 0
}
} else {
globalQueue.Met_SLA_Percentage = 100
globalQueue.Drop_SLA_Percentage = 0
}
globalQueue.Met_SLA_Colour = sla_met_colour(globalQueue.Met_SLA_Percentage)
globalQueue.Drop_SLA_Colour = sla_drop_colour(globalQueue.Drop_SLA_Percentage)
return globalQueue
}
func (c *Conn) Escape(txt string) string {
return mysql.Escape(c, txt)
}