light-apiengine/models/Filter.go

// Copyright (c) 2015 qianqiusoft.com
// Licensed to You under the GNU Affero GPL v3
// See the LICENSE file at git.qianqiusoft.com/qianqiusoft/light-vocation/LICENSE
// http://www.gnu.org/licenses/why-affero-gpl.en.html

package models

import (
	"encoding/json"
	"errors"
	"fmt"
	"strings"
)

const (
	_RULE_DATA_TYPE_FIELD_NAME  = "field_name"
	_RULE_DATA_TYPE_USER_VALUE  = "user_value"
	_RULE_DATA_TYPE_FIELD_VALUE = "field_value"
)

type Filter struct {
	GroupOp string         `json:"groupOp"`
	Rules   []*FilterField `json:"rules"`
	Groups  []*Filter      `json:"groups"`
}

type FilterField struct {
	Field string `json:"field"`
	Op    string `json:"op"`
	Data  string `json:"data"`
}

/**
 * @brief new filter by string
 * @param filtersStr json data of filter
 *
 * @return point of Filter
 * @return error
 */
func NewFilter(filtersStr string) (*Filter, error) {
	filters := &Filter{}
	err := json.Unmarshal([]byte(filtersStr), filters)
	if err != nil {
		return nil, err
	}
	return filters, nil
}

/**
 * @brief parse the filter
 *
 * @return select querybuilder
 * @return count select querybuilder
 * @return error
 */
func (this *Filter) Parse(tableName string) (string, []interface{}, error) {

	whereStatement := ""
	groupWhereStatement := ""
	datas := make([]interface{}, 0)

	isAnd := true
	if strings.ToLower(strings.TrimSpace(this.GroupOp)) == "or" {
		isAnd = false
	}

	if len(this.Rules) > 0 {
		whereStr, ruleDatas := this.ParseRule(tableName, this.Rules[0])
		if whereStr != "" {
			whereStatement = whereStr
			for _, ruleData := range ruleDatas {
				datas = append(datas, ruleData)
			}
		} else {
			return whereStatement, datas, errors.New(fmt.Sprintf("the 0 rule is invalid"))
		}
		for i := 1; i < len(this.Rules); i++ {
			whereStr, ruleDatas = this.ParseRule(tableName, this.Rules[i])
			if whereStr != "" {
				if isAnd {
					whereStatement += " AND " + whereStr
				} else {
					whereStatement += " OR " + whereStr
				}
				for _, ruleData := range ruleDatas {
					datas = append(datas, ruleData)
				}
			} else {
				return whereStatement, datas, errors.New(fmt.Sprintf("the %d rule is invalid", i))
			}
		}
	}

	if len(this.Groups) > 0 {

		whereStr, groupDatas, err := this.Groups[0].Parse(tableName)
		if err != nil {
			return whereStatement, datas, err
		}
		if whereStr != "" {
			groupWhereStatement = whereStr
			for _, groupData := range groupDatas {
				datas = append(datas, groupData)
			}
		} else {
			return whereStatement, datas, errors.New("the 0 group is invalid")
		}
		for i := 1; i < len(this.Groups); i++ {
			whereStr, groupDatas, err := this.Groups[i].Parse(tableName)
			if err != nil {
				return whereStatement, datas, err
			}
			if whereStr != "" {
				if isAnd {
					groupWhereStatement += " AND " + whereStr
				} else {
					groupWhereStatement += " OR " + whereStr
				}
				for _, groupData := range groupDatas {
					datas = append(datas, groupData)
				}
			} else {
				return whereStatement, datas, errors.New(fmt.Sprintf("the %d group is invalid", i))
			}
		}
		if groupWhereStatement != "" {
			groupWhereStatement = "(" + groupWhereStatement + ")"
		}
	}
	if whereStatement == "" {
		whereStatement = groupWhereStatement
	} else {
		whereStatement = "(" + whereStatement + ")"
		if groupWhereStatement != "" {
			if isAnd {
				whereStatement += " AND " + groupWhereStatement
			} else {
				whereStatement += " OR " + groupWhereStatement
			}
		}
	}

	return whereStatement, datas, nil
}

func (this *Filter) ParseRule(tableName string, field *FilterField) (string, []interface{}) {
	switch ruleDataType(field.Field) {
	case _RULE_DATA_TYPE_FIELD_NAME:
		return ParsePureRule(tableName, field)
	case _RULE_DATA_TYPE_FIELD_VALUE:
		//		if strings.HasSuffix(field.Field, ".org") {
		//			a, b, _ := parseFieldOrgRule(field, tableName, nil)
		//			return a, b
		//			return "", make([]interface{}, 0)
		//		} else {
		//			return "", make([]interface{}, 0)
		//		}
		return "", make([]interface{}, 0)
	default:
		return "", make([]interface{}, 0)
	}
}

func ParsePureRule(tableName string, field *FilterField) (string, []interface{}) {
	datas := make([]interface{}, 0)
	if field == nil {
		return "", datas
	}
	fData := FormatData(field.Op, field.Data)
	switch fData.(type) {
	case string:
		{
			if len(fData.(string)) >= 0 {
				datas = append(datas, fData)
			}
		}
	case []string:
		{
			fDataArr := fData.([]string)
			for _, arr := range fDataArr {
				if len(arr) > 0 {
					datas = append(datas, arr)
				}
			}
		}
	}

	whereStr := FormatOp(tableName, field.Op, field.Field, field.Data)

	if len(whereStr) > 0 {
		return whereStr, datas
	} else {
		return "", datas
	}
}

func FormatOp(table, op, field, data string) string {
	if strings.Contains(field, ".") {
		// the field is "talbe.field"
		switch strings.ToLower(op) {
		case "eq":
			{
				return fmt.Sprintf(" %s = ? ", field)
			}
		case "ne":
			{
				return fmt.Sprintf(" %s <> ? ", field)
			}
		case "lt":
			{
				return fmt.Sprintf(" %s < ? ", field)
			}
		case "le":
			{
				return fmt.Sprintf(" %s <= ? ", field)
			}
		case "gt":
			{
				return fmt.Sprintf(" %s > ? ", field)
			}
		case "ge":
			{
				return fmt.Sprintf(" %s >= ? ", field)
			}
		case "bw":
			{
				return fmt.Sprintf(" %s LIKE ? ", field)
			}
		case "bn":
			{
				return fmt.Sprintf(" %s NOT LIKE ? ", field)
			}
		case "ew":
			{
				return fmt.Sprintf(" %s LIKE ? ", field)
			}
		case "en":
			{
				return fmt.Sprintf(" %s NOT LIKE ? ", field)
			}
		case "cn":
			{
				return fmt.Sprintf(" %s LIKE ? ", field)
			}
		case "nc":
			{
				return fmt.Sprintf(" %s NOT LIKE ? ", field)
			}
		case "nu":
			{
				return fmt.Sprintf(" %s IS NULL ", field)
			}
		case "nn":
			{
				return fmt.Sprintf(" %s IS NOT NULL ", field)
			}
		case "date-cn":
			{
				return fmt.Sprintf(" %s >= ? and %s <= ? ", field, field)
			}
		case "in":
			{
				dataArr := strings.Split(data, ",")
				params := ""
				for _, arr := range dataArr {
					if len(arr) > 0 {
						params += "?,"
					}
				}
				params = strings.TrimRight(params, ",")
				if len(params) > 0 {
					return fmt.Sprintf(" %s IN (%s)", field, params)
				}
			}
		case "ni":
			{
				dataArr := strings.Split(data, ",")
				params := ""
				for _, arr := range dataArr {
					if len(arr) > 0 {
						params += "?,"
					}
				}
				params = strings.TrimRight(params, ",")
				if len(params) > 0 {
					return fmt.Sprintf(" %s NOT IN (%s)", field, params)
				}
			}
		}
	} else {
		if table == "" {
			switch strings.ToLower(op) {
			case "eq":
				{
					return fmt.Sprintf(" `%s` = ? ", field)
				}
			case "ne":
				{
					return fmt.Sprintf(" `%s` <> ? ", field)
				}
			case "lt":
				{
					return fmt.Sprintf(" `%s` < ? ", field)
				}
			case "le":
				{
					return fmt.Sprintf(" `%s` <= ? ", field)
				}
			case "gt":
				{
					return fmt.Sprintf(" `%s` > ? ", field)
				}
			case "ge":
				{
					return fmt.Sprintf(" `%s` >= ? ", field)
				}
			case "bw":
				{
					return fmt.Sprintf(" `%s` LIKE ? ", field)
				}
			case "bn":
				{
					return fmt.Sprintf(" `%s` NOT LIKE ? ", field)
				}
			case "ew":
				{
					return fmt.Sprintf(" `%s` LIKE ? ", field)
				}
			case "en":
				{
					return fmt.Sprintf(" `%s` NOT LIKE ? ", field)
				}
			case "cn":
				{
					return fmt.Sprintf(" `%s` LIKE ? ", field)
				}
			case "nc":
				{
					return fmt.Sprintf(" `%s` NOT LIKE ? ", field)
				}
			case "nu":
				{
					return fmt.Sprintf(" `%s` IS NULL ", field)
				}
			case "nn":
				{
					return fmt.Sprintf(" `%s` IS NOT NULL ", field)
				}
			case "date-cn":
				{
					return fmt.Sprintf(" %s >= ? and %s <= ? ", field, field)
				}
			case "in":
				{
					dataArr := strings.Split(data, ",")
					params := ""
					for _, arr := range dataArr {
						if len(arr) > 0 {
							params += "?,"
						}
					}
					params = strings.TrimRight(params, ",")
					if len(params) > 0 {
						return fmt.Sprintf(" `%s` IN (%s)", field, params)
					}
				}
			case "ni":
				{
					dataArr := strings.Split(data, ",")
					params := ""
					for _, arr := range dataArr {
						if len(arr) > 0 {
							params += "?,"
						}
					}
					params = strings.TrimRight(params, ",")
					if len(params) > 0 {
						return fmt.Sprintf(" `%s` NOT IN (%s)", field, params)
					}
				}
			}
		} else {
			switch strings.ToLower(op) {
			case "eq":
				{
					return fmt.Sprintf(" %s.%s = ? ", table, field)
				}
			case "ne":
				{
					return fmt.Sprintf(" %s.%s <> ? ", table, field)
				}
			case "lt":
				{
					return fmt.Sprintf(" %s.%s < ? ", table, field)
				}
			case "le":
				{
					return fmt.Sprintf(" %s.%s <= ? ", table, field)
				}
			case "gt":
				{
					return fmt.Sprintf(" %s.%s > ? ", table, field)
				}
			case "ge":
				{
					return fmt.Sprintf(" %s.%s >= ? ", table, field)
				}
			case "bw":
				{
					return fmt.Sprintf(" %s.%s LIKE ? ", table, field)
				}
			case "bn":
				{
					return fmt.Sprintf(" %s.%s NOT LIKE ? ", table, field)
				}
			case "ew":
				{
					return fmt.Sprintf(" %s.%s LIKE ? ", table, field)
				}
			case "en":
				{
					return fmt.Sprintf(" %s.%s NOT LIKE ? ", table, field)
				}
			case "cn":
				{
					return fmt.Sprintf(" %s.%s LIKE ? ", table, field)
				}
			case "nc":
				{
					return fmt.Sprintf(" %s.%s NOT LIKE ? ", table, field)
				}
			case "nu":
				{
					return fmt.Sprintf(" %s.%s IS NULL ", table, field)
				}
			case "nn":
				{
					return fmt.Sprintf(" %s.%s IS NOT NULL ", table, field)
				}
			case "date-cn":
				{
					return fmt.Sprintf(" %s.%s >= ? and %s.%s <= ? ", table, field, table, field)
				}
			case "in":
				{
					dataArr := strings.Split(data, ",")
					params := ""
					for _, arr := range dataArr {
						if len(arr) > 0 {
							params += "?,"
						}
					}
					params = strings.TrimRight(params, ",")
					if len(params) > 0 {
						return fmt.Sprintf(" %s.%s IN (%s)", table, field, params)
					}
				}
			case "ni":
				{
					dataArr := strings.Split(data, ",")
					params := ""
					for _, arr := range dataArr {
						if len(arr) > 0 {
							params += "?,"
						}
					}
					params = strings.TrimRight(params, ",")
					if len(params) > 0 {
						return fmt.Sprintf(" %s.%s NOT IN (%s)", table, field, params)
					}
				}
			}
		}
	}
	return ""
}

func FormatData(op, data string) interface{} {
	switch strings.ToLower(op) {
	case "eq", "ne", "lt", "le", "gt", "ge":
		{
			return data
		}
	case "bw":
		{
			return fmt.Sprintf("%s%%", data)
		}
	case "bn":
		{
			return fmt.Sprintf("%s%%", data)
		}
	case "ew":
		{
			return fmt.Sprintf("%%%s", data)
		}
	case "en":
		{
			return fmt.Sprintf("%%%s", data)
		}
	case "cn":
		{
			return fmt.Sprintf("%%%s%%", data)
		}
	case "nc":
		{
			return fmt.Sprintf("%%%s%%", data)
		}
	case "in":
		{
			return strings.Split(data, ",")
		}
	case "ni":
		{
			return strings.Split(data, ",")
		}
	case "date-cn":
		{
			data = strings.TrimSpace(data)
			if data == "" {
				return []string{"", ""}
			} else {
				data = data[0:10]
				return []string{data + " 00:00:00", data + " 23:59:59"}
			}
		}
	}
	return ""
}

func formatData(op, data string) interface{} {
	return FormatData(op, data)
}

func formatOp(table, op, field, data string) string {
	return FormatOp(table, op, field, data)
}

// where the value begin with $, return user_value
// where the value begin with #, return field_value
// where the value begin with letter, return value
func ruleDataType(data string) string {
	data = strings.TrimSpace(data)
	if strings.HasPrefix(data, "$") {
		return _RULE_DATA_TYPE_USER_VALUE
	} else if strings.HasPrefix(data, "#") {
		return _RULE_DATA_TYPE_FIELD_VALUE
	} else {
		return _RULE_DATA_TYPE_FIELD_NAME
	}
}