Example #1
0
// 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
}
Example #5
0
func (c *Conn) Escape(txt string) string {
	return mysql.Escape(c, txt)
}