xdmy/apiserver/routers/restful/sql_utils.go

package restful

import (
	"database/sql"
	"fmt"
	"log"
	"math"
	"reflect"
	"strconv"
	"strings"
	"time"
	"unsafe"

	"github.com/pkg/errors"

	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
	"github.com/patrickmn/go-cache"
	"github.com/tealeg/xlsx"
	"github.com/xormplus/xorm/schemas"
	"kpt.xdmy/pkg/setting"
)

var (
	Dbs          *sqlx.DB
	Apisql_cache *cache.Cache
)

// SQLInit 初始化数据库操作句柄，这里要提供:
// driverName string: 数据库类型，例如mysql、sqlite等，参考github.com/go-sql-driver/mysql官方介绍
// dataSourceName string: 数据库地址，参考github.com/go-sql-driver/mysql官方介绍
// MaxOpenConns int: 最大缓存连接数，这个数值包含了MaxIdleConns
// MaxIdleConns int：预备的最大空闲连接数
func SQLInit(driverName, dataSourceName string, maxOpenConns, maxIdleConns int, showlog bool) error {

	if Dbs == nil {
		var err error
		if Dbs, err = sqlx.Open(driverName, dataSourceName); err != nil {
			return err
		}
		Dbs.SetMaxOpenConns(maxOpenConns)
		Dbs.SetMaxIdleConns(maxIdleConns)
		Dbs.SetConnMaxLifetime(300 * time.Second)
	}
	if Apisql_cache == nil {
		Apisql_cache = cache.New(600*time.Second, 10*time.Second)
	}
	// SetCacheSql()
	return nil
}

func SetCacheSql() {
	rows, err := Dbs.Queryx("SELECT sqlname, CONCAT(sqlstr,'|',IFNULL(params,'')) AS sqlstr FROM apisql WHERE ENABLE>0 ")
	if err != nil {
		return
	}
	valuesMap, err := Rows2Strings(rows)

	if err == nil && len(valuesMap) > 0 {
		for _, v := range valuesMap {
			Apisql_cache.Set(v["sqlname"], v["sqlstr"], cache.DefaultExpiration)
		}
	}
}
func sqlCheckParam(param string) error {

	if strings.Contains(param, ";") {
		return errors.New("can not have ;")
	}

	return nil
}

func GetSqlByName(name string, tx *sqlx.Tx) (string, string) {
	sqls := ""
	param := ""
	if sql, ok := Apisql_cache.Get(name); ok {
		tempstrb := strings.IndexAny(sql.(string), "|")
		sqls = sql.(string)[0:tempstrb]
		param = sql.(string)[tempstrb+1:]
	} else {
		valuesMap, err := MapStr("SELECT sqlstr, params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", name)
		if err != nil {
			return err.Error(), "err"
		}
		if len(valuesMap) > 0 {
			sqls = valuesMap["sqlstr"]
			param = valuesMap["params"]
			// Apisql_cache.Set(name, (sqls + "|" + param), cache.DefaultExpiration)
		}
	}
	if setting.DatabaseSetting.ShowGetSqllog {

		fmt.Println("==============")
		fmt.Println("apisql:" + sqls)
		fmt.Println("--------------")
		fmt.Println("param:" + param)
		fmt.Println("==============")
	}
	return sqls, param
}
func GetSqlByNameDB(name string) (string, string) {

	var sqls, param string
	if sql, ok := Apisql_cache.Get(name); ok {
		tempstrb := strings.IndexAny(sql.(string), "|")
		sqls = sql.(string)[0:tempstrb]
		param = sql.(string)[tempstrb+1:]
	} else {
		rows, err := Dbs.Queryx("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", name)
		if err != nil {
			return err.Error(), "err"
		}
		valuesMap, err := Rows2Strings(rows)
		if err == nil && len(valuesMap) > 0 {
			sqls = valuesMap[0]["sqlstr"]
			param = valuesMap[0]["params"]
			// Apisql_cache.Set(name, (sqls + "|" + param), cache.DefaultExpiration)
		}
	}

	if setting.DatabaseSetting.ShowGetSqllog {

		fmt.Println("==============")
		fmt.Println("apisql:" + sqls)
		fmt.Println("--------------")
		fmt.Println("param:" + param)
		fmt.Println("==============")
	}
	return sqls, param
}

func GetSqlByNameDBT(name string, tx *sqlx.Tx) (string, string) {
	var sqls, param string
	if sql, ok := Apisql_cache.Get(name); ok {
		tempstrb := strings.IndexAny(sql.(string), "|")
		sqls = sql.(string)[0:tempstrb]
		param = sql.(string)[tempstrb+1:]
	} else {

		rows, err := Dbs.Queryx("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", name)
		if err != nil {
			log.Default().Printf("GetSqlByNameDBT :%v", err)
			return "", ""
		}
		if rows.Next() {
			rows.Scan(&sqls, &param)
		}
		if sqls != "" {
			// Apisql_cache.Set(name, (sqls + "|" + param), cache.DefaultExpiration)
		}
	}
	if setting.DatabaseSetting.ShowGetSqllog {

		fmt.Println("==============")
		fmt.Println("apisql:" + sqls)
		fmt.Println("--------------")
		fmt.Println("param:" + param)
		fmt.Println("==============")
	}
	return sqls, param
}

// sqlGetValues 根据结构体中指向实际数据的指针获取出数据，并存储到另一张表中返回
func sqlGetValues(pvs []interface{}, columnsType []*sql.ColumnType, columnsLen int) map[string]interface{} {

	result := make(map[string]interface{}, columnsLen)

	for a := 0; a < columnsLen; a++ {
		switch s := pvs[a].(type) {
		case *int8:
			result[columnsType[a].Name()] = *s
		case *int16:
			result[columnsType[a].Name()] = *s
		case *int32:
			result[columnsType[a].Name()] = *s
		case *int64:
			result[columnsType[a].Name()] = *s
		case *float32:
			result[columnsType[a].Name()] = *s
		case *float64:
			result[columnsType[a].Name()] = *s
		case *string:
			result[columnsType[a].Name()] = *s
		case *sql.NullInt64:
			result[columnsType[a].Name()] = *s
		case *sql.NullString:
			result[columnsType[a].Name()] = *s
		}
	}
	return result
}

// 这里返回的是原始数组的基础上加上了字段名标识
func sqlQuery(columnsType []*sql.ColumnType, columnsLen int, queryData [][]interface{}, queryCount int) ([]map[string]interface{}, error) {

	jsondata := make([]map[string]interface{}, queryCount)
	for k1, v1 := range queryData {
		if k1 >= queryCount {
			break
		}

		jsondata[k1] = sqlGetValues(v1, columnsType, columnsLen)
	}

	return jsondata, nil
}

func sqlQueryByTinyIntMap(columnName string, columnsType []*sql.ColumnType, columnsLen int, queryData [][]interface{}, queryCount int) (map[int8]map[string]interface{}, error) {

	jsondata := make(map[int8]map[string]interface{}, queryCount)
	for k1, v1 := range queryData {
		if k1 >= queryCount {
			break
		}

		for a := 0; a < columnsLen; a++ {
			if columnsType[a].Name() == columnName {
				if value, ok := v1[a].(*int8); ok {
					jsondata[*value] = sqlGetValues(v1, columnsType, columnsLen)
				}
				break
			}
		}
	}

	return jsondata, nil
}

func sqlQueryBySmallIntMap(columnName string, columnsType []*sql.ColumnType, columnsLen int, queryData [][]interface{}, queryCount int) (map[int16]map[string]interface{}, error) {

	jsondata := make(map[int16]map[string]interface{}, queryCount)
	for k1, v1 := range queryData {
		if k1 >= queryCount {
			break
		}

		for a := 0; a < columnsLen; a++ {
			if columnsType[a].Name() == columnName {
				if value, ok := v1[a].(*int16); ok {
					jsondata[*value] = sqlGetValues(v1, columnsType, columnsLen)
				}
				break
			}
		}
	}

	return jsondata, nil
}

func sqlQueryByIntMap(columnName string, columnsType []*sql.ColumnType, columnsLen int, queryData [][]interface{}, queryCount int) (map[int32]map[string]interface{}, error) {

	jsondata := make(map[int32]map[string]interface{}, queryCount)
	for k1, v1 := range queryData {
		if k1 >= queryCount {
			break
		}

		for a := 0; a < columnsLen; a++ {
			if columnsType[a].Name() == columnName {
				if value, ok := v1[a].(*int32); ok {
					jsondata[*value] = sqlGetValues(v1, columnsType, columnsLen)
				}
				break
			}
		}
	}

	return jsondata, nil
}

func sqlQueryByBigIntMap(columnName string, columnsType []*sql.ColumnType, columnsLen int, queryData [][]interface{}, queryCount int) (map[int64]map[string]interface{}, error) {

	jsondata := make(map[int64]map[string]interface{}, queryCount)
	for k1, v1 := range queryData {
		if k1 >= queryCount {
			break
		}

		for a := 0; a < columnsLen; a++ {
			if columnsType[a].Name() == columnName {
				if value, ok := v1[a].(*int64); ok {
					jsondata[*value] = sqlGetValues(v1, columnsType, columnsLen)
				}
				break
			}
		}
	}

	return jsondata, nil
}

func sqlQueryByFloatIntMap(columnName string, columnsType []*sql.ColumnType, columnsLen int, queryData [][]interface{}, queryCount int) (map[float32]map[string]interface{}, error) {

	jsondata := make(map[float32]map[string]interface{}, queryCount)
	for k1, v1 := range queryData {
		if k1 >= queryCount {
			break
		}

		for a := 0; a < columnsLen; a++ {
			if columnsType[a].Name() == columnName {
				if value, ok := v1[a].(*float32); ok {
					jsondata[*value] = sqlGetValues(v1, columnsType, columnsLen)
				}
				break
			}
		}
	}

	return jsondata, nil
}

func sqlQueryByDoubleMap(columnName string, columnsType []*sql.ColumnType, columnsLen int, queryData [][]interface{}, queryCount int) (map[float64]map[string]interface{}, error) {

	jsondata := make(map[float64]map[string]interface{}, queryCount)
	for k1, v1 := range queryData {
		if k1 >= queryCount {
			break
		}

		for a := 0; a < columnsLen; a++ {
			if columnsType[a].Name() == columnName {
				if value, ok := v1[a].(*float64); ok {
					jsondata[*value] = sqlGetValues(v1, columnsType, columnsLen)
				}
				break
			}
		}
	}

	return jsondata, nil
}

func sqlQueryByStringMap(columnName string, columnsType []*sql.ColumnType, columnsLen int, queryData [][]interface{}, queryCount int) (map[string]map[string]interface{}, error) {

	jsondata := make(map[string]map[string]interface{}, queryCount)
	for k1, v1 := range queryData {
		if k1 >= queryCount {
			break
		}

		for a := 0; a < columnsLen; a++ {
			if columnsType[a].Name() == columnName {
				if value, ok := v1[a].(*string); ok {
					jsondata[*value] = sqlGetValues(v1, columnsType, columnsLen)
				}
				break
			}
		}
	}

	return jsondata, nil
}

func sqlGetColumnType(columnsType []*sql.ColumnType, columnsLen int, valueName string) string {

	for a := 0; a < columnsLen; a++ {
		if columnsType[a].Name() == valueName {
			return columnsType[a].DatabaseTypeName()
		}
	}

	return ""
}

func round(num float64) int {
	return int(num + math.Copysign(0.5, num))
}

func toFixed(num float64, precision int) float64 {
	output := math.Pow(10, float64(precision))
	return float64(round(num*output)) / output
}

// QueryByMap 将查询到的数据，按照指定字段的值做为索引构建map并返回
// columnName string: 作为索引的字段名称
// sql string: 查询需要获取哪些字段的值，就是select后面跟着的部分，一般用"*"
// where string: 过滤条件，就是where后面跟着的部分
// order string: 排序条件，就是order by后面跟着的部分。默认是ASC排序，除非"-"开头则DESC排序
// offset string: limit后面逗号相隔的两个数值，前者就是offset，后者就是count
// count string: limit后面逗号相隔的两个数值，前者就是offset，后者就是count
func QueryByMap(sqlstr string, offset, count int, params []interface{}) (interface{}, error) {
	if err := sqlCheckParam(sqlstr); err != nil {
		return 0, err
	}
	recordcount := int64(0)

	if count > 1 {
		sqlstr = fmt.Sprintf("SELECT COUNT(*) as count FROM (%s) a", sqlstr)
		err := BasicList(sqlstr, params, &recordcount)
		if err != nil {
			fmt.Println("s1", err)
			return nil, err
		}
	}
	queryData := make([]map[string]interface{}, 1)
	var err error
	if count == 0 {
		rows, err := Dbs.Queryx(sqlstr, params...)
		if err != nil {
			return err.Error(), err
		}
		queryData, err = rows2mapObjects(rows)
	} else {
		if count > 0 && offset > 0 {
			offset = offset * count
		}
		rows, err := Dbs.Queryx(fmt.Sprintf(sqlstr+" limit %d , %d", offset, count), params...)
		if err != nil {
			return err.Error(), err
		}
		queryData, err = rows2mapObjects(rows)
	}

	if err != nil {
		return nil, err
	}
	if recordcount == 0 {
		recordcount = int64(len(queryData))
	}

	data := make(map[string]interface{})
	data["list"] = queryData
	data["total"] = recordcount
	data["code"] = 0
	data["message"] = "ok"
	if count > 0 {
		data["pages"] = math.Ceil(float64(recordcount) / float64(count))
		data["pageNum"] = math.Ceil(float64(offset+1) / float64(count))
	}
	data["pageSize"] = count
	return data, nil
}

// func QueryByMapT(sqlstr string, offset, count int, params []interface{}, tx *sqlx.Tx) (interface{}, error) {
func QueryByMapT(sqlstr string, offset, count int, params []interface{}) (interface{}, error) {
	if err := sqlCheckParam(sqlstr); err != nil {
		return 0, err
	}
	recordcount := int64(0)

	if count > 1 {
		sql := "SELECT COUNT(*) as count FROM (" + sqlstr + ") a"
		err := BasicList(sql, params, &recordcount)
		if err != nil {
			fmt.Println("s1", err)
			return nil, err
		}
	}
	queryData := make([]map[string]interface{}, 1)
	// queryData := make([]map[string]string, 1)
	var err error
	if count == 0 {
		// queryData, err = MapStrList(sqlstr, params...)
		queryData, err = MapInterList(sqlstr, params...)
	} else {
		// if count > 0 && offset > 0 {
		// 	offset = offset * count
		// }
		sqllist := sqlstr + fmt.Sprintf("  limit %d  offset %d", count, offset*count)
		// queryData, err = MapStrList(sqllist, params...)
		queryData, err = MapInterList(sqllist, params...)
	}
	if err != nil {
		return nil, err
	}
	if recordcount == 0 {
		recordcount = int64(len(queryData))
	}

	data := make(map[string]interface{})
	data["list"] = queryData
	data["total"] = recordcount
	data["code"] = 0
	data["message"] = "ok"
	if count > 0 {
		data["pages"] = math.Ceil(float64(recordcount) / float64(count))
		data["pageNum"] = math.Ceil(float64(offset+1) / float64(count))
	}
	data["pageSize"] = count
	return data, nil
}

// QueryByXlsx 将查询到的数据，按照指定字段的值做为索引构建map并返回
// sql string: 查询需要获取哪些字段的值，就是select后面跟着的部分，一般用"*"
// params string: 过滤条件，就是where后面跟着的部分

func QueryByXlsx(sqlstr string, params []interface{}) (*xlsx.File, error) {
	if err := sqlCheckParam(sqlstr); err != nil {
		return nil, err
	}

	rows, err := Dbs.Queryx(sqlstr, params...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	columnsType, _ := rows.ColumnTypes()
	columnsLen := len(columnsType)
	columns, _ := rows.Columns()
	values := make([]interface{}, columnsLen)
	valuePtrs := make([]interface{}, columnsLen)

	var sheet *xlsx.Sheet
	var row *xlsx.Row
	var cell *xlsx.Cell

	file := xlsx.NewFile()
	sheet, err = file.AddSheet("Sheet1")

	if err != nil {
		fmt.Printf(err.Error())
	}
	row = sheet.AddRow()
	for _, columnname := range columns {
		cell = row.AddCell()
		cell.Value = columnname
	}
	for rows.Next() {
		for a := 0; a < columnsLen; a++ {
			valuePtrs[a] = &values[a]
		}
		rows.Scan(valuePtrs...)
		row = sheet.AddRow()
		for i := range columns {
			var v interface{}
			val := values[i]
			b, ok := val.([]byte)
			cell = row.AddCell()
			if ok {
				v1 := string(b)
				switch columnsType[i].DatabaseTypeName() {
				case "TINYINT", "SMALLINT", "MEDIUMINT", "INT", "INTEGER", "BIGINT":
					{
						v, _ = strconv.ParseInt(v1, 10, 32)
						cell.Value = fmt.Sprintf("%v", v)
					}
				case "FLOAT", "DOUBLE", "DECIMAL":
					{
						cindex := len(val.([]byte)) - strings.Index(v1, ".") - 1
						v2, _ := strconv.ParseFloat(v1, 32)
						v = toFixed(v2, cindex)
						cell.Value = fmt.Sprintf("%v", v)
					}
				case "BOOL":
					{
						v, _ = strconv.ParseBool(v1)
						cell.Value = fmt.Sprintf("%v", v)
					}
				default:
					{
						v = v1
						cell.Value = fmt.Sprintf("%v", v)
					}
				}

			} else {
				v = val
				cell.Value = fmt.Sprintf("%v", v)
			}
			if cell.Value == "<nil>" {
				cell.Value = ""
			}
		}
	}

	return file, nil
}

// QueryByMap 将查询到的数据，按照指定字段的值做为索引构建map并返回
// columnName string: 作为索引的字段名称
// sql string: 查询需要获取哪些字段的值，就是select后面跟着的部分，一般用"*"
// where string: 过滤条件，就是where后面跟着的部分
// order string: 排序条件，就是order by后面跟着的部分。默认是ASC排序，除非"-"开头则DESC排序
// offset string: limit后面逗号相隔的两个数值，前者就是offset，后者就是count
// count string: limit后面逗号相隔的两个数值，前者就是offset，后者就是count

func QueryByMapMenu(sqlstr string, offset, count int, params []interface{}) ([]map[string]interface{}, error) {
	if err := sqlCheckParam(sqlstr); err != nil {
		return nil, err
	}

	if offset < 0 {
		offset = 0
	}
	if count <= 0 {
		count = 0
	}
	if count > 0 {
		sqlstr += " limit " + strconv.Itoa(offset) + "," + strconv.Itoa(count)
	}

	rows, err := Dbs.Query(sqlstr, params...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	columnsType, _ := rows.ColumnTypes()
	columnsLen := len(columnsType)
	columns, _ := rows.Columns()
	queryData := make([]map[string]interface{}, 0)
	values := make([]interface{}, columnsLen)
	valuePtrs := make([]interface{}, columnsLen)

	for rows.Next() {
		for a := 0; a < columnsLen; a++ {
			valuePtrs[a] = &values[a]
		}
		rows.Scan(valuePtrs...)
		entry := make(map[string]interface{})
		for i, col := range columns {
			entry[col] = getValue(values[i], columnsType[i].DatabaseTypeName())
		}
		queryData = append(queryData, entry)
	}

	data := queryData
	return data, nil
}

func QueryByList(sqlstr string, offset, count int, params []interface{}) (interface{}, error) {
	if err := sqlCheckParam(sqlstr); err != nil {
		return 0, err
	}
	if count == 0 {
		count = 1000
	}
	recordcount := int64(0)
	if count > 1 {
		trow, err := Dbs.Queryx("SELECT COUNT(1) as count FROM ("+sqlstr+") a", params...)
		if err != nil {
			return nil, err
		}
		tt, err := rows2mapObjects(trow)
		if err != nil {
			return nil, err
		}
		recordcount = tt[0]["count"].(int64)
	}
	queryData := make([]map[string]interface{}, 1)
	var err error
	if count == 0 {
		tqueryData, err := Dbs.Queryx(sqlstr, params...)
		if err != nil {
			return nil, err
		}
		queryData, err = rows2mapObjects(tqueryData)
	} else {
		// if count > 0 && offset > 0 {
		// 	offset = offset * count
		// }
		sqlstr += fmt.Sprintf(" limit %d offset %d", count, offset*count)
		tqueryData, err := Dbs.Queryx(sqlstr, params...)
		if err != nil {
			return nil, err
		}
		queryData, err = rows2mapObjects(tqueryData)
	}
	if err != nil {
		return nil, err
	}
	columnsname := make([]string, 0)
	listdata := make(map[string][]interface{})
	if len(queryData) > 0 {
		for i := range queryData {
			if i == 0 {
				for i := range queryData[0] {
					columnsname = append(columnsname, i)
				}
			}

			for _, v := range columnsname {
				listdata[v] = append(listdata[v], queryData[i][v])
			}

		}
		recordcount = int64(len(queryData))
	}

	data := make(map[string]interface{})
	data["list"] = listdata
	//data["columnsname"] = columnsname
	data["total"] = recordcount
	data["code"] = 0
	data["message"] = "ok"
	if count > 0 {
		data["pages"] = math.Ceil(float64(recordcount) / float64(count))
		data["pageNum"] = math.Ceil(float64(offset+1) / float64(count))
	}
	data["pageSize"] = count
	return data, nil
}

func QueryByListList(sqlstr string, offset, count int, params []interface{}) (interface{}, error) {
	if err := sqlCheckParam(sqlstr); err != nil {
		return 0, err
	}
	if count == 0 {
		count = 1000
	}
	recordcount := int64(0)
	if count > 1 {
		trow, err := Dbs.Queryx("SELECT COUNT(1) as count FROM ("+sqlstr+") a", params...)
		if err != nil {
			return nil, err
		}
		tt, err := rows2mapObjects(trow)
		if err != nil {
			return nil, err
		}
		recordcount = tt[0]["count"].(int64)
	}

	queryData := make([]map[string]interface{}, 1)
	var err error
	if count == 0 {
		tqueryData, err := Dbs.Queryx(sqlstr, params...)
		if err != nil {
			return nil, err
		}
		queryData, err = rows2mapObjects(tqueryData)
	} else {
		if count > 0 && offset > 0 {
			offset = offset * count
		}
		tqueryData, err := Dbs.Queryx(fmt.Sprintf(sqlstr+" limit %d , %d", offset, count), params...)
		if err != nil {
			return nil, err
		}
		queryData, err = rows2mapObjects(tqueryData)
	}

	if err != nil {
		return nil, err
	}
	columnsname := make([]string, 0)
	listdata := make([]interface{}, 0)
	if len(queryData) > 0 {
		for i := range queryData {
			if i == 0 {
				for i := range queryData[0] {
					columnsname = append(columnsname, i)
				}
			}
			listdatarow := make([]interface{}, 0)

			for _, v := range columnsname {
				listdatarow = append(listdatarow, queryData[i][v])
			}
			listdata = append(listdata, listdatarow)
		}
		recordcount = int64(len(queryData))
	}
	data := make(map[string]interface{})
	data["list"] = listdata
	data["columnsname"] = columnsname
	data["total"] = recordcount
	data["code"] = 0
	data["message"] = "ok"
	if count > 0 {
		data["pages"] = math.Ceil(float64(recordcount) / float64(count))
		data["pageNum"] = math.Ceil(float64(offset+1) / float64(count))
	}
	data["pageSize"] = count
	return data, nil
}

// func QueryByListT(sqlstr string, offset, count int, params []interface{}, tx *sqlx.Tx) (interface{}, error) {
func QueryByListT(sqlstr string, offset, count int, params []interface{}) (interface{}, error) {
	if err := sqlCheckParam(sqlstr); err != nil {
		return 0, err
	}
	if count == 0 {
		count = 1000
	}
	recordcount := int64(0)
	if count > 1 {
		tt, err := Dbs.Queryx("SELECT COUNT(*) as count FROM ("+sqlstr+") a", params...)
		if err != nil {
			return nil, err
		}
		tt.Next()
		tt.Scan(&recordcount)
	}
	var err error
	var sqllist string
	if count > 0 {
		sqllist = sqlstr + fmt.Sprintf(" limit %d offset %d", count, offset*count)
	}
	listdata, err := MapInterList(sqllist, params...)
	if err != nil {
		log.Default().Printf("QueryByListT MapInterList : %v", err)
	}

	data := make(map[string]interface{})
	data["lists"] = listdata
	data["total"] = recordcount
	data["code"] = 0
	data["message"] = "ok"
	if count > 0 {
		data["pages"] = math.Ceil(float64(recordcount) / float64(count))
		data["pageNum"] = math.Ceil(float64(offset+1) / float64(count))
	}
	data["pageSize"] = count
	return data, nil
}

// func QueryByListListT(sqlstr string, offset, count int, params []interface{}, tx *sqlx.Tx) (interface{}, error) {
func QueryByListListT(sqlstr string, offset, count int, params []interface{}) (interface{}, error) {
	if err := sqlCheckParam(sqlstr); err != nil {
		return 0, err
	}
	if count == 0 {
		count = 1000
	}
	recordcount := int64(0)
	if count > 1 {
		sqlstr = fmt.Sprintf("SELECT COUNT(*) as count FROM (%s) a", sqlstr)
		err := BasicList(sqlstr, params, &recordcount)
		if err != nil {
			return nil, err
		}
	}

	queryData := make([]map[string]string, 1)
	var err error
	if count == 0 {
		queryData, err = MapStrList(sqlstr, params...)
	} else {
		// if count > 0 && offset > 0 {
		// 	offset = offset * count
		// }
		sqllist := sqlstr + fmt.Sprintf(" limit %d offset %d", count, offset*count)
		queryData, err = MapStrList(sqllist, params...)
	}

	if err != nil {
		return nil, err
	}
	columnsname := make([]string, 0)
	listdata := make([]interface{}, 0)
	if len(queryData) > 0 {
		for i := range queryData {
			if i == 0 {
				for i := range queryData[0] {
					columnsname = append(columnsname, i)
				}
			}
			listdatarow := make([]interface{}, 0)

			for _, v := range columnsname {
				listdatarow = append(listdatarow, queryData[i][v])
			}
			listdata = append(listdata, listdatarow)
		}
		recordcount = int64(len(queryData))
	}
	data := make(map[string]interface{})
	data["list"] = listdata
	data["columnsname"] = columnsname
	data["total"] = recordcount
	data["code"] = 0
	data["message"] = "ok"
	if count > 0 {
		data["pages"] = math.Ceil(float64(recordcount) / float64(count))
		data["pageNum"] = math.Ceil(float64(offset+1) / float64(count))
	}
	data["pageSize"] = count
	return data, nil
}

func ExecQuery(sqlstr string, params []interface{}) (interface{}, error) {
	if err := sqlCheckParam(sqlstr); err != nil {
		return 0, err
	}

	rows, err := Dbs.Exec(sqlstr, params...)
	if err != nil {
		return nil, err
	}
	data := make(map[string]interface{})
	LastInsertId, _ := rows.LastInsertId()
	idPointer := (*int)(unsafe.Pointer(&LastInsertId))
	idd16 := *idPointer
	RowsAffected, _ := rows.RowsAffected()
	_idPointer := (*int)(unsafe.Pointer(&RowsAffected))
	_idd16 := *_idPointer
	data["result"] = rows
	data["LastInsertId"] = idd16

	data["RowsAffected"] = _idd16
	return data, nil
}

func ExecQueryT(sqlstr string, params []interface{}, tx *sqlx.Tx) (interface{}, error) {
	if err := sqlCheckParam(sqlstr); err != nil {
		return 0, err
	}

	rows, err := tx.Exec(sqlstr, params...)
	if err != nil {

		fmt.Println("exe", err)
		return nil, err
	}

	LastInsertId, _ := rows.LastInsertId()
	idPointer := (*int)(unsafe.Pointer(&LastInsertId))
	idd16 := *idPointer
	data := make(map[string]interface{})

	RowsAffected, _ := rows.RowsAffected()
	_idPointer := (*int)(unsafe.Pointer(&RowsAffected))
	_idd16 := *_idPointer
	data["LastInsertId"] = idd16
	data["RowsAffected"] = _idd16
	data["result"] = rows
	return data, nil
}

func ExecQuerys(sqlstr string, params []interface{}) (interface{}, error) {
	if err := sqlCheckParam(sqlstr); err != nil {
		return 0, err
	}
	var res sql.Result
	var e error
	res, e = Dbs.Exec(sqlstr, params...)
	if e != nil {
		return nil, e
	}
	fmt.Printf("%#v\n", res)
	data := make(map[string]interface{})
	data["result"] = nil
	return data, nil
}

func getValue(value interface{}, CT string) interface{} {
	var v interface{}
	val := value
	b, ok := val.([]byte)
	if ok {
		v1 := string(b)
		switch CT {
		case "TINYINT", "SMALLINT", "MEDIUMINT", "INT", "INTEGER", "BIGINT":
			{
				v, _ = strconv.ParseInt(v1, 10, 32)
			}
		case "FLOAT", "DOUBLE", "DECIMAL":
			{
				cindex := len(val.([]byte)) - strings.Index(v1, ".") - 1
				v2, _ := strconv.ParseFloat(v1, 32)
				v = toFixed(v2, cindex)
			}
		case "BOOL":
			{
				v, _ = strconv.ParseBool(v1)
			}
		default:
			{
				v = v1
			}
		}
	} else {
		v = val
	}
	return v
}
func reflect2object(rawValue *reflect.Value) (value interface{}, err error) {
	aa := reflect.TypeOf((*rawValue).Interface())
	vv := reflect.ValueOf((*rawValue).Interface())
	switch aa.Kind() {
	case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
		value = vv.Int()
	case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64:
		value = vv.Uint()
	case reflect.Float32, reflect.Float64:
		value = vv.Float()
	case reflect.String:
		value = vv.String()
	case reflect.Array, reflect.Slice:
		switch aa.Elem().Kind() {
		case reflect.Uint8:
			data := rawValue.Interface().([]byte)
			value = string(data)
		default:
			err = fmt.Errorf("Unsupported struct type %v", vv.Type().Name())
		}
	// time type
	case reflect.Struct:
		if aa.ConvertibleTo(schemas.TimeType) {
			value = vv.Convert(schemas.TimeType).Interface().(time.Time)
		} else {
			err = fmt.Errorf("Unsupported struct type %v", vv.Type().Name())
		}
	case reflect.Bool:
		value = vv.Bool()
	case reflect.Complex128, reflect.Complex64:
		value = vv.Complex()
	/* TODO: unsupported types below
	   case reflect.Map:
	   case reflect.Ptr:
	   case reflect.Uintptr:
	   case reflect.UnsafePointer:
	   case reflect.Chan, reflect.Func, reflect.Interface:
	*/
	default:
		err = fmt.Errorf("Unsupported struct type %v", vv.Type().Name())
	}
	return
}
func value2Object(rawValue *reflect.Value) (data interface{}, err error) {
	data, err = reflect2object(rawValue)
	if err != nil {
		return
	}
	return
}

func rows2mapObjects(rows *sqlx.Rows) (resultsSlice []map[string]interface{}, err error) {
	fields, err := rows.Columns()
	if err != nil {
		return nil, err
	}
	for rows.Next() {
		result, err := rows2mapObject(rows, fields)
		if err != nil {
			return nil, err
		}
		resultsSlice = append(resultsSlice, result)
	}

	return resultsSlice, nil
}

func rows2mapObject(rows *sqlx.Rows, fields []string) (resultsMap map[string]interface{}, err error) {
	result := make(map[string]interface{})
	scanResultContainers := make([]interface{}, len(fields))
	for i := 0; i < len(fields); i++ {
		var scanResultContainer interface{}
		scanResultContainers[i] = &scanResultContainer
	}
	if err := rows.Scan(scanResultContainers...); err != nil {
		return nil, err
	}

	for ii, key := range fields {
		rawValue := reflect.Indirect(reflect.ValueOf(scanResultContainers[ii]))
		//if row is null then ignore
		if rawValue.Interface() == nil {
			continue
		}

		if data, err := value2Object(&rawValue); err == nil {
			result[key] = data
		} else {
			return nil, err // !nashtsai! REVIEW, should return err or just error log?
		}

	}
	return result, nil
}

func row2mapStr(rows *sqlx.Rows, fields []string) (resultsMap map[string]string, err error) {
	result := make(map[string]string)
	scanResultContainers := make([]interface{}, len(fields))
	for i := 0; i < len(fields); i++ {
		var scanResultContainer interface{}
		scanResultContainers[i] = &scanResultContainer
	}
	if err := rows.Scan(scanResultContainers...); err != nil {
		return nil, err
	}

	for ii, key := range fields {
		rawValue := reflect.Indirect(reflect.ValueOf(scanResultContainers[ii]))
		// if row is null then as empty string
		if rawValue.Interface() == nil {
			result[key] = ""
			continue
		}

		if data, err := value2String(&rawValue); err == nil {
			result[key] = data
		} else {
			return nil, err
		}
	}
	return result, nil
}

func Rows2Strings(rows *sqlx.Rows) (resultsSlice []map[string]string, err error) {
	fields, err := rows.Columns()
	if err != nil {
		return nil, err
	}
	for rows.Next() {
		result, err := row2mapStr(rows, fields)
		if err != nil {
			return nil, err
		}
		resultsSlice = append(resultsSlice, result)
	}

	return resultsSlice, nil
}
func value2String(rawValue *reflect.Value) (str string, err error) {
	aa := reflect.TypeOf((*rawValue).Interface())
	vv := reflect.ValueOf((*rawValue).Interface())
	switch aa.Kind() {
	case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
		str = strconv.FormatInt(vv.Int(), 10)
	case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64:
		str = strconv.FormatUint(vv.Uint(), 10)
	case reflect.Float32, reflect.Float64:
		str = strconv.FormatFloat(vv.Float(), 'f', -1, 64)
	case reflect.String:
		str = vv.String()
	case reflect.Array, reflect.Slice:
		switch aa.Elem().Kind() {
		case reflect.Uint8:
			data := rawValue.Interface().([]byte)
			str = string(data)
			if str == "\x00" {
				str = "0"
			}
		default:
			err = fmt.Errorf("Unsupported struct type %v", vv.Type().Name())
		}
	// time type
	case reflect.Struct:
		if aa.ConvertibleTo(schemas.TimeType) {
			str = vv.Convert(schemas.TimeType).Interface().(time.Time).Format(time.RFC3339Nano)
		} else {
			err = fmt.Errorf("Unsupported struct type %v", vv.Type().Name())
		}
	case reflect.Bool:
		str = strconv.FormatBool(vv.Bool())
	case reflect.Complex128, reflect.Complex64:
		str = fmt.Sprintf("%v", vv.Complex())
	/* TODO: unsupported types below
	   case reflect.Map:
	   case reflect.Ptr:
	   case reflect.Uintptr:
	   case reflect.UnsafePointer:
	   case reflect.Chan, reflect.Func, reflect.Interface:
	*/
	default:
		err = fmt.Errorf("Unsupported struct type %v", vv.Type().Name())
	}
	return
}

func MapStrList(sql string, param ...interface{}) ([]map[string]string, error) {
	ress := make([]map[string]string, 0, 0)
	rows, err := Dbs.Queryx(sql, param...)
	if err != nil {
		log.Default().Printf("MapStrList Queryx: %v", err)
		return ress, err
	}
	col, err := rows.Columns()
	if err != nil {
		log.Default().Printf("BigpartUse clolumns: %v", err)
		return ress, err
	}
	for rows.Next() {
		m := make(map[string]string)
		rs, err := rows.SliceScan()
		if err != nil {
			log.Default().Printf("BigpartUse SliceScan: %v", err)
			return ress, err
		}
		for i, r := range rs {
			switch r.(type) {
			case []uint8:
				m[col[i]] = string(r.([]uint8))
			case int64:
				m[col[i]] = strconv.FormatInt(r.(int64), 10)
			}
		}
		ress = append(ress, m)
	}
	defer func() {
		if err := rows.Close(); err != nil {
			log.Default().Printf("sqlx row  close:%v", err)
		}
	}()
	return ress, nil
}
func MapInterList(sql string, param ...interface{}) ([]map[string]interface{}, error) {
	ress := make([]map[string]interface{}, 0, 0)
	rows, err := Dbs.Queryx(sql, param...)
	if err != nil {
		log.Default().Printf("MapInterList Queryx: %v", err)
		return ress, err
	}
	col, err := rows.Columns()
	if err != nil {
		log.Default().Printf("BigpartUse clolumns: %v", err)
		return ress, err
	}
	for rows.Next() {
		m := make(map[string]interface{})
		rs, err := rows.SliceScan()
		if err != nil {
			log.Default().Printf("BigpartUse SliceScan: %v", err)
			return ress, err
		}
		for i, r := range rs {
			switch r.(type) {
			case []uint8:
				m[col[i]] = string(r.([]uint8))
			case int64:
				m[col[i]] = r.(int64)
			}
		}
		ress = append(ress, m)
	}
	defer func() {
		if err := rows.Close(); err != nil {
			log.Default().Printf("sqlx row  close:%v", err)
		}
	}()
	return ress, nil
}

func BasicList(sql string, params []interface{}, res ...interface{}) error {
	// fmt.Printf("%s\n", sql)
	if params == nil {
		params = make([]interface{}, 0)
	}
	// var count int
	tt, err := Dbs.Queryx(sql, params...)

	if err != nil {
		fmt.Println("s1", err)
		return err
	}
	if tt.Next() {
		tt.Scan(res...)
		// tt.Scan(&count)
	}
	defer func() {
		if err := tt.Close(); err != nil {
			log.Default().Printf("sqlx row  close:%v", err)
		}
	}()
	return nil
}

func MapStr(sql string, param ...interface{}) (map[string]string, error) {
	m := make(map[string]string)
	rows, err := Dbs.Queryx(sql, param...)
	if err != nil {
		log.Default().Printf("MapStr Queryx: %v", err)
		return m, err
	}
	col, err := rows.Columns()
	if err != nil {
		log.Default().Printf("BigpartUse clolumns: %v", err)
		return m, err
	}
	if rows.Next() {

		rs, err := rows.SliceScan()
		if err != nil {
			log.Default().Printf("BigpartUse SliceScan: %v", err)
			return m, err
		}
		for i, r := range rs {
			switch r.(type) {
			case []uint8:
				m[col[i]] = string(r.([]uint8))
			case int64:
				m[col[i]] = strconv.FormatInt(r.(int64), 10)
			}
		}
	}
	defer func() {
		if err := rows.Close(); err != nil {
			log.Default().Printf("sqlx row  close:%v", err)
		}
	}()
	return m, nil
}

func MapStrListTx(tx *sqlx.Tx, sql string, param ...interface{}) ([]map[string]string, error) {
	ress := make([]map[string]string, 0, 0)
	rows, err := tx.Queryx(sql, param...)
	if err != nil {
		log.Default().Printf("MapStrListTx Queryx: %v", err)
		return ress, err
	}
	col, err := rows.Columns()
	if err != nil {
		log.Default().Printf("BigpartUse clolumns: %v", err)
		return ress, err
	}
	for rows.Next() {
		m := make(map[string]string)
		rs, err := rows.SliceScan()
		if err != nil {
			log.Default().Printf("BigpartUse SliceScan: %v", err)
			return ress, err
		}
		for i, r := range rs {
			switch r.(type) {
			case []uint8:
				m[col[i]] = string(r.([]uint8))
			case int64:
				m[col[i]] = strconv.FormatInt(r.(int64), 10)
			}
		}
		ress = append(ress, m)
	}
	defer func() {
		if err := rows.Close(); err != nil {
			log.Default().Printf("sqlx row  close:%v", err)
		}
	}()
	return ress, nil
}