neckring/internal/dao/auth.go

package dao

import (
	"demo/internal/setting"
	"demo/internal/util"
	"fmt"
	"github.com/patrickmn/go-cache"
	"github.com/pkg/errors"
	"github.com/siddontang/go/log"
	"math"
	"strconv"
	"strings"
	"unsafe"
)

var (
	Apisql_cache *cache.Cache
)

func (d *Dao) CheckUser(username, passwd string) (bool, error) {
	return d.db.Table("user").Where("username = ? ", username).And(" password = ? ", util.EncodeMD5(passwd)).Exist()
}

func (d *Dao) GetSqlByNameDBT(sqlname string) (string, string, error) {
	apiList, err := d.db.Table("apisql").Where("sqlname = ?", sqlname).QueryString()
	if err != nil {
		err = errors.Wrap(err, "GetSqlByNameDBT-db-error")
		log.Error(err)
		return "", "", err
	}

	sqlstr := ""
	p := ""
	for _, api := range apiList {
		sqlstr = api["sqlstr"]
		p = api["params"]
	}
	return sqlstr, p, nil
}

func (d *Dao) QueryByListT(sqlstr string, offset, count int, params []interface{}) (interface{}, error) {
	if strings.Contains(sqlstr, ";") {
		return nil, errors.New("can not have ;")
	}

	if count == 0 {
		count = 1000
	}
	recordcount := int64(0)
	if count > 1 {
		tt, err := d.db.SQL("SELECT COUNT(*) as count FROM ("+sqlstr+") a", params...).QueryInterface()
		if err != nil {
			return nil, err
		}
		switch tt[0]["count"].(type) {
		case int64:
			recordcount = tt[0]["count"].(int64)
		case string:
			temp, _ := strconv.Atoi(tt[0]["count"].(string))
			recordcount = int64(temp)

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

	}
	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 (d *Dao) QueryByListListT(sqlstr string, offset, count int, params []interface{}) (interface{}, error) {
	if strings.Contains(sqlstr, ";") {
		return nil, errors.New("can not have ;")
	}

	if count == 0 {
		count = 1000
	}
	recordcount := int64(0)
	if count > 1 {
		tt, err := d.db.SQL("SELECT COUNT(*) as count FROM ("+sqlstr+") a", params...).QueryInterface()
		if err != nil {
			return nil, err
		}
		switch tt[0]["count"].(type) {
		case int64:
			recordcount = tt[0]["count"].(int64)
		case string:
			temp, _ := strconv.Atoi(tt[0]["count"].(string))
			recordcount = int64(temp)

		}
	}

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

	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 (d *Dao) QueryByMapT(sqlstr string, offset, count int, params []interface{}) (interface{}, error) {
	if strings.Contains(sqlstr, ";") {
		return nil, errors.New("can not have ;")
	}

	recordcount := int64(0)

	if count > 1 {
		tt, err := d.db.SQL("SELECT COUNT(*) as count FROM ("+sqlstr+") a", params...).Count()
		if err != nil {
			fmt.Println("s1", err)
			return nil, err
		}
		recordcount = tt
		//if tt == nil {
		//	recordcount = 0
		//} else {
		//	switch tt[0]["count"].(type) {
		//	case int64:
		//		recordcount = tt[0]["count"].(int64)
		//	case string:
		//		temp, _ := strconv.Atoi(tt[0]["count"].(string))
		//		recordcount = int64(temp)
		//
		//	}
		//
		//}

	}
	queryData := make([]map[string]string, 0)
	var err error
	if count == 0 {
		queryData, err = d.db.SQL(sqlstr, params...).QueryString()
	} else {
		offset := (offset - 1) * count
		queryData, err = d.db.SQL(fmt.Sprintf("%s limit %d,%d", sqlstr, offset, count), params...).QueryString()
	}
	//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
}

func (d *Dao) QueryByMap(sqlstr string, offset, count int, params []interface{}) (interface{}, error) {
	recordcount := int64(0)
	if count > 1 {
		tt, err := d.db.SQL("SELECT COUNT(*) as count FROM ("+sqlstr+") a", params...).QueryInterface()
		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]string, 1)
	var err error
	if count == 0 {
		queryData, err = d.db.SQL(sqlstr, params...).QueryString()
	} else {
		if count > 0 && offset > 0 {
			offset = offset * count
		}
		queryData, err = d.db.SQL(sqlstr, params...).Limit(count, offset).QueryString()
	}
	//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
}

func (d *Dao) ExecQueryT(sqlstr string, params []interface{}) (interface{}, error) {
	//if err := sqlCheckParam(sqlstr); err != nil {
	//	return 0, err
	//}
	var args []interface{}
	args = append(args, sqlstr)
	args = append(args, params...)
	rows, err := d.db.Exec(args...)
	if err != nil {
		log.Error(err)
		return nil, err
	}

	LastInsertId, err := 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"] = strconv.Itoa(idd16)
	data["LastInsertIdStr"] = idd16
	data["RowsAffected"] = _idd16
	data["result"] = rows
	return data, nil
}

func (d *Dao) GetUserInfo(username string) map[string]interface{} {
	valuesMap, err := d.db.SQL("SELECT * FROM  `user` WHERE `username` = ? and `enable` >0 ", username).QueryString()

	if err != nil {
		return nil
	}
	resp := make(map[string]interface{})
	if len(valuesMap) > 0 {
		valuesMap2, err := d.db.SQL("SELECT r.`name`,r.`id` FROM `user` u  JOIN `role` r ON r.id=u.`roleid` WHERE r.`enable`>0  AND u.username=?", username).QueryString()
		if err == nil {
			resp["role"] = valuesMap2
		}
		for k, v := range valuesMap[0] {
			resp[k] = v
		}
		return resp
	}
	return nil
}

func (d *Dao) GetSqlByNameDB(name string) (string, string) {

	sqls := ""
	param := ""
	//Apisql_cache.Get(name)
	//fmt.Println(setting.DatabaseSetting.CacheApiSql)
	//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 := d.db.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
}

func (d *Dao) QueryByMapMenu(sqlstr string, offset, count int, params []interface{}) ([]map[string]interface{}, error) {

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

	rows, err := d.db.SQL(sqlstr, params...).QueryString()
	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)
	//}

	dataList := make([]map[string]interface{}, 0)
	for _, row := range rows {
		data := make(map[string]interface{}, 0)
		for k, v := range row {
			data[k] = v
		}
		dataList = append(dataList, data)
	}
	//data := rows
	return dataList, nil
	//return Engine.SQL(sqlstr, params...).Query().List()
}