property-device/model/where.go

// Copyright 2019 github.com. All rights reserved.
// Use of this source code is governed by github.com.

package model

import (
	"errors"
	"fmt"
	"strings"
)

type NullType byte

const (
	_ NullType = iota
	// IsNull the same as `is null`
	IsNull
	// IsNotNull the same as `is not null`
	IsNotNull
)

func WhereAdd(where [][2]interface{}, key string, value interface{}) [][2]interface{} {
	where = append(where, [2]interface{}{key, value})
	return where
}

// sql build where
func whereBuild(where [][2]interface{}) (whereSQL string, vals []interface{}, err error) {
	for _, keyvalue := range where {
		k, ok := keyvalue[0].(string)
		if !ok {
			return "", nil, errors.New("where参数错误")
		}
		v := keyvalue[1]
		ks := strings.Split(k, " ")
		src := k
		if whereSQL != "" {
			whereSQL += " AND "
		}
		strings.Join(ks, ",")
		switch len(ks) {
		case 1:
			switch v := v.(type) {
			case NullType:
				if v == IsNotNull {
					whereSQL += fmt.Sprint(k, " IS NOT NULL")
				} else {
					whereSQL += fmt.Sprint(k, " IS NULL")
				}
			default:
				whereSQL += fmt.Sprint(k, "=?")
				vals = append(vals, v)
			}
			break
		case 2:
			k = ks[0]
			switch ks[1] {
			case "=":
				whereSQL += fmt.Sprint(k, " = ?")
				vals = append(vals, v)
				break
			case ">":
				whereSQL += fmt.Sprint(k, " > ?")
				vals = append(vals, v)
				break
			case ">=":
				whereSQL += fmt.Sprint(k, " >= ?")
				vals = append(vals, v)
				break
			case "<":
				whereSQL += fmt.Sprint(k, " < ?")
				vals = append(vals, v)
				break
			case "<=":
				whereSQL += fmt.Sprint(k, " <= ?")
				vals = append(vals, v)
				break
			case "!=":
				whereSQL += fmt.Sprint(k, " != ?")
				vals = append(vals, v)
				break
			case "<>":
				whereSQL += fmt.Sprint(k, " != ?")
				vals = append(vals, v)
				break
			case "in":
				whereSQL += fmt.Sprint(k, " in (?)")
				vals = append(vals, v)
				break
			case "like":
				whereSQL += fmt.Sprint(k, " like ?")
				vals = append(vals, v)
			}
		default:
			whereSQL += fmt.Sprint(src)
			if fmt.Sprintf("%v", v) != "" {
				vals = append(vals, v)
			}
			break
		}
	}
	return
}

func whereBuildOr(where [][2]interface{}) (whereSQL string, vals []interface{}, err error) {
	for _, keyvalue := range where {
		k, ok := keyvalue[0].(string)
		if !ok {
			return "", nil, errors.New("where参数错误")
		}
		v := keyvalue[1]
		ks := strings.Split(k, " ")
		src := k

		if whereSQL != "" {
			whereSQL += " Or "
		}
		strings.Join(ks, ",")
		switch len(ks) {
		case 1:
			switch v := v.(type) {
			case NullType:
				if v == IsNotNull {
					whereSQL += fmt.Sprint(k, " IS NOT NULL")
				} else {
					whereSQL += fmt.Sprint(k, " IS NULL")
				}
			default:
				whereSQL += fmt.Sprint(k, "=?")
				vals = append(vals, v)
			}
			break
		case 2:
			k = ks[0]
			switch ks[1] {
			case "=":
				whereSQL += fmt.Sprint(k, " = ?")
				vals = append(vals, v)
				break
			case ">":
				whereSQL += fmt.Sprint(k, " > ?")
				vals = append(vals, v)
				break
			case ">=":
				whereSQL += fmt.Sprint(k, " >= ?")
				vals = append(vals, v)
				break
			case "<":
				whereSQL += fmt.Sprint(k, " < ?")
				vals = append(vals, v)
				break
			case "<=":
				whereSQL += fmt.Sprint(k, " <= ?")
				vals = append(vals, v)
				break
			case "!=":
				whereSQL += fmt.Sprint(k, " != ?")
				vals = append(vals, v)
				break
			case "<>":
				whereSQL += fmt.Sprint(k, " != ?")
				vals = append(vals, v)
				break
			case "in":
				whereSQL += fmt.Sprint(k, " in (?)")
				vals = append(vals, v)
				break
			case "like":
				whereSQL += fmt.Sprint(k, " like ?")
				vals = append(vals, v)
			}
		default:
			whereSQL += fmt.Sprint(src)
			if fmt.Sprintf("%v", v) != "" {
				vals = append(vals, v)
			}
			break
		}
	}
	return
}

func whereBuildAndOr(where [][2]interface{}, or [][2]interface{}) (whereSQL string, vals []interface{}, err error) {
	if len(where) > 0 {
		andSql, andVars, err := whereBuild(where)
		if err != nil {
			return "", nil, err
		}
		whereSQL = andSql
		vals = andVars
	}
	if len(or) > 0 {
		orSql, orVars, err := whereBuildOr(or)
		if err != nil {
			return "", nil, err
		}
		if whereSQL != "" {
			whereSQL = fmt.Sprintf("%s and (%s)", whereSQL, orSql)
		} else {
			whereSQL = fmt.Sprintf("%s", orSql)
		}

		vals = append(vals, orVars...)
	}
	return
}