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 }