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)
}
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
}
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()))
}
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)
}
}
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
}
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
}
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
}
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
}
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
}
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
}
// 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
}
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
}
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
}
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
}
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
}
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
}
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
}
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
}
// 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
}
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
}
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
}
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()
}
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
}
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
}
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
}
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
}
