func filterCondition(cond toolkit.M) *dbox.Filter {
fb := new(dbox.Filter)
for key, val := range cond {
if key == "$and" || key == "$or" {
afb := []*dbox.Filter{}
for _, sVal := range val.([]interface{}) {
rVal := sVal.(map[string]interface{})
mVal := toolkit.M{}
for rKey, mapVal := range rVal {
mVal.Set(rKey, mapVal)
}
afb = append(afb, filterCondition(mVal))
}
if key == "$and" {
fb = dbox.And(afb...)
} else {
fb = dbox.Or(afb...)
}
} else {
if reflect.ValueOf(val).Kind() == reflect.Map {
mVal := val.(map[string]interface{})
tomVal, _ := toolkit.ToM(mVal)
switch {
case tomVal.Has("$eq"):
fb = dbox.Eq(key, tomVal["$eq"].(string))
case tomVal.Has("$ne"):
fb = dbox.Ne(key, tomVal["$ne"].(string))
case tomVal.Has("$regex"):
fb = dbox.Contains(key, tomVal["$regex"].(string))
case tomVal.Has("$gt"):
fb = dbox.Gt(key, tomVal["$gt"].(string))
case tomVal.Has("$gte"):
fb = dbox.Gte(key, tomVal["$gte"].(string))
case tomVal.Has("$lt"):
fb = dbox.Lt(key, tomVal["$lt"].(string))
case tomVal.Has("$lte"):
fb = dbox.Lte(key, tomVal["$lte"].(string))
}
} else {
fb = dbox.Eq(key, val)
}
}
}
return fb
}
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 (d *DataSourceController) filterParse(where toolkit.M) *dbox.Filter {
field := where.Get("field", "").(string)
value := fmt.Sprintf("%v", where["value"])
if key := where.Get("key", "").(string); key == "Eq" {
valueInt, errv := strconv.Atoi(fmt.Sprintf("%v", where["value"]))
if errv == nil {
return dbox.Eq(field, valueInt)
} else {
return dbox.Eq(field, value)
}
} else if key == "Ne" {
valueInt, errv := strconv.Atoi(fmt.Sprintf("%v", where["value"]))
if errv == nil {
return dbox.Ne(field, valueInt)
} else {
return dbox.Ne(field, value)
}
} else if key == "Lt" {
valueInt, errv := strconv.Atoi(fmt.Sprintf("%v", where["value"]))
if errv == nil {
return dbox.Lt(field, valueInt)
} else {
return dbox.Lt(field, value)
}
} else if key == "Lte" {
valueInt, errv := strconv.Atoi(fmt.Sprintf("%v", where["value"]))
if errv == nil {
return dbox.Lte(field, valueInt)
} else {
return dbox.Lte(field, value)
}
} else if key == "Gt" {
valueInt, errv := strconv.Atoi(fmt.Sprintf("%v", where["value"]))
if errv == nil {
return dbox.Gt(field, valueInt)
} else {
return dbox.Gt(field, value)
}
} else if key == "Gte" {
valueInt, errv := strconv.Atoi(fmt.Sprintf("%v", where["value"]))
if errv == nil {
return dbox.Gte(field, valueInt)
} else {
return dbox.Gte(field, value)
}
} else if key == "In" {
valueArray := []interface{}{}
for _, e := range strings.Split(value, ",") {
valueArray = append(valueArray, strings.Trim(e, ""))
}
return dbox.In(field, valueArray...)
} else if key == "Nin" {
valueArray := []interface{}{}
for _, e := range strings.Split(value, ",") {
valueArray = append(valueArray, strings.Trim(e, ""))
}
return dbox.Nin(field, valueArray...)
} else if key == "Contains" {
return dbox.Contains(field, value)
} else if key == "Or" {
subs := where.Get("value", []interface{}{}).([]interface{})
filtersToMerge := []*dbox.Filter{}
for _, eachSub := range subs {
eachWhere, _ := toolkit.ToM(eachSub)
filtersToMerge = append(filtersToMerge, d.filterParse(eachWhere))
}
return dbox.Or(filtersToMerge...)
} else if key == "And" {
subs := where.Get("value", []interface{}{}).([]interface{})
filtersToMerge := []*dbox.Filter{}
for _, eachSub := range subs {
eachWhere, _ := toolkit.ToM(eachSub)
filtersToMerge = append(filtersToMerge, d.filterParse(eachWhere))
}
return dbox.And(filtersToMerge...)
}
return nil
}
func filterCondition(cond toolkit.M) *dbox.Filter {
fb := new(dbox.Filter)
for key, val := range cond {
if key == "$and" || key == "$or" {
afb := []*dbox.Filter{}
for _, sVal := range val.([]interface{}) {
rVal := sVal.(map[string]interface{})
mVal := toolkit.M{}
for rKey, mapVal := range rVal {
mVal.Set(rKey, mapVal)
}
afb = append(afb, filterCondition(mVal))
}
if key == "$and" {
fb = dbox.And(afb...)
} else {
fb = dbox.Or(afb...)
}
} else {
if toolkit.TypeName(val) == "map[string]interface {}" {
mVal := val.(map[string]interface{})
tomVal, _ := toolkit.ToM(mVal)
switch {
case tomVal.Has("$eq"):
fb = dbox.Eq(key, tomVal["$eq"])
case tomVal.Has("$ne"):
fb = dbox.Ne(key, tomVal["$ne"])
case tomVal.Has("$regex"):
fb = dbox.Contains(key, toolkit.ToString(tomVal["$regex"]))
case tomVal.Has("$gt"):
fb = dbox.Gt(key, tomVal["$gt"])
case tomVal.Has("$gte"):
fb = dbox.Gte(key, tomVal["$gte"])
case tomVal.Has("$lt"):
fb = dbox.Lt(key, tomVal["$lt"])
case tomVal.Has("$lte"):
fb = dbox.Lte(key, tomVal["$lte"])
case tomVal.Has("$in"):
tval := make([]interface{}, 0, 0)
if toolkit.TypeName(tomVal["$in"]) == "[]interface {}" {
for _, tin := range tomVal["$in"].([]interface{}) {
tval = append(tval, tin)
}
} else {
tval = append(tval, tomVal["$in"])
}
fb = dbox.In(key, tval...)
case tomVal.Has("$nin"):
tval := make([]interface{}, 0, 0)
if toolkit.TypeName(tomVal["$nin"]) == "[]interface {}" {
for _, tin := range tomVal["$nin"].([]interface{}) {
tval = append(tval, tin)
}
} else {
tval = append(tval, tomVal["$nin"])
}
fb = dbox.Nin(key, tval...)
}
} else {
fb = dbox.Eq(key, val)
}
}
}
return fb
}
// 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 TestSelectFilter(t *testing.T) {
c, e := prepareConnection()
if e != nil {
t.Errorf("Unable to connect %s \n", e.Error())
return
}
defer c.Close()
csr, e := c.NewQuery().
Select("id", "name", "tanggal", "umur").
From("tes").
//Where(dbox.Eq("name", "Bourne")).
//Where(dbox.Ne("name", "Bourne")).
//Where(dbox.Gt("umur", 25)).
//Where(dbox.Gte("umur", 25)).
Where(dbox.Lt("umur", 25)).
//Where(dbox.Lte("umur", 25)).
//Where(dbox.In("name", "vidal", "bourne")).
//Where(dbox.In("umur", "25", "30")).
//Where(dbox.Nin("umur", "25", "30")).
//Where(dbox.In("tanggal", "2016-01-12 14:35:54", "2016-01-12 14:36:15")).
//Where(dbox.And(dbox.Gt("umur", 25), dbox.Eq("name", "Roy"))).
Cursor(nil)
if e != nil {
t.Errorf("Cursor pre error: %s \n", e.Error())
return
}
if csr == nil {
t.Errorf("Cursor not initialized")
return
}
defer csr.Close()
// results := make([]map[string]interface{}, 0)
results := make([]User, 0)
err := csr.Fetch(&results, 0, false)
if err != nil {
t.Errorf("Unable to fetch: %s \n", err.Error())
} else {
fmt.Println("======================")
fmt.Println("Select with FILTER")
fmt.Println("======================")
fmt.Printf("Fetch N OK. Result:\n")
// for _, val := range results {
// fmt.Printf("%v \n", toolkit.JsonString(val))
// }
for i := 0; i < len(results); i++ {
fmt.Printf("%v \n", toolkit.JsonString(results[i]))
}
}
// results := new(Player)
// ms := toolkit.M{}
// errSerde := toolkit.FromBytes(toolkit.ToBytes(results, ""), "", &ms)
// if errSerde != nil {
// fmt.Printf("Serde using bytes fail: %s\n", errSerde.Error())
// return
// }
// err := csr.Fetch(&ms, 0, false)
// fmt.Println("Nilai MS : ", ms)
// if err != nil {
// t.Errorf("Unable to fetch: %s \n", err.Error())
// } else {
// fmt.Println("======================")
// fmt.Println("Select with FILTER")
// fmt.Println("======================")
// errSerde = toolkit.FromBytes(toolkit.ToBytes(ms, ""), "", results)
// fmt.Printf("Object value after serde and be changed on M: \n%s \n\n", toolkit.JsonString(results))
// }
}
// generateMORFlatCalculationSummary
func (m *GenMOR) generateMORFlatCalculationSummary() error {
ctx := m.BaseController.Ctx
c := ctx.Connection
var (
query []*dbox.Filter
)
tk.Println("Generating MOR Flat Calculation Summary..")
Years := []int{2013, 2014, 2015}
query = []*dbox.Filter{}
query = append(query, dbox.Gte("TopElement", "Maintenance"))
MORSummaryList := []MORSummary{}
csr, e := c.NewQuery().Select("Element").From(new(MORSummary).TableName()).Where(query...).Group("Element").Cursor(nil)
if e != nil {
return e
}
e = csr.Fetch(&MORSummaryList, 0, false)
if e != nil {
return e
}
csr.Close()
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)))
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(&MaintenanceCostList).Group(func(x interface{}) interface{} {
return x.(MaintenanceCost).Plant
}, nil).Exec().Result.Data().([]crowd.KV)
for _, p := range Plants {
plant := p.Key.(string)
EqType := crowd.From(&MaintenanceCostList).Where(func(x interface{}) interface{} {
return x.(MaintenanceCost).Plant == plant
}).Group(func(x interface{}) interface{} {
return x.(MaintenanceCost).EquipmentType
}, nil).Exec().Result.Data().([]crowd.KV)
for _, eqt := range EqType {
eq := eqt.Key.(string)
ActType := crowd.From(&MaintenanceCostList).Where(func(x interface{}) interface{} {
o := x.(MaintenanceCost)
return o.Plant == plant && o.EquipmentType == eq
}).Group(func(x interface{}) interface{} {
return x.(MaintenanceCost).MaintenanceActivityType
}, nil).Exec().Result.Data().([]crowd.KV)
for _, a := range ActType {
act := a.Key.(string)
OrderType := crowd.From(&MaintenanceCostList).Where(func(x interface{}) interface{} {
o := x.(MaintenanceCost)
return o.Plant == plant && o.EquipmentType == eq && o.MaintenanceActivityType == act
}).Group(func(x interface{}) interface{} {
return x.(MaintenanceCost).OrderType
}, nil).Exec().Result.Data().([]crowd.KV)
for _, o := range OrderType {
order := o.Key.(string)
for _, mor := range MORSummaryList {
d := new(MORCalculationFlatSummary)
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(&MaintenanceCostList).Where(func(x interface{}) interface{} {
o := x.(MaintenanceCost)
return o.Plant == plant && o.EquipmentType == eq
}).Exec().Result.Data().([]MaintenanceCost)
if len(EqTypeDesc) > 0 {
d.EquipmentTypeDescription = EqTypeDesc[0].EquipmentTypeDesc
}
}
d.ActivityType = act
d.Plant = PlantNormalization(plant)
d.Element = mor.Element
d.MOCount = len(OrderType)
switch d.Element {
case "Internal Labor":
d.Value = 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).InternalLaborActual
}).Exec().Result.Sum
break
case "Internal Material":
d.Value = 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).InternalMaterialActual
}).Exec().Result.Sum
break
case "Direct Material":
d.Value = 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).DirectMaterialActual
}).Exec().Result.Sum
break
case "External Service":
d.Value = 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).ExternalServiceActual
}).Exec().Result.Sum
break
default:
break
}
_, e := ctx.InsertOut(d)
if e != nil {
tk.Println(e)
break
}
}
}
}
}
}
}
tk.Println(len(MORSummaryList))
return nil
}