func (d *DataBrowserController) hasAggr(ctx dbox.IConnection, data *colonycore.DataBrowser, conn *colonycore.Connection) (*colonycore.DataBrowser, error) {
var fieldArr, aggrArr []string
var indexAggr []map[int]string
var query dbox.IQuery
fieldAggr := toolkit.M{}
for i, v := range data.MetaData {
if v.Aggregate != "" {
result := toolkit.M{}
toolkit.UnjsonFromString(v.Aggregate, &result)
cursor := []toolkit.M{}
if data.QueryType == "" {
aggregate, e := d.dboxAggr(data.TableNames, v.Field, ctx, query, result, fieldAggr, cursor, conn)
if e != nil {
return nil, e
}
v.Aggregate = toolkit.JsonString(aggregate)
} else if data.QueryType == "SQL" {
names := map[int]string{}
fieldArr = append(fieldArr, v.Field)
if _, sumOK := result["SUM"]; sumOK {
aggrArr = append(aggrArr, "SUM("+v.Field+")")
if len(result) > 1 {
indexAggr = append(indexAggr, map[int]string{i: "sum"})
} else {
names[i] = "sum"
}
}
if _, avgOK := result["AVG"]; avgOK {
aggrArr = append(aggrArr, "AVG("+v.Field+")")
if len(result) > 1 {
indexAggr = append(indexAggr, map[int]string{i: "avg"})
} else {
names[i] = "avg"
}
}
if _, maxOK := result["MAX"]; maxOK {
aggrArr = append(aggrArr, "MAX("+v.Field+")")
if len(result) > 1 {
indexAggr = append(indexAggr, map[int]string{i: "max"})
} else {
names[i] = "max"
}
}
if _, minOK := result["MIN"]; minOK {
aggrArr = append(aggrArr, "MIN("+v.Field+")")
if len(result) > 1 {
indexAggr = append(indexAggr, map[int]string{i: "min"})
} else {
names[i] = "min"
}
}
if _, minOK := result["COUNT"]; minOK {
aggrArr = append(aggrArr, "COUNT("+v.Field+")")
if len(result) > 1 {
indexAggr = append(indexAggr, map[int]string{i: "count"})
} else {
names[i] = "count"
}
}
if len(result) > 1 {
fieldAggr.Set(v.Field, indexAggr)
} else {
fieldAggr.Set(v.Field, names)
}
} else if data.QueryType == "Dbox" {
getQuery := toolkit.M{}
toolkit.UnjsonFromString(data.QueryText, &getQuery)
aggregate, e := d.dboxAggr(getQuery.Get("from").(string), v.Field, ctx, query, result, fieldAggr, cursor, conn)
if e != nil {
return nil, e
}
v.Aggregate = toolkit.JsonString(aggregate)
}
}
}
if data.QueryType == "SQL" {
// fieldString := strings.Join(fieldArr, ", ")
aggrString := strings.Join(aggrArr, ", ")
var queryText string
r := regexp.MustCompile(`(([Ff][Rr][Oo][Mm])) (?P<from>([a-zA-Z][_a-zA-Z]+[_a-zA-Z0-1].*))`)
temparray := r.FindStringSubmatch(data.QueryText)
sqlpart := toolkit.M{}
for i, val := range r.SubexpNames() {
if val != "" {
sqlpart.Set(val, temparray[i])
}
}
if fromOK := sqlpart.Get("from", "").(string); fromOK != "" {
queryText = toolkit.Sprintf("select %s FROM %s", aggrString, sqlpart.Get("from", "").(string))
// toolkit.Printf("queryString:%v\n", queryString)
}
query = ctx.NewQuery().Command("freequery", toolkit.M{}.
Set("syntax", queryText))
csr, e := query.Cursor(nil)
if e != nil {
return nil, e
}
defer csr.Close()
cursor := []toolkit.M{}
e = csr.Fetch(&cursor, 0, false)
if e != nil {
return nil, e
}
for f, m := range fieldAggr {
aggrData := toolkit.M{}
for _, aggs := range cursor {
for k, agg := range aggs {
if toolkit.SliceLen(m) > 0 {
for _, vals := range m.([]map[int]string) {
for key, val := range vals {
if strings.Contains(k, f) && strings.Contains(k, data.MetaData[key].Field) && strings.Contains(k, val) {
aggrData.Set(val, agg)
data.MetaData[key].Aggregate = toolkit.JsonString(aggrData)
}
}
}
} else {
for key, val := range m.(map[int]string) {
if strings.Contains(k, f) && strings.Contains(k, data.MetaData[key].Field) && strings.Contains(k, val) {
aggrData.Set(val, agg)
data.MetaData[key].Aggregate = toolkit.JsonString(aggrData)
// toolkit.Printf("k:%v f:%v key:%v val:%v agg:%v\n", k, f, key, val, data.MetaData[key].Aggregate)
}
}
}
}
}
}
}
return data, nil
}
func (d *DataBrowserController) dboxAggr(tblename string, field string, ctx dbox.IConnection, query dbox.IQuery, result, fieldAggr toolkit.M, cursor []toolkit.M, conn *colonycore.Connection) (toolkit.M, error) {
aggregate := toolkit.M{}
if conn.Driver == "mongo" {
field = "$" + field
}
query = ctx.NewQuery().From(tblename)
if result != nil {
for k, _ := range result {
if k == "SUM" {
query = query.Aggr(dbox.AggrSum, field, "SUM")
}
if k == "AVG" {
query = query.Aggr(dbox.AggrAvr, field, "AVG")
}
if k == "MAX" {
query = query.Aggr(dbox.AggrMax, field, "MAX")
}
if k == "MIN" {
query = query.Aggr(dbox.AggrMin, field, "MIN")
}
if conn.Driver == "mongo" {
if k != "COUNT" {
query = query.Group()
}
}
csr, e := query.Cursor(nil)
if e != nil {
return nil, e
}
defer csr.Close()
hasCount := []toolkit.M{}
if k == "COUNT" {
csr, e := query.Cursor(nil)
if e != nil {
return nil, e
}
defer csr.Close()
count := csr.Count()
hasCount = append(hasCount, aggregate.Set("count", count))
aggregate.Set("count", count)
} else {
e = csr.Fetch(&cursor, 0, false)
if e != nil {
return nil, e
}
}
if _, countOK := aggregate["count"]; countOK {
cursor = append(cursor, hasCount...)
}
for _, agg := range cursor {
aggregate = agg
if conn.Driver == "mongo" {
for f, _ := range aggregate {
if f == "_id" {
aggregate.Unset(f)
}
}
}
fieldAggr.Set(field, aggregate)
// toolkit.Printf("k:%v fieldArr:%v cursor:%v\n", k, field, fieldAggr)
}
}
}
return aggregate, nil
}
