tmrgo/.svn/pristine/b0/b0a8e2c10fac3cb77b75cf56cb70868211295144.svn-base

package restful

import (
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"../../pkg/setting"
	"github.com/patrickmn/go-cache"
	"github.com/tealeg/xlsx"
	"github.com/xormplus/xorm"
	"math"
	"strconv"
	"strings"
	"time"
	"unsafe"
)

var (
	Engine       *xorm.Engine
	Apisql_cache *cache.Cache
	dbs *sql.DB
)

func SQLT() *xorm.Session {
	tx := Engine.NewSession()
	return tx
}

// 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 Engine == nil {
		var err error
		if Engine, err = xorm.NewEngine(driverName, dataSourceName); err != nil {
			return err
		}
		if showlog {
			Engine.ShowSQL(true)
		}
		Engine.SetMaxIdleConns(maxIdleConns)
		Engine.SetMaxOpenConns(maxOpenConns)

	}
	if Apisql_cache == nil {
		Apisql_cache = cache.New(600*time.Second, 10*time.Second)
		SetCacheSql()
	}
	err := SQLInitRow(driverName, dataSourceName, maxOpenConns, maxIdleConns)
	if err != nil {
		return err
	}
	return nil
}

func SQLInitRow(driverName, dataSourceName string, maxOpenConns, maxIdleConns int) error {
	if dbs == nil {
		var err error
		if dbs, err = sql.Open(driverName, dataSourceName); err != nil {
			return err
		}
		dbs.SetMaxOpenConns(maxOpenConns)
		dbs.SetMaxIdleConns(maxIdleConns)
	}
	return nil
}
func SetCacheSql() {
	valuesMap, err := Engine.SQL("SELECT sqlname, CONCAT(sqlstr,'|',IFNULL(params,'')) AS sqlstr FROM apisql WHERE ENABLE>0 ").QueryString()
	if err == nil && len(valuesMap) > 0 {
		for _, v := range valuesMap {
			Apisql_cache.Set(v["sqlname"], v["sqlstr"], cache.DefaultExpiration)
		}
	}
}
func sqlCheckParam1(param string) error {
	/*if strings.Contains(param, "where") {
		return errors.New("can not have where")
	}

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

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

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

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

	return nil
}

func GetSqlByName(name string, tx *xorm.Session) (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 := tx.SQL("SELECT sqlstr, params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", name).QueryString()  //20200615 13:17

		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 GetSqlByNameDB(name string) (string, string) {

	sqls := ""
	param := ""
	//if sql, ok := Apisql_cache.Get(name); ok && setting.DatabaseSetting.CacheApiSql {
	//	tempstrb := strings.IndexAny(sql.(string), "|")
	//	sqls = sql.(string)[0:tempstrb]
	//	param = sql.(string)[tempstrb+1:]
	//} else {
		//valuesMap, err := Engine.SQL("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", name).QueryString()
	  valuesMap, err := Engine.SQL("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", name).QueryString()

		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
}

// sqlGetValues 根据结构体中指向实际数据的指针获取出数据，并存储到另一张表中返回
func sqlGetValues1(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 sqlQuery1(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 sqlQueryByTinyIntMap1(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 sqlQueryBySmallIntMap1(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 sqlQueryByIntMap1(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 sqlQueryByBigIntMap1(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 sqlQueryByFloatIntMap1(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 sqlQueryByDoubleMap1(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 sqlQueryByStringMap1(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 sqlGetColumnType1(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 round1(num float64) int {
	return int(num + math.Copysign(0.5, num))
}

func toFixed1(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 QueryByMap1(sqlstr string, offset, count int, params []interface{}) (interface{}, error) {
	if err := sqlCheckParam(sqlstr); err != nil {
		return 0, err
	}
	recordcount := int64(0)

	if count > 1 {
		tt, err := Engine.SQL("SELECT COUNT(*) as count FROM ("+sqlstr+") a", params...).Query().List()
		if err != nil {
			fmt.Println("s1", err)
			return nil, err
		}
		if tt == nil {
			recordcount = 0
		} else {
			recordcount = tt[0]["count"].(int64)
		}

	}
	queryData := make([]map[string]interface{},1)
	var err error
	if(count==0){
		queryData, err = Engine.SQL(sqlstr, params...).Query().List()
	} else {
		if count > 0 && offset > 0 {
			offset = offset * count
		}
		queryData, err = Engine.SQL(sqlstr, params...).Limit(count, offset).Query().List()
	}
	//fmt.Println("count, offset==================",count, offset)

	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 QueryByXlsx1(sqlstr string, params []interface{}) (*xlsx.File, error) {
	if err := sqlCheckParam(sqlstr); err != nil {
		return nil, err
	}

	rows, err := Engine.SQL(sqlstr, params...).QueryRows()
	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 QueryByMapMenu1(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 := Engine.SQL(sqlstr, params...).QueryRows()
	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
	//return Engine.SQL(sqlstr, params...).Query().List()
}

func QueryByList1(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 := Engine.SQL("SELECT COUNT(*) as count FROM ("+sqlstr+") a", params...).Query().List()
		if err != nil {
			return nil, err
		}
		recordcount = tt[0]["count"].(int64)
	}
	queryData := make([]map[string]interface{},1)
	var err error
	if(count==0){
		queryData, err = Engine.SQL(sqlstr, params...).Query().List()
	} else {
		if count > 0 && offset > 0 {
			offset = offset * count
		}
		queryData, err = Engine.SQL(sqlstr, params...).Limit(count, offset).Query().List()
	}
	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["lists"] = 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 QueryByListList1(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 := Engine.SQL("SELECT COUNT(*) as count FROM ("+sqlstr+") a", params...).Query().List()
		if err != nil {
			return nil, err
		}
		recordcount = tt[0]["count"].(int64)
	}


	queryData := make([]map[string]interface{},1)
	var err error
	if(count==0){
		queryData, err = Engine.SQL(sqlstr, params...).Query().List()
	} else {
		if count > 0 && offset > 0 {
			offset = offset * count
		}
		queryData, err = Engine.SQL(sqlstr, params...).Limit(count, offset).Query().List()
	}

	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 ExecQuery1(sqlstr string, params []interface{}) (interface{}, error) {
	if err := sqlCheckParam(sqlstr); err != nil {
		return 0, err
	}

	rows, err := Engine.SQL(sqlstr, params...).Execute()
	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 *xorm.Session) (interface{}, error) {
	if err := sqlCheckParam(sqlstr); err != nil {
		return 0, err
	}

	rows, err := tx.SQL(sqlstr, params...).Execute()
	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
	}
	/*for i, v := range params{
		_, err := dbs.Exec(sqlstr, v[i]...)

		if err != nil {
			return nil, err
		}
	}

	*/
	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 sqlCheckParam(param string) error {
	/*if strings.Contains(param, "where") {
		return errors.New("can not have where")
	}

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

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

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

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

	return nil
}

// SQLInsert 增加一条数据
// tableName string: 操作的表名
// data []byte: 需要更新的内容，用string转换后是json格式
func SQLInsert(tableName string, data []byte) (int64, error) {
	if err := sqlCheckParam(tableName); err != nil {
		return 0, err
	}

	var f []map[string]interface{}
	err := json.Unmarshal(data, &f)
	if err != nil {
		return 0, err
	}

	var res sql.Result

	for _, data := range f {
		var sqlset string

		for k, v := range data {
			if sqlset != "" {
				sqlset += ","
			}

			switch vv := v.(type) {
			case string:
				sqlset += k + "='" + vv + "'"
			case int:
				sqlset += k + "=" + strconv.Itoa(vv)
			case float64:
				sqlset += k + "=" + strconv.FormatFloat(vv, 'f', -1, 64)
			default:
				fmt.Println(k, "is of a type I don't know how to handle")
			}
		}

		stmt, err := dbs.Prepare("INSERT " + tableName + " set " + sqlset)
		if err != nil {
			return 0, err
		}

		res, err = stmt.Exec()
		if err != nil {
			return 0, err
		}
	}

	return res.LastInsertId()
}

// SQLUpdate 更新一条数据
// tableName string: 操作的表名
// where string: 过滤条件，就是where后面跟着的部分
// data []byte: 需要更新的内容，用string转换后是json格式
func SQLUpdate(tableName, where string, data []byte) (int64, error) {
	if err := sqlCheckParam(tableName + where); err != nil {
		return 0, err
	}

	var f map[string]interface{}
	err := json.Unmarshal(data, &f)

	var sqlset string

	for k, v := range f {
		if sqlset != "" {
			sqlset += ","
		}

		switch vv := v.(type) {
		case string:
			sqlset += k + "='" + vv + "'"
		case int:
			sqlset += k + "=" + strconv.Itoa(vv)
		case float64:
			sqlset += k + "=" + strconv.FormatFloat(vv, 'f', -1, 64)
		default:
			fmt.Println(k, "is of a type I don't know how to handle")
		}
	}

	stmt, err := dbs.Prepare("UPDATE " + tableName + " set " + sqlset + " where " + where)
	if err != nil {
		return 0, err
	}

	res, err := stmt.Exec()
	if err != nil {
		return 0, err
	}

	return res.RowsAffected()
}

// SQLDelete 根据where条件删除数据
// tableName string: 操作的表名
// where string: 过滤条件，就是where后面跟着的部分
func SQLDelete(tableName, where string) (int64, error) {
	if err := sqlCheckParam(tableName + where); err != nil {
		return 0, err
	}

	if dbs == nil {
		return 0, errors.New("gogo sql not init")
	}

	//删除数据
	stmt, err := dbs.Prepare("DELETE from " + tableName + " where " + where)
	if err != nil {
		return 0, err
	}

	res, err := stmt.Exec()
	if err != nil {
		return 0, err
	}

	return res.RowsAffected()
}

// sqlQueryTable 从数据库中查询到的数据，这里是以数组方式存储的，需要做二次转换
func sqlQueryTable(feilds, tableName, where, order string, offset, count int) ([]*sql.ColumnType, int, [][]interface{}, int, error) {
	if dbs == nil {
		return nil, 0, nil, 0, errors.New("gogo sql not init")
	}

	if feilds == "" {
		feilds = "*"
	}

	sqlstr := "select " + feilds + " from " + tableName
	if where != "" {
		sqlstr += " where " + where
	}
	if order != "" {
		sqlstr += " order by "
		if strings.HasPrefix(order, "-") {
			sqlstr += string([]byte(order)[1:]) + " desc"
		} else {
			sqlstr += order + " asc"
		}
	}
	if offset < 0 {
		offset = 0
	}
	if count <= 0 {
		count = 20
	}
	sqlstr += " limit " + strconv.Itoa(offset) + "," + strconv.Itoa(count)

	rows, err := dbs.Query(sqlstr)
	if err != nil {
		return nil, 0, nil, 0, err
	}

	columnsType, _ := rows.ColumnTypes()
	columnsLen := len(columnsType)

	queryData := make([][]interface{}, count)
	queryCount := 0

	for rows.Next() {
		queryData[queryCount] = make([]interface{}, columnsLen)
		for a := 0; a < columnsLen; a++ {
			switch columnsType[a].DatabaseTypeName() {
			case "TINYINT":
				{
					queryData[queryCount][a] = new(int8)
				}
			case "SMALLINT":
				{
					queryData[queryCount][a] = new(int16)
				}
			case "MEDIUMINT":
				{
					queryData[queryCount][a] = new(int32)
				}
			case "INT":
				{
					queryData[queryCount][a] = new(sql.NullInt64)
				}
			case "INTEGER":
				{
					queryData[queryCount][a] = new(int32)
				}
			case "BIGINT":
				{
					queryData[queryCount][a] = new(int64)
				}
			case "FLOAT":
				{
					queryData[queryCount][a] = new(float32)
				}
			case "DOUBLE":
				{
					queryData[queryCount][a] = new(float64)
				}
			default:
				{
					queryData[queryCount][a] = new(sql.NullString)
				}
			}
		}

		if err = rows.Scan(queryData[queryCount]...); err != nil {
			return nil, 0, nil, 0, err
		}

		queryCount = queryCount + 1
	}

	return columnsType, columnsLen, queryData, queryCount, nil
}

// sqlQueryTable 从数据库中查询到的数据，这里是以数组方式存储的，需要做二次转换
func sqlQuerySql(sqlstr, order string, offset, count int) ([]*sql.ColumnType, int, [][]interface{}, int, error) {
	if dbs == nil {
		return nil, 0, nil, 0, errors.New("gogo sql not init")
	}

	if sqlstr == "" {
		return nil, 0, nil, 0, errors.New("sql not set")
	}


	if order != "" {
		sqlstr += " order by "
		if strings.HasPrefix(order, "-") {
			sqlstr += string([]byte(order)[1:]) + " desc"
		} else {
			sqlstr += order + " asc"
		}
	}
	if offset < 0 {
		offset = 0
	}
	if count <= 0 {
		count = 20
	}
	sqlstr += " limit " + strconv.Itoa(offset) + "," + strconv.Itoa(count)

	rows, err := dbs.Query(sqlstr)
	if err != nil {
		return nil, 0, nil, 0, err
	}

	columnsType, _ := rows.ColumnTypes()
	columnsLen := len(columnsType)

	queryData := make([][]interface{}, count)
	queryCount := 0

	for rows.Next() {
		queryData[queryCount] = make([]interface{}, columnsLen)
		for a := 0; a < columnsLen; a++ {
			switch columnsType[a].DatabaseTypeName() {
			case "TINYINT":
				{
					queryData[queryCount][a] = new(int8)
				}
			case "SMALLINT":
				{
					queryData[queryCount][a] = new(int16)
				}
			case "MEDIUMINT":
				{
					queryData[queryCount][a] = new(int32)
				}
			case "INT":
				{
					queryData[queryCount][a] = new(sql.NullInt64)
				}
			case "INTEGER":
				{
					queryData[queryCount][a] = new(int32)
				}
			case "BIGINT":
				{
					queryData[queryCount][a] = new(int64)
				}
			case "FLOAT":
				{
					queryData[queryCount][a] = new(float32)
				}
			case "DOUBLE":
				{
					queryData[queryCount][a] = new(float64)
				}
			default:
				{
					queryData[queryCount][a] = new(sql.NullString)
				}
			}
		}

		if err = rows.Scan(queryData[queryCount]...); err != nil {
			return nil, 0, nil, 0, err
		}

		queryCount = queryCount + 1
	}

	return columnsType, columnsLen, queryData, queryCount, nil
}

// 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 ""
}

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

	columnsType, columnsLen, queryData, queryCount, err := sqlQueryTable(feilds, tableName, where, order, offset, count)
	if err != nil {
		return nil, err
	}

	if queryCount == 0 {
		return "", errors.New("0")
	}

	if columnName == "" {
		return sqlQuery(columnsType, columnsLen, queryData, queryCount)
	}

	switch sqlGetColumnType(columnsType, columnsLen, columnName) {
	case "TINYINT":
		return sqlQueryByTinyIntMap(columnName, columnsType, columnsLen, queryData, queryCount)
	case "SMALLINT":
		return sqlQueryBySmallIntMap(columnName, columnsType, columnsLen, queryData, queryCount)
	case "MEDIUMINT":
		return sqlQueryByIntMap(columnName, columnsType, columnsLen, queryData, queryCount)
	case "INT":
		return sqlQueryByIntMap(columnName, columnsType, columnsLen, queryData, queryCount)
	case "INTEGER":
		return sqlQueryByIntMap(columnName, columnsType, columnsLen, queryData, queryCount)
	case "BIGINT":
		return sqlQueryByBigIntMap(columnName, columnsType, columnsLen, queryData, queryCount)
	case "FLOAT":
		return sqlQueryByFloatIntMap(columnName, columnsType, columnsLen, queryData, queryCount)
	case "DOUBLE":
		return sqlQueryByDoubleMap(columnName, columnsType, columnsLen, queryData, queryCount)
	}

	return sqlQueryByStringMap(columnName, columnsType, columnsLen, queryData, queryCount)
}
func SQLQuerySqlByMap(sqlstr, order string, offset, count int) (interface{}, error) {
	if err := sqlCheckParam(sqlstr + order); err != nil {
		return 0, err
	}
	columnsType, columnsLen, queryData, queryCount, err := sqlQuerySql(sqlstr, order, offset, count)
	if err != nil {
		return nil, err
	}

	if queryCount == 0 {
		return "", errors.New("0")
	}
	columnName:=""
	if columnName == "" {
		return sqlQuery(columnsType, columnsLen, queryData, queryCount)
	}

	switch sqlGetColumnType(columnsType, columnsLen, columnName) {
	case "TINYINT":
		return sqlQueryByTinyIntMap(columnName, columnsType, columnsLen, queryData, queryCount)
	case "SMALLINT":
		return sqlQueryBySmallIntMap(columnName, columnsType, columnsLen, queryData, queryCount)
	case "MEDIUMINT":
		return sqlQueryByIntMap(columnName, columnsType, columnsLen, queryData, queryCount)
	case "INT":
		return sqlQueryByIntMap(columnName, columnsType, columnsLen, queryData, queryCount)
	case "INTEGER":
		return sqlQueryByIntMap(columnName, columnsType, columnsLen, queryData, queryCount)
	case "BIGINT":
		return sqlQueryByBigIntMap(columnName, columnsType, columnsLen, queryData, queryCount)
	case "FLOAT":
		return sqlQueryByFloatIntMap(columnName, columnsType, columnsLen, queryData, queryCount)
	case "DOUBLE":
		return sqlQueryByDoubleMap(columnName, columnsType, columnsLen, queryData, queryCount)
	}

	return sqlQueryByStringMap(columnName, columnsType, columnsLen, queryData, queryCount)
}

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
	}

	if offset <= 0 {
		offset = 1
	}
	if count <= 0 {
		count = 0
	}
	//if count > 0 {
	//	sqlstr += " limit " + strconv.Itoa(count*(offset-1)) + "," + 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)

	curoffset := 0
	beginoffset := 	count*(offset-1)
	for rows.Next() {
		if (curoffset>=beginoffset && curoffset-beginoffset< count) || count ==0 {
			for a := 0; a < columnsLen; a++ {
				valuePtrs[a] = &values[a]
			}
			rows.Scan(valuePtrs...)
			entry := make(map[string]interface{})
			for i, col := range columns {
				var v interface{}
				val := values[i]
				b, ok := val.([]byte)
				if ok {
					v1 := string(b)
					switch columnsType[i].DatabaseTypeName() {
					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
				}

				entry[col] = v
			}
			queryData = append(queryData, entry)

		}
		curoffset++
	}
	//results, _ := engine.Sql(sqlstr).Query().List()
	data := make(map[string]interface{})
	data["list"] = queryData
	data["total"] =  curoffset
	data["pages"] = math.Ceil(float64(curoffset)/float64(count))
	data["pageNum"] = math.Ceil(float64(offset)/float64(count))
	data["pageSize"] = count
	data["code"] = 0
	data["message"] = "ok"
	return data,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 QueryByMapEQ(sqlstr string, offset, count int, returntype string,params []interface{}) (interface{}, error) {
	if err := sqlCheckParam(sqlstr); err != nil {
		return 0, err
	}

	if offset <= 0 {
		offset = 1
	}
	if count <= 0 {
		count = 0
	}

	//if count > 0 {
	//	sqlstr += " limit " + strconv.Itoa(count*(offset-1)) + "," + 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)

	curoffset := 0
	beginoffset := 	count*(offset-1)
	if beginoffset < 0 {
		beginoffset=0
	}
	for rows.Next() {
		if (curoffset>=beginoffset && curoffset-beginoffset< count) || count ==0 {
			for a := 0; a < columnsLen; a++ {
				valuePtrs[a] = &values[a]
			}
			rows.Scan(valuePtrs...)
			entry := make(map[string]interface{})
			for i, col := range columns {
				var v interface{}
				val := values[i]
				b, ok := val.([]byte)
				if ok {
					v1 := string(b)
					switch columnsType[i].DatabaseTypeName() {
					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
				}

				entry[col] = v
			}
			queryData = append(queryData, entry)

		}
		curoffset++
	}
	//results, _ := engine.Sql(sqlstr).Query().List()
	data := make(map[string]interface{})
	data1 := make(map[string]interface{})
	if returntype == "0" {
		data["data"] = queryData
		data["count"] = len(queryData)
	}else if returntype == "2" {
		data["data"] = queryData[0]
		data["count"] = len(queryData)
	}else if returntype == "3" {
		data["data"] = queryData[0]["fsion"]
		data["count"] = len(queryData)
	}else {
		data1["list"] = queryData
		data1["total"] =  curoffset
		data1["pages"] = math.Ceil(float64(curoffset)/float64(count))
		data1["pageNum"] = math.Ceil(float64(offset)/float64(count))
		data1["pageSize"] = count
		data1["count"] = len(queryData)
		data["data"]= data1
	}
	return data,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 {
			var v interface{}
			val := values[i]
			b, ok := val.([]byte)
			if ok {
				v1 := string(b)
				switch columnsType[i].DatabaseTypeName() {
				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
			}

			entry[col] = v
		}
		queryData = append(queryData, entry)
	}
	//results, _ := engine.Sql(sqlstr).Query().List()
	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 offset <= 0 {
		offset = 1
	}
	if count <= 0 {
		count = 0
	}

	//	if count > 0 {
	//		sqlstr += " limit " + strconv.Itoa(count*(offset-1)) + "," + 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{})

	values := make([]interface{}, columnsLen)
	valuePtrs := make([]interface{}, columnsLen)
	curoffset := 0
	beginoffset := 	count*(offset-1)

	for rows.Next() {
		if (curoffset>=beginoffset && curoffset-beginoffset< count) || count ==0 {
			for a := 0; a < columnsLen; a++ {
				valuePtrs[a] = &values[a]
			}
			rows.Scan(valuePtrs...)

			for i, col := range columns {
				var v interface{}
				val := values[i]
				b, ok := val.([]byte)
				if ok {
					v1 := string(b)
					switch columnsType[i].DatabaseTypeName() {
					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
				}

				queryData[col] = append(queryData[col], v)
			}

		}
		curoffset++
	}
	data := make(map[string]interface{})
	data["lists"] = queryData
	data["total"] = curoffset
	data["offset"] = offset
	data["count"] = count
	data["code"] = 0
	data["message"] = "ok"
	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.Query(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
}

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{})
	data["result"] = rows
	return data,nil
}


func ExecQuerys1(sqlstr string, params []interface{}) (interface{}, error) {
	if err := sqlCheckParam(sqlstr); err != nil {
		return 0, err
	}
	/*for i, v := range params{
		_, err := dbs.Exec(sqlstr, v[i]...)

		if err != nil {
			return nil, err
		}
	}

*/
	data := make(map[string]interface{})
	data["result"] = nil
	return data,nil
}

func QueryByListList(sqlstr string, offset, count int, params []interface{}) (interface{}, error) {
	if err := sqlCheckParam(sqlstr); err != nil {
		return 0, err
	}
	if offset <= 0 {
		offset = 1
	}
	if count <= 0 {
		count = 0
	}
	//if count > 0 {
	//	sqlstr += " limit " + strconv.Itoa(count*(offset-1)) + "," + 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()
	columnsname := make([]interface{}, columnsLen)
	for i, col := range columns {
		columnsname[i] = col
	}
	queryData := make([]interface{}, 0)

	values := make([]interface{}, columnsLen)
	valuePtrs := make([]interface{}, columnsLen)

	curoffset := 0
	beginoffset := count * (offset - 1)
	for rows.Next() {
		if (curoffset >= beginoffset && curoffset-beginoffset < count) || count == 0 {
			for a := 0; a < columnsLen; a++ {
				valuePtrs[a] = &values[a]
			}
			rows.Scan(valuePtrs...)
			entry := make([]interface{}, 0)
			for i, _ := range columns {
				var v interface{}
				val := values[i]
				b, ok := val.([]byte)
				if ok {
					v1 := string(b)
					switch columnsType[i].DatabaseTypeName() {
					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
				}

				entry = append(entry, v)
			}
			queryData = append(queryData, entry)
		}
		curoffset++
	}
	data := make(map[string]interface{})
	data["lists"] = queryData
	data["columnsname"] = columnsname
	data["total"] = curoffset
	data["offset"] = offset
	data["count"] = count
	data["code"] = 0
	data["message"] = "ok"
	return data, nil
}