package models
import (
"bytes"
"fmt"
"log"
"reflect"
"strconv"
"strings"
"time"
)
type PageUtil struct {
Current int64 `json:"current"`
PageSize int64 `json:"pageSize"`
TotalPage int64 `json:"totalPage"`
Total int64 `json:"total"`
// 前端返回出去是查询出来的list
Object interface{} `json:"object"`
// 后端返回出去是查询出来的list
List interface{} `json:"list"`
// sql param
SqlParam []string `json:"sqlParam"`
}
func (q *PageUtil) Offset() int64 {
offset := (q.Current - 1) * q.PageSize
if offset < 0 {
offset = 0
}
return offset
}
func (q *PageUtil) Limit() int64 {
return q.PageSize
}
func (q *PageUtil) GetPageLimitSql() (sql string) {
log.Println("11111111111:" + reflect.TypeOf(q.Object).Name())
QueryBuilder := mysqlBuilder().Select(" * ").From(
q.ToLowerAndUnderline(reflect.TypeOf(q.Object).Name()).String()).
Where(q.GetWhereSql(q.Object).String()).Limit(int(q.Limit())).Offset(int(q.Offset()))
log.Println("print sql = " + QueryBuilder.String())
return QueryBuilder.String()
}
func (q *PageUtil) GetCount() (sql string) {
QueryBuilder := mysqlBuilder().Select(" count(*) ").From(
q.ToLowerAndUnderline(reflect.TypeOf(q.Object).Name()).String()).
Where(q.GetWhereSql(q.Object).String())
log.Println("print sql = " + QueryBuilder.String())
return QueryBuilder.String()
}
// 获取当前对象中不为null的字段
func (q *PageUtil) GetWhereSql(structName interface{}) (sql *bytes.Buffer) {
// GetWhereSql
t := reflect.TypeOf(structName)
v := reflect.ValueOf(structName)
if t.Kind() == reflect.Ptr {
t = t
}
if t.Kind() != reflect.Struct {
log.Println("Check type error not Struct")
return
}
fieldNum := t.NumField()
sql = bytes.NewBufferString("")
sql.WriteString(" 1=1 ")
for i := 0; i < fieldNum; i++ {
sql.WriteString(q.GetConcatSql(t.Field(i), v.Field(i)))
}
return sql
}
// 拼接sql
func (q *PageUtil) GetConcatSql(structField reflect.StructField, v reflect.Value) (sqlstr string) {
sql := bytes.NewBufferString("")
// 依次判断字段是否为空如果不为空那么拼接字符串
value := q.getFieldValue(structField, v)
log.Println("result :"+structField.Name+" Type:", structField.Type.Name()+"value", value)
// 判断传递的各类值为空的情况
if value != "" && value != "0" &&value !="01-01-01 00:00:00" {
// 判断类型拼接字符串
if structField.Type.Name() == "int64" || structField.Type.Name() == "int" {
sql.WriteString(" and " + q.ToLowerAndUnderline(structField.Name).String() + " = " + value)
} else if structField.Type.Name() == "bool" {
// bool类型在数据库中表示的类型是tinyint 0表示false
if value == "false" {
sql.WriteString(" and " + q.ToLowerAndUnderline(structField.Name).String() + " = 0")
} else {
sql.WriteString(" and " + q.ToLowerAndUnderline(structField.Name).String() + " = 1")
}
} else if structField.Type.Name() == "Time" {
sql.WriteString(" and " + q.ToLowerAndUnderline(structField.Name).String() + " = '"+value+"'")
} else if structField.Type.Name() == "string" {
sql.WriteString(" and " + q.ToLowerAndUnderline(structField.Name).String() + " = '" + value + "'")
}
}
return sql.String()
}
// 获取当前字段的值
func (q *PageUtil) getFieldValue(structField reflect.StructField, v reflect.Value) (value string) {
if structField.Type.Name() == "int64" || structField.Type.Name() == "int" {
return strconv.FormatInt(v.Int(), 10)
} else if structField.Type.Name() == "string" {
return v.String()
} else if structField.Type.Name() == "bool" {
return strconv.FormatBool(v.Bool())
}else if structField.Type.Name() == "Time" {
t := v.Interface().(time.Time)
return FormatDateTime(t)
//return t
//return strconv.F(v.())
}
return v.String()
}
// 转化字符形式如:AppRepo 转app_repo
func (q *PageUtil) ToLowerAndUnderline(str string) (byte *bytes.Buffer) {
byte = bytes.NewBufferString("")
for i := 0; i < len(str); i++ {
if i == 0 {
byte.WriteString(strings.ToLower(string(str[i])))
} else if str[i] >= 'A' && str[i] <= 'Z' {
byte.WriteString("_" + strings.ToLower(string(str[i])))
} else {
byte.WriteString(string(str[i]))
}
}
return
}
func FormatDateTime(t time.Time) string {
var buffer bytes.Buffer
buffer.WriteString(fmt.Sprintf("%.2d-%.2d-%.2d %.2d:%.2d:%.2d", t.Year(),t.Month(),t.Day(), t.Hour(), t.Minute(),t.Second()))
// fmt.Println(buffer.String())
return buffer.String()
}
直接
func (m *appDiskModel) PageList(p PageUtil) (PageUtil, error) {
// qb := mysqlBuilder().Select("T0.*,T3.name as namespace_name,(case when isnull(T1.id ) then 0 else 1 end ) as starred").From("app T0")
// 接口转对象
var AppDiskVar AppDisk
jsonbyte, _ := json.Marshal(p.Object)
json.Unmarshal(jsonbyte, &AppDiskVar)
AppDiskList := make([]AppDisk, 0)
p.Object = AppDiskVar
// 获取总行数
error := Ormer().Raw(p.GetCount()).QueryRow(&p.Total)
// 获取获取当前页list
_, error = Ormer().Raw(p.GetPageLimitSql()).QueryRows(&AppDiskList)
p.List = AppDiskList
return p, error
}