smart-government-management/model/where.go

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

package model

import (
	"fmt"
	"strings"
)

type NullType byte

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

// sql build where
func whereBuild(where map[string]interface{}) (whereSQL string, vals []interface{}, err error) {
	for k, v := range where {
		ks := strings.Split(k, " ")
		if len(ks) > 2 {
			return "", nil, fmt.Errorf("Error in query condition: %s. ", 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)
			}
			break
		}
	}
	return
}

func whereBuildOr(where map[string]interface{}) (whereSQL string, vals []interface{}, err error) {
	for k, v := range where {
		ks := strings.Split(k, " ")
		if len(ks) > 2 {
			return "", nil, fmt.Errorf("Error in query condition: %s. ", 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)
			}
			break
		}
	}
	return
}

func whereBuildAndOr(where map[string]interface{}, or map[string]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)
		fmt.Printf("xxxx:yyyy:%s,%v\n", orSql, orVars)
		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
}