Exemple #1
0
func TestFrom(t *testing.T) {
	for i := 0; i < sampleCount; i++ {
		obj := Obj{}
		obj.F = toolkit.ToFloat64(toolkit.RandInt(1000), 2, toolkit.RoundingAuto)
		obj.I = toolkit.RandInt(1000) + 5
		objs = append(objs, obj)

		if i == 0 {
			min = obj.I
			max = obj.I
			sum = obj.I
			avg = toolkit.ToFloat64(obj.I, 4, toolkit.RoundingAuto)
		} else {
			sum += obj.I
			avg = toolkit.ToFloat64(sum, 4, toolkit.RoundingAuto) /
				toolkit.ToFloat64(i+1, 4, toolkit.RoundingAuto)
			if min > obj.I {
				min = obj.I
			}
			if max < obj.I {
				max = obj.I
			}
		}
	}

	c = crowd.From(&objs)
	check(t, c.Error, "")
	toolkit.Printf("Data len: %d, max: %d, min: %d, sum: %d, avg: %5.4f\n",
		c.Len(), max, min, sum, avg)
}
Exemple #2
0
func (q *Query) Filters(parm toolkit.M) (toolkit.M, error) {
	filters := toolkit.M{}

	parts := crowd.From(q.Parts()).Group(func(x interface{}) interface{} {
		qp := x.(*dbox.QueryPart)
		return qp.PartType
	}, nil).Data

	var fields []string
	selectParts, hasSelect := parts[dbox.QueryPartSelect]
	if hasSelect {
		// fields = toolkit.M{}
		for _, sl := range selectParts.([]interface{}) {
			qp := sl.(*dbox.QueryPart)
			for _, fid := range qp.Value.([]string) {
				fields = append(fields, fid)
				// fields.Set(fid, fid)
			}
		}
		filters.Set("cmdType", dbox.QueryPartSelect)
	} else {
		_, hasDelete := parts[dbox.QueryPartDelete]
		_, hasInsert := parts[dbox.QueryPartInsert]
		_, hasUpdate := parts[dbox.QueryPartUpdate]
		_, hasSave := parts[dbox.QueryPartSave]

		if hasDelete {
			filters.Set("cmdType", dbox.QueryPartDelete)
		} else if hasInsert {
			filters.Set("cmdType", dbox.QueryPartInsert)
		} else if hasUpdate {
			filters.Set("cmdType", dbox.QueryPartUpdate)
		} else if hasSave {
			filters.Set("cmdType", dbox.QueryPartSave)
		} else {
			filters.Set("cmdType", dbox.QueryPartSelect)
		}
	}
	filters.Set("select", fields)

	whereParts, hasWhere := parts[dbox.QueryPartWhere]
	var where []*dbox.Filter
	if hasWhere {
		for _, p := range whereParts.([]interface{}) {
			fs := p.(*dbox.QueryPart).Value.([]*dbox.Filter)
			for _, f := range fs {
				where = append(where, f)
			}
		}
		filters.Set("where", where)
	}
	return filters, nil
}
Exemple #3
0
func TestJoin(t *testing.T) {
	a := []string{"satu", "dua", "tiga", "empat", "lima", "enam", "tujuh", "delapan", "sembilan", "sepuluh"}
	b := []int{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}

	cjoin := crowd.From(&a).Join(&b, func(x, y interface{}) bool {
		xi := len(x.(string))
		yi := y.(int)
		return xi == yi
	}, nil).Group(func(x interface{}) interface{} {
		return x.(toolkit.M).Get("data2")
	}, func(x interface{}) interface{} {
		return x.(toolkit.M).Get("data1")
	}).Exec()
	check(t, cjoin.Error, "")
	toolkit.Printfn("Data:\n%s", toolkit.JsonString(cjoin.Result.Data()))
}
Exemple #4
0
func TestStorageGet(t *testing.T) {
	skipIfClientNil(t)

	in := toolkit.M{}
	in.Set("key", "dataku")
	getResult := client.Call("get", in)
	if getResult.Status != toolkit.Status_OK {
		t.Errorf(getResult.Message)
		return
	}

	data := []int{}
	e := toolkit.FromBytes(getResult.Data.([]byte), "", &data)
	if e != nil {
		t.Errorf("Unable to decode: " + e.Error())
	}
	fmt.Println("Result received: %s \n", toolkit.JsonString(data))

	total := int(crowd.From(data).Sum(nil))

	if total != totalInt {
		t.Errorf("Wrong summation expecting %d got %d", totalInt, total)
	}
}
Exemple #5
0
func (q *Query) Cursor(in toolkit.M) (dbox.ICursor, error) {
	var e error
	/*
		if q.Parts == nil {
			return nil, errorlib.Error(packageName, modQuery,
				"Cursor", fmt.Sprintf("No Query Parts"))
		}
	*/

	aggregate := false
	dbname := q.Connection().Info().Database
	tablename := ""

	/*
		parts will return E - map{interface{}}interface{}
		where each interface{} returned is slice of interfaces --> []interface{}
	*/
	parts := crowd.From(q.Parts()).Group(func(x interface{}) interface{} {
		qp := x.(*dbox.QueryPart)
		return qp.PartType
	}, nil).Data

	//return nil, errorlib.Error(packageName, modQuery, "Cursor", "asdaa")
	//fmt.Printf("Query parts: %s\n", toolkit.JsonString(q.Parts()))
	fromParts, hasFrom := parts[dbox.QueryPartFrom]
	if hasFrom == false {
		return nil, errorlib.Error(packageName, "Query", "Cursor", "Invalid table name")
	}
	tablename = fromParts.([]interface{})[0].(*dbox.QueryPart).Value.(string)

	skip := 0
	if skipParts, hasSkip := parts[dbox.QueryPartSkip]; hasSkip {
		skip = skipParts.([]interface{})[0].(*dbox.QueryPart).
			Value.(int)
	}

	take := 0
	if takeParts, has := parts[dbox.QueryPartTake]; has {
		take = takeParts.([]interface{})[0].(*dbox.QueryPart).
			Value.(int)
	}

	aggrParts, hasAggr := parts[dbox.QueryPartAggr]
	aggrExpression := toolkit.M{}
	if hasAggr {
		aggregate = true
		aggrElements := func() []*dbox.QueryPart {
			var qps []*dbox.QueryPart
			for _, v := range aggrParts.([]interface{}) {
				qps = append(qps, v.(*dbox.QueryPart))
			}
			return qps
		}()
		for _, el := range aggrElements {
			aggr := el.Value.(dbox.AggrInfo)
			//if aggr.Op == dbox.AggrSum {
			aggrExpression.Set(aggr.Alias, toolkit.M{}.Set(aggr.Op, aggr.Field))
			//}
		}
		//toolkit.Printf("Aggr: %s\n", toolkit.JsonString(aggrExpression))
	}
	partGroup, hasGroup := parts[dbox.QueryPartGroup]
	if hasGroup {
		aggregate = true
		groups := func() toolkit.M {
			s := toolkit.M{}
			for _, v := range partGroup.([]interface{}) {
				gs := v.(*dbox.QueryPart).Value.([]string)
				for _, g := range gs {
					if strings.TrimSpace(g) != "" {
						s.Set(g, "$"+g)
					}
				}
			}
			return s
		}()
		if len(groups) == 0 {
			aggrExpression.Set("_id", "")
		} else {
			aggrExpression.Set("_id", groups)
		}
	}

	var fields toolkit.M
	selectParts, hasSelect := parts[dbox.QueryPartSelect]
	if hasSelect {
		fields = toolkit.M{}
		for _, sl := range selectParts.([]interface{}) {
			qp := sl.(*dbox.QueryPart)
			for _, fid := range qp.Value.([]string) {
				fields.Set(fid, 1)
			}
		}
	} else {
		_, hasUpdate := parts[dbox.QueryPartUpdate]
		_, hasInsert := parts[dbox.QueryPartInsert]
		_, hasDelete := parts[dbox.QueryPartDelete]
		_, hasSave := parts[dbox.QueryPartSave]

		if hasUpdate || hasInsert || hasDelete || hasSave {
			return nil, errorlib.Error(packageName, modQuery, "Cursor",
				"Valid operation for a cursor is select only")
		}
	}
	//fmt.Printf("Result: %s \n", toolkit.JsonString(fields))
	//fmt.Printf("Database:%s table:%s \n", dbname, tablename)
	var sort []string
	sortParts, hasSort := parts[dbox.QueryPartOrder]
	if hasSort {
		sort = []string{}
		for _, sl := range sortParts.([]interface{}) {
			qp := sl.(*dbox.QueryPart)
			for _, fid := range qp.Value.([]string) {
				sort = append(sort, fid)
			}
		}
	}

	//where := toolkit.M{}
	var where interface{}
	whereParts, hasWhere := parts[dbox.QueryPartWhere]
	if hasWhere {
		fb := q.Connection().Fb()
		for _, p := range whereParts.([]interface{}) {
			fs := p.(*dbox.QueryPart).Value.([]*dbox.Filter)
			for _, f := range fs {
				fb.AddFilter(f)
			}
		}
		where, e = fb.Build()
		if e != nil {
			return nil, errorlib.Error(packageName, modQuery, "Cursor",
				e.Error())
		} else {
			//fmt.Printf("Where: %s\n", toolkit.JsonString(where))
		}
		//where = iwhere.(toolkit.M)
	}

	pipes := []toolkit.M{}
	pipe := parts["pipe"]
	if pipe != nil {
		aggregate = true
		pipes = pipe.([]interface{})[0].(*dbox.QueryPart).Value.([]toolkit.M)
	}

	session := q.Session()
	mgoColl := session.DB(dbname).C(tablename)
	cursor := dbox.NewCursor(new(Cursor))
	cursor.(*Cursor).session = session
	cursor.(*Cursor).isPoolingSession = q.usePooling

	if aggregate == true {
		if len(pipes) == 0 {
			pipes = append(pipes, toolkit.M{}.Set("$group", aggrExpression))
		}
		if hasWhere {
			pipes = append(append([]toolkit.M{}, toolkit.M{}.Set("$match", where)), pipes...)
		}
		mgoPipe := session.DB(dbname).C(tablename).
			Pipe(pipes).AllowDiskUse()
		//toolkit.Printf("Pipe: %s \n", toolkit.JsonString(pipes))
		//iter := mgoPipe.Iter()

		cursor.(*Cursor).ResultType = QueryResultPipe
		cursor.(*Cursor).mgoPipe = mgoPipe
		//cursor.(*Cursor).mgoIter = iter

	} else {
		mgoCursor := mgoColl.Find(where)
		count, e := mgoCursor.Count()
		if e != nil {
			//fmt.Println("Error: " + e.Error())
			return nil, errorlib.Error(packageName,
				modQuery, "Cursor", e.Error())
		}
		if fields != nil {
			mgoCursor = mgoCursor.Select(fields)
		}
		if hasSort {
			mgoCursor = mgoCursor.Sort(sort...)
		}
		if skip > 0 {
			mgoCursor = mgoCursor.Skip(skip)
		}
		if take > 0 {
			mgoCursor = mgoCursor.Limit(take)
		}
		cursor.(*Cursor).ResultType = QueryResultCursor
		cursor.(*Cursor).mgoCursor = mgoCursor
		cursor.(*Cursor).count = count
		//cursor.(*Cursor).mgoIter = mgoCursor.Iter()
	}

	if cursor == nil {
		return nil, errorlib.Error(packageName, modQuery, "Cursor", "Unable to initialize cursor. This is likely caused by unimplemented command or invalid series of query")
	}

	//fmt.Println("Where: " + toolkit.JsonString(where))
	return cursor, nil
}
Exemple #6
0
func (q *Query) Exec(parm toolkit.M) error {
	var e error
	if parm == nil {
		parm = toolkit.M{}
	}
	/*
		if q.Parts == nil {
			return errorlib.Error(packageName, modQuery,
				"Cursor", fmt.Sprintf("No Query Parts"))
		}
	*/

	dbname := q.Connection().Info().Database
	tablename := ""

	if parm == nil {
		parm = toolkit.M{}
	}
	data := parm.Get("data", nil)

	/*
		parts will return E - map{interface{}}interface{}
		where each interface{} returned is slice of interfaces --> []interface{}
	*/
	parts := crowd.From(q.Parts()).Group(func(x interface{}) interface{} {
		qp := x.(*dbox.QueryPart)
		return qp.PartType
	}, nil).Data

	fromParts, hasFrom := parts[dbox.QueryPartFrom]
	if !hasFrom {
		return errorlib.Error(packageName, modQuery, "Exec", "Invalid table name")
	}
	tablename = fromParts.([]interface{})[0].(*dbox.QueryPart).Value.(string)

	var where interface{}
	commandType := ""
	multi := false

	_, hasDelete := parts[dbox.QueryPartDelete]
	_, hasInsert := parts[dbox.QueryPartInsert]
	_, hasUpdate := parts[dbox.QueryPartUpdate]
	_, hasSave := parts[dbox.QueryPartSave]

	if hasDelete {
		commandType = dbox.QueryPartDelete
	} else if hasInsert {
		commandType = dbox.QueryPartInsert
	} else if hasUpdate {
		commandType = dbox.QueryPartUpdate
	} else if hasSave {
		commandType = dbox.QueryPartSave
	}

	whereParts, hasWhere := parts[dbox.QueryPartWhere]
	if hasWhere {
		fb := q.Connection().Fb()
		for _, p := range whereParts.([]interface{}) {
			fs := p.(*dbox.QueryPart).Value.([]*dbox.Filter)
			for _, f := range fs {
				fb.AddFilter(f)
			}
		}
		where, e = fb.Build()
		if e != nil {
			return errorlib.Error(packageName, modQuery, "Exec",
				e.Error())
		} else {
			//fmt.Printf("Where: %s\n", toolkit.JsonString(where))
		}
	}

	if data == nil {
		multi = true
	} else {
		if where == nil {
			id := toolkit.Id(data)
			if id != nil {
				where = (toolkit.M{}).Set("_id", id)
			}
		} else {
			multi = true
		}
	}

	session := q.Session()

	multiExec := q.Config("multiexec", false).(bool)
	if !multiExec && !q.usePooling && session != nil {
		defer session.Close()
	}
	mgoColl := session.DB(dbname).C(tablename)
	if commandType == dbox.QueryPartInsert {
		e = mgoColl.Insert(data)
	} else if commandType == dbox.QueryPartUpdate {
		if multi {
			dataM, e := toolkit.ToM(data)
			if e != nil {
				return errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
			}
			if len(dataM) == 0 {
				return errorlib.Error(packageName, modQuery+".Exec", commandType, "Update data points is empty")
			}
			updatedData := toolkit.M{}.Set("$set", dataM)
			_, e = mgoColl.UpdateAll(where, updatedData)
		} else {
			e = mgoColl.Update(where, data)
			if e != nil {
				e = fmt.Errorf("%s [%v]", e.Error(), where)
			}
		}
	} else if commandType == dbox.QueryPartDelete {
		if multi {
			_, e = mgoColl.RemoveAll(where)
		} else {
			e = mgoColl.Remove(where)
			if e != nil {
				e = fmt.Errorf("%s [%v]", e.Error(), where)
			}
		}
	} else if commandType == dbox.QueryPartSave {
		_, e = mgoColl.Upsert(where, data)
	}
	if e != nil {
		return errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
	}
	return nil
}
Exemple #7
0
func (q *Query) Filters(parm toolkit.M) (toolkit.M, error) {
	filters := toolkit.M{}

	quyerParts := q.Parts()
	c := crowd.From(&quyerParts)

	groupParts := c.Group(func(x interface{}) interface{} {
		return x.(*dbox.QueryPart).PartType
	}, nil).Exec()

	parts := map[interface{}]interface{}{}
	if len(groupParts.Result.Data().([]crowd.KV)) > 0 {
		for _, kv := range groupParts.Result.Data().([]crowd.KV) {
			parts[kv.Key] = kv.Value
		}
	}

	skip := 0
	if skipPart, hasSkip := parts[dbox.QueryPartSkip]; hasSkip {
		skip = skipPart.([]*dbox.QueryPart)[0].Value.(int)
	}
	filters.Set("skip", skip)

	take := 0
	if takeParts, hasTake := parts[dbox.QueryPartTake]; hasTake {
		take = takeParts.([]*dbox.QueryPart)[0].Value.(int)
	}
	filters.Set("take", take)

	var aggregate bool
	if _, hasAggr := parts[dbox.QueryPartAggr]; hasAggr {
		aggregate = true
	}
	filters.Set("aggregate", aggregate)

	var sort []string
	if sortParts, hasSort := parts[dbox.QueryPartOrder]; hasSort {
		sort = []string{}
		for _, sl := range sortParts.([]*dbox.QueryPart) {
			// qp := sl.(*dbox.QueryPart)
			for _, fid := range sl.Value.([]string) {
				sort = append(sort, fid)
			}
		}
	}
	filters.Set("sort", sort)

	var fields []string
	selectParts, hasSelect := parts[dbox.QueryPartSelect]
	if hasSelect {
		// fields = toolkit.M{}
		for _, sl := range selectParts.([]*dbox.QueryPart) {
			// qp := sl.(*dbox.QueryPart)
			for _, fid := range sl.Value.([]string) {
				fields = append(fields, fid)
				// fields.Set(fid, fid)
			}
		}
		filters.Set("cmdType", dbox.QueryPartSelect)
	} else {
		_, hasDelete := parts[dbox.QueryPartDelete]
		_, hasInsert := parts[dbox.QueryPartInsert]
		_, hasUpdate := parts[dbox.QueryPartUpdate]
		_, hasSave := parts[dbox.QueryPartSave]

		if hasDelete {
			filters.Set("cmdType", dbox.QueryPartDelete)
		} else if hasInsert {
			filters.Set("cmdType", dbox.QueryPartInsert)
		} else if hasUpdate {
			filters.Set("cmdType", dbox.QueryPartUpdate)
		} else if hasSave {
			filters.Set("cmdType", dbox.QueryPartSave)
		} else {
			filters.Set("cmdType", dbox.QueryPartSelect)
		}
	}
	filters.Set("select", fields)

	whereParts, hasWhere := parts[dbox.QueryPartWhere]
	var where []*dbox.Filter
	if hasWhere {
		fb := new(FilterBuilder)
		for _, p := range whereParts.([]*dbox.QueryPart) {
			fs := p.Value.([]*dbox.Filter)
			for _, f := range fs {
				f := fb.CheckFilter(f, parm)

				where = append(where, f)
			}
		}
		filters.Set("where", where)
	}
	// toolkit.Printf("where>%v\n", toolkit.JsonString(filters.Get("where")))

	return filters, nil
}
Exemple #8
0
func (q *Query) Cursor(in toolkit.M) (dbox.ICursor, error) {
	var e error

	aggregate := false

	if q.Connection().(*Connection).setNewHeader {
		q.Connection().(*Connection).Close()
		filename := q.Connection().(*Connection).Info().Host
		os.Remove(filename)
		return nil, errorlib.Error(packageName, "Cursor", modQuery, "Only Insert Query Permited")
	}

	quyerParts := q.Parts()
	c := crowd.From(&quyerParts)

	groupParts := c.Group(func(x interface{}) interface{} {
		return x.(*dbox.QueryPart).PartType
	}, nil).Exec()

	parts := map[interface{}]interface{}{}
	if len(groupParts.Result.Data().([]crowd.KV)) > 0 {
		for _, kv := range groupParts.Result.Data().([]crowd.KV) {
			parts[kv.Key] = kv.Value
		}
	}

	skip := 0
	if skipParts, hasSkip := parts[dbox.QueryPartSkip]; hasSkip {
		skip = skipParts.([]*dbox.QueryPart)[0].
			Value.(int)
	}

	take := 0
	if takeParts, has := parts[dbox.QueryPartTake]; has {
		take = takeParts.([]*dbox.QueryPart)[0].
			Value.(int)
	}

	var fields toolkit.M
	selectParts, hasSelect := parts[dbox.QueryPartSelect]
	if hasSelect {
		fields = toolkit.M{}
		for _, sl := range selectParts.([]*dbox.QueryPart) {
			// qp := sl.(*dbox.QueryPart)
			for _, fid := range sl.Value.([]string) {
				fields.Set(strings.ToLower(fid), 1)
			}
		}
	} else {
		_, hasUpdate := parts[dbox.QueryPartUpdate]
		_, hasInsert := parts[dbox.QueryPartInsert]
		_, hasDelete := parts[dbox.QueryPartDelete]
		_, hasSave := parts[dbox.QueryPartSave]

		if hasUpdate || hasInsert || hasDelete || hasSave {
			return nil, errorlib.Error(packageName, modQuery, "Cursor",
				"Valid operation for a cursor is select only")
		}
	}

	var sort []string
	sortParts, hasSort := parts[dbox.QueryPartSelect]
	if hasSort {
		sort = []string{}
		for _, sl := range sortParts.([]*dbox.QueryPart) {
			// qp := sl.(*dbox.QueryPart)
			for _, fid := range sl.Value.([]string) {
				sort = append(sort, fid)
			}
		}
	}

	var where []*dbox.Filter
	whereParts, hasWhere := parts[dbox.QueryPartWhere]
	if hasWhere {
		for _, p := range whereParts.([]*dbox.QueryPart) {
			fs := p.Value.([]*dbox.Filter)
			for _, f := range fs {
				// if len(in) > 0 {
				f = ReadVariable(f, in)
				// }
				where = append(where, f)
			}
		}
	}

	cursor := dbox.NewCursor(new(Cursor))
	cursor = cursor.SetConnection(q.Connection())

	cursor.(*Cursor).file = q.File()
	cursor.(*Cursor).reader = q.Reader()
	cursor.(*Cursor).headerColumn = q.Connection().(*Connection).headerColumn
	cursor.(*Cursor).count = 0
	if e != nil {
		return nil, errorlib.Error(packageName, modQuery, "Cursor", e.Error())
	}

	if !aggregate {
		cursor.(*Cursor).ConditionVal.where = where
		if fields != nil {
			cursor.(*Cursor).ConditionVal.Select = fields
		}

		if hasSort {
			cursor.(*Cursor).ConditionVal.Sort = sort
		}
		cursor.(*Cursor).ConditionVal.skip = skip
		cursor.(*Cursor).ConditionVal.limit = take
		if skip > 0 && take > 0 {
			cursor.(*Cursor).ConditionVal.limit += skip
		}

		e = cursor.(*Cursor).generateIndexes()

	} else {
		/*		pipes := toolkit.M{}
				mgoPipe := session.DB(dbname).C(tablename).
					Pipe(pipes).AllowDiskUse()
				//iter := mgoPipe.Iter()

				cursor.(*Cursor).ResultType = QueryResultPipe
				cursor.(*Cursor).mgoPipe = mgoPipe
				//cursor.(*Cursor).mgoIter = iter
		*/
	}
	if e != nil {
		return nil, errorlib.Error(packageName, modQuery, "Cursor", e.Error())
	}
	return cursor, nil
}
Exemple #9
0
func (q *Query) prepare(in toolkit.M) (output toolkit.M, e error) {
	output = toolkit.M{}
	quyerParts := q.Parts()
	c := crowd.From(&quyerParts)
	groupParts := c.Group(func(x interface{}) interface{} {
		return x.(*dbox.QueryPart).PartType
	}, nil).Exec()
	parts := map[interface{}]interface{}{}
	if len(groupParts.Result.Data().([]crowd.KV)) > 0 {
		for _, kv := range groupParts.Result.Data().([]crowd.KV) {
			parts[kv.Key] = kv.Value
		}
	}

	fromParts, hasFrom := parts[dbox.QueryPartFrom]
	if hasFrom == false {
		return nil, err.Error(packageName, "Query", "prepare", "Invalid table name")
	}
	tablename := fromParts.([]*dbox.QueryPart)[0].Value.(string)
	output.Set("tablename", tablename)
	q.filePath = filepath.Join(q.Connection().(*Connection).folder, tablename+".csv")

	skip := 0
	if skipParts, hasSkip := parts[dbox.QueryPartSkip]; hasSkip {
		skip = skipParts.([]*dbox.QueryPart)[0].Value.(int)
	}
	output.Set("skip", skip)

	take := 0
	if takeParts, has := parts[dbox.QueryPartTake]; has {
		take = takeParts.([]*dbox.QueryPart)[0].Value.(int)
	}
	output.Set("take", take)

	var aggregate bool
	aggrParts, hasAggr := parts[dbox.QueryPartAggr]
	aggrExpression := toolkit.M{}
	if hasAggr {
		aggregate = true
		aggrElements := func() []*dbox.QueryPart {
			var qps []*dbox.QueryPart
			for _, v := range aggrParts.([]*dbox.QueryPart) {
				qps = append(qps, v)
			}
			return qps
		}()
		for _, el := range aggrElements {
			aggr := el.Value.(dbox.AggrInfo)
			aggrExpression.Set(aggr.Alias, toolkit.M{}.Set(aggr.Op, aggr.Field))
		}
	}

	partGroup, hasGroup := parts[dbox.QueryPartGroup]
	if hasGroup {
		aggregate = true
		groups := func() toolkit.M {
			s := toolkit.M{}
			for _, v := range partGroup.([]*dbox.QueryPart) {
				gs := v.Value.([]string)
				for _, g := range gs {
					if strings.TrimSpace(g) != "" {
						s.Set(g, "$"+g)
					}
				}
			}
			return s
		}()
		if len(groups) == 0 {
			aggrExpression.Set("_id", "")
		} else {
			aggrExpression.Set("_id", groups)
		}
	}

	output.Set("aggregate", aggregate)
	output.Set("aggrExpression", aggrExpression)

	var fields toolkit.M
	selectParts, hasSelect := parts[dbox.QueryPartSelect]
	if hasSelect {
		fields = toolkit.M{}
		for _, sl := range selectParts.([]*dbox.QueryPart) {
			for _, fid := range sl.Value.([]string) {
				fields.Set(fid, 1)
			}
		}
		output.Set("commandtype", dbox.QueryPartSelect)
	} else {
		_, hasUpdate := parts[dbox.QueryPartUpdate]
		_, hasInsert := parts[dbox.QueryPartInsert]
		_, hasDelete := parts[dbox.QueryPartDelete]
		_, hasSave := parts[dbox.QueryPartSave]

		if hasInsert {
			output.Set("commandtype", dbox.QueryPartInsert)
		} else if hasUpdate {
			output.Set("commandtype", dbox.QueryPartUpdate)
		} else if hasDelete {
			output.Set("commandtype", dbox.QueryPartDelete)
		} else if hasSave {
			output.Set("commandtype", dbox.QueryPartSave)
		} else {
			output.Set("commandtype", dbox.QueryPartSelect)
		}
	}
	output.Set("fields", fields)

	var sort []string
	sortParts, hasSort := parts[dbox.QueryPartOrder]
	if hasSort {
		sort = []string{}
		for _, sl := range sortParts.([]*dbox.QueryPart) {
			for _, fid := range sl.Value.([]string) {
				sort = append(sort, fid)
			}
		}
	}
	output.Set("sort", sort)

	var filters []*dbox.Filter
	whereParts, hasWhere := parts[dbox.QueryPartWhere]
	if hasWhere {
		for _, p := range whereParts.([]*dbox.QueryPart) {
			fs := p.Value.([]*dbox.Filter)
			for _, f := range fs {
				f = ReadVariable(f, in)
				filters = append(filters, f)
			}
		}
	}
	output.Set("where", filters)
	return
}
Exemple #10
0
func (q *Query) insertBulk(parm toolkit.M) error {

	var e error
	if parm == nil {
		parm = toolkit.M{}
	}

	driverName := q.GetDriverDB()
	// driverName = "oracle"
	tablename := ""
	data := parm.Get("data")

	var attributes string

	var dataM toolkit.M
	var dataMs []toolkit.M

	if toolkit.IsSlice(data) {
		e = toolkit.Unjson(toolkit.Jsonify(data), &dataMs)
		if e != nil {
			return errorlib.Error(packageName, modQuery, "Exec: data extraction", "Data encoding error: "+e.Error())
		}
	} else {
		dataM, e = toolkit.ToM(data)
		dataMs = append(dataMs, dataM)
		if e != nil {
			return errorlib.Error(packageName, modQuery, "Exec: data extraction", "Data encoding error: "+e.Error())
		}
	}

	temp := ""
	quyerParts := q.Parts()
	c := crowd.From(&quyerParts)
	groupParts := c.Group(func(x interface{}) interface{} {
		qp := x.(*dbox.QueryPart)
		temp = toolkit.JsonString(qp)
		return qp.PartType
	}, nil).Exec()
	parts := map[interface{}]interface{}{}
	if len(groupParts.Result.Data().([]crowd.KV)) > 0 {
		for _, kv := range groupParts.Result.Data().([]crowd.KV) {
			parts[kv.Key] = kv.Value
		}
	}

	commandType := ""

	_, hasInsert := parts[dbox.QueryPartInsert]

	if hasInsert {
		commandType = dbox.QueryPartInsert
	} else {
		_, e = q.ExecOut(parm)
		return e
		// return errorlib.Error(packageName, "Query", modQuery+".InsertBulk", "Invalid Operation")
	}

	fromParts, hasFrom := parts[dbox.QueryPartFrom]

	if !hasFrom {
		return errorlib.Error(packageName, "Query", modQuery, "Invalid table name")
	}
	tablename = fromParts.([]*dbox.QueryPart)[0].Value.(string)

	session := q.Session()
	attributeList := extractFields(dataMs[0])

	var datas []string

	for _, dataVal := range dataMs {
		var values string
		tmp := toolkit.M{}

		for _, attr := range attributeList {
			tmp.Set(attr, dataVal.Get(attr))
		}

		values = extractDataBulk(attributeList, tmp, driverName)
		// toolkit.Printf("test: \n %v \n------\n %v \n------\n %v \n------\n %v \n", attributeList, dataVal, tmp, values)

		datas = append(datas, values)
	}

	attributes = "(" + strings.Join(attributeList, ",") + ")"

	if attributes != "" && nil != datas {
		var statement string
		if driverName == "hive" {
			/*statement = "INSERT INTO " + tablename + " VALUES " + values
			e = sessionHive.Exec(statement, nil)*/
			return errorlib.Error(packageName, modQuery+".Exec", commandType,
				"Not Implemented Yet for HIVE")
		} else {
			statement = fmt.Sprintf("INSERT INTO "+tablename+" "+attributes+" VALUES %s", strings.Join(datas, ","))
			_, e = session.Exec(statement)
		}

		if e != nil {
			return errorlib.Error(packageName, modQuery+".Exec", commandType,
				cast.ToString(e.Error()))
		}
	} else {
		return errorlib.Error(packageName, modQuery+".Exec", commandType,
			"please provide the data")
	}

	return nil
}
Exemple #11
0
// GeneratePlantMaster To Generate Master Plant
func (d *GenPlantMaster) generatePlantMaster() error {
	tk.Println("Generating..")
	ctx := d.BaseController.Ctx
	c := ctx.Connection
	query := []*dbox.Filter{}
	FunctionalLocationList := []FunctionalLocation{}

	query = append(query, dbox.Eq("LEN(FunctionalLocationCode)", 4))
	csr, e := c.NewQuery().From(new(FunctionalLocation).TableName()).Where(query...).Cursor(nil)

	defer csr.Close()

	if e != nil {
		return e
	}

	e = csr.Fetch(&FunctionalLocationList, 0, false)

	if e != nil {
		return e
	}

	PowerPlantInfoList := []PowerPlantInfo{}
	csr, e = c.NewQuery().From(new(PowerPlantInfo).TableName()).Cursor(nil)
	if e != nil {
		return e
	}

	e = csr.Fetch(&PowerPlantInfoList, 0, false)

	if e != nil {
		return e
	}

	RegenMP := new(RegenMasterPlant)
	temp := []PowerPlantInfo{}

	for _, plant := range FunctionalLocationList {

		RegenMP = new(RegenMasterPlant)
		temp = []PowerPlantInfo{}
		var tempValue float64

		plantDes := PlantNormalization(plant.Description)

		datas := crowd.From(&PowerPlantInfoList).Where(func(x interface{}) interface{} {
			return strings.Contains(strings.ToLower(x.(PowerPlantInfo).Name), strings.ToLower(plantDes))
		}).Exec().Result.Data().([]PowerPlantInfo)

		RegenMP.PlantCode = plant.FunctionalLocationCode
		RegenMP.PlantName = plantDes
		RegenMP.City = datas[0].City
		RegenMP.Province = datas[0].Province
		RegenMP.Region = datas[0].Region
		temp = crowd.From(&datas).Where(func(x interface{}) interface{} {
			return x.(PowerPlantInfo).FuelTypes_Crude
		}).Exec().Result.Data().([]PowerPlantInfo)

		if len(temp) > 0 {
			RegenMP.FuelTypes_Crude = true
		} else {
			RegenMP.FuelTypes_Crude = false
		}
		temp = crowd.From(&datas).Where(func(x interface{}) interface{} {
			return x.(PowerPlantInfo).FuelTypes_Diesel
		}).Exec().Result.Data().([]PowerPlantInfo)

		if len(temp) > 0 {
			RegenMP.FuelTypes_Diesel = true
		} else {
			RegenMP.FuelTypes_Diesel = false
		}

		temp = crowd.From(&datas).Where(func(x interface{}) interface{} {
			return x.(PowerPlantInfo).FuelTypes_Gas
		}).Exec().Result.Data().([]PowerPlantInfo)

		if len(temp) > 0 {
			RegenMP.FuelTypes_Gas = true
		} else {
			RegenMP.FuelTypes_Gas = false
		}

		temp = crowd.From(&datas).Where(func(x interface{}) interface{} {
			return x.(PowerPlantInfo).FuelTypes_Heavy
		}).Exec().Result.Data().([]PowerPlantInfo)

		if len(temp) > 0 {
			RegenMP.FuelTypes_Heavy = true
		} else {
			RegenMP.FuelTypes_Heavy = false
		}

		tempValue = crowd.From(&datas).Sum(func(x interface{}) interface{} {
			return x.(PowerPlantInfo).GasTurbineUnit
		}).Exec().Result.Sum

		RegenMP.GasTurbineUnit = int(tempValue)

		RegenMP.GasTurbineCapacity = crowd.From(&datas).Sum(func(x interface{}) interface{} {
			return x.(PowerPlantInfo).GasTurbineCapacity
		}).Exec().Result.Sum

		tempValue = crowd.From(&datas).Sum(func(x interface{}) interface{} {
			return x.(PowerPlantInfo).SteamUnit
		}).Exec().Result.Sum

		RegenMP.SteamUnit = int(tempValue)

		RegenMP.SteamCapacity = crowd.From(&datas).Sum(func(x interface{}) interface{} {
			return x.(PowerPlantInfo).SteamCapacity
		}).Exec().Result.Sum

		tempValue = crowd.From(&datas).Sum(func(x interface{}) interface{} {
			return x.(PowerPlantInfo).DieselUnit
		}).Exec().Result.Sum

		RegenMP.DieselUnit = int(tempValue)

		RegenMP.DieselCapacity = crowd.From(&datas).Sum(func(x interface{}) interface{} {
			return x.(PowerPlantInfo).DieselCapacity
		}).Exec().Result.Sum

		tempValue = crowd.From(&datas).Sum(func(x interface{}) interface{} {
			return x.(PowerPlantInfo).CombinedCycleUnit
		}).Exec().Result.Sum

		RegenMP.CombinedCycleUnit = int(tempValue)

		RegenMP.CombinedCycleCapacity = crowd.From(&datas).Sum(func(x interface{}) interface{} {
			return x.(PowerPlantInfo).CombinedCycleCapacity
		}).Exec().Result.Sum

		e := ctx.Insert(RegenMP)

		if e != nil {
			tk.Println(e.Error())
		}
	}

	return e
}
func (q *Query) Cursor(in toolkit.M) (dbox.ICursor, error) {
	var e error
	/*
		if q.Parts == nil {
			return nil, errorlib.Error(packageName, modQuery,
				"Cursor", fmt.Sprintf("No Query Parts"))
		}
	*/

	aggregate := false
	dbname := q.Connection().Info().Database
	session := q.Session()
	cursor := dbox.NewCursor(new(Cursor))
	cursor.(*Cursor).session = session
	driverName := q.GetDriverDB()
	//driverName := "mssql"

	/*
		parts will return E - map{interface{}}interface{}
		where each interface{} returned is slice of interfaces --> []interface{}
	*/
	parts := crowd.From(q.Parts()).Group(func(x interface{}) interface{} {
		qp := x.(*dbox.QueryPart)
		return qp.PartType
	}, nil).Data

	fromParts, hasFrom := parts[dbox.QueryPartFrom]
	procedureParts, hasProcedure := parts["procedure"]

	if hasFrom {
		tablename := ""
		tablename = fromParts.([]interface{})[0].(*dbox.QueryPart).Value.(string)

		skip := 0
		if skipParts, hasSkip := parts[dbox.QueryPartSkip]; hasSkip {
			skip = skipParts.([]interface{})[0].(*dbox.QueryPart).
				Value.(int)
		}

		take := 0
		if takeParts, has := parts[dbox.QueryPartTake]; has {
			take = takeParts.([]interface{})[0].(*dbox.QueryPart).
				Value.(int)
		}

		var fields toolkit.M
		selectParts, hasSelect := parts[dbox.QueryPartSelect]
		var attribute string
		incAtt := 0
		if hasSelect {
			fields = toolkit.M{}
			for _, sl := range selectParts.([]interface{}) {
				qp := sl.(*dbox.QueryPart)
				for _, fid := range qp.Value.([]string) {
					if incAtt == 0 {
						attribute = fid
					} else {
						attribute = attribute + "," + fid
					}
					incAtt++
					fields.Set(fid, 1)
				}
			}
		} else {
			_, hasUpdate := parts[dbox.QueryPartUpdate]
			_, hasInsert := parts[dbox.QueryPartInsert]
			_, hasDelete := parts[dbox.QueryPartDelete]
			_, hasSave := parts[dbox.QueryPartSave]

			if hasUpdate || hasInsert || hasDelete || hasSave {
				return nil, errorlib.Error(packageName, modQuery, "Cursor",
					"Valid operation for a cursor is select only")
			}
		}
		//fmt.Printf("Result: %s \n", toolkit.JsonString(fields))
		//fmt.Printf("Database:%s table:%s \n", dbname, tablename)
		var sort []string
		sortParts, hasSort := parts[dbox.QueryPartSelect]
		if hasSort {
			sort = []string{}
			for _, sl := range sortParts.([]interface{}) {
				qp := sl.(*dbox.QueryPart)
				for _, fid := range qp.Value.([]string) {
					sort = append(sort, fid)
				}
			}
		}

		//where := toolkit.M{}
		var where interface{}
		whereParts, hasWhere := parts[dbox.QueryPartWhere]
		if hasWhere {
			fb := q.Connection().Fb()
			for _, p := range whereParts.([]interface{}) {
				fs := p.(*dbox.QueryPart).Value.([]*dbox.Filter)
				for _, f := range fs {
					fb.AddFilter(f)
				}
			}
			where, e = fb.Build()
			if e != nil {
				return nil, errorlib.Error(packageName, modQuery, "Cursor",
					e.Error())
			} else {
				//fmt.Printf("Where: %s", toolkit.JsonString(where))
			}
			//where = iwhere.(toolkit.M)
		}

		if dbname != "" && tablename != "" && e != nil && skip == 0 && take == 0 && where == nil {

		}
		if !aggregate {
			QueryString := ""
			if attribute == "" {
				QueryString = "SELECT * FROM " + tablename
			} else {
				QueryString = "SELECT " + attribute + " FROM " + tablename
			}
			if cast.ToString(where) != "" {
				QueryString = QueryString + " WHERE " + cast.ToString(where)
			}
			cursor.(*Cursor).QueryString = QueryString
		}
	} else if hasProcedure {
		procCommand := procedureParts.([]interface{})[0].(*dbox.QueryPart).Value.(interface{})
		fmt.Println("Isi Proc command : ", procCommand)

		spName := procCommand.(toolkit.M)["name"].(string) + " "
		params := procCommand.(toolkit.M)["parms"]
		incParam := 0
		ProcStatement := ""

		if driverName == "mysql" {
			paramValue := ""
			paramName := ""

			for key, val := range params.(toolkit.M) {
				if incParam == 0 {
					paramValue = "('" + val.(string) + "'"
					paramName = key
				} else {
					paramValue += ",'" + val.(string) + "'"
				}
				incParam += 1
			}
			paramValue += ", " + paramName + ")"

			ProcStatement = "CALL " + spName + paramValue
		} else if driverName == "mssql" {
			paramstring := ""
			incParam := 0
			for key, val := range params.(toolkit.M) {
				if incParam == 0 {
					paramstring = key + " = '" + val.(string) + "'"
				} else {
					paramstring += ", " + key + " = '" + val.(string) + "'"
				}
				incParam += 1
			}
			paramstring += ";"

			ProcStatement = "EXECUTE " + spName + paramstring
		} else if driverName == "oracle" {
			paramstring := ""
			incParam := 0
			for key, val := range params.(toolkit.M) {
				if incParam == 0 {
					paramstring = key + " = '" + val.(string) + "'"
				} else {
					paramstring += ", " + key + " = '" + val.(string) + "'"
				}
				incParam += 1
			}
			paramstring += ";"

			ProcStatement = "EXECUTE " + spName + paramstring

		}

		cursor.(*Cursor).QueryString = ProcStatement

		fmt.Println("Proc Statement : ", ProcStatement)
	}
	return cursor, nil
}
func (q *Query) Exec(parm toolkit.M) error {
	var e error
	if parm == nil {
		parm = toolkit.M{}
	}
	// fmt.Println("Parameter Exec : ", parm)

	dbname := q.Connection().Info().Database
	tablename := ""

	if parm == nil {
		parm = toolkit.M{}
	}
	data := parm.Get("data", nil)

	// fmt.Println("Hasil ekstraksi Param : ", data)

	//========================EXTRACT FIELD, DATA AND FORMAT DATE=============================

	var attributes string
	var values string
	var setUpdate string

	if data != nil {

		var reflectValue = reflect.ValueOf(data)
		if reflectValue.Kind() == reflect.Ptr {
			reflectValue = reflectValue.Elem()
		}
		var reflectType = reflectValue.Type()

		for i := 0; i < reflectValue.NumField(); i++ {
			namaField := reflectType.Field(i).Name
			dataValues := reflectValue.Field(i).Interface()
			stringValues := StringValue(dataValues, q.GetDriverDB())
			if i == 0 {
				attributes = "(" + namaField
				values = "(" + stringValues
				setUpdate = namaField + " = " + stringValues
			} else {
				attributes += " , " + namaField
				values += " , " + stringValues
				setUpdate += " , " + namaField + " = " + stringValues
			}
		}
		attributes += ")"
		values += ")"
	}

	//=================================END OF EXTRACTION=======================================

	temp := ""
	parts := crowd.From(q.Parts()).Group(func(x interface{}) interface{} {
		qp := x.(*dbox.QueryPart)
		temp = toolkit.JsonString(qp)
		return qp.PartType
	}, nil).Data

	fromParts, hasFrom := parts[dbox.QueryPartFrom]
	if !hasFrom {

		return errorlib.Error(packageName, "Query", modQuery, "Invalid table name")
	}
	tablename = fromParts.([]interface{})[0].(*dbox.QueryPart).Value.(string)

	var where interface{}
	whereParts, hasWhere := parts[dbox.QueryPartWhere]
	if hasWhere {
		fb := q.Connection().Fb()
		for _, p := range whereParts.([]interface{}) {
			fs := p.(*dbox.QueryPart).Value.([]*dbox.Filter)
			for _, f := range fs {
				fb.AddFilter(f)
			}
		}
		where, e = fb.Build()
		if e != nil {

		} else {

		}

	}
	commandType := ""
	multi := false

	_, hasDelete := parts[dbox.QueryPartDelete]
	_, hasInsert := parts[dbox.QueryPartInsert]
	_, hasUpdate := parts[dbox.QueryPartUpdate]
	_, hasSave := parts[dbox.QueryPartSave]

	if hasDelete {
		commandType = dbox.QueryPartDelete
	} else if hasInsert {
		commandType = dbox.QueryPartInsert
	} else if hasUpdate {
		commandType = dbox.QueryPartUpdate
	} else if hasSave {
		commandType = dbox.QueryPartSave
	}

	if data == nil {
		//---
		multi = true
	} else {
		if where == nil {
			id := toolkit.Id(data)
			if id != nil {
				where = (toolkit.M{}).Set("_id", id)
			}
		} else {
			multi = true
		}
	}
	session := q.Session()

	if dbname != "" && tablename != "" && multi == true {

	}
	if commandType == dbox.QueryPartInsert {

	} else if commandType == dbox.QueryPartUpdate {
		statement := "UPDATE " + tablename + " SET " + setUpdate + " WHERE " + cast.ToString(where)
		fmt.Println("Update Statement : ", statement)
		_, e = session.Exec(statement)
		if e != nil {
			fmt.Println(e.Error())
		}

	} else if commandType == dbox.QueryPartDelete {
		if where == nil {
			statement := "DELETE FROM " + tablename
			fmt.Println(statement)
			_, e = session.Exec(statement)
			if e != nil {
				fmt.Println(e.Error())
			}
		} else {
			statement := "DELETE FROM " + tablename + " where " + cast.ToString(where)
			fmt.Println(statement)
			_, e = session.Exec(statement)
			if e != nil {
				fmt.Println(e.Error())
			}
		}

	} else if commandType == dbox.QueryPartSave {

		statement := "INSERT INTO " + tablename + " " + attributes + " VALUES " + values
		fmt.Println("Insert Statement : ", statement)
		_, e = session.Exec(statement)
		if e != nil {
			fmt.Println(e.Error())
		}
	}
	if e != nil {
		return errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
	}
	return nil
}
// generatePreventiveCorrectiveSummary
func (s *GenPreventiveCorrectiveSummary) generatePreventiveCorrectiveSummary() error {
	var e error
	ctx := s.BaseController.Ctx
	c := ctx.Connection

	years := [3]int{2013, 2014, 2015}
	sintax := "select Distinct(Element) from MORSummary"
	csr, e := c.NewQuery().Command("freequery", tk.M{}.Set("syntax", sintax)).Cursor(nil)
	defer csr.Close()

	if e != nil {
		return e
	}

	MROElements := []tk.M{}
	e = csr.Fetch(&MROElements, 0, false)

	csr1, e := c.NewQuery().From(new(MasterEquipmentType).TableName()).Cursor(nil)
	defer csr1.Close()

	if e != nil {
		return e
	}

	query := []*dbox.Filter{}

	for _, year := range years {
		yearFirst := strconv.Itoa(year)
		yearFirst = yearFirst + "-01-01 00:00:00.000"

		yearLast := strconv.Itoa(year + 1)
		yearLast = yearLast + "-01-01 00:00:00.000"

		query = append(query, dbox.And(dbox.Gte("Period", yearFirst), dbox.Lte("Period", yearLast)))

		csr2, e := c.NewQuery().From(new(MaintenanceCost).TableName()).Where(query...).Cursor(nil)
		defer csr2.Close()

		if e != nil {
			return e
		}

		datas := []tk.M{}
		e = csr2.Fetch(&datas, 0, false)

		Plants := crowd.From(&datas).Group(func(x interface{}) interface{} {
			return x.(tk.M).GetString("plant")
		}, nil).Exec().Result.Data().([]crowd.KV)

		if len(Plants) > 0 {
			for _, p := range Plants {
				plant := p.Key.(string)
				EqType := crowd.From(&datas).Where(func(x interface{}) interface{} {
					period := x.(tk.M).GetString("period")
					return strings.Contains(period, strconv.Itoa(year)) && x.(tk.M).GetString("plant") == plant
				}).Exec().Result.Data().([]tk.M)

				if len(EqType) > 0 {
					EquipmentTypes := crowd.From(&EqType).Group(func(x interface{}) interface{} {
						return x.(tk.M).GetString("equipmenttype")
					}, nil).Exec().Result.Data().([]crowd.KV)

					for _, eq := range EquipmentTypes {
						EquipmentType := eq.Key.(string)
						ActType := crowd.From(&EqType).Where(func(x interface{}) interface{} {
							return x.(tk.M).GetString("equipmenttype") == EquipmentType
						}).Exec().Result.Data().([]tk.M)

						if len(ActType) > 0 {
							MaintActivityTypes := crowd.From(&ActType).Group(func(x interface{}) interface{} {
								return x.(tk.M).GetString("maintenanceactivitytype")
							}, nil).Exec().Result.Data().([]crowd.KV)

							for _, act := range MaintActivityTypes {
								MaintActivityType := act.Key.(string)

								OrderType := crowd.From(&ActType).Where(func(x interface{}) interface{} {
									return x.(tk.M).GetString("maintenanceactivitytype") == MaintActivityType
								}).Exec().Result.Data().([]tk.M)

								if len(OrderType) > 0 {
									OrderTypes := crowd.From(&OrderType).Group(func(x interface{}) interface{} {
										return x.(tk.M).GetString("ordertype")
									}, nil).Exec().Result.Data().([]crowd.KV)

									for _, order := range OrderTypes {
										OrderTypeString := order.Key.(string)
										OrderNo := crowd.From(&OrderType).Where(func(x interface{}) interface{} {
											return x.(tk.M).GetString("ordertype") == OrderTypeString
										}).Exec().Result.Data().([]tk.M)
										if len(OrderNo) > 0 {
											Equipment := crowd.From(&OrderNo).Group(func(x interface{}) interface{} {
												return x.(tk.M).GetString("equipment")
											}, nil).Exec().Result.Data().([]crowd.KV)

											for _, eqNo := range Equipment {
												eqNoString := eqNo.Key.(string)
												for _, element := range MROElements {
													_ = element
													pcs := new(PreventiveCorrectiveSummary)
													pcs.PeriodYear = year
													pcs.OrderType = OrderTypeString
													pcs.EquipmentNo = eqNoString

													equipmentDescription := crowd.From(&OrderNo).Where(func(x interface{}) interface{} {
														return x.(tk.M).GetString("equipment") == eqNoString
													}).Exec().Result.Data().([]tk.M)

													if len(equipmentDescription) > 0 {
														pcs.EquipmentDescription = equipmentDescription[0].GetString("equipmentdesc")
													}

													if len(EquipmentTypes) == 1 {
														pcs.EquipmentType = "Other"
														pcs.EquipmentTypeDescription = "Other"
													} else {
														pcs.EquipmentType = EquipmentType
														pcs.EquipmentTypeDescription = equipmentDescription[0].GetString("equipmenttypedesc")
													}

													pcs.ActivityType = MaintActivityType
													pcs.Plant = PlantNormalization(plant)
													pcs.Element = element.GetString("element")

													result := float64(len(equipmentDescription) / len(MROElements))
													pcs.MOCount = int(Round(result, .5, 2))

													switch element.GetString("element") {
													case "Internal Labor":
														pcs.Value = crowd.From(&equipmentDescription).Sum(func(x interface{}) interface{} {
															return x.(tk.M).GetFloat64("internallaboractual")
														}).Exec().Result.Sum
														break
													case "Internal Material":
														pcs.Value = crowd.From(&equipmentDescription).Sum(func(x interface{}) interface{} {
															return x.(tk.M).GetFloat64("internalmaterialactual")
														}).Exec().Result.Sum
														break
													case "Direct Material":
														pcs.Value = crowd.From(&equipmentDescription).Sum(func(x interface{}) interface{} {
															return x.(tk.M).GetFloat64("directmaterialactual")
														}).Exec().Result.Sum
														break
													case "External Service":
														pcs.Value = crowd.From(&equipmentDescription).Sum(func(x interface{}) interface{} {
															return x.(tk.M).GetFloat64("externalserviceactual")
														}).Exec().Result.Sum
														break
													}

													_, e := ctx.InsertOut(pcs)

													if e != nil {
														log.Println(e.Error())
													}
												}
											}
										}
									}
								}
							}
						}
					}
				}
			}
		}
	}

	return e
}
Exemple #15
0
func (q *Query) Cursor(in toolkit.M) (dbox.ICursor, error) {
	var e error
	/*
		if q.Parts == nil {
			return nil, errorlib.Error(packageName, modQuery,
				"Cursor", fmt.Sprintf("No Query Parts"))
		}
	*/

	aggregate := false
	dbname := q.Connection().Info().Database
	tablename := ""

	/*
		parts will return E - map{interface{}}interface{}
		where each interface{} returned is slice of interfaces --> []interface{}
	*/
	parts := crowd.From(q.Parts()).Group(func(x interface{}) interface{} {
		qp := x.(*dbox.QueryPart)
		return qp.PartType
	}, nil).Data

	fromParts, hasFrom := parts[dbox.QueryPartFrom]
	if hasFrom == false {
		return nil, errorlib.Error(packageName, "Query", "Cursor", "Invalid table name")
	}
	tablename = fromParts.([]interface{})[0].(*dbox.QueryPart).Value.(string)

	skip := 0
	if skipParts, hasSkip := parts[dbox.QueryPartSkip]; hasSkip {
		skip = skipParts.([]interface{})[0].(*dbox.QueryPart).
			Value.(int)
	}

	take := 0
	if takeParts, has := parts[dbox.QueryPartTake]; has {
		take = takeParts.([]interface{})[0].(*dbox.QueryPart).
			Value.(int)
	}

	var fields toolkit.M
	selectParts, hasSelect := parts[dbox.QueryPartSelect]
	var attribute string
	incAtt := 0
	if hasSelect {
		fields = toolkit.M{}
		for _, sl := range selectParts.([]interface{}) {
			qp := sl.(*dbox.QueryPart)
			for _, fid := range qp.Value.([]string) {
				if incAtt == 0 {
					attribute = fid
				} else {
					attribute = attribute + "," + fid
				}
				incAtt++
				fields.Set(fid, 1)
			}
		}
	} else {
		_, hasUpdate := parts[dbox.QueryPartUpdate]
		_, hasInsert := parts[dbox.QueryPartInsert]
		_, hasDelete := parts[dbox.QueryPartDelete]
		_, hasSave := parts[dbox.QueryPartSave]

		if hasUpdate || hasInsert || hasDelete || hasSave {
			return nil, errorlib.Error(packageName, modQuery, "Cursor",
				"Valid operation for a cursor is select only")
		}
	}
	//fmt.Printf("Result: %s \n", toolkit.JsonString(fields))
	//fmt.Printf("Database:%s table:%s \n", dbname, tablename)
	var sort []string
	sortParts, hasSort := parts[dbox.QueryPartSelect]
	if hasSort {
		sort = []string{}
		for _, sl := range sortParts.([]interface{}) {
			qp := sl.(*dbox.QueryPart)
			for _, fid := range qp.Value.([]string) {
				sort = append(sort, fid)
			}
		}
	}

	//where := toolkit.M{}
	var where interface{}
	whereParts, hasWhere := parts[dbox.QueryPartWhere]
	if hasWhere {
		fb := q.Connection().Fb()
		for _, p := range whereParts.([]interface{}) {
			fs := p.(*dbox.QueryPart).Value.([]*dbox.Filter)
			for _, f := range fs {
				fb.AddFilter(f)
			}
		}
		where, e = fb.Build()
		if e != nil {
			return nil, errorlib.Error(packageName, modQuery, "Cursor",
				e.Error())
		} else {
			//fmt.Printf("Where: %s", toolkit.JsonString(where))
		}
		//where = iwhere.(toolkit.M)
	}

	session := q.Session()
	cursor := dbox.NewCursor(new(Cursor))
	cursor.(*Cursor).session = session
	if dbname != "" && tablename != "" && e != nil && skip == 0 && take == 0 && where == nil {

	}
	if !aggregate {
		QueryString := ""
		if attribute == "" {
			QueryString = "SELECT * FROM " + tablename
		} else {
			QueryString = "SELECT " + attribute + " FROM " + tablename
		}
		if cast.ToString(where) != "" {
			QueryString = QueryString + " WHERE " + cast.ToString(where)
		}
		cursor.(*Cursor).QueryString = QueryString
	} else {

	}
	return cursor, nil
}
Exemple #16
0
func (q *Query) Cursor(in toolkit.M) (dbox.ICursor, error) {
	var e error
	/*
		if q.Parts == nil {
			return nil, errorlib.Error(packageName, modQuery,
				"Cursor", fmt.Sprintf("No Query Parts"))
		}
	*/

	aggregate := false
	t, _ := ioutil.ReadFile(q.Connection().(*Connection).filePath)
	cursor := dbox.NewCursor(new(Cursor))
	cursor = cursor.SetConnection(q.Connection())
	cursor.(*Cursor).readFile = t

	/*
		parts will return E - map{interface{}}interface{}
		where each interface{} returned is slice of interfaces --> []interface{}
	*/
	parts := crowd.From(q.Parts()).Group(func(x interface{}) interface{} {
		qp := x.(*dbox.QueryPart)
		return qp.PartType
	}, nil).Data

	// var fields toolkit.M
	var fields []string
	selectParts, hasSelect := parts[dbox.QueryPartSelect]
	if hasSelect {
		// fields = toolkit.M{}
		for _, sl := range selectParts.([]interface{}) {
			qp := sl.(*dbox.QueryPart)
			for _, fid := range qp.Value.([]string) {
				fields = append(fields, fid)
				// fields.Set(fid, fid)
			}
		}
	} else {
		_, hasUpdate := parts[dbox.QueryPartUpdate]
		_, hasInsert := parts[dbox.QueryPartInsert]
		_, hasDelete := parts[dbox.QueryPartDelete]
		_, hasSave := parts[dbox.QueryPartSave]

		if hasUpdate || hasInsert || hasDelete || hasSave {
			return nil, errorlib.Error(packageName, modQuery, "Cursor",
				"Valid operation for a cursor is select only")
		}
	}

	// //where := toolkit.M{}
	var where interface{}
	whereParts, hasWhere := parts[dbox.QueryPartWhere]
	if hasWhere {
		fb := q.Connection().Fb()
		for _, p := range whereParts.([]interface{}) {
			fs := p.(*dbox.QueryPart).Value.([]*dbox.Filter)
			for _, f := range fs {
				fb.AddFilter(f)
			}
		}
		where, e = fb.Build()
		if e != nil {
			return nil, errorlib.Error(packageName, modQuery, "Cursor",
				e.Error())
		} else {
		}
	}

	if !aggregate {
		var whereFields interface{}
		var dataInterface interface{}
		json.Unmarshal(t, &dataInterface)
		count, ok := dataInterface.([]interface{})

		if !ok {
			return nil, errorlib.Error(packageName,
				modQuery, "Cursor", "the file contains invalid json data")
		}
		cursor.(*Cursor).count = len(count)
		/*if fields != nil {
			q.Connection().(*Connection).FetchSession()
			// jsonSelect = fields
		}*/
		if where != nil {
			whereFields = where
			// jsonSelect = fields
			cursor.(*Cursor).isWhere = true
		}
		// cursor.(*Cursor).tempPathFile = q.Connection().(*Connection).tempPathFile
		cursor.(*Cursor).whereFields = whereFields
		cursor.(*Cursor).jsonSelect = fields
	} else {

	}
	return cursor, nil
}
Exemple #17
0
func (q *Query) Exec(parm toolkit.M) error {
	var e error

	if parm == nil {
		parm = toolkit.M{}
	}

	data := parm.Get("data", nil)
	filePath := q.Connection().(*Connection).filePath
	/*
		p arts will return E - map{interface{}}interface{}
		where each interface{} returned is slice of interfaces --> []interface{}
	*/
	parts := crowd.From(q.Parts()).Group(func(x interface{}) interface{} {
		qp := x.(*dbox.QueryPart)
		/*
			fmt.Printf("[%s] QP = %s \n",
				toolkit.Id(data),
				toolkit.JsonString(qp))
		*/
		return qp.PartType
	}, nil).Data

	var where interface{}
	commandType := ""
	multi := false

	_, hasDelete := parts[dbox.QueryPartDelete]
	_, hasInsert := parts[dbox.QueryPartInsert]
	_, hasUpdate := parts[dbox.QueryPartUpdate]
	_, hasSave := parts[dbox.QueryPartSave]

	if hasDelete {
		commandType = dbox.QueryPartDelete
	} else if hasInsert {
		commandType = dbox.QueryPartInsert
	} else if hasUpdate {
		commandType = dbox.QueryPartUpdate
	} else if hasSave {
		commandType = dbox.QueryPartSave
	}
	//fmt.Printf("Data:\n%v\n", toolkit.JsonString(data))

	if data == nil {
		//---
		multi = true

		//
		whereParts, hasWhere := parts[dbox.QueryPartWhere]
		if hasWhere {

			fb := q.Connection().Fb()
			for _, p := range whereParts.([]interface{}) {
				fs := p.(*dbox.QueryPart).Value.([]*dbox.Filter)
				for _, f := range fs {
					fb.AddFilter(f)
				}
			}
			where, e = fb.Build()
			if e != nil {
				return errorlib.Error(packageName, modQuery, "Cursor",
					e.Error())
			} else {
				//fmt.Printf("Where: %s", toolkit.JsonString(where))
			}
		}
	} else {
		if where == nil {
			id := toolkit.Id(data)
			if id != nil {
				where = (toolkit.M{}).Set("id", id)
			}
		} else {
			multi = true
		}
	}

	if commandType == dbox.QueryPartInsert {

		var jsonUpdatedValue []byte
		if reflect.ValueOf(data).Kind() == reflect.Slice {
			readF, _ := ioutil.ReadFile(filePath)

			var dataMap []toolkit.M
			e := json.Unmarshal(readF, &dataMap)
			if e != nil {
				return errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
			}

			j, err := json.Marshal(data)
			if err != nil {
				return errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
			}

			var jToMap []toolkit.M
			e = json.Unmarshal(j, &jToMap)

			var sliceData []toolkit.M
			for _, v := range jToMap {
				id := toolkit.Id(v)
				if id == nil {
					return errorlib.Error(packageName, modCursor+".Exec", commandType, "Unable to find ID when insert slice data")
				}
				sliceData = finUpdateObj(dataMap, v, "insert")
			}

			jsonUpdatedValue, err = json.MarshalIndent(sliceData, "", "  ")
			if err != nil {
				return errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
			}
		} else {
			readF, e := ioutil.ReadFile(filePath)
			if e != nil {
				return errorlib.Error(packageName, modCursor+".Exec", commandType, e.Error())
			}

			var dataMap []toolkit.M
			e = json.Unmarshal(readF, &dataMap)
			if e != nil {
				return errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
			}
			dataToMap, e := toolkit.ToM(data)
			if e != nil {
				return errorlib.Error(packageName, modCursor+".Exec", commandType, e.Error())
			}

			id := toolkit.Id(dataToMap)
			if id == nil {
				return errorlib.Error(packageName, modCursor+".Exec", commandType, "Unable to find ID when insert struct data")
			}
			idToS := ToString(reflect.ValueOf(id).Kind(), id)

			for _, vDataMap := range dataMap {
				idDataMap := toolkit.Id(vDataMap)
				idToSMap := ToString(reflect.ValueOf(idDataMap).Kind(), idDataMap)
				if id == nil {
					return errorlib.Error(packageName, modCursor+".Exec", commandType, "Unable to find ID when insert struct data")
				}

				if strings.ToLower(idToS) == strings.ToLower(idToSMap) {
					return errorlib.Error(packageName, modCursor+".Exec", commandType, "ID "+idToSMap+" already exist, unable insert data ")
				}
			}

			updatedValue := finUpdateObj(dataMap, dataToMap, "insert")
			jsonUpdatedValue, e = json.MarshalIndent(updatedValue, "", "  ")
			if e != nil {
				return errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
			}
		}

		q.Connection().(*Connection).WriteSession()
		i, e := q.Connection().(*Connection).openFile.Write(jsonUpdatedValue) //t.WriteString(string(j))
		if i == 0 || e != nil {
			return errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
		}

		err := q.Connection().(*Connection).CloseWriteSession()
		if err != nil {
			return errorlib.Error(packageName, modQuery+".Exec", commandType, err.Error())
		}
	} else if commandType == dbox.QueryPartUpdate {
		if multi {
			// 		_, e = mgoColl.UpdateAll(where, data)
		} else {
			readF, e := ioutil.ReadFile(filePath)
			if e != nil {
				return errorlib.Error(packageName, modCursor+".Exec", commandType, e.Error())
			}

			var dataMap []toolkit.M
			e = json.Unmarshal(readF, &dataMap)
			if e != nil {
				return errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
			}
			a, e := toolkit.ToM(data)
			if e != nil {
				return errorlib.Error(packageName, modCursor+".Exec", commandType, e.Error())
			}

			id := toolkit.Id(a)
			if id == nil {
				return errorlib.Error(packageName, modCursor+".Exec", commandType, "Unable to find ID when update data")
			}

			updatedValue := finUpdateObj(dataMap, a, "update")

			jsonUpdatedValue, err := json.MarshalIndent(updatedValue, "", "  ")
			if err != nil {
				return errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
			}

			err = q.Connection().(*Connection).WriteSession()
			if err != nil {
				return errorlib.Error(packageName, modQuery+".Exec", commandType, err.Error())
			}

			i, e := q.Connection().(*Connection).openFile.Write(jsonUpdatedValue) //t.WriteString(string(j))
			if i == 0 || e != nil {
				return errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
			}

			err = q.Connection().(*Connection).CloseWriteSession()
			if err != nil {
				return errorlib.Error(packageName, modQuery+".Exec", commandType, err.Error())
			}
		}
	} else if commandType == dbox.QueryPartDelete {
		if multi {
			if where != nil {
				readF, e := ioutil.ReadFile(filePath)
				if e != nil {
					return errorlib.Error(packageName, modCursor+".Exec", commandType, e.Error())
				}

				var dataMap []toolkit.M
				e = json.Unmarshal(readF, &dataMap)
				if e != nil {
					return errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
				}
				a, e := toolkit.ToM(where)
				if e != nil {
					return errorlib.Error(packageName, modCursor+".Exec", commandType, e.Error())
				}

				id := toolkit.Id(a)
				if id == nil {
					return errorlib.Error(packageName, modCursor+".Exec", commandType, "Unable to find ID when delete data")
				}

				updatedValue := finUpdateObj(dataMap, a, "deleteMulti")

				jsonUpdatedValue, err := json.MarshalIndent(updatedValue, "", "  ")
				if err != nil {
					return errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
				}

				err = q.Connection().(*Connection).WriteSession()
				if err != nil {
					return errorlib.Error(packageName, modQuery+".Exec", commandType, err.Error())
				}

				if string(jsonUpdatedValue) == "null" {
					ioutil.WriteFile(q.Connection().(*Connection).filePath, []byte("[\n]"), 0666)
				} else {
					i, e := q.Connection().(*Connection).openFile.Write(jsonUpdatedValue) //t.WriteString(string(j))
					if i == 0 || e != nil {
						return errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
					}

					err = q.Connection().(*Connection).CloseWriteSession()
					if err != nil {
						return errorlib.Error(packageName, modQuery+".Exec", commandType, err.Error())
					}
				}
			} else {
				e := os.Remove(filePath)
				if e != nil {
					return errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
				}

				_, err := os.Stat(filePath)
				if os.IsNotExist(err) {
					cf, e := os.Create(filePath)
					if e != nil {
						return errorlib.Error(packageName, modCursor+".Exec", commandType, e.Error())
					}
					cf.Close()
				}
				ioutil.WriteFile(q.Connection().(*Connection).filePath, []byte("[\n]"), 0666)
			}
		} else {

		}
	} else if commandType == dbox.QueryPartSave {
		/*dataType := reflect.ValueOf(data).Kind()
		//fmt.Printf("DataType: %s\nData:\n%v\n", dataType.String(), toolkit.JsonString(data))
		if reflect.Slice == dataType {
			if q.Connection().(*Connection).openFile == nil {
				q.Connection().(*Connection).OpenSession()
			}

			if q.Connection().(*Connection).isNewSave {
				j, err := json.MarshalIndent(data, "", "  ")
				if err != nil {
					return errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
				}

				i, e := q.Connection().(*Connection).openFile.Write(j) //t.WriteString(string(j))
				if i == 0 || e != nil {
					return errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
				}
			} else {
				readF, e := ioutil.ReadFile(filePath)
				if e != nil {
					return errorlib.Error(packageName, modCursor+".Exec", commandType, e.Error())
				}

				var dataMap, newData []map[string]interface{}
				if e := json.Unmarshal(readF, &dataMap); e != nil {
					return errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
				}

				v := toolkit.JsonString(data)
				if e = json.Unmarshal([]byte(v), &newData); e != nil {
					return errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
				}

				mergeData := append(dataMap, newData...)

				jsonUpdatedValue, err := json.MarshalIndent(mergeData, "", "  ")
				if err != nil {
					return errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
				}

				e = ioutil.WriteFile(filePath, jsonUpdatedValue, 0666)
				if e != nil {
					return errorlib.Error(packageName, modQuery+".Exec", "Write file", e.Error())
				}
			}
		} else {*/
		if q.Connection().(*Connection).openFile == nil {
			q.Connection().(*Connection).OpenSession()
		}
		q.dataType = "struct"
		dataMap, e := toolkit.ToM(data)
		if e != nil {
			return errorlib.Error(packageName, modCursor+".Exec", commandType, e.Error())
		}

		id := toolkit.Id(dataMap)
		if id == nil {
			return errorlib.Error(packageName, modCursor+".Exec", commandType, "Unable to find ID")
		}

		idString := ToString(reflect.ValueOf(id).Kind(), id)

		if q.Connection().(*Connection).sData == "" {
			q.Connection().(*Connection).sData = idString
		} else if q.Connection().(*Connection).sData == idString {
			q.Connection().(*Connection).sData = idString
			updatedValue := finUpdateObj(q.sliceData, dataMap, "update")
			q.sliceData = updatedValue
		} else {
			q.Connection().(*Connection).sData = idString
			_ = finUpdateObj(q.sliceData, dataMap, "update")
			q.sliceData = append(q.sliceData, dataMap)
		}

		if len(q.sliceData) == 0 {
			q.sliceData = append(q.sliceData, dataMap)
		}

		if q.Connection().(*Connection).isNewSave {
			q.hasNewSave = hasSave
		} else {
			q.hasSave = hasSave
		}
		/*}*/
	}

	if e != nil {
		return errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
	}

	return nil
}
Exemple #18
0
func (q *Query) Cursor(in toolkit.M) (dbox.ICursor, error) {
	var e error

	aggregate := false

	if q.Connection().(*Connection).setNewHeader {
		q.Connection().(*Connection).Close()
		filename := q.Connection().(*Connection).Info().Host
		os.Remove(filename)
		return nil, errorlib.Error(packageName, "Cursor", modQuery, "Only Insert Query Permited")
	}

	parts := crowd.From(q.Parts()).Group(func(x interface{}) interface{} {
		qp := x.(*dbox.QueryPart)
		return qp.PartType
	}, nil).Data

	skip := 0
	if skipParts, hasSkip := parts[dbox.QueryPartSkip]; hasSkip {
		skip = skipParts.([]interface{})[0].(*dbox.QueryPart).
			Value.(int)
	}

	take := 0
	if takeParts, has := parts[dbox.QueryPartTake]; has {
		take = takeParts.([]interface{})[0].(*dbox.QueryPart).
			Value.(int)
	}

	var fields toolkit.M
	selectParts, hasSelect := parts[dbox.QueryPartSelect]
	if hasSelect {
		fields = toolkit.M{}
		for _, sl := range selectParts.([]interface{}) {
			qp := sl.(*dbox.QueryPart)
			for _, fid := range qp.Value.([]string) {
				fields.Set(fid, 1)
			}
		}
	} else {
		_, hasUpdate := parts[dbox.QueryPartUpdate]
		_, hasInsert := parts[dbox.QueryPartInsert]
		_, hasDelete := parts[dbox.QueryPartDelete]
		_, hasSave := parts[dbox.QueryPartSave]

		if hasUpdate || hasInsert || hasDelete || hasSave {
			return nil, errorlib.Error(packageName, modQuery, "Cursor",
				"Valid operation for a cursor is select only")
		}
	}

	var sort []string
	sortParts, hasSort := parts[dbox.QueryPartSelect]
	if hasSort {
		sort = []string{}
		for _, sl := range sortParts.([]interface{}) {
			qp := sl.(*dbox.QueryPart)
			for _, fid := range qp.Value.([]string) {
				sort = append(sort, fid)
			}
		}
	}

	var where interface{}
	whereParts, hasWhere := parts[dbox.QueryPartWhere]
	if hasWhere {
		fb := q.Connection().Fb()
		for _, p := range whereParts.([]interface{}) {
			fs := p.(*dbox.QueryPart).Value.([]*dbox.Filter)
			for _, f := range fs {
				fb.AddFilter(f)
			}
		}
		where, e = fb.Build()
		if e != nil {
			return nil, errorlib.Error(packageName, modQuery, "Cursor",
				e.Error())
		} else {
			//fmt.Printf("Where: %s", toolkit.JsonString(where))
		}
		//where = iwhere.(toolkit.M)
	}

	cursor := dbox.NewCursor(new(Cursor))
	cursor = cursor.SetConnection(q.Connection())

	cursor.(*Cursor).file = q.File()
	cursor.(*Cursor).reader = q.Reader()
	cursor.(*Cursor).headerColumn = q.Connection().(*Connection).headerColumn
	cursor.(*Cursor).count = 0
	// fmt.Println(cursor.(*Cursor).headerColumn)
	if e != nil {
		return nil, errorlib.Error(packageName, modQuery, "Cursor", e.Error())
	}

	if !aggregate {
		// fmt.Println("Query 173 : ", where)
		cursor.(*Cursor).ConditionVal.Find, _ = toolkit.ToM(where)

		if fields != nil {
			cursor.(*Cursor).ConditionVal.Select = fields
		}

		if hasSort {
			cursor.(*Cursor).ConditionVal.Sort = sort
		}
		cursor.(*Cursor).ConditionVal.skip = skip
		cursor.(*Cursor).ConditionVal.limit = take

	} else {
		/*		pipes := toolkit.M{}
				mgoPipe := session.DB(dbname).C(tablename).
					Pipe(pipes).AllowDiskUse()
				//iter := mgoPipe.Iter()

				cursor.(*Cursor).ResultType = QueryResultPipe
				cursor.(*Cursor).mgoPipe = mgoPipe
				//cursor.(*Cursor).mgoIter = iter
		*/
	}
	return cursor, nil
}
Exemple #19
0
func (q *Query) Exec(parm toolkit.M) error {
	var e error
	q.save = false

	// useHeader := q.Connection().Info().Settings.Get("useheader", false).(bool)
	if parm == nil {
		parm = toolkit.M{}
	}

	data := parm.Get("data", nil)

	parts := crowd.From(q.Parts()).Group(func(x interface{}) interface{} {
		qp := x.(*dbox.QueryPart)
		return qp.PartType
	}, nil).Data

	// fromParts, hasFrom := parts[dbox.QueryPartFrom]
	// if !hasFrom {
	// 	return errorlib.Error(packageName, "Query", modQuery, "Invalid table name")
	// }
	// tablename = fromParts.([]interface{})[0].(*dbox.QueryPart).Value.(string)

	// var where interface{}
	commandType := ""
	//	multi := false

	_, hasDelete := parts[dbox.QueryPartDelete]
	_, hasInsert := parts[dbox.QueryPartInsert]
	_, hasUpdate := parts[dbox.QueryPartUpdate]
	_, hasSave := parts[dbox.QueryPartSave]

	if hasDelete {
		commandType = dbox.QueryPartDelete
	} else if hasInsert {
		commandType = dbox.QueryPartInsert
	} else if hasUpdate {
		commandType = dbox.QueryPartUpdate
	} else if hasSave {
		commandType = dbox.QueryPartSave
		q.save = true
	}

	var where interface{}
	whereParts, hasWhere := parts[dbox.QueryPartWhere]
	if hasWhere {
		fb := q.Connection().Fb()
		for _, p := range whereParts.([]interface{}) {
			fs := p.(*dbox.QueryPart).Value.([]*dbox.Filter)
			for _, f := range fs {
				fb.AddFilter(f)
			}
		}
		where, e = fb.Build()
		if e != nil {
			return errorlib.Error(packageName, modQuery, "Cursor", e.Error())
		}
	}

	//Check setNewHeader First
	if q.Connection().(*Connection).setNewHeader && commandType != dbox.QueryPartInsert {
		q.Connection().(*Connection).Close()
		filename := q.Connection().(*Connection).Info().Host
		os.Remove(filename)
		return errorlib.Error(packageName, "Query", modQuery, "Only Insert Permited")
	}

	q.Connection().(*Connection).TypeOpenFile = TypeOpenFile_Append
	if hasDelete || hasUpdate {
		q.Connection().(*Connection).TypeOpenFile = TypeOpenFile_Create
	}

	q.Connection().(*Connection).ExecOpr = false
	if !q.Connection().(*Connection).setNewHeader && (commandType != dbox.QueryPartSave || (commandType == dbox.QueryPartSave && q.Connection().(*Connection).writer == nil)) {
		e = q.Connection().(*Connection).StartSessionWrite()
	}

	if e != nil {
		return errorlib.Error(packageName, "Query", modQuery, e.Error())
	}

	writer := q.Connection().(*Connection).writer
	reader := q.Connection().(*Connection).reader

	var execCond QueryCondition
	execCond.Find, _ = toolkit.ToM(where)

	switch commandType {
	case dbox.QueryPartInsert, dbox.QueryPartSave:
		var dataTemp []string
		dataMformat, _ := toolkit.ToM(data)
		// fmt.Println("LINE338:", q.Connection().(*Connection).setNewHeader)
		if q.Connection().(*Connection).setNewHeader {
			q.Connection().(*Connection).SetHeaderToolkitM(dataMformat)
			q.Connection().(*Connection).setNewHeader = false

			for _, v := range q.Connection().(*Connection).headerColumn {
				dataTemp = append(dataTemp, v.name)
			}

			if len(dataTemp) > 0 {
				writer.Write(dataTemp)
				writer.Flush()
			}
			// fmt.Println("LINE342:", q.Connection().(*Connection).headerColumn)
			dataTemp = []string{}
		}

		for _, v := range q.Connection().(*Connection).headerColumn {
			if dataMformat.Has(v.name) {
				dataTemp = append(dataTemp, cast.ToString(dataMformat[v.name]))
			} else {
				dataTemp = append(dataTemp, "")
			}
		}

		if len(dataTemp) > 0 {
			writer.Write(dataTemp)
			writer.Flush()
		}
	case dbox.QueryPartDelete:
		var tempHeader []string

		for _, val := range q.Connection().(*Connection).headerColumn {
			tempHeader = append(tempHeader, val.name)
		}

		for {
			foundDelete := true
			recData := toolkit.M{}

			dataTemp, e := reader.Read()
			for i, val := range dataTemp {
				recData.Set(tempHeader[i], val)
			}

			foundDelete = execCond.getCondition(recData)

			if e == io.EOF {
				if !foundDelete && dataTemp != nil {
					writer.Write(dataTemp)
					writer.Flush()
				}
				break
			} else if e != nil {
				return errorlib.Error(packageName, modQuery, "Delete", e.Error())
			}

			if !foundDelete && dataTemp != nil {
				writer.Write(dataTemp)
				writer.Flush()
			}
		}
	case dbox.QueryPartUpdate:
		var tempHeader []string

		if data == nil {
			break
		}

		dataMformat, _ := toolkit.ToM(data)

		for _, val := range q.Connection().(*Connection).headerColumn {
			tempHeader = append(tempHeader, val.name)
		}

		for {
			foundChange := false

			recData := toolkit.M{}
			dataTemp, e := reader.Read()
			for i, val := range dataTemp {
				recData.Set(tempHeader[i], val)
			}

			foundChange = execCond.getCondition(recData)
			if foundChange && len(dataTemp) > 0 {
				for n, v := range tempHeader {
					if dataMformat.Has(v) {
						dataTemp[n] = cast.ToString(dataMformat[v])
					}
				}
			}

			if e == io.EOF {
				if dataTemp != nil {
					writer.Write(dataTemp)
					writer.Flush()
				}
				break
			} else if e != nil {
				return errorlib.Error(packageName, modQuery, "Update", e.Error())
			}
			if dataTemp != nil {
				writer.Write(dataTemp)
				writer.Flush()
			}
		}
	}

	q.Connection().(*Connection).ExecOpr = true
	if commandType != dbox.QueryPartSave {
		e = q.Connection().(*Connection).EndSessionWrite()
	}

	return nil
}
Exemple #20
0
func (q *Query) Exec(parm toolkit.M) error {
	var e error
	q.save = false

	// useHeader := q.Connection().Info().Settings.Get("useheader", false).(bool)
	if parm == nil {
		parm = toolkit.M{}
	}

	data := toolkit.M{}
	if parm.Has("data") {
		data, _ = toolkit.ToM(parm["data"])
	}

	parts := crowd.From(q.Parts()).Group(func(x interface{}) interface{} {
		qp := x.(*dbox.QueryPart)
		return qp.PartType
	}, nil).Data

	// fromParts, hasFrom := parts[dbox.QueryPartFrom]
	// if !hasFrom {
	// 	return errorlib.Error(packageName, "Query", modQuery, "Invalid table name")
	// }
	// tablename = fromParts.([]interface{})[0].(*dbox.QueryPart).Value.(string)

	// var where interface{}
	commandType := ""
	//	multi := false

	_, hasDelete := parts[dbox.QueryPartDelete]
	_, hasInsert := parts[dbox.QueryPartInsert]
	_, hasUpdate := parts[dbox.QueryPartUpdate]
	_, hasSave := parts[dbox.QueryPartSave]

	if hasDelete {
		commandType = dbox.QueryPartDelete
	} else if hasInsert {
		commandType = dbox.QueryPartInsert
	} else if hasUpdate {
		commandType = dbox.QueryPartUpdate
	} else if hasSave {
		commandType = dbox.QueryPartSave
		q.save = true
	}

	var where interface{}
	whereParts, hasWhere := parts[dbox.QueryPartWhere]
	if hasWhere {
		fb := q.Connection().Fb()
		for _, p := range whereParts.([]interface{}) {
			fs := p.(*dbox.QueryPart).Value.([]*dbox.Filter)
			for _, f := range fs {
				if len(parm) > 0 {
					f = ReadVariable(f, parm)
				}
				fb.AddFilter(f)
			}
		}
		where, e = fb.Build()
		if e != nil {
			return errorlib.Error(packageName, modQuery, "Cursor", e.Error())
		}
	}

	//Check setNewHeader First
	if q.Connection().(*Connection).setNewHeader && commandType != dbox.QueryPartInsert {
		q.Connection().(*Connection).Close()
		filename := q.Connection().(*Connection).Info().Host
		os.Remove(filename)
		return errorlib.Error(packageName, "Query", modQuery, "Only Insert Permited")
	}

	q.Connection().(*Connection).TypeOpenFile = TypeOpenFile_Append
	if hasDelete || hasUpdate {
		q.Connection().(*Connection).TypeOpenFile = TypeOpenFile_Create
	}

	q.Connection().(*Connection).ExecOpr = false
	if !q.Connection().(*Connection).setNewHeader && (commandType != dbox.QueryPartSave || (commandType == dbox.QueryPartSave && q.Connection().(*Connection).writer == nil)) {
		e = q.Connection().(*Connection).StartSessionWrite()
	}

	if e != nil {
		return errorlib.Error(packageName, "Query", modQuery, e.Error())
	}

	var execCond QueryCondition
	execCond.Find, _ = toolkit.ToM(where)

	switch commandType {
	case dbox.QueryPartSave:
		q.updatessave = false
		e = q.execQueryPartSave(data)
	case dbox.QueryPartInsert:
		e = q.execQueryPartInsert(data)
	case dbox.QueryPartDelete:
		e = q.execQueryPartDelete(execCond)
	case dbox.QueryPartUpdate:
		e = q.execQueryPartUpdate(data, execCond)
	}

	if e != nil {
		return e
	}

	q.Connection().(*Connection).ExecOpr = true
	if commandType != dbox.QueryPartSave || q.updatessave {
		e = q.Connection().(*Connection).EndSessionWrite()
		q.Connection().(*Connection).TypeOpenFile = TypeOpenFile_Append
	}

	if e != nil {
		return errorlib.Error(packageName, "Query", modQuery, e.Error())
	}

	return nil
}
Exemple #21
0
func (q *Query) ExecOut(parm toolkit.M) (int64, error) {
	var e error
	if parm == nil {
		parm = toolkit.M{}
	}

	driverName := q.GetDriverDB()
	// driverName = "oracle"
	tablename := ""
	data := parm.Get("data")

	var attributes string
	var values string
	var setUpdate string

	var dataM toolkit.M
	var dataMs []toolkit.M
	var returnId int64

	if toolkit.IsSlice(data) {
		e = toolkit.Unjson(toolkit.Jsonify(data), &dataMs)
		if e != nil {
			return returnId, errorlib.Error(packageName, modQuery, "Exec: data extraction", "Data encoding error: "+e.Error())
		}
	} else {
		dataM, e = toolkit.ToM(data)
		dataMs = append(dataMs, dataM)
		if e != nil {
			return returnId, errorlib.Error(packageName, modQuery, "Exec: data extraction", "Data encoding error: "+e.Error())
		}
	}

	for _, dataVal := range dataMs {
		temp := ""
		quyerParts := q.Parts()
		c := crowd.From(&quyerParts)
		groupParts := c.Group(func(x interface{}) interface{} {
			qp := x.(*dbox.QueryPart)
			temp = toolkit.JsonString(qp)
			return qp.PartType
		}, nil).Exec()
		parts := map[interface{}]interface{}{}
		if len(groupParts.Result.Data().([]crowd.KV)) > 0 {
			for _, kv := range groupParts.Result.Data().([]crowd.KV) {
				parts[kv.Key] = kv.Value
			}
		}

		commandType := ""

		_, hasDelete := parts[dbox.QueryPartDelete]
		_, hasInsert := parts[dbox.QueryPartInsert]
		_, hasUpdate := parts[dbox.QueryPartUpdate]
		_, hasSave := parts[dbox.QueryPartSave]

		if hasDelete {
			commandType = dbox.QueryPartDelete
		} else if hasInsert {
			commandType = dbox.QueryPartInsert
		} else if hasUpdate {
			commandType = dbox.QueryPartUpdate
		} else if hasSave {
			commandType = dbox.QueryPartSave
		}

		if hasInsert || hasUpdate || hasSave {
			attributes, setUpdate, values = extractData(dataVal, driverName)
		} else if hasDelete {

		}

		fromParts, hasFrom := parts[dbox.QueryPartFrom]
		if !hasFrom {
			return returnId, errorlib.Error(packageName, "Query", modQuery, "Invalid table name")
		}
		tablename = fromParts.([]*dbox.QueryPart)[0].Value.(string)

		var where interface{}
		whereParts, hasWhere := parts[dbox.QueryPartWhere]
		if hasWhere {
			fb := q.Connection().Fb()
			for _, p := range whereParts.([]*dbox.QueryPart) {
				fs := p.Value.([]*dbox.Filter)
				for _, f := range fs {
					fb.AddFilter(f)
				}
			}

			where, e = fb.Build()
			if e != nil {

			} else {

			}

		}

		var id string
		var idVal interface{}

		if where == nil {
			id, idVal = toolkit.IdInfo(dataVal)
			if id != "" {
				where = id + " = " + StringValue(idVal, "non")
			}
		}

		session := q.Session()
		sessionHive := q.SessionHive()

		if commandType == dbox.QueryPartInsert {
			if attributes != "" && values != "" {
				var statement string
				if driverName == "hive" {
					statement = "INSERT INTO " + tablename + " VALUES " + values
					e = sessionHive.Exec(statement, nil)
				} else {
					statement = "INSERT INTO " + tablename + " " + attributes + " VALUES " + values
					var res sql.Result
					res, e = session.Exec(statement)
					if res != nil {
						returnId, _ = res.LastInsertId()
					}
				}

				if e != nil {
					return returnId, errorlib.Error(packageName, modQuery+".Exec", commandType,
						cast.ToString(e.Error()))
				}
			} else {
				return returnId, errorlib.Error(packageName, modQuery+".Exec", commandType,
					"please provide the data")
			}
		} else if commandType == dbox.QueryPartUpdate {
			if setUpdate != "" {
				var statement string
				if where != nil {
					statement = "UPDATE " + tablename + " SET " + setUpdate +
						" WHERE " + cast.ToString(where)
				} else {
					statement = "UPDATE " + tablename + " SET " + setUpdate
				}
				if driverName == "hive" {
					e = sessionHive.Exec(statement, nil)
				} else {
					_, e = session.Exec(statement)
				}
				if e != nil {
					return returnId, errorlib.Error(packageName, modQuery+".Exec", commandType,
						cast.ToString(e.Error()))
				}
			} else {
				return returnId, errorlib.Error(packageName, modQuery+".Exec", commandType,
					"please provide the data")
			}

		} else if commandType == dbox.QueryPartDelete {
			var statement string
			if where != nil {
				statement = "DELETE FROM " + tablename + " where " + cast.ToString(where)
			} else {
				statement = "DELETE FROM " + tablename
			}
			if driverName == "hive" {
				e = sessionHive.Exec(statement, nil)
			} else {
				_, e = session.Exec(statement)
			}
			if e != nil {
				return returnId, errorlib.Error(packageName, modQuery+".Exec", commandType,
					cast.ToString(e.Error()))
			}
		} else if commandType == dbox.QueryPartSave {
			if attributes != "" && values != "" {
				var querystmt string
				if where != nil {
					querystmt = "select 1 as data from " + tablename + " where " + cast.ToString(where)
				}
				var rowCount int
				if driverName == "hive" {
					rowCount = 0
					// row := sessionHive.Exec(querystmt, nil)
					// rowCount = toolkit.ToInt(row[0], "auto")
				} else {
					if querystmt != "" {
						rows, _ := session.Query(querystmt)
						for rows.Next() {
							rows.Scan(&rowCount)
						}
					}
				}

				var statement string
				if rowCount == 0 || where == nil {
					if driverName == "hive" {
						statement = "INSERT INTO " + tablename + " VALUES " + values
					} else {
						statement = "INSERT INTO " + tablename + " " +
							attributes + " VALUES " + values
					}
				} else {
					statement = "UPDATE " + tablename + " SET " + setUpdate +
						" WHERE " + cast.ToString(where)
				}

				if driverName == "hive" {
					e = sessionHive.Exec(statement, nil)
				} else {
					_, e = session.Exec(statement)
				}
				if e != nil {
					return returnId, errorlib.Error(packageName, modQuery+".Exec", commandType,
						cast.ToString(e.Error()))
				}

			} else if values == "" {
				return returnId, errorlib.Error(packageName, modQuery+".Exec", commandType,
					"please provide the data")
			}

		}
		if e != nil {
			return returnId, errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
		}
	}
	return returnId, nil
}
Exemple #22
0
// generateDurationIntervalSummary ...
func (d *GenWOListSummary) generateDurationIntervalSummary() error {
	years := [3]int{2013, 2014, 2015}

	c := d.Ctx.Connection

	csr, e := c.NewQuery().From(new(TempMstPlant).TableName()).Cursor(nil)

	if e != nil {
		return e
	} else {
		defer csr.Close()
	}

	MstPlantData := []tk.M{}
	e = csr.Fetch(&MstPlantData, 0, false)

	for _, year := range years {
		query := []*dbox.Filter{}

		yearFirst := strconv.Itoa(year)
		yearFirst = yearFirst + "-01-01 00:00:00.000"

		yearLast := strconv.Itoa(year + 1)
		yearLast = yearLast + "-01-01 00:00:00.000"

		query = append(query, dbox.And(dbox.Gte("ActualStart", yearFirst), dbox.Lte("ActualFinish", yearLast)))

		csr1, e := c.NewQuery().From(new(WOList).TableName()).Where(query...).Order("ActualStart").Cursor(nil)

		if e != nil {
			return e
		} else {
			defer csr1.Close()
		}

		datas := []tk.M{}
		e = csr1.Fetch(&datas, 0, false)

		if len(datas) > 0 {
			for _, data := range datas {
				woles := new(WOListSummary)
				woles.PeriodYear = year
				woles.OrderType = data.GetString("type")
				woles.FunctionalLocation = data.GetString("functionallocation")

				query = nil
				query = append(query, dbox.Eq("FunctionalLocationCode", data.GetString("functionallocation")))

				csr2, e := c.NewQuery().From(new(FunctionalLocation).TableName()).Where(query...).Select("CatProf").Cursor(nil)

				if e != nil {
					return e
				} else {
					defer csr2.Close()
				}

				tempResult := []tk.M{}
				e = csr2.Fetch(&tempResult, 0, false)

				if len(tempResult) > 0 {
					woles.EquipmentType = tempResult[0].GetString("catprof")
				}

				woles.MainenanceOrderCode = data.GetString("ordercode")
				woles.NotificationCode = data.GetString("notificationcode")

				query = nil
				query = append(query, dbox.Eq("FunctionalLocationCode", data.GetString("plant")))

				csr3, e := c.NewQuery().From(new(FunctionalLocation).TableName()).Where(query...).Select("Description").Cursor(nil)

				if e != nil {
					return e
				} else {
					defer csr3.Close()
				}

				tempResult = []tk.M{}
				e = csr3.Fetch(&tempResult, 0, false)

				if len(tempResult) > 0 {
					woles.Plant = tempResult[0].GetString("description")
				}

				woles.PlanStart, e = time.Parse(time.RFC3339, data["scheduledstart"].(string))

				if e != nil {
					return e
				}

				woles.PlanEnd, e = time.Parse(time.RFC3339, data["scheduledfinish"].(string))

				if e != nil {
					return e
				}

				subTime := woles.PlanEnd.Sub(woles.PlanStart)
				woles.PlanDuration = subTime.Hours()

				woles.ActualStart, e = time.Parse(time.RFC3339, data.GetString("actualstart"))
				if e != nil {
					return e
				}

				woles.ActualEnd, e = time.Parse(time.RFC3339, data.GetString("actualfinish"))
				if e != nil {
					return e
				}

				subTime = woles.ActualEnd.Sub(woles.ActualStart)
				woles.ActualDuration = subTime.Hours()

				actualstartPart := strings.Split(woles.ActualStart.String(), " ")
				actualstartPart = []string{actualstartPart[0], actualstartPart[1]}
				query = nil
				query = append(query, dbox.Lt("ActualFinish", strings.Join(actualstartPart, " ")))
				query = append(query, dbox.Eq("FunctionalLocation", woles.FunctionalLocation))
				csr4, e := c.NewQuery().Select("ActualFinish").From(new(WOList).TableName()).Order("-ActualFinish").Where(query...).Cursor(nil)

				if e != nil {
					return e
				} else {
					defer csr4.Close()
				}

				tempResult = []tk.M{}
				e = csr4.Fetch(&tempResult, 0, false)

				if len(tempResult) > 0 {
					woles.LastMaintenanceEnd, e = time.Parse(time.RFC3339, tempResult[0].GetString("actualfinish"))
				}

				if woles.ActualStart.String() != "" && woles.LastMaintenanceEnd.String() != "" {
					subTime = woles.ActualStart.Sub(woles.LastMaintenanceEnd)
					woles.LastMaintenanceInterval = subTime.Seconds() / 86400
				}

				woles.Cost = data.GetFloat64("actualcost")
				plantTypes := crowd.From(&MstPlantData).Where(func(x interface{}) interface{} {
					return x.(tk.M).GetString("plantcode") == data.GetString("plant")
				}).Exec().Result.Data().([]tk.M)

				if len(plantTypes) > 0 {
					woles.PlantType = plantTypes[0].GetString("plantcode")
				}

				for {
					e = d.Ctx.Insert(woles)
					if e == nil {
						break
					} else {
						d.Ctx.Connection.Connect()
					}
				}
			}
		}
	}
	return nil
}
Exemple #23
0
func (q *Query) Cursor(in toolkit.M) (dbox.ICursor, error) {
	var e error

	aggregate := false
	tablename := ""

	parts := crowd.From(q.Parts()).Group(func(x interface{}) interface{} {
		qp := x.(*dbox.QueryPart)
		return qp.PartType
	}, nil).Data

	fromParts, hasFrom := parts[dbox.QueryPartFrom]
	if hasFrom == false {
		for _, xsheet := range q.Reader().Sheets {
			tablename = xsheet.Name
			break
		}
	} else {
		tablename = fromParts.([]interface{})[0].(*dbox.QueryPart).Value.(string)
	}

	skip := 0
	if skipParts, hasSkip := parts[dbox.QueryPartSkip]; hasSkip {
		skip = skipParts.([]interface{})[0].(*dbox.QueryPart).
			Value.(int)
	}

	take := 0
	if takeParts, has := parts[dbox.QueryPartTake]; has {
		take = takeParts.([]interface{})[0].(*dbox.QueryPart).
			Value.(int)
	}

	var fields toolkit.M
	selectParts, hasSelect := parts[dbox.QueryPartSelect]
	if hasSelect {
		fields = toolkit.M{}
		for _, sl := range selectParts.([]interface{}) {
			qp := sl.(*dbox.QueryPart)
			for _, fid := range qp.Value.([]string) {
				fields.Set(fid, 1)
			}
		}
	} else {
		_, hasUpdate := parts[dbox.QueryPartUpdate]
		_, hasInsert := parts[dbox.QueryPartInsert]
		_, hasDelete := parts[dbox.QueryPartDelete]
		_, hasSave := parts[dbox.QueryPartSave]

		if hasUpdate || hasInsert || hasDelete || hasSave {
			return nil, errorlib.Error(packageName, modQuery, "Cursor",
				"Valid operation for a cursor is select only")
		}
		// else {
		// 	return nil, errorlib.Error(packageName, modQuery, "Cursor",
		// 		"Invalid operation for a cursor, select syntax not found")
		// }
	}

	var sort []string
	sortParts, hasSort := parts[dbox.QueryPartSelect]
	if hasSort {
		sort = []string{}
		for _, sl := range sortParts.([]interface{}) {
			qp := sl.(*dbox.QueryPart)
			for _, fid := range qp.Value.([]string) {
				sort = append(sort, fid)
			}
		}
	}

	var where []*dbox.Filter
	whereParts, hasWhere := parts[dbox.QueryPartWhere]
	if hasWhere {
		for _, p := range whereParts.([]interface{}) {
			fs := p.(*dbox.QueryPart).Value.([]*dbox.Filter)
			for _, f := range fs {
				// if len(in) > 0 {
				f = ReadVariable(f, in)
				// }
				where = append(where, f)
			}
		}
	}

	cursor := dbox.NewCursor(new(Cursor))
	cursor = cursor.SetConnection(q.Connection())

	// cursor.(*Cursor).file = q.File()
	cursor.(*Cursor).reader = q.Reader()
	cursor.(*Cursor).headerColumn = q.Connection().(*Connection).headerColumn
	cursor.(*Cursor).sheetname = tablename
	cursor.(*Cursor).count = cursor.(*Cursor).Count()
	cursor.(*Cursor).fetchRow = 0
	cursor.(*Cursor).rowstart = q.Connection().(*Connection).rowstart

	if e != nil {
		return nil, errorlib.Error(packageName, modQuery, "Cursor", e.Error())
	}

	if !aggregate {
		cursor.(*Cursor).ConditionVal.Find = where

		if fields != nil {
			cursor.(*Cursor).ConditionVal.Select = fields
		}

		if hasSort {
			cursor.(*Cursor).ConditionVal.Sort = sort
		}
		cursor.(*Cursor).ConditionVal.skip = skip
		cursor.(*Cursor).ConditionVal.limit = take

	} else {
		/*		pipes := toolkit.M{}
				mgoPipe := session.DB(dbname).C(tablename).
					Pipe(pipes).AllowDiskUse()
				//iter := mgoPipe.Iter()

				cursor.(*Cursor).ResultType = QueryResultPipe
				cursor.(*Cursor).mgoPipe = mgoPipe
				//cursor.(*Cursor).mgoIter = iter
		*/
	}
	return cursor, nil
}
Exemple #24
0
func (q *Query) Exec(parm toolkit.M) error {
	var e error
	if parm == nil {
		parm = toolkit.M{}
	}

	dbname := q.Connection().Info().Database
	driverName := q.GetDriverDB()
	// driverName = "oracle"
	tablename := ""
	data := parm.Get("data", nil)
	// fmt.Println("Hasil ekstraksi Param : ", data)

	/*========================EXTRACT FIELD, DATA AND FORMAT DATE=============================*/

	var attributes string
	var values string
	var setUpdate, statement string
	var i int

	dataM, e := toolkit.ToM(data)
	if e != nil {
		return errorlib.Error(packageName, modQuery, "Exec: data extraction", "Data encoding error: "+e.Error())
	}

	if data != nil {
		for field, val := range dataM {
			namaField := field
			dataValues := val
			stringValues := StringValue(dataValues, driverName)
			if i == 0 {
				attributes = "(" + namaField
				setUpdate = namaField + " = " + stringValues
				values = "(" + stringValues

			} else {
				attributes += ", " + namaField
				setUpdate += ", " + namaField + " = " + stringValues
				values += ", " + stringValues

			}
			i += 1
		}
		attributes += ")"
		values += ")"
	}

	/*=================================END OF EXTRACTION=======================================*/

	temp := ""
	quyerParts := q.Parts()
	c := crowd.From(&quyerParts)
	groupParts := c.Group(func(x interface{}) interface{} {
		qp := x.(*dbox.QueryPart)
		temp = toolkit.JsonString(qp)
		return qp.PartType
	}, nil).Exec()
	parts := map[interface{}]interface{}{}
	if len(groupParts.Result.Data().([]crowd.KV)) > 0 {
		for _, kv := range groupParts.Result.Data().([]crowd.KV) {
			parts[kv.Key] = kv.Value
		}
	}

	fromParts, hasFrom := parts[dbox.QueryPartFrom]
	if !hasFrom {

		return errorlib.Error(packageName, "Query", modQuery, "Invalid table name")
	}
	tablename = fromParts.([]*dbox.QueryPart)[0].Value.(string)

	var where interface{}
	whereParts, hasWhere := parts[dbox.QueryPartWhere]
	if hasWhere {
		fb := q.Connection().Fb()
		for _, p := range whereParts.([]*dbox.QueryPart) {
			fs := p.Value.([]*dbox.Filter)
			for _, f := range fs {
				fb.AddFilter(f)
			}
		}

		where, e = fb.Build()
		if e != nil {

		} else {

		}

	}
	commandType := ""
	multi := false

	_, hasDelete := parts[dbox.QueryPartDelete]
	_, hasInsert := parts[dbox.QueryPartInsert]
	_, hasUpdate := parts[dbox.QueryPartUpdate]
	_, hasSave := parts[dbox.QueryPartSave]

	if hasDelete {
		commandType = dbox.QueryPartDelete
	} else if hasInsert {
		commandType = dbox.QueryPartInsert
	} else if hasUpdate {
		commandType = dbox.QueryPartUpdate
	} else if hasSave {
		commandType = dbox.QueryPartSave
	}

	var id string
	var idVal interface{}
	if data == nil {
		multi = true
	} else {
		if where == nil {
			id, idVal = toolkit.IdInfo(data)
			if id != "" {
				where = id + " = " + StringValue(idVal, "non")
			}
		} else {
			multi = true
		}
	}

	session := q.Session()
	sessionHive := q.SessionHive()

	if dbname != "" && tablename != "" && multi == true {

	}
	if commandType == dbox.QueryPartInsert {
		if attributes != "" && values != "" {
			if driverName == "hive" {
				statement = "INSERT INTO " + tablename + " VALUES " + values
				e = sessionHive.Exec(statement, nil)
			} else {
				statement = "INSERT INTO " + tablename + " " + attributes + " VALUES " + values
				_, e = session.Exec(statement)
			}

			if e != nil {
				fmt.Println(e.Error())
			}
		} else {
			return errorlib.Error(packageName, modQuery+".Exec", commandType,
				"please provide the data")
		}

	} else if commandType == dbox.QueryPartUpdate {
		if setUpdate != "" {
			var statement string
			if where != nil {
				statement = "UPDATE " + tablename + " SET " + setUpdate +
					" WHERE " + cast.ToString(where)
			} else {
				statement = "UPDATE " + tablename + " SET " + setUpdate
			}
			if driverName == "hive" {
				e = sessionHive.Exec(statement, nil)
			} else {
				_, e = session.Exec(statement)
			}
			if e != nil {
				return errorlib.Error(packageName, modQuery+".Exec", commandType,
					cast.ToString(e.Error()))
			}
		} else {
			return errorlib.Error(packageName, modQuery+".Exec", commandType,
				"please provide the data")
		}

	} else if commandType == dbox.QueryPartDelete {
		var statement string
		if where != nil {
			statement = "DELETE FROM " + tablename + " where " + cast.ToString(where)
		} else {
			statement = "DELETE FROM " + tablename
		}
		if driverName == "hive" {
			e = sessionHive.Exec(statement, nil)
		} else {
			_, e = session.Exec(statement)
		}
		if e != nil {
			return errorlib.Error(packageName, modQuery+".Exec", commandType,
				cast.ToString(e.Error()))
		}
	} else if commandType == dbox.QueryPartSave {
		if attributes != "" && values != "" {
			var querystmt string
			if where != nil {
				querystmt = "select 1 as data from " + tablename + " where " + cast.ToString(where)
			}
			var rowCount int
			if driverName == "hive" {
				rowCount = 0
				// row := sessionHive.Exec(querystmt, nil)
				// rowCount = toolkit.ToInt(row[0], "auto")
			} else {
				if querystmt != "" {
					rows, _ := session.Query(querystmt)
					for rows.Next() {
						rows.Scan(&rowCount)
					}
				}
			}

			var statement string
			if rowCount == 0 || where == nil {
				if driverName == "hive" {
					statement = "INSERT INTO " + tablename + " VALUES " + values
				} else {
					statement = "INSERT INTO " + tablename + " " +
						attributes + " VALUES " + values
				}
			} else {
				statement = "UPDATE " + tablename + " SET " + setUpdate +
					" WHERE " + cast.ToString(where)
			}

			if driverName == "hive" {
				e = sessionHive.Exec(statement, nil)
			} else {
				_, e = session.Exec(statement)
			}
			if e != nil {
				return errorlib.Error(packageName, modQuery+".Exec", commandType,
					cast.ToString(e.Error()))
			}

		} else if values == "" {
			return errorlib.Error(packageName, modQuery+".Exec", commandType,
				"please provide the data")
		}

	}
	if e != nil {
		return errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
	}
	return nil
}
Exemple #25
0
func doGenerateMaintenanceDataBrowser(wg *sync.WaitGroup, d *GenDataBrowser, plant PowerPlantCoordinates) {
	var e error
	ctx := d.BaseController.Ctx
	c := ctx.Connection

	saveDatas := []orm.IModel{}
	plantCodeStr := plant.PlantCode
	dataCount := 0

	tmpPlantCondition := conditions.Get(plantCodeStr)
	if tmpPlantCondition != nil {
		plantCondition := tmpPlantCondition.(tk.M)

		length := plantCondition.GetInt("length")
		dets := plantCondition.Get("det").([]tk.M)
		dets = append(dets, tk.M{})

		turbinesCodes := []string{}
		genIDTempTable := tk.GenerateRandomString("", 20)

		detsLen := len(dets) - 1

		tk.Printf("detsLen: %v \n", detsLen)

		for i, det := range dets {
			freeQuery := false
			tk.Println(i)
			tk.Printf("dets: %#v \n", det)
			assets := []FunctionalLocation{}
			systemAssets := []FunctionalLocation{}
			desc := det.GetString("desc")

			query := []*dbox.Filter{}
			query = append(query, dbox.Contains("FunctionalLocationCode", plantCodeStr))
			query = append(query, dbox.Eq("PIPI", plantCodeStr))

			queryStr := "select * from FunctionalLocation where FunctionalLocationCode like('%" + plantCodeStr + "%') and PIPI = '" + plantCodeStr + "' "

			if i == 0 {
				query = append(query, dbox.Contains("Description", desc))
				if plantCodeStr == "2220" {
					query = append(query, dbox.Lte("LEN(FunctionalLocationCode)", length))
				} else {
					query = append(query, dbox.Eq("LEN(FunctionalLocationCode)", length))
				}
			} else if i == 1 && detsLen == 2 {
				query = append(query, dbox.Contains("Description", desc))
				if plantCodeStr == "2220" {
					query = append(query, dbox.Lte("LEN(FunctionalLocationCode)", length))
				} else {
					query = append(query, dbox.Eq("LEN(FunctionalLocationCode)", length))
				}
			} else {
				tk.Printf("turbinesCodes: %v \n", len(turbinesCodes))
				if len(turbinesCodes) > 1 {
					maxLength := 1000
					maxLoop := tk.ToInt(tk.ToFloat64(len(turbinesCodes)/maxLength, 0, tk.RoundingUp), tk.RoundingUp)

					for i := 0; i <= maxLoop; i++ {
						datas := []string{}

						if i != maxLoop {
							datas = turbinesCodes[i*maxLength : (i*maxLength)+maxLength]

						} else {
							datas = turbinesCodes[i*maxLength:]
						}

						tmpNotIn := []orm.IModel{}
						for _, val := range datas {
							tmpData := new(GenDataBrowserNotInTmp)
							tmpData.ID = genIDTempTable
							tmpData.FLCode = val
							tmpNotIn = append(tmpNotIn, tmpData)
						}
						// tk.Printf("tmpNotIn: %v \n", len(tmpNotIn))
						e = ctx.InsertBulk(tmpNotIn)
						ErrorHandler(e, "generateMaintenanceDataBrowser")
					}

					queryStr = queryStr + " and FunctionalLocationCode not in(select flcode from GenDataBrowserNotInTmp where ID = '" + genIDTempTable + "')"
					freeQuery = true
				}
			}

			if freeQuery {
				csrDet, e := c.NewQuery().Command("freequery", tk.M{}.Set("syntax", queryStr)).Cursor(nil)
				ErrorHandler(e, "generateMaintenanceDataBrowser")

				e = csrDet.Fetch(&assets, 0, false)
				ErrorHandler(e, "generateMaintenanceDataBrowser")
				csrDet.Close()
			} else {
				csrDet, e := c.NewQuery().From(new(FunctionalLocation).TableName()).Where(dbox.And(query...)).Cursor(nil)
				ErrorHandler(e, "generateMaintenanceDataBrowser")

				e = csrDet.Fetch(&assets, 0, false)
				ErrorHandler(e, "generateMaintenanceDataBrowser")
				csrDet.Close()
			}

			tk.Printf("-- assets: %v \n", len(assets))

			if len(assets) > 0 {
				relatedAssets := []FunctionalLocation{}

				for _, asset := range assets {

					if plantCodeStr == "2110" {
						if len(asset.FunctionalLocationCode) <= 13 {
							query = []*dbox.Filter{}
							query = append(query,
								dbox.And(
									dbox.Eq("PG", "MP1"),
									dbox.Eq("PIPI", plantCodeStr),
									dbox.Contains("FunctionalLocationCode", asset.FunctionalLocationCode),
									dbox.And(
										dbox.Lte("LEN(FunctionalLocationCode)", 13),
										dbox.Gte("LEN(FunctionalLocationCode)", 12),
									),
								),
							)

							csrDet, e := c.NewQuery().From(new(FunctionalLocation).TableName()).Where(query...).Cursor(nil)
							ErrorHandler(e, "generateMaintenanceDataBrowser")
							e = csrDet.Fetch(&systemAssets, 0, false)
							ErrorHandler(e, "generateMaintenanceDataBrowser")
							csrDet.Close()
						}

						if systemAssets == nil || len(systemAssets) == 0 {
							query = []*dbox.Filter{}

							if i != 2 {
								query = append(query, dbox.Contains("FunctionalLocationCode", asset.FunctionalLocationCode))
							} else {
								query = append(query, dbox.Eq("FunctionalLocationCode", asset.FunctionalLocationCode))
							}

							csrDet, e := c.NewQuery().From(new(FunctionalLocation).TableName()).Where(query...).Cursor(nil)
							ErrorHandler(e, "generateMaintenanceDataBrowser")
							e = csrDet.Fetch(&relatedAssets, 0, false)
							ErrorHandler(e, "generateMaintenanceDataBrowser")
							csrDet.Close()

							for _, relasset := range relatedAssets {
								isTurbineSystem := false
								if relasset.FunctionalLocationCode == asset.FunctionalLocationCode && i != 2 {
									isTurbineSystem = true
								}

								newEquipment := d.getNewEquipmentType(relasset.ObjectType, isTurbineSystem)

								if newEquipment != "" {

									if i != 2 {
										turbinesCodes = append(turbinesCodes, relasset.FunctionalLocationCode)
									}

									for _, year := range years {
										data := DataBrowser{}
										data.PeriodYear = year
										data.FunctionalLocation = relasset.FunctionalLocationCode
										data.FLDescription = relasset.Description
										data.IsTurbine = false

										// tk.Printf("%v | %v | ", relasset.FunctionalLocationCode, i)

										if relasset.FunctionalLocationCode == asset.FunctionalLocationCode && i != 2 {
											data.IsTurbine = true
											// tk.Println(" isTurbine: TRUE")
										} else {
											// tk.Println(" isTurbine: FALSE")
											data.TurbineParent = asset.FunctionalLocationCode
										}

										data.AssetType = "Other"

										if i == 0 {
											data.AssetType = "Steam"
										} else if i == 1 {
											data.AssetType = "Gas"
										}

										data.EquipmentType = newEquipment
										data.EquipmentTypeDescription = newEquipment
										data.Plant = plant
										data.PlantCode = plant.PlantCode

										if data.IsTurbine {
											info := GeneralInfo{}
											substr := ""
											substrValInt := 0
											if data.AssetType == "Steam" {
												substrValInt = 1
												substr = "ST"
											} else if data.AssetType == "Gas" {
												substrValInt = 2
											}

											if substrValInt != 0 {
												tmpInfo := crowd.From(&generalInfos).Where(func(x interface{}) interface{} {
													y := x.(GeneralInfo)
													substr = substr + data.FunctionalLocation[len(data.FunctionalLocation)-substrValInt:]
													return strings.Contains(strings.ToLower(strings.Trim(y.Plant, " ")), strings.ToLower(plant.PlantName)) && y.Unit == substr
												}).Exec().Result.Data().([]GeneralInfo)
												if len(tmpInfo) > 0 {
													info = tmpInfo[0]
												}

												if info.Id != "" {
													data.TInfShortName = info.Unit
													data.TInfManufacturer = info.Manufacturer
													data.TInfModel = info.Model
													data.TInfUnitType = info.UnitType
													data.TInfInstalledCapacity = info.InstalledCapacity
													data.TInfOperationalCapacity = info.OperationalCapacity
													data.TInfPrimaryFuel = info.PrimaryFuel1
													data.TInfPrimaryFuel2 = info.PrimaryFuel2Startup
													data.TInfBackupFuel = info.BackupFuel
													data.TInfHeatRate = info.HeatRate
													data.TInfEfficiency = info.Efficiency

													commDate, e := time.Parse("01/02/2006", "01/01"+tk.ToString(info.CommissioningDate))
													ErrorHandler(e, "generateMaintenanceDataBrowser")
													data.TInfCommisioningDate = commDate

													if info.RetirementPlan != "" {
														retirementPlanStr := strings.Split(info.RetirementPlan, "(")[0]
														retirementPlan, e := time.Parse("01/02/2006", "01/01"+retirementPlanStr)
														ErrorHandler(e, "generateMaintenanceDataBrowser")
														data.TInfRetirementPlan = retirementPlan
													}

													installedMWH := crowd.From(&generalInfoDetails).Where(func(x interface{}) interface{} {
														y := x.(GeneralInfoDetails)
														return y.GenID == info.Id && y.Type == "InstalledMWH" && y.Year == year
													}).Exec().Result.Data().([]GeneralInfoDetails)[0]

													data.TInfInstalledMWH = installedMWH.Value

													actualEnergyGeneration := crowd.From(&generalInfoDetails).Where(func(x interface{}) interface{} {
														y := x.(GeneralInfoDetails)
														return y.GenID == info.Id && y.Type == "ActualEnergyGeneration" && y.Year == year
													}).Exec().Result.Data().([]GeneralInfoDetails)[0]

													data.TInfActualEnergyGeneration = actualEnergyGeneration.Value

													capacityFactor := crowd.From(&generalInfoDetails).Where(func(x interface{}) interface{} {
														y := x.(GeneralInfoDetails)
														return y.GenID == info.Id && y.Type == "CapacityFactor" && y.Year == year
													}).Exec().Result.Data().([]GeneralInfoDetails)[0]

													data.TInfCapacityFactor = capacityFactor.Value

													actualFuelConsumption := crowd.From(&generalInfoActualFuelConsumption).Where(func(x interface{}) interface{} {
														y := x.(GeneralInfoActualFuelConsumption)
														return y.GenID == info.Id && y.Year == year
													}).Exec().Result.Data().([]GeneralInfoActualFuelConsumption)[0]

													data.TInfActualFuelConsumption_CrudeBarrel = actualFuelConsumption.CrudeBarrel
													data.TInfActualFuelConsumption_DieselBarrel = actualFuelConsumption.DieselBarrel
													data.TInfActualFuelConsumption_GASMMSCF = actualFuelConsumption.GASMMSCF
													data.TInfActualFuelConsumption_HFOBarrel = actualFuelConsumption.HFOBarrel

													fuelCostCrowd := crowd.From(&fuelCosts).Where(func(x interface{}) interface{} {
														y := x.(FuelCost)
														unitID := strings.Replace(
															strings.Replace(
																strings.Replace(
																	strings.Replace(y.UnitId, ".", "", -1), " 0", "", -1), " ", "", -1), "C.C ", "", -1)

														return y.Year == year && y.Plant == data.Plant.PlantName && unitID == data.TInfShortName
													})

													data.TInfUpdateEnergyGeneration = fuelCostCrowd.Sum(func(x interface{}) interface{} {
														y := x.(FuelCost)
														return y.EnergyNetProduction
													}).Exec().Result.Data().(float64)

													data.TInfUpdateFuelConsumption = fuelCostCrowd.Sum(func(x interface{}) interface{} {
														y := x.(FuelCost)
														return y.PrimaryFuelConsumed
													}).Exec().Result.Data().(float64)
												}
											}
											// Vibrations handled by sql query
										}

										// Maintenance handled by sql query
										// FailureNotifications handled by sql query
										// MROElements handled by sql query
										// Operationals handled by sql query

										// e = ctx.Insert(&data)
										saveDatas = append(saveDatas, &data)
										dataCount++
										if len(saveDatas) == 1000 {
											mugen.Lock()
											e = ctx.InsertBulk(saveDatas)
											mugen.Unlock()
											ErrorHandler(e, "generateMaintenanceDataBrowser")
											tk.Printf("%v : %v \n", plantCodeStr, dataCount)

											saveDatas = []orm.IModel{}
										}

										/*ErrorHandler(e, "generateMaintenanceDataBrowser")
										tk.Println("save 1")*/
									}
								}
							}

						} else {
							// with system assets
							if i != 2 {
								turbinesCodes = append(turbinesCodes, asset.FunctionalLocationCode)
							}

							for _, year := range years {
								data := DataBrowser{}
								data.PeriodYear = year
								data.FunctionalLocation = asset.FunctionalLocationCode
								data.FLDescription = asset.Description
								data.IsTurbine = false
								data.IsSystem = false

								// tk.Printf("%v | %v | ", asset.FunctionalLocationCode, i)

								if asset.FunctionalLocationCode == asset.FunctionalLocationCode && i != 2 {
									data.IsTurbine = true
									data.IsSystem = true
									// tk.Println(" isTurbine: TRUE")
								} else {
									// tk.Println(" isTurbine: FALSE")
									data.TurbineParent = asset.FunctionalLocationCode
									data.SystemParent = asset.FunctionalLocationCode
								}

								data.AssetType = "Other"

								if i == 0 {
									data.AssetType = "Steam"
								} else if i == 1 {
									data.AssetType = "Gas"
								}

								data.EquipmentType = "System"
								data.EquipmentTypeDescription = "System"
								data.Plant = plant
								data.PlantCode = plant.PlantCode

								if data.IsTurbine {
									info := GeneralInfo{}
									substr := ""
									substrValInt := 0
									if data.AssetType == "Steam" {
										substrValInt = 1
										substr = "ST"
									} else if data.AssetType == "Gas" {
										substrValInt = 2
									}

									if substrValInt != 0 {
										tmpInfo := crowd.From(&generalInfos).Where(func(x interface{}) interface{} {
											y := x.(GeneralInfo)
											substr = substr + data.FunctionalLocation[len(data.FunctionalLocation)-substrValInt:]
											return strings.Contains(strings.ToLower(strings.Trim(y.Plant, " ")), strings.ToLower(plant.PlantName)) && y.Unit == substr
										}).Exec().Result.Data().([]GeneralInfo)
										if len(tmpInfo) > 0 {
											info = tmpInfo[0]
										}

										if info.Id != "" {
											data.TInfShortName = info.Unit
											data.TInfManufacturer = info.Manufacturer
											data.TInfModel = info.Model
											data.TInfUnitType = info.UnitType
											data.TInfInstalledCapacity = info.InstalledCapacity
											data.TInfOperationalCapacity = info.OperationalCapacity
											data.TInfPrimaryFuel = info.PrimaryFuel1
											data.TInfPrimaryFuel2 = info.PrimaryFuel2Startup
											data.TInfBackupFuel = info.BackupFuel
											data.TInfHeatRate = info.HeatRate
											data.TInfEfficiency = info.Efficiency

											commDate, e := time.Parse("01/02/2006", "01/01"+tk.ToString(info.CommissioningDate))
											ErrorHandler(e, "generateMaintenanceDataBrowser")
											data.TInfCommisioningDate = commDate

											if info.RetirementPlan != "" {
												retirementPlanStr := strings.Split(info.RetirementPlan, "(")[0]
												retirementPlan, e := time.Parse("01/02/2006", "01/01"+retirementPlanStr)
												ErrorHandler(e, "generateMaintenanceDataBrowser")
												data.TInfRetirementPlan = retirementPlan
											}

											installedMWH := crowd.From(&generalInfoDetails).Where(func(x interface{}) interface{} {
												y := x.(GeneralInfoDetails)
												return y.GenID == info.Id && y.Type == "InstalledMWH" && y.Year == year
											}).Exec().Result.Data().([]GeneralInfoDetails)[0]

											data.TInfInstalledMWH = installedMWH.Value

											actualEnergyGeneration := crowd.From(&generalInfoDetails).Where(func(x interface{}) interface{} {
												y := x.(GeneralInfoDetails)
												return y.GenID == info.Id && y.Type == "ActualEnergyGeneration" && y.Year == year
											}).Exec().Result.Data().([]GeneralInfoDetails)[0]

											data.TInfActualEnergyGeneration = actualEnergyGeneration.Value

											capacityFactor := crowd.From(&generalInfoDetails).Where(func(x interface{}) interface{} {
												y := x.(GeneralInfoDetails)
												return y.GenID == info.Id && y.Type == "CapacityFactor" && y.Year == year
											}).Exec().Result.Data().([]GeneralInfoDetails)[0]

											data.TInfCapacityFactor = capacityFactor.Value

											actualFuelConsumption := crowd.From(&generalInfoActualFuelConsumption).Where(func(x interface{}) interface{} {
												y := x.(GeneralInfoActualFuelConsumption)
												return y.GenID == info.Id && y.Year == year
											}).Exec().Result.Data().([]GeneralInfoActualFuelConsumption)[0]

											data.TInfActualFuelConsumption_CrudeBarrel = actualFuelConsumption.CrudeBarrel
											data.TInfActualFuelConsumption_DieselBarrel = actualFuelConsumption.DieselBarrel
											data.TInfActualFuelConsumption_GASMMSCF = actualFuelConsumption.GASMMSCF
											data.TInfActualFuelConsumption_HFOBarrel = actualFuelConsumption.HFOBarrel

											fuelCostCrowd := crowd.From(&fuelCosts).Where(func(x interface{}) interface{} {
												y := x.(FuelCost)
												unitID := strings.Replace(
													strings.Replace(
														strings.Replace(
															strings.Replace(y.UnitId, ".", "", -1), " 0", "", -1), " ", "", -1), "C.C ", "", -1)

												return y.Year == year && y.Plant == data.Plant.PlantName && unitID == data.TInfShortName
											})

											data.TInfUpdateEnergyGeneration = fuelCostCrowd.Sum(func(x interface{}) interface{} {
												y := x.(FuelCost)
												return y.EnergyNetProduction
											}).Exec().Result.Data().(float64)

											data.TInfUpdateFuelConsumption = fuelCostCrowd.Sum(func(x interface{}) interface{} {
												y := x.(FuelCost)
												return y.PrimaryFuelConsumed
											}).Exec().Result.Data().(float64)
										}
									}
									// Vibrations handled by sql query
								}

								// Maintenance handled by sql query
								// FailureNotifications handled by sql query
								// MROElements handled by sql query
								// Operationals handled by sql query

								// e = ctx.Insert(&data)
								// dataCount++
								saveDatas = append(saveDatas, &data)
								dataCount++
								if len(saveDatas) == 1000 {
									mugen.Lock()
									e = ctx.InsertBulk(saveDatas)
									mugen.Unlock()
									ErrorHandler(e, "generateMaintenanceDataBrowser")
									tk.Printf("%v : %v \n", plantCodeStr, dataCount)

									saveDatas = []orm.IModel{}
								}

								/*ErrorHandler(e, "generateMaintenanceDataBrowser")
								tk.Println("save 2")*/

							}

							for _, sysAsset := range systemAssets {
								query = []*dbox.Filter{}

								if i != 2 {
									query = append(query, dbox.Contains("FunctionalLocationCode", sysAsset.FunctionalLocationCode))
								} else {
									query = append(query, dbox.Eq("FunctionalLocationCode", sysAsset.FunctionalLocationCode))
								}

								csrDet, e := c.NewQuery().From(new(FunctionalLocation).TableName()).Where(query...).Cursor(nil)
								ErrorHandler(e, "generateMaintenanceDataBrowser")
								e = csrDet.Fetch(&relatedAssets, 0, false)
								ErrorHandler(e, "generateMaintenanceDataBrowser")
								csrDet.Close()

								for _, relasset := range relatedAssets {
									isTurbineSystem := false
									if (relasset.FunctionalLocationCode == asset.FunctionalLocationCode || relasset.FunctionalLocationCode == sysAsset.FunctionalLocationCode) && i != 2 {
										isTurbineSystem = true
									}

									newEquipment := d.getNewEquipmentType(relasset.ObjectType, isTurbineSystem)

									if newEquipment != "" {

										if i != 2 {
											turbinesCodes = append(turbinesCodes, relasset.FunctionalLocationCode)
										}

										for _, year := range years {
											data := DataBrowser{}
											data.PeriodYear = year
											data.FunctionalLocation = relasset.FunctionalLocationCode
											data.FLDescription = relasset.Description
											data.IsTurbine = false
											data.IsSystem = false

											// tk.Printf("%v | %v | ", relasset.FunctionalLocationCode, i)

											if relasset.FunctionalLocationCode == sysAsset.FunctionalLocationCode && i != 2 {
												data.IsTurbine = true
												data.IsSystem = true
												// tk.Println(" isTurbine: TRUE")
											} else {
												// tk.Println(" isTurbine: FALSE")
												data.TurbineParent = asset.FunctionalLocationCode
												data.SystemParent = asset.FunctionalLocationCode
											}

											data.AssetType = "Other"

											if i == 0 {
												data.AssetType = "Steam"
											} else if i == 1 {
												data.AssetType = "Gas"
											}

											data.EquipmentType = newEquipment
											data.EquipmentTypeDescription = newEquipment
											data.Plant = plant
											data.PlantCode = plant.PlantCode

											if data.IsTurbine {
												info := GeneralInfo{}
												substr := ""
												substrValInt := 0
												if data.AssetType == "Steam" {
													substrValInt = 1
													substr = "ST"
												} else if data.AssetType == "Gas" {
													substrValInt = 2
												}

												if substrValInt != 0 {
													tmpInfo := crowd.From(&generalInfos).Where(func(x interface{}) interface{} {
														y := x.(GeneralInfo)
														substr = substr + data.FunctionalLocation[len(data.FunctionalLocation)-substrValInt:]
														return strings.Contains(strings.ToLower(strings.Trim(y.Plant, " ")), strings.ToLower(plant.PlantName)) && y.Unit == substr
													}).Exec().Result.Data().([]GeneralInfo)
													if len(tmpInfo) > 0 {
														info = tmpInfo[0]
													}

													if info.Id != "" {
														data.TInfShortName = info.Unit
														data.TInfManufacturer = info.Manufacturer
														data.TInfModel = info.Model
														data.TInfUnitType = info.UnitType
														data.TInfInstalledCapacity = info.InstalledCapacity
														data.TInfOperationalCapacity = info.OperationalCapacity
														data.TInfPrimaryFuel = info.PrimaryFuel1
														data.TInfPrimaryFuel2 = info.PrimaryFuel2Startup
														data.TInfBackupFuel = info.BackupFuel
														data.TInfHeatRate = info.HeatRate
														data.TInfEfficiency = info.Efficiency

														commDate, e := time.Parse("01/02/2006", "01/01"+tk.ToString(info.CommissioningDate))
														ErrorHandler(e, "generateMaintenanceDataBrowser")
														data.TInfCommisioningDate = commDate

														if info.RetirementPlan != "" {
															retirementPlanStr := strings.Split(info.RetirementPlan, "(")[0]
															retirementPlan, e := time.Parse("01/02/2006", "01/01"+retirementPlanStr)
															ErrorHandler(e, "generateMaintenanceDataBrowser")
															data.TInfRetirementPlan = retirementPlan
														}

														installedMWH := crowd.From(&generalInfoDetails).Where(func(x interface{}) interface{} {
															y := x.(GeneralInfoDetails)
															return y.GenID == info.Id && y.Type == "InstalledMWH" && y.Year == year
														}).Exec().Result.Data().([]GeneralInfoDetails)[0]

														data.TInfInstalledMWH = installedMWH.Value

														actualEnergyGeneration := crowd.From(&generalInfoDetails).Where(func(x interface{}) interface{} {
															y := x.(GeneralInfoDetails)
															return y.GenID == info.Id && y.Type == "ActualEnergyGeneration" && y.Year == year
														}).Exec().Result.Data().([]GeneralInfoDetails)[0]

														data.TInfActualEnergyGeneration = actualEnergyGeneration.Value

														capacityFactor := crowd.From(&generalInfoDetails).Where(func(x interface{}) interface{} {
															y := x.(GeneralInfoDetails)
															return y.GenID == info.Id && y.Type == "CapacityFactor" && y.Year == year
														}).Exec().Result.Data().([]GeneralInfoDetails)[0]

														data.TInfCapacityFactor = capacityFactor.Value

														actualFuelConsumption := crowd.From(&generalInfoActualFuelConsumption).Where(func(x interface{}) interface{} {
															y := x.(GeneralInfoActualFuelConsumption)
															return y.GenID == info.Id && y.Year == year
														}).Exec().Result.Data().([]GeneralInfoActualFuelConsumption)[0]

														data.TInfActualFuelConsumption_CrudeBarrel = actualFuelConsumption.CrudeBarrel
														data.TInfActualFuelConsumption_DieselBarrel = actualFuelConsumption.DieselBarrel
														data.TInfActualFuelConsumption_GASMMSCF = actualFuelConsumption.GASMMSCF
														data.TInfActualFuelConsumption_HFOBarrel = actualFuelConsumption.HFOBarrel

														fuelCostCrowd := crowd.From(&fuelCosts).Where(func(x interface{}) interface{} {
															y := x.(FuelCost)
															unitID := strings.Replace(
																strings.Replace(
																	strings.Replace(
																		strings.Replace(y.UnitId, ".", "", -1), " 0", "", -1), " ", "", -1), "C.C ", "", -1)

															return y.Year == year && y.Plant == data.Plant.PlantName && unitID == data.TInfShortName
														})

														data.TInfUpdateEnergyGeneration = fuelCostCrowd.Sum(func(x interface{}) interface{} {
															y := x.(FuelCost)
															return y.EnergyNetProduction
														}).Exec().Result.Data().(float64)

														data.TInfUpdateFuelConsumption = fuelCostCrowd.Sum(func(x interface{}) interface{} {
															y := x.(FuelCost)
															return y.PrimaryFuelConsumed
														}).Exec().Result.Data().(float64)
													}
												}
												// Vibrations handled by sql query
											}

											// Maintenance handled by sql query
											// FailureNotifications handled by sql query
											// MROElements handled by sql query
											// Operationals handled by sql query

											// e = ctx.Insert(&data)
											saveDatas = append(saveDatas, &data)
											dataCount++
											if len(saveDatas) == 1000 {
												mugen.Lock()
												e = ctx.InsertBulk(saveDatas)
												mugen.Unlock()
												ErrorHandler(e, "generateMaintenanceDataBrowser")
												tk.Printf("%v : %v \n", plantCodeStr, dataCount)

												saveDatas = []orm.IModel{}
											}

											/*ErrorHandler(e, "generateMaintenanceDataBrowser")
											tk.Println("save 3")*/
										}
									}
								}
							}
						}
					} else {
						// another plant(s)

						query = []*dbox.Filter{}

						if i != detsLen {
							query = append(query, dbox.Contains("FunctionalLocationCode", asset.FunctionalLocationCode))
						} else {
							query = append(query, dbox.Eq("FunctionalLocationCode", asset.FunctionalLocationCode))
						}

						csrDet, e := c.NewQuery().From(new(FunctionalLocation).TableName()).Where(query...).Cursor(nil)
						ErrorHandler(e, "generateMaintenanceDataBrowser")
						e = csrDet.Fetch(&relatedAssets, 0, false)
						ErrorHandler(e, "generateMaintenanceDataBrowser")
						csrDet.Close()
						tk.Printf("-- related assets: %v \n", len(relatedAssets))

						for _, relasset := range relatedAssets {
							isTurbineSystem := false
							if relasset.FunctionalLocationCode == asset.FunctionalLocationCode && i != detsLen {
								isTurbineSystem = true
							}

							newEquipment := ""
							newEquipment = d.getNewEquipmentType(relasset.ObjectType, isTurbineSystem)

							if newEquipment != "" {

								if i != detsLen {
									turbinesCodes = append(turbinesCodes, relasset.FunctionalLocationCode)
								}

								for _, year := range years {
									_ = year
									data := DataBrowser{}
									data.PeriodYear = year
									data.FunctionalLocation = relasset.FunctionalLocationCode
									data.FLDescription = relasset.Description
									data.IsTurbine = false

									if relasset.FunctionalLocationCode == asset.FunctionalLocationCode && i != detsLen {
										data.IsTurbine = true
									} else {
										data.TurbineParent = asset.FunctionalLocationCode
									}

									data.AssetType = "Other"

									if i == 0 {
										data.AssetType = "Steam"
									} else if i == 1 && detsLen > 1 {
										data.AssetType = "Gas"
									}

									data.EquipmentType = newEquipment
									data.EquipmentTypeDescription = newEquipment
									data.Plant = plant
									data.PlantCode = plant.PlantCode

									if data.IsTurbine {
										info := GeneralInfo{}
										substr := ""
										substrValInt := 0
										if data.AssetType == "Steam" {
											// substrValInt = 1
											substrValInt = 2
											substr = "ST" + substr
										} else if data.AssetType == "Gas" {
											substrValInt = 2
											substr = "GT" + substr
										}

										if substrValInt != 0 {
											tmpInfo := crowd.From(&generalInfos).Where(func(x interface{}) interface{} {
												y := x.(GeneralInfo)
												substr = data.FunctionalLocation[len(data.FunctionalLocation)-substrValInt:]
												return strings.Contains(strings.ToLower(strings.Trim(y.Plant, " ")), strings.ToLower(plant.PlantName)) && y.Unit == substr
											}).Exec().Result.Data().([]GeneralInfo)
											if len(tmpInfo) > 0 {
												info = tmpInfo[0]
											}

											if info.Id != "" {
												data.TInfShortName = info.Unit
												data.TInfManufacturer = info.Manufacturer
												data.TInfModel = info.Model
												data.TInfUnitType = info.UnitType
												data.TInfInstalledCapacity = info.InstalledCapacity
												data.TInfOperationalCapacity = info.OperationalCapacity
												data.TInfPrimaryFuel = info.PrimaryFuel1
												data.TInfPrimaryFuel2 = info.PrimaryFuel2Startup
												data.TInfBackupFuel = info.BackupFuel
												data.TInfHeatRate = info.HeatRate
												data.TInfEfficiency = info.Efficiency

												commDate, e := time.Parse("01/02/2006", "01/01"+tk.ToString(info.CommissioningDate))
												ErrorHandler(e, "generateMaintenanceDataBrowser")

												data.TInfCommisioningDate = commDate

												if info.RetirementPlan != "" {
													retirementPlanStr := strings.Split(info.RetirementPlan, "(")[0]
													retirementPlan, e := time.Parse("01/02/2006", "01/01"+retirementPlanStr)
													ErrorHandler(e, "generateMaintenanceDataBrowser")
													data.TInfRetirementPlan = retirementPlan
												}

												installedMWH := crowd.From(&generalInfoDetails).Where(func(x interface{}) interface{} {
													y := x.(GeneralInfoDetails)
													return y.GenID == info.Id && y.Type == "InstalledMWH" && y.Year == year
												}).Exec().Result.Data().([]GeneralInfoDetails)[0]

												data.TInfInstalledMWH = installedMWH.Value

												actualEnergyGeneration := crowd.From(&generalInfoDetails).Where(func(x interface{}) interface{} {
													y := x.(GeneralInfoDetails)
													return y.GenID == info.Id && y.Type == "ActualEnergyGeneration" && y.Year == year
												}).Exec().Result.Data().([]GeneralInfoDetails)[0]

												data.TInfActualEnergyGeneration = actualEnergyGeneration.Value

												capacityFactor := crowd.From(&generalInfoDetails).Where(func(x interface{}) interface{} {
													y := x.(GeneralInfoDetails)
													return y.GenID == info.Id && y.Type == "CapacityFactor" && y.Year == year
												}).Exec().Result.Data().([]GeneralInfoDetails)[0]

												data.TInfCapacityFactor = capacityFactor.Value

												actualFuelConsumption := crowd.From(&generalInfoActualFuelConsumption).Where(func(x interface{}) interface{} {
													y := x.(GeneralInfoActualFuelConsumption)
													return y.GenID == info.Id && y.Year == year
												}).Exec().Result.Data().([]GeneralInfoActualFuelConsumption)[0]

												data.TInfActualFuelConsumption_CrudeBarrel = actualFuelConsumption.CrudeBarrel
												data.TInfActualFuelConsumption_DieselBarrel = actualFuelConsumption.DieselBarrel
												data.TInfActualFuelConsumption_GASMMSCF = actualFuelConsumption.GASMMSCF
												data.TInfActualFuelConsumption_HFOBarrel = actualFuelConsumption.HFOBarrel

												fuelCostCrowd := crowd.From(&fuelCosts).Where(func(x interface{}) interface{} {
													y := x.(FuelCost)
													unitID := strings.Replace(
														strings.Replace(
															strings.Replace(
																strings.Replace(y.UnitId, ".", "", -1), " 0", "", -1), " ", "", -1), "C.C ", "", -1)

													return y.Year == year && y.Plant == data.Plant.PlantName && unitID == data.TInfShortName
												})

												data.TInfUpdateEnergyGeneration = fuelCostCrowd.Sum(func(x interface{}) interface{} {
													y := x.(FuelCost)
													return y.EnergyNetProduction
												}).Exec().Result.Data().(float64)

												data.TInfUpdateFuelConsumption = fuelCostCrowd.Sum(func(x interface{}) interface{} {
													y := x.(FuelCost)
													return y.PrimaryFuelConsumed
												}).Exec().Result.Data().(float64)
											}
										}
										// Vibrations handled by sql query
									}

									// Maintenance handled by sql query
									// FailureNotifications handled by sql query
									// MROElements handled by sql query
									// Operationals handled by sql query

									// e = ctx.Insert(&data)
									saveDatas = append(saveDatas, &data)
									dataCount++
									if len(saveDatas) == 1000 {
										mugen.Lock()
										e = ctx.InsertBulk(saveDatas)
										mugen.Unlock()
										ErrorHandler(e, "generateMaintenanceDataBrowser")
										tk.Printf("%v : %v \n", plantCodeStr, dataCount)

										saveDatas = []orm.IModel{}
									}

									/*ErrorHandler(e, "generateMaintenanceDataBrowser")
									tk.Println("save 4")*/
								}
							}
						}
					}

				}
			}
		}

		e = c.NewQuery().Delete().From(new(GenDataBrowserNotInTmp).TableName()).SetConfig("multiexec", true).Where(dbox.Eq("ID", genIDTempTable)).Exec(nil)

		if len(saveDatas) > 0 {
			e = ctx.InsertBulk(saveDatas)
			ErrorHandler(e, "generateMaintenanceDataBrowser")
			tk.Printf("%v : %v \n", plantCodeStr, dataCount)
		}

	}

	mugen.Lock()
	dataCounts.Set(plantCodeStr, dataCount)
	mugen.Unlock()

	wg.Done()
}
Exemple #26
0
func (q *Query) Exec(parm toolkit.M) error {
	var e error
	q.save = false

	// useHeader := q.Connection().Info().Settings.Get("useheader", false).(bool)
	if parm == nil {
		parm = toolkit.M{}
	}

	data := toolkit.M{}
	if parm.Has("data") {
		data, _ = toolkit.ToM(parm["data"])
	}

	quyerParts := q.Parts()
	c := crowd.From(&quyerParts)

	groupParts := c.Group(func(x interface{}) interface{} {
		return x.(*dbox.QueryPart).PartType
	}, nil).Exec()

	parts := map[interface{}]interface{}{}
	if len(groupParts.Result.Data().([]crowd.KV)) > 0 {
		for _, kv := range groupParts.Result.Data().([]crowd.KV) {
			parts[kv.Key] = kv.Value
		}
	}

	// fromParts, hasFrom := parts[dbox.QueryPartFrom]
	// if !hasFrom {
	// 	return errorlib.Error(packageName, "Query", modQuery, "Invalid table name")
	// }
	// tablename = fromParts.([]interface{})[0].(*dbox.QueryPart).Value.(string)

	// var where interface{}
	commandType := ""
	//	multi := false

	_, hasDelete := parts[dbox.QueryPartDelete]
	_, hasInsert := parts[dbox.QueryPartInsert]
	_, hasUpdate := parts[dbox.QueryPartUpdate]
	_, hasSave := parts[dbox.QueryPartSave]

	if hasDelete {
		commandType = dbox.QueryPartDelete
	} else if hasInsert {
		commandType = dbox.QueryPartInsert
	} else if hasUpdate {
		commandType = dbox.QueryPartUpdate
	} else if hasSave {
		commandType = dbox.QueryPartSave
		q.save = true
	}

	var where []*dbox.Filter
	whereParts, hasWhere := parts[dbox.QueryPartWhere]
	if hasWhere {
		for _, p := range whereParts.([]*dbox.QueryPart) {
			fs := p.Value.([]*dbox.Filter)
			for _, f := range fs {
				// if len(parm) > 0 {
				f = ReadVariable(f, parm)
				// }
				where = append(where, f)
			}
		}
	}

	//Check setNewHeader First
	if q.Connection().(*Connection).setNewHeader && (commandType != dbox.QueryPartInsert && commandType != dbox.QueryPartSave) {
		q.Connection().(*Connection).Close()
		filename := q.Connection().(*Connection).Info().Host
		os.Remove(filename)
		return errorlib.Error(packageName, "Query", modQuery, "Only Insert Permited")
	}

	q.Connection().(*Connection).TypeOpenFile = TypeOpenFile_Append
	if hasDelete || hasUpdate {
		q.Connection().(*Connection).TypeOpenFile = TypeOpenFile_Create
	}

	q.Connection().(*Connection).ExecOpr = false
	if !q.Connection().(*Connection).setNewHeader && (commandType != dbox.QueryPartSave || (commandType == dbox.QueryPartSave && q.Connection().(*Connection).writer == nil)) {
		e = q.Connection().(*Connection).StartSessionWrite()
		// toolkit.Printf("Debug 333 : %v \n\n", "masuk")
	}

	if e != nil {
		return errorlib.Error(packageName, "Query", modQuery, e.Error())
	}

	var execCond QueryCondition
	execCond.where = where

	switch commandType {
	case dbox.QueryPartSave:
		q.updatessave = false
		e = q.execQueryPartSave(data)
	case dbox.QueryPartInsert:
		e = q.execQueryPartInsert(data)
	case dbox.QueryPartDelete:
		e = q.execQueryPartDelete(execCond)
	case dbox.QueryPartUpdate:
		e = q.execQueryPartUpdate(data, execCond)
	}

	q.Connection().(*Connection).ExecOpr = true
	if e != nil {
		q.Connection().(*Connection).ExecOpr = false
	}

	if commandType != dbox.QueryPartSave || q.updatessave {
		e = q.Connection().(*Connection).EndSessionWrite()
		q.Connection().(*Connection).TypeOpenFile = TypeOpenFile_Append
	}

	if e != nil {
		return errorlib.Error(packageName, "Query", modQuery, e.Error())
	}

	return nil
}
Exemple #27
0
func (q *Query) Cursor(in toolkit.M) (dbox.ICursor, error) {
	var e error
	/*
		if q.Parts == nil {
			return nil, errorlib.Error(packageName, modQuery,
				"Cursor", fmt.Sprintf("No Query Parts"))
		}
	*/

	dbname := q.Connection().Info().Database
	cursor := dbox.NewCursor(new(Cursor))
	if q.GetDriverDB() == "hive" {
		session := q.SessionHive()
		cursor.(*Cursor).sessionHive = session
	} else {
		session := q.Session()
		cursor.(*Cursor).session = session
	}
	cursor.(*Cursor).DateFormat = q.Connection().(*Connection).DateFormat
	driverName := q.GetDriverDB()
	// driverName = "oracle"
	var QueryString string

	/*
		parts will return E - map{interface{}}interface{}
		where each interface{} returned is slice of interfaces --> []interface{}
	*/
	quyerParts := q.Parts()
	c := crowd.From(&quyerParts)
	groupParts := c.Group(func(x interface{}) interface{} {
		return x.(*dbox.QueryPart).PartType
	}, nil).Exec()
	parts := map[interface{}]interface{}{}
	if len(groupParts.Result.Data().([]crowd.KV)) > 0 {
		for _, kv := range groupParts.Result.Data().([]crowd.KV) {
			parts[kv.Key] = kv.Value
		}
	}

	fromParts, hasFrom := parts[dbox.QueryPartFrom]
	procedureParts, hasProcedure := parts["procedure"]
	freeQueryParts, hasFreeQuery := parts["freequery"]

	idMap := toolkit.M{}
	if hasFrom {
		tablename := ""
		tablename = fromParts.([]*dbox.QueryPart)[0].Value.(string)

		selectParts, hasSelect := parts[dbox.QueryPartSelect]
		var attribute string
		incAtt := 0
		if hasSelect {
			for _, sl := range selectParts.([]*dbox.QueryPart) {
				for _, fid := range sl.Value.([]string) {
					if incAtt == 0 {
						attribute = fid
					} else {
						attribute = attribute + ", " + fid
					}

					idMap.Set(fid, fid)
					incAtt++
				}
			}
		} else {
			_, hasUpdate := parts[dbox.QueryPartUpdate]
			_, hasInsert := parts[dbox.QueryPartInsert]
			_, hasDelete := parts[dbox.QueryPartDelete]
			_, hasSave := parts[dbox.QueryPartSave]

			if hasUpdate || hasInsert || hasDelete || hasSave {
				return nil, errorlib.Error(packageName, modQuery, "Cursor",
					"Valid operation for a cursor is select only")
			}
		}

		aggrParts, hasAggr := parts[dbox.QueryPartAggr]

		var aggrExpression string
		if hasAggr {
			incAtt := 0
			// for _, aggr := range aggrParts.([]interface{}) {
			for _, aggr := range aggrParts.([]*dbox.QueryPart) {
				// qp := aggr.(*dbox.QueryPart)
				/* isi qp :  &{AGGR {$sum 1 Total Item}}*/
				aggrInfo := aggr.Value.(dbox.AggrInfo)
				/* isi Aggr Info :  {$sum 1 Total Item}*/

				if incAtt == 0 {
					if driverName == "hive" {
						aggrExpression = strings.Replace(aggrInfo.Op, "$", "", 1) + "(" +
							cast.ToString(aggrInfo.Field) + ")" + " as " + aggrInfo.Alias
					} else {
						aggrExpression = strings.Replace(aggrInfo.Op, "$", "", 1) + "(" +
							cast.ToString(aggrInfo.Field) + ")" + " as \"" + aggrInfo.Alias + "\""
					}
				} else {
					if driverName == "hive" {
						aggrExpression += ", " + strings.Replace(aggrInfo.Op, "$", "", 1) + "(" +
							cast.ToString(aggrInfo.Field) + ")" + " as " + aggrInfo.Alias
					} else {
						aggrExpression += ", " + strings.Replace(aggrInfo.Op, "$", "", 1) + "(" +
							cast.ToString(aggrInfo.Field) + ")" + " as \"" + aggrInfo.Alias + "\""
					}
				}
				incAtt++
			}
			/* isi Aggr Expression :  sum(1) as 'Total Item', max(amount) as 'Max Amount', avg(amount) as 'Average Amount'*/
		}

		var where interface{}
		whereParts, hasWhere := parts[dbox.QueryPartWhere]
		if hasWhere {
			fb := q.Connection().Fb()
			for _, p := range whereParts.([]*dbox.QueryPart) {
				fs := p.Value.([]*dbox.Filter)
				for _, f := range fs {
					if in != nil {
						f = ReadVariable(f, in)
					}
					fb.AddFilter(f)
				}
			}
			where, e = fb.Build()
			if e != nil {
				return nil, errorlib.Error(packageName, modQuery, "Cursor",
					e.Error())
			}
		}

		var orderExpression string
		orderParts, hasOrder := parts[dbox.QueryPartOrder]
		if hasOrder {
			qp := orderParts.([]*dbox.QueryPart)[0]
			for i, fid := range qp.Value.([]string) {
				if i == 0 {
					if string(fid[0]) == "-" {
						orderExpression = strings.Replace(fid, "-", "", 1) + " DESC"
					} else {
						orderExpression = fid + " ASC"
					}
				} else {
					if string(fid[0]) == "-" {
						orderExpression += ", " + strings.Replace(fid, "-", "", 1) + " DESC"
					} else {
						orderExpression += ", " + fid + " ASC"
					}
				}
			}
			// for _, oval := range orderParts.([]interface{}) {
			// 	qp := oval.(*dbox.QueryPart)
			// 	for i, fid := range qp.Value.([]string) {
			// 		if i == 0 {
			// 			if string(fid[0]) == "-" {
			// 				orderExpression = strings.Replace(fid, "-", "", 1) + " DESC"
			// 			} else {
			// 				orderExpression = fid + " ASC"
			// 			}
			// 		} else {
			// 			if string(fid[0]) == "-" {
			// 				orderExpression += ", " + strings.Replace(fid, "-", "", 1) + " DESC"
			// 			} else {
			// 				orderExpression += ", " + fid + " ASC"
			// 			}
			// 		}
			// 	}
			// }
		}

		skip := 0
		skipParts, hasSkip := parts[dbox.QueryPartSkip]
		if hasSkip {
			skip = skipParts.([]*dbox.QueryPart)[0].Value.(int)
		}

		take := 0
		takeParts, hasTake := parts[dbox.QueryPartTake]
		if hasTake {
			take = takeParts.([]*dbox.QueryPart)[0].Value.(int)
		}

		partGroup, hasGroup := parts[dbox.QueryPartGroup]
		var groupExpression string
		if hasGroup {
			for _, aggr := range partGroup.([]*dbox.QueryPart) {
				// qp := aggr.(*dbox.QueryPart)
				groupValue := aggr.Value.([]string)
				for i, val := range groupValue {
					if i == 0 {
						groupExpression += val
					} else {
						groupExpression += ", " + val
					}
				}
			}
			/* isi group expression :  GROUP BY nama*/
		}

		if dbname != "" && tablename != "" && e != nil && skip == 0 && take == 0 && where == nil {

		}
		if hasAggr {
			if hasSelect && attribute != "" {
				QueryString = "SELECT " + attribute + ", " + aggrExpression + " FROM " + tablename
			} else {
				QueryString = "SELECT " + aggrExpression + " FROM " + tablename
			}

		} else {
			if attribute == "" {
				QueryString = "SELECT * FROM " + tablename
			} else {
				if driverName == "oci8" {
					_, idVal := toolkit.IdInfo(idMap)
					QueryString = "SELECT " + attribute + ", rank() over(order by " + idVal.(string) + " asc) rn FROM " + tablename
				} else {
					QueryString = "SELECT " + attribute + " FROM " + tablename
				}
			}
		}

		if hasWhere {
			QueryString += " WHERE " + cast.ToString(where)
		}
		if hasGroup {
			QueryString += " GROUP BY " + groupExpression
		}
		if hasOrder {
			QueryString += " ORDER BY " + orderExpression
		}

		if driverName == "mysql" {
			if hasSkip && hasTake {
				QueryString += " LIMIT " + cast.ToString(take) +
					" OFFSET " + cast.ToString(skip)
			} else if hasSkip && !hasTake {
				QueryString += " LIMIT " + cast.ToString(9999999) +
					" OFFSET " + cast.ToString(skip)
			} else if hasTake && !hasSkip {
				QueryString += " LIMIT " + cast.ToString(take)
			}
		} else if driverName == "mssql" {
			if hasSkip && hasTake {
				QueryString += " OFFSET " + cast.ToString(skip) + " ROWS FETCH NEXT " +
					cast.ToString(take) + " ROWS ONLY "
			} else if hasSkip && !hasTake {
				QueryString += " OFFSET " + cast.ToString(skip) + " ROWS"
			} else if hasTake && !hasSkip {
				top := "SELECT TOP " + cast.ToString(take) + " "
				QueryString = strings.Replace(QueryString, "SELECT", top, 1)
			}

		} else if driverName == "oci8" {
			if hasSkip && hasTake {
				var lower, upper int
				upper = skip + take
				lower = upper - take + 1

				QueryString = "select * from (" + QueryString + ") t1 WHERE t1.rn BETWEEN " + cast.ToString(lower) + " AND " + cast.ToString(upper)
			} else if hasSkip && !hasTake {
				QueryString = "select * from (" + QueryString +
					") t1 WHERE t1.rn > " + cast.ToString(skip)
			} else if hasTake && !hasSkip {
				QueryString = "select * from (" + QueryString +
					") WHERE ROWNUM <= " + cast.ToString(take)
			}

		} else if driverName == "postgres" {
			if hasSkip && hasTake {
				QueryString += " LIMIT " + cast.ToString(take) +
					" OFFSET " + cast.ToString(skip)
			} else if hasSkip && !hasTake {
				QueryString += " LIMIT ALL" +
					" OFFSET " + cast.ToString(skip)
			} else if hasTake && !hasSkip {
				QueryString += " LIMIT " + cast.ToString(take)
			}
		} else if driverName == "hive" {
			if hasSkip && hasTake {
				var lower, upper int
				upper = skip + take
				lower = upper - take

				//toolkit.Println("take: ", take, "skip: ", skip, "lower: ", lower, "upper: ", upper)
				QueryString = "SELECT " + attribute + " FROM (SELECT *,row_number() over () as rowid FROM " + tablename + ") x WHERE rowid > " +
					toolkit.ToString(lower) + " and rowid <= " + toolkit.ToString(upper)
			} else if hasSkip && !hasTake {

			} else if hasTake && !hasSkip {
				QueryString += " LIMIT " + toolkit.ToString(take)
			}
		}
		// toolkit.Println(QueryString)
		var querystmt string
		if where != nil {
			querystmt = "select count(*) from " + tablename +
				" where " + cast.ToString(where)
		} else {
			querystmt = "select count(*) from " + tablename
		}

		/*populate fetch.count*/
		var rowCount int
		if driverName == "hive" {
			// rowCount = 999999999
			// row := sessionHive.Exec(querystmt)
			// rowCount = toolkit.ToInt(row[0], "auto")
		} else {
			rows, _ := cursor.(*Cursor).session.Query(querystmt)
			for rows.Next() {
				rows.Scan(&rowCount)
			}
		}
		if rowCount <= skip {
			rowCount = 0
		} else {
			rowCount -= skip
		}
		if rowCount >= take && take > 0 {
			rowCount = take
		}
		cursor.(*Cursor).count = rowCount
		cursor.(*Cursor).driver = driverName
		/*assign cursor.QueryString*/
		cursor.(*Cursor).QueryString = QueryString
	} else if hasProcedure {
		procCommand := procedureParts.([]*dbox.QueryPart)[0].Value.(interface{})

		spName := procCommand.(toolkit.M)["name"].(string) + " "
		params, hasParam := procCommand.(toolkit.M)["parms"]
		orderparam, hasOrder := procCommand.(toolkit.M)["orderparam"]
		ProcStatement := ""

		if driverName == "mysql" {
			paramstring := ""
			if hasParam && hasOrder {
				paramToolkit := params.(toolkit.M)
				orderString := orderparam.([]string)
				for i := 0; i < len(paramToolkit); i++ {
					if i == 0 {
						if strings.Contains(orderString[i], "@@") {
							paramstring = "(" + strings.Replace(orderString[i], "@@", "@", 1)
						} else if StringValue(paramToolkit[orderString[i]], driverName) != "''" {
							paramstring = "(" + StringValue(paramToolkit[orderString[i]], driverName)

						} else {
							paramstring = "("
						}

					} else {
						if strings.Contains(orderString[i], "@@") {
							paramstring += ", " + strings.Replace(orderString[i], "@@", "@", 1)
						} else {
							paramstring += ", " + StringValue(paramToolkit[orderString[i]], driverName)

						}
					}
				}

			} else if hasParam && !hasOrder {
				return nil, errorlib.Error(packageName, modQuery, "procedure", "please provide order parameter")
			} else {
				paramstring = "("
			}
			paramstring += ");"

			ProcStatement = "CALL " + spName + paramstring
		} else if driverName == "mssql" {
			paramstring := ""
			incParam := 0
			if hasParam {
				for key, val := range params.(toolkit.M) {
					if key != "" {
						if incParam == 0 {
							paramstring = key + " = " + StringValue(val, driverName) + ""

						} else {
							paramstring += ", " + key + " = " + StringValue(val, driverName) + ""
						}
						incParam += 1
					}
				}
				paramstring += ";"
			}

			ProcStatement = "EXECUTE " + spName + paramstring
		} else if driverName == "oci8" {
			var paramstring string
			var variable string
			var isEmpty bool
			if hasParam && hasOrder {
				paramToolkit := params.(toolkit.M)
				orderString := orderparam.([]string)
				for i := 0; i < len(paramToolkit); i++ {
					if i == 0 {
						if strings.Contains(orderString[i], "@@") {
							variable = "var " + strings.Replace(orderString[i], "@@", "", 1) +
								" " + cast.ToString(paramToolkit[orderString[i]]) + ";"
							paramstring = "(" + strings.Replace(orderString[i], "@@", ":", 1)
							isEmpty = false
						} else if StringValue(paramToolkit[orderString[i]], driverName) != "''" {
							paramstring = "(" + StringValue(paramToolkit[orderString[i]], driverName)
							isEmpty = false
						}

					} else {
						if strings.Contains(orderString[i], "@@") {
							variable += "var " + strings.Replace(orderString[i], "@@", "", 1) +
								" " + cast.ToString(paramToolkit[orderString[i]]) + ";"
							paramstring += ", " + strings.Replace(orderString[i], "@@", ":", 1)
						} else {
							paramstring += ", " + StringValue(paramToolkit[orderString[i]], driverName)

						}
					}
				}
				if !isEmpty {
					paramstring += ");"
				}
			} else if hasParam && !hasOrder {
				return nil, errorlib.Error(packageName, modQuery, "procedure", "please provide order parameter")
			}

			ProcStatement = variable + "EXECUTE " + spName + paramstring
			// toolkit.Println("ProcStatement>", ProcStatement)
		} else if driverName == "postgres" {
			paramstring := ""
			if hasParam && hasOrder {
				paramToolkit := params.(toolkit.M)
				orderString := orderparam.([]string)
				for i := 0; i < len(paramToolkit); i++ {
					if i == 0 {
						if strings.Contains(orderString[i], "@@") {
							paramstring = "(" + strings.Replace(orderString[i], "@@", "@", 1)
						} else if StringValue(paramToolkit[orderString[i]], driverName) != "''" {
							paramstring = "(" + StringValue(paramToolkit[orderString[i]], driverName)

						} else {
							paramstring = "("
						}

					} else {
						if strings.Contains(orderString[i], "@@") {
							paramstring += ", " + strings.Replace(orderString[i], "@@", "@", 1)
						} else {
							paramstring += ", " + StringValue(paramToolkit[orderString[i]], driverName)

						}
					}

					// fmt.Println("Print value order", paramstring)
				}

			} else if hasParam && !hasOrder {

				return nil, errorlib.Error(packageName, modQuery, "procedure", "please provide order parameter")

			} else {
				paramstring = "("
			}
			paramstring += ")"

			ProcStatement = "SELECT " + spName + paramstring
		}

		cursor.(*Cursor).QueryString = ProcStatement

		// fmt.Println("Proc Statement : ", ProcStatement)
	} else if hasFreeQuery {
		querySyntax := freeQueryParts.([]*dbox.QueryPart)[0].Value.(interface{})
		syntax := querySyntax.(toolkit.M)["syntax"].(string)
		cursor.(*Cursor).QueryString = syntax
	}
	return cursor, nil
}
Exemple #28
0
func TestWhereSelectGroup(t *testing.T) {
	skipIfNil(t)

	c1 := *c
	cwhere := c1.Where(func(x interface{}) interface{} {
		if x.(Obj).I < 200 {
			return true
		}
		return false
	}).Exec()
	check(t, cwhere.Error, "")
	toolkit.Println("First 20 data: ", toolkit.JsonString(cwhere.Result.Data().([]Obj)))

	cselect := cwhere.Apply(func(x interface{}) interface{} {
		return x.(Obj).F
	}).Exec()
	check(t, cselect.Error, "")
	toolkit.Println("Select : First 20 data: ", toolkit.JsonString(cselect.Result.Data().([]float64)[:20]))

	cgroup := cselect.Group(func(x interface{}) interface{} {
		return (x.(float64) - math.Mod(x.(float64), float64(100))) / float64(100)
	}, nil).Exec()
	check(t, cgroup.Error, "")
	datas := cgroup.Result.Data().([]crowd.KV)
	for _, v := range datas {
		toolkit.Printf("Group %2.0f: %d data: %v\n",
			v.Key,
			len(v.Value.([]float64)),
			v.Value.([]float64))
	}

	cgroupaggr := cselect.Apply(func(x interface{}) interface{} {
		kv := x.(crowd.KV)
		vs := kv.Value.([]float64)
		sum := crowd.From(&vs).Sum(nil).Exec().Result.Sum
		return crowd.KV{kv.Key, sum}
	}).Exec()
	check(t, cgroupaggr.Error, "")
	toolkit.Println("GroupAggr: First 20 data: ", toolkit.JsonString(cgroupaggr.Result.Data().([]crowd.KV)))

	cgroupaggrmax := cgroupaggr.Max(func(x interface{}) interface{} {
		return x.(crowd.KV).Value
	}).Exec()
	check(t, cgroupaggrmax.Error, "")
	toolkit.Println("GroupAggrMax: ", cgroupaggrmax.Result.Max)

	//c = crowd.From(&objs)
	toolkit.Printfn("Data len: %d", c.Len())
	c2 := *c
	oneshot := c2.Apply(func(x interface{}) interface{} {
		return float64(x.(Obj).I)
	}).Group(func(x interface{}) interface{} {
		return (x.(float64) - math.Mod(x.(float64), float64(100))) / float64(100)
	}, nil).Apply(func(x interface{}) interface{} {
		kv := x.(crowd.KV)
		vs := kv.Value.([]float64)
		sum := crowd.From(&vs).Sum(nil).Exec().Result.Sum
		return crowd.KV{kv.Key, sum}
	}).Sum(func(x interface{}) interface{} {
		return x.(crowd.KV).Value
	}).Exec()
	check(t, oneshot.Error, "")
	toolkit.Println("Oneshot: ", oneshot.Result.Sum)
}
func (s *GenWODurationSummary) generateDurationCostWorkOrderSummary() error {
	ctx := s.BaseController.Ctx
	c := ctx.Connection
	var (
		query []*dbox.Filter
	)
	tk.Println("Generating Duration Cost Work Order Summary..")
	Years := []int{2013, 2014, 2015}
	query = []*dbox.Filter{}

	EqTypes := []MappedEquipmentType{}
	csr, e := c.NewQuery().From(new(MappedEquipmentType).TableName()).Cursor(nil)
	defer csr.Close()

	if e != nil {
		return e
	}

	e = csr.Fetch(&EqTypes, 0, false)
	if e != nil {
		return e
	}
	for _, year := range Years {
		query = []*dbox.Filter{}
		query = append(query, dbox.Gte("Period", time.Date(year, 1, 1, 0, 0, 0, 0, time.UTC)))
		query = append(query, dbox.Lt("Period", time.Date((year+1), 1, 1, 0, 0, 0, 0, time.UTC)))

		MaintenanceCostByHours := []MaintenanceCostByHour{}
		csr, e := c.NewQuery().From(new(MaintenanceCostByHour).TableName()).Where(query...).Cursor(nil)
		if e != nil {
			return e
		}
		e = csr.Fetch(&MaintenanceCostByHours, 0, false)
		if e != nil {
			return e
		}
		csr.Close()

		MaintenanceCostList := []MaintenanceCost{}
		csr, e = c.NewQuery().From(new(MaintenanceCost).TableName()).Where(query...).Cursor(nil)
		if e != nil {
			return e
		}
		e = csr.Fetch(&MaintenanceCostList, 0, false)
		if e != nil {
			return e
		}
		csr.Close()

		Plants := crowd.From(&MaintenanceCostByHours).Group(func(x interface{}) interface{} {
			return x.(MaintenanceCostByHour).Plant
		}, nil).Exec().Result.Data().([]crowd.KV)

		for _, p := range Plants {
			plant := p.Key.(string)
			EqType := crowd.From(&MaintenanceCostByHours).Where(func(x interface{}) interface{} {
				return x.(MaintenanceCostByHour).Plant == plant
			}).Group(func(x interface{}) interface{} {
				return x.(MaintenanceCostByHour).EquipmentType
			}, nil).Exec().Result.Data().([]crowd.KV)

			for _, eqt := range EqType {
				eq := eqt.Key.(string)
				ActType := crowd.From(&MaintenanceCostByHours).Where(func(x interface{}) interface{} {
					o := x.(MaintenanceCostByHour)
					return o.Plant == plant && o.EquipmentType == eq
				}).Group(func(x interface{}) interface{} {
					return x.(MaintenanceCostByHour).MaintenanceActivityType
				}, nil).Exec().Result.Data().([]crowd.KV)

				for _, a := range ActType {
					act := a.Key.(string)
					OrderType := crowd.From(&MaintenanceCostByHours).Where(func(x interface{}) interface{} {
						o := x.(MaintenanceCostByHour)
						return o.Plant == plant && o.EquipmentType == eq && o.MaintenanceActivityType == act
					}).Group(func(x interface{}) interface{} {
						return x.(MaintenanceCostByHour).OrderType
					}, nil).Exec().Result.Data().([]crowd.KV)

					for _, o := range OrderType {
						order := o.Key.(string)
						d := new(WODurationSummary)
						d.PeriodYear = year
						d.OrderType = order
						if len(eq) == 1 {
							d.EquipmentType = "Other"
						} else {
							d.EquipmentType = eq
						}
						if len(eq) == 1 {
							d.EquipmentTypeDescription = "Other"
						} else {
							EqTypeDesc := crowd.From(&MaintenanceCostByHours).Where(func(x interface{}) interface{} {
								o := x.(MaintenanceCostByHour)
								return o.Plant == plant && o.EquipmentType == eq
							}).Exec().Result.Data().([]MaintenanceCostByHour)
							if len(EqTypeDesc) > 0 {
								d.EquipmentTypeDescription = EqTypeDesc[0].EquipmentTypeDesc
							}
						}

						d.ActivityType = act
						d.Plant = PlantNormalization(plant)
						d.PlanValue = crowd.From(&MaintenanceCostByHours).Where(func(x interface{}) interface{} {
							o := x.(MaintenanceCostByHour)
							return o.Plant == plant && o.EquipmentType == eq && o.MaintenanceActivityType == act
						}).Sum(func(x interface{}) interface{} {
							return x.(MaintenanceCostByHour).PlanVal
						}).Exec().Result.Sum
						d.ActualValue = crowd.From(&MaintenanceCostByHours).Where(func(x interface{}) interface{} {
							o := x.(MaintenanceCostByHour)
							return o.Plant == plant && o.EquipmentType == eq && o.MaintenanceActivityType == act
						}).Sum(func(x interface{}) interface{} {
							return x.(MaintenanceCostByHour).Actual
						}).Exec().Result.Sum
						d.WOCount = len(OrderType)
						d.Cost = crowd.From(&MaintenanceCostList).Where(func(x interface{}) interface{} {
							o := x.(MaintenanceCost)
							return o.Plant == plant && o.EquipmentType == eq && o.MaintenanceActivityType == act
						}).Sum(func(x interface{}) interface{} {
							return x.(MaintenanceCost).PeriodTotalActual
						}).Exec().Result.Sum
						_, e := ctx.InsertOut(d)
						tk.Println("#")
						if e != nil {
							tk.Println(e)
							break
						}

					}

				}
			}
		}
	}
	return e
}
Exemple #30
0
func (q *Query) prepare(in toolkit.M) (out toolkit.M, e error) {
	out = toolkit.M{}

	quyerParts := q.Parts()
	c := crowd.From(&quyerParts)
	groupParts := c.Group(func(x interface{}) interface{} {
		return x.(*dbox.QueryPart).PartType
	}, nil).Exec()

	parts := map[interface{}]interface{}{}
	if len(groupParts.Result.Data().([]crowd.KV)) > 0 {
		for _, kv := range groupParts.Result.Data().([]crowd.KV) {
			parts[kv.Key] = kv.Value
		}
	}

	_, hasUpdate := parts[dbox.QueryPartUpdate]
	_, hasInsert := parts[dbox.QueryPartInsert]
	_, hasDelete := parts[dbox.QueryPartDelete]
	_, hasSave := parts[dbox.QueryPartSave]
	_, hasFrom := parts[dbox.QueryPartFrom]
	procedureParts, hasProcedure := parts["procedure"]

	var tableName string
	if hasFrom {
		fromParts, _ := parts[dbox.QueryPartFrom]
		tableName = fromParts.([]*dbox.QueryPart)[0].Value.(string)
	} else {

		return nil, err.Error(packageName, "Query", "prepare", "Invalid table name")
	}
	out.Set("tableName", tableName)

	if freeQueryParts, hasFreeQuery := parts["freequery"]; hasFreeQuery {
		var syntax string
		qsyntax := freeQueryParts.([]*dbox.QueryPart)[0].Value.(interface{})
		syntax = qsyntax.(toolkit.M)["syntax"].(string)
		out.Set("freequery", syntax)
		out.Set("cmdType", dbox.QueryPartSelect)
	} else if hasInsert || hasUpdate || hasDelete || hasSave {
		if hasUpdate {
			out.Set("cmdType", dbox.QueryPartUpdate)
		} else if hasInsert {
			out.Set("cmdType", dbox.QueryPartInsert)
		} else if hasDelete {
			out.Set("cmdType", dbox.QueryPartDelete)
		} else if hasSave {
			out.Set("cmdType", dbox.QueryPartSave)
		}

		var where interface{}
		whereParts, hasWhere := parts[dbox.QueryPartWhere]
		if hasWhere {
			fb := q.Connection().Fb()
			for _, p := range whereParts.([]*dbox.QueryPart) {
				fs := p.Value.([]*dbox.Filter)
				for _, f := range fs {
					fb.AddFilter(f)
				}
			}

			where, e = fb.Build()
			if e != nil {

			}
			out.Set("where", where)
		}

		var dataM toolkit.M
		var dataMs []toolkit.M

		hasData := in.Has("data")
		var dataIsSlice bool
		if hasData {
			data := in.Get("data")
			if toolkit.IsSlice(data) {
				dataIsSlice = true
				e = toolkit.Unjson(toolkit.Jsonify(data), dataMs)
				if e != nil {
					return nil, err.Error(packageName, modQuery, "Exec: ", "Data encoding error: "+e.Error())
				}
			} else {
				dataM, e = toolkit.ToM(data)
				dataMs = append(dataMs, dataM)
				if e != nil {
					return nil, err.Error(packageName, modQuery, "Exec: ", "Data encoding error: "+e.Error())
				}
			}

			var id string
			var idVal interface{}
			if where == nil {
				id, idVal = toolkit.IdInfo(data)
				if id != "" {
					where = id + " = " + StringValue(idVal, "non")
				}
				out.Set("where", where)
			}

			if !dataIsSlice {
				var fields string
				var values string
				var setUpdate string
				var inc int
				for field, val := range dataM {
					stringval := StringValue(val, "non")
					if inc == 0 {
						fields = "(" + field
						values = "(" + stringval
						setUpdate = field + " = " + stringval
					} else {
						fields += ", " + field
						values += ", " + stringval
						setUpdate += ", " + field + " = " + stringval
					}
					inc++
				}
				fields += ")"
				values += ")"
				if hasInsert || hasSave {
					out.Set("fields", fields)
					out.Set("values", values)
				}
				if hasUpdate || hasSave {
					out.Set("setUpdate", setUpdate)
				}
			}
		}
	} else if hasProcedure {
		cmd := procedureParts.([]*dbox.QueryPart)[0].Value.(interface{})

		spName := cmd.(toolkit.M)["name"].(string) + " "
		params, hasParams := cmd.(toolkit.M)["params"]
		orderparam, hasOrder := cmd.(toolkit.M)["orderparam"]
		ProcStatement := ""
		toolkit.Println(spName, params, hasParams, orderparam, hasOrder, ProcStatement)

	} else {
		var selectField string
		incAtt := 0
		if selectParts, hasSelect := parts[dbox.QueryPartSelect]; hasSelect {
			for _, sl := range selectParts.([]*dbox.QueryPart) {
				for _, fid := range sl.Value.([]string) {
					if incAtt == 0 {
						selectField = fid
					} else {
						selectField = selectField + ", " + fid
					}
					incAtt++
				}
			}
		}
		out.Set("cmdType", dbox.QueryPartSelect)
		out.Set("selectField", selectField)

		///
		/// not yet iimplement
		var aggrExp string
		if aggrParts, hasAggr := parts[dbox.QueryPartAggr]; hasAggr {
			incAtt := 0
			for _, aggr := range aggrParts.([]*dbox.QueryPart) {
				/* isi qp :  &{AGGR {$sum 1 Total Item}}*/
				aggrInfo := aggr.Value.(dbox.AggrInfo)
				/* isi Aggr Info :  {$sum 1 Total Item}*/

				if incAtt == 0 {
					aggrExp = strings.Replace(aggrInfo.Op, "$", "", 1) + "(" +
						toolkit.ToString(aggrInfo.Field) + ")" + " as \"" + aggrInfo.Alias + "\""
				} else {
					aggrExp += ", " + strings.Replace(aggrInfo.Op, "$", "", 1) + "(" +
						toolkit.ToString(aggrInfo.Field) + ")" + " as \"" + aggrInfo.Alias + "\""
				}
				incAtt++
			}
		}
		out.Set("aggr", aggrExp)

		///
		/// Where Condition
		var where interface{}
		if whereParts, hasWhere := parts[dbox.QueryPartWhere]; hasWhere {
			fb := q.Connection().Fb()
			for _, p := range whereParts.([]*dbox.QueryPart) {
				for _, f := range p.Value.([]*dbox.Filter) {
					if in != nil {
						f = rdbms.ReadVariable(f, in)
					}
					fb.AddFilter(f)
				}
			}
			where, e = fb.Build()
			if e != nil {
				return nil, err.Error(packageName, modQuery, "prepare", e.Error())
			}
		}
		out.Set("where", where)

		///
		/// Sort Condition
		var sort []string
		if sortParts, hasSort := parts[dbox.QueryPartOrder]; hasSort {
			sort = []string{}
			for _, sr := range sortParts.([]*dbox.QueryPart) {
				for _, s := range sr.Value.([]string) {
					sort = append(sort, s)
				}
			}
		}
		out.Set("sort", sort)

		///
		/// Take Condition
		take := 0
		isTake := false
		if takeParts, hasTake := parts[dbox.QueryPartTake]; hasTake {
			isTake = true
			take = takeParts.([]*dbox.QueryPart)[0].Value.(int)
		}
		out.Set("isTake", isTake)
		out.Set("take", take)

		///
		/// Skip Condition
		skip := 0
		isSkip := false
		if skipParts, hasSkip := parts[dbox.QueryPartSkip]; hasSkip {
			isSkip = true
			skip = skipParts.([]*dbox.QueryPart)[0].Value.(int)
		}
		out.Set("isSkip", isSkip)
		out.Set("skip", skip)

		///
		/// Group By Condition
		var groupExp string
		hasAggr := false
		if groupParts, hasGroup := parts[dbox.QueryPartGroup]; hasGroup {
			hasAggr = true
			for _, pg := range groupParts.([]*dbox.QueryPart) {
				for i, grValue := range pg.Value.([]string) {
					if i == 0 {
						groupExp += grValue
					} else {
						groupExp += ", " + grValue
					}
				}
			}
		}
		out.Set("group", groupExp)
		out.Set("hasAggr", hasAggr)

		///
		/// Order By Condition
		var orderExp string
		if orderParts, hasOrder := parts[dbox.QueryPartOrder]; hasOrder {
			for _, ordrs := range orderParts.([]*dbox.QueryPart) {
				for i, oVal := range ordrs.Value.([]string) {
					if i == 0 {
						if string(oVal[0]) == "-" {
							orderExp = strings.Replace(oVal, "-", "", 1) + " DESC"
						} else {
							orderExp = oVal + " ASC"
						}
					} else {
						if string(oVal[0]) == "-" {
							orderExp += ", " + strings.Replace(oVal, "-", "", 1) + " DESC"
						} else {
							orderExp += ", " + oVal + " ASC"
						}
					}
				}
			}
		}
		out.Set("order", orderExp)
	}

	return
}