/*
spark draws an svg spark line to b. Assumes f.loadPK has been called first.
*/
func dataCompletenessSpark(siteID, typeID string, b *bytes.Buffer) *weft.Result {
var p ts.Plot
p.SetXAxis(time.Now().UTC().Add(time.Hour*-12), time.Now().UTC())
var err error
var rows *sql.Rows
var expected int
var typePK int
if err = dbR.QueryRow(`SELECT typePK, expected FROM data.completeness_type WHERE typeID = $1`,
typeID).Scan(&typePK, &expected); err != nil {
if err == sql.ErrNoRows {
return &weft.NotFound
}
return weft.InternalServerError(err)
}
expectedf := float64(expected)
if rows, err = dbR.Query(`SELECT date_trunc('hour',time) as t, sum(count) FROM data.completeness
WHERE sitePK = (SELECT sitePK FROM data.site WHERE siteID = $1)
AND typePK = $2
AND time > now() - interval '28 days'
GROUP BY date_trunc('hour',time)
ORDER BY t ASC`,
siteID, typePK); err != nil {
return weft.InternalServerError(err)
}
defer rows.Close()
var pts []ts.Point
for rows.Next() {
var pt ts.Point
var v int
if err = rows.Scan(&pt.DateTime, &v); err != nil {
return weft.InternalServerError(err)
}
// No need to scale spark data for display.
pt.Value = float64(v) / expectedf
pts = append(pts, pt)
}
if len(pts) > 0 {
p.AddSeries(ts.Series{Colour: "deepskyblue", Points: pts})
}
if err = ts.SparkLine.Draw(p, b); err != nil {
return weft.InternalServerError(err)
}
return &weft.StatusOK
}
/*
plot draws an svg plot to b.
Valid values for resolution are 'minute', 'five_minutes', 'hour'.
*/
func (f fieldMetric) plot(deviceID, typeID, resolution string, plotter ts.SVGPlot, b *bytes.Buffer) *weft.Result {
// we need the devicePK often so read it once.
var devicePK int
if err := dbR.QueryRow(`SELECT devicePK FROM field.device WHERE deviceID = $1`,
deviceID).Scan(&devicePK); err != nil {
if err == sql.ErrNoRows {
return &weft.NotFound
}
return weft.InternalServerError(err)
}
var typePK int
var scale float64
var display string
if err := dbR.QueryRow(`SELECT typePK, scale, display FROM field.type WHERE typeID = $1`,
typeID).Scan(&typePK, &scale, &display); err != nil {
if err == sql.ErrNoRows {
return &weft.NotFound
}
return weft.InternalServerError(err)
}
var p ts.Plot
p.SetUnit(display)
var rows *sql.Rows
var err error
var lower, upper int
if err := dbR.QueryRow(`SELECT lower,upper FROM field.threshold
WHERE devicePK = $1 AND typePK = $2`,
devicePK, typePK).Scan(&lower, &upper); err != nil && err != sql.ErrNoRows {
return weft.InternalServerError(err)
}
if !(lower == 0 && upper == 0) {
p.SetThreshold(float64(lower)*scale, float64(upper)*scale)
}
var tags []string
if rows, err = dbR.Query(`SELECT tag FROM field.metric_tag JOIN mtr.tag USING (tagpk) WHERE
devicePK = $1 AND typePK = $2
ORDER BY tag asc`,
devicePK, typePK); err != nil {
return weft.InternalServerError(err)
}
defer rows.Close()
for rows.Next() {
var s string
if err = rows.Scan(&s); err != nil {
return weft.InternalServerError(err)
}
tags = append(tags, s)
}
rows.Close()
p.SetSubTitle("Tags: " + strings.Join(tags, ","))
var mod string
// TODO move into first select for devicePK
if err = dbR.QueryRow(`SELECT modelid FROM field.device JOIN field.model using (modelpk)
WHERE devicePK = $1`,
devicePK).Scan(&mod); err != nil && err != sql.ErrNoRows {
return weft.InternalServerError(err)
}
p.SetTitle(fmt.Sprintf("Device: %s, Model: %s, Metric: %s", deviceID, mod, strings.Title(typeID)))
switch resolution {
case "minute":
p.SetXAxis(time.Now().UTC().Add(time.Hour*-12), time.Now().UTC())
p.SetXLabel("12 hours")
case "five_minutes":
p.SetXAxis(time.Now().UTC().Add(time.Hour*-24*2), time.Now().UTC())
p.SetXLabel("48 hours")
case "hour":
p.SetXAxis(time.Now().UTC().Add(time.Hour*-24*28), time.Now().UTC())
p.SetXLabel("4 weeks")
default:
return weft.BadRequest("invalid resolution")
}
var timeRange []time.Time
if timeRange, err = defaultTimeRange(resolution); err != nil {
return weft.InternalServerError(err)
}
rows, err = queryMetricRows(devicePK, typePK, resolution, timeRange)
if err != nil {
return weft.InternalServerError(err)
}
defer rows.Close()
var pts []ts.Point
for rows.Next() {
var pt ts.Point
if err = rows.Scan(&pt.DateTime, &pt.Value); err != nil {
return weft.InternalServerError(err)
}
pt.Value = pt.Value * scale
pts = append(pts, pt)
}
rows.Close()
// Add the latest value to the plot - this may be different to the average at minute or hour resolution.
var pt ts.Point
if err = dbR.QueryRow(`SELECT time, value FROM field.metric WHERE
devicePK = $1 AND typePK = $2
ORDER BY time DESC
LIMIT 1`,
devicePK, typePK).Scan(&pt.DateTime, &pt.Value); err != nil {
return weft.InternalServerError(err)
}
pt.Value = pt.Value * scale
pts = append(pts, pt)
p.SetLatest(pt, "deepskyblue")
p.AddSeries(ts.Series{Colour: "deepskyblue", Points: pts})
if err = plotter.Draw(p, b); err != nil {
return weft.InternalServerError(err)
}
return &weft.StatusOK
}
/*
plot draws an svg plot to b. Assumes f.loadPK has been called first.
*/
func dataCompletenessPlot(siteID, typeID, resolution string, plotter ts.SVGPlot, b *bytes.Buffer) *weft.Result {
var err error
// we need the sitePK often so read it once.
var sitePK int
if err = dbR.QueryRow(`SELECT sitePK FROM data.site WHERE siteID = $1`,
siteID).Scan(&sitePK); err != nil {
if err == sql.ErrNoRows {
return &weft.NotFound
}
return weft.InternalServerError(err)
}
var typePK int
var expected int
if err = dbR.QueryRow(`SELECT typePK, expected FROM data.completeness_type WHERE typeID = $1`,
typeID).Scan(&typePK, &expected); err != nil {
if err == sql.ErrNoRows {
return &weft.NotFound
}
return weft.InternalServerError(err)
}
expectedf := float64(expected)
var p ts.Plot
var tags []string
var rows *sql.Rows
if rows, err = dbR.Query(`SELECT tag FROM data.completeness_tag JOIN mtr.tag USING (tagpk) WHERE
sitePK = $1 AND typePK = $2
ORDER BY tag asc`,
sitePK, typePK); err != nil {
return weft.InternalServerError(err)
}
defer rows.Close()
for rows.Next() {
var s string
if err = rows.Scan(&s); err != nil {
return weft.InternalServerError(err)
}
tags = append(tags, s)
}
rows.Close()
p.SetSubTitle("Tags: " + strings.Join(tags, ","))
p.SetTitle(fmt.Sprintf("Site: %s - %s", siteID, strings.Title(typeID)))
p.SetUnit("completeness")
switch resolution {
case "five_minutes":
p.SetXAxis(time.Now().UTC().Add(time.Hour*-24*2), time.Now().UTC())
p.SetXLabel("48 hours")
expectedf /= 288
rows, err = dbR.Query(`SELECT date_trunc('hour', time) + extract(minute from time)::int / 5 * interval '5 min' as t,
sum(count) FROM data.completeness WHERE
sitePK = $1 AND typePK = $2
AND time > now() - interval '2 days'
GROUP BY date_trunc('hour', time) + extract(minute from time)::int / 5 * interval '5 min'
ORDER BY t ASC`,
sitePK, typePK)
case "hour":
p.SetXAxis(time.Now().UTC().Add(time.Hour*-24*28), time.Now().UTC())
p.SetXLabel("4 weeks")
expectedf /= 24
rows, err = dbR.Query(`SELECT date_trunc('`+resolution+`',time) as t, sum(count) FROM data.completeness WHERE
sitePK = $1 AND typePK = $2
AND time > now() - interval '28 days'
GROUP BY date_trunc('`+resolution+`',time)
ORDER BY t ASC`,
sitePK, typePK)
case "twelve_hours":
p.SetXAxis(time.Now().UTC().Add(time.Hour*-24*28), time.Now().UTC())
p.SetXLabel("4 weeks")
expectedf /= 2
rows, err = dbR.Query(`SELECT date_trunc('hour', time) + extract(hour from time)::int / 12 * interval '12 hour' as t, sum(count) FROM data.completeness WHERE
sitePK = $1 AND typePK = $2
AND time > now() - interval '28 days'
GROUP BY date_trunc('hour', time) + extract(hour from time)::int / 12 * interval '12 hour'
ORDER BY t ASC`,
sitePK, typePK)
default:
return weft.BadRequest("invalid resolution")
}
if err != nil {
return weft.InternalServerError(err)
}
var pts []ts.Point
for rows.Next() {
var pt ts.Point
var v int
if err = rows.Scan(&pt.DateTime, &v); err != nil {
return weft.InternalServerError(err)
}
pt.Value = float64(v) / expectedf
pts = append(pts, pt)
}
// Add the latest value to the plot.
var pt ts.Point
if err = dbR.QueryRow(`SELECT time, count FROM data.completeness WHERE
sitePK = $1 AND typePK = $2
ORDER BY time DESC
LIMIT 1`,
sitePK, typePK).Scan(&pt.DateTime, &pt.Value); err != nil {
// Note: We keep rendering the plot even there's no data.
if err != sql.ErrNoRows {
return weft.InternalServerError(err)
}
pt.Value = pt.Value / expectedf
pts = append(pts, pt)
p.SetLatest(pt, "deepskyblue")
}
p.AddSeries(ts.Series{Colour: "deepskyblue", Points: pts})
if err = plotter.Draw(p, b); err != nil {
return weft.InternalServerError(err)
}
return &weft.StatusOK
}
func dataLatencyPlot(siteID, typeID, resolution string, plotter ts.SVGPlot, b *bytes.Buffer) *weft.Result {
var err error
// we need the sitePK often so read it once.
var sitePK int
if err = dbR.QueryRow(`SELECT sitePK FROM data.site WHERE siteID = $1`,
siteID).Scan(&sitePK); err != nil {
if err == sql.ErrNoRows {
return &weft.NotFound
}
return weft.InternalServerError(err)
}
var typePK int
var scale float64
var display string
if err = dbR.QueryRow(`SELECT typePK, scale, display FROM data.type WHERE typeID = $1`,
typeID).Scan(&typePK, &scale, &display); err != nil {
if err == sql.ErrNoRows {
return &weft.NotFound
}
return weft.InternalServerError(err)
}
var p ts.Plot
p.SetUnit(display)
var lower, upper int
if err := dbR.QueryRow(`SELECT lower,upper FROM data.latency_threshold
WHERE sitePK = $1 AND typePK = $2`,
sitePK, typePK).Scan(&lower, &upper); err != nil && err != sql.ErrNoRows {
return weft.InternalServerError(err)
}
if !(lower == 0 && upper == 0) {
p.SetThreshold(float64(lower)*scale, float64(upper)*scale)
}
var tags []string
var rows *sql.Rows
if rows, err = dbR.Query(`SELECT tag FROM data.latency_tag JOIN mtr.tag USING (tagpk) WHERE
sitePK = $1 AND typePK = $2
ORDER BY tag asc`,
sitePK, typePK); err != nil {
return weft.InternalServerError(err)
}
defer rows.Close()
for rows.Next() {
var s string
if err = rows.Scan(&s); err != nil {
return weft.InternalServerError(err)
}
tags = append(tags, s)
}
rows.Close()
p.SetSubTitle("Tags: " + strings.Join(tags, ","))
p.SetTitle(fmt.Sprintf("Site: %s - %s", siteID, strings.Title(typeID)))
// TODO - loading avg(mean) at each resolution. Need to add max(fifty) and max(ninety) when there are some values.
switch resolution {
case "minute":
p.SetXAxis(time.Now().UTC().Add(time.Hour*-12), time.Now().UTC())
p.SetXLabel("12 hours")
case "five_minutes":
p.SetXAxis(time.Now().UTC().Add(time.Hour*-24*2), time.Now().UTC())
p.SetXLabel("48 hours")
case "hour":
p.SetXAxis(time.Now().UTC().Add(time.Hour*-24*28), time.Now().UTC())
p.SetXLabel("4 weeks")
default:
return weft.BadRequest("invalid resolution")
}
if err != nil {
return weft.InternalServerError(err)
}
var timeRange []time.Time
if timeRange, err = defaultTimeRange(resolution); err != nil {
weft.InternalServerError(err)
}
rows, err = queryLatencyRows(sitePK, typePK, resolution, timeRange)
defer rows.Close()
pts := make(map[internal.ID]([]ts.Point))
var mean float64
var fifty int
var ninety int
var pt ts.Point
for rows.Next() {
if err = rows.Scan(&pt.DateTime, &mean, &fifty, &ninety); err != nil {
return weft.InternalServerError(err)
}
pt.Value = mean * scale
pts[internal.Mean] = append(pts[internal.Mean], pt)
pt.Value = float64(fifty) * scale
pts[internal.Fifty] = append(pts[internal.Fifty], pt)
pt.Value = float64(ninety) * scale
pts[internal.Ninety] = append(pts[internal.Ninety], pt)
}
rows.Close()
// Add the latest value to the plot - this may be different to the average at minute or hour resolution.
if err = dbR.QueryRow(`SELECT time, mean, fifty, ninety FROM data.latency WHERE
sitePK = $1 AND typePK = $2
ORDER BY time DESC
LIMIT 1`,
sitePK, typePK).Scan(&pt.DateTime, &mean, &fifty, &ninety); err != nil {
return weft.InternalServerError(err)
}
pt.Value = mean * scale
pts[internal.Mean] = append(pts[internal.Mean], pt)
p.SetLatest(pt, internal.Colour(int(internal.Mean)))
// No latest label for fifty and ninety
pt.Value = float64(fifty) * scale
pts[internal.Fifty] = append(pts[internal.Fifty], pt)
pt.Value = float64(ninety) * scale
pts[internal.Ninety] = append(pts[internal.Ninety], pt)
for k, v := range pts {
i := int(k)
p.AddSeries(ts.Series{Colour: internal.Colour(i), Points: v})
}
// We need the labels in the order of "Mean, Fifty, Ninety" so put labels in the "range pts" won't work
var labels ts.Labels
labels = append(labels, ts.Label{Label: internal.Label(int(internal.Mean)), Colour: internal.Colour(int(internal.Mean))})
labels = append(labels, ts.Label{Label: internal.Label(int(internal.Fifty)), Colour: internal.Colour(int(internal.Fifty))})
labels = append(labels, ts.Label{Label: internal.Label(int(internal.Ninety)), Colour: internal.Colour(int(internal.Ninety))})
p.SetLabels(labels)
if err = plotter.Draw(p, b); err != nil {
return weft.InternalServerError(err)
}
return &weft.StatusOK
}