ipsomc/ipsomcapi/core/dao/mysql/mysqlapi/mysqlapi.go

// 数据库底层操作接口
// 秒寻科技
// zt 2024-01-04
package mysqlapi

import (
	"encoding/json"
	"errors"
	"fmt"
	"ipsomc/core/dao/mysql/mysqlmodel"
	"ipsomc/util"
	"reflect"
	"strings"
	"time"

	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/mysql"
)

type MySqlApi struct {
}

// 全局数据库操作对象
var (
	Db *gorm.DB
)

// 初始化数据库模块，创建一个数据连接对象
func OpenSqlDb() error {
	var err error

	myViper := util.GetViper()
	dbHostAddress := myViper.GetString("dbDefault.dbHost") //主机地址
	dbHostPort := myViper.GetString("dbDefault.dbPort")    //主机端口

	dbName := myViper.GetString("dbDefault.dbName")         //数据库名
	dbUser := myViper.GetString("dbDefault.dbUser")         //数据库用户名
	dbPassword := myViper.GetString("dbDefault.dbPassword") //数据库用户登录密码

	//组织dsn字符串
	dsn := dbUser + ":" + dbPassword + "@tcp(" + dbHostAddress + ":" + dbHostPort + ")/" + dbName + "?charset=utf8mb4&parseTime=True&loc=Local"

	//连接数据库
	for Db, err = gorm.Open("mysql", dsn); err != nil; {
		fmt.Println("连接数据库异常，之后自动重连")
		time.Sleep(5 * time.Second)
		Db, err = gorm.Open("mysql", dsn)
	}
	Db.LogMode(true)       //打开日志开关
	Db.SingularTable(true) //禁用数据库表名复数

	TimerConnectSqlDb(Db) //5秒定时连接数据库，以免连接断开

	return nil
}

// CloseDb 关闭数据
func CloseSqlDb() {
	Db.Close() //关闭数据
}

// GetDb  返回当前db对象
func (obj *MySqlApi) GetDb() *gorm.DB {
	return Db
}

// 创建数据库表，不会删除和修改数据库中的数据，只增加新新的表字段
func (obj *MySqlApi) GenerateDbTable(dataModel interface{}) {
	Db.AutoMigrate(dataModel)
}

// TimerConnectSqlDb 定时连接数据库
func TimerConnectSqlDb(db *gorm.DB) {
	go func() {
		myTiker := time.NewTicker(30 * time.Second)
		for range myTiker.C {
			var dtTime time.Time
			if err := db.Raw("select now() as time").Scan(&dtTime).Error; err != nil {
				fmt.Println(err)
			}
		}
	}()
}

// AddItem 增加数据库记录
func (obj *MySqlApi) AddItem(dataModel interface{}) error {
	if err := Db.Create(dataModel).Error; err != nil {
		return err
	}

	return nil
}

// UpdateItem 更新记录
func (obj *MySqlApi) UpdateItem(dataModel interface{}, dataMap map[string]interface{}) error {
	val := reflect.ValueOf(dataModel)
	if val.Kind() != reflect.Ptr {
		return errors.New("更新失败，函数参数检查错误，应为指针类型")
	}
	val = val.Elem()
	if val.Kind() != reflect.Struct {
		return errors.New("更新失败，函数参数检查错误，应为结构体指针类型")
	}
	id := val.FieldByName("ID").Int()
	if id == 0 {
		return errors.New("更新失败，请设置要更新的数据的ID值")
	}

	//更新数据
	if err := Db.Model(dataModel).Updates(dataMap).Error; err != nil {
		return err
	}
	return nil
}

// DeleteItem 删除记录
func (obj *MySqlApi) DeleteItem(dataModel interface{}) error {
	val := reflect.ValueOf(dataModel)
	if val.Kind() != reflect.Ptr {
		return errors.New("删除失败，函数参数检查错误，应为指针类型")
	}
	val = val.Elem()
	if val.Kind() != reflect.Struct {
		return errors.New("删除失败，函数参数检查错误，应为结构体指针类型")
	}

	id := val.FieldByName("ID").Int()
	if id == 0 {
		return errors.New("删除失败,请设置要删除的数据的ID值")
	}

	if err := Db.Where("owner_id <> 0").Delete(dataModel).Error; err != nil {
		return err
	}
	return nil
}

// GetItemByID 根据id获得记录数据
func (obj *MySqlApi) GetItemByID(dataModel interface{}) error {
	val := reflect.ValueOf(dataModel)
	if val.Kind() != reflect.Ptr {
		return errors.New("查询数据失败，函数参数检查错误，应为指针类型")
	}
	val = val.Elem()
	if val.Kind() != reflect.Struct {
		return errors.New("查询数据失败，函数参数检查错误，应为结构体指针类型")
	}

	id := val.FieldByName("ID").Int()
	if id == 0 {
		return errors.New("查询数据失败，请设置要查询的数据的ID值")
	}

	if err := Db.First(dataModel).Error; err != nil {
		return err
	}

	return nil
}

// 组织公司参数(公司ID和项目ID)
func (obj *MySqlApi) GenerateCompanyParam(db *gorm.DB, companyId int, projectId int) *gorm.DB {
	if companyId > 0 {
		db = db.Where("company_id=?", companyId)
	}

	if projectId > 0 {
		db = db.Where("project_id=?", projectId)
	}

	return db
}

// 组织search参数
func (obj *MySqlApi) GenerateSearchParam(db *gorm.DB, searchParam map[string]interface{}, dataMap map[string]interface{}) *gorm.DB {
	//组织Search参数
	for queryKey, value := range searchParam {
		queryKey = strings.ToLower(queryKey)
		for mapKey := range dataMap {
			mapKey = strings.ToLower(mapKey)
			if mapKey == queryKey {
				db = db.Where(queryKey+"=?", value)
			}
		}
	}

	return db
}

// 组织Query参数
func (obj *MySqlApi) GenerateQueryParam(db *gorm.DB, queryParam []mysqlmodel.QueryParam, dataMap map[string]interface{}) *gorm.DB {
	wLen := len(queryParam)
	for i := 0; i < wLen; i++ {
		obj := queryParam[i]
		for mapKey := range dataMap {
			mapKey = strings.ToLower(mapKey)
			queryKey := strings.ToLower(obj.Key)
			if mapKey == queryKey {
				if obj.Opt == "like" {
					db = db.Where(queryKey+"  like ?", fmt.Sprintf("%v", obj.Data)) //%通配在前端处理
				} else if obj.Opt == "in" {
					db = db.Where(queryKey+" in (?)", obj.Data)
				} else {
					db = db.Where(queryKey+obj.Opt+"?", obj.Data)
				}
			}
		}
	}

	return db
}

// 组织Order参数
func (obj *MySqlApi) GenerateOrderParam(db *gorm.DB, orderBy string) *gorm.DB {
	strOrderBy := orderBy
	if len(strOrderBy) > 0 {
		orderList := strings.Split(strOrderBy, ",")
		for _, item := range orderList {
			db = db.Order(item)
		}
	} else {
		db = db.Order("id") //默认按照ID排序
	}

	return db
}

// 组织查询条件where参数
func (obj *MySqlApi) GenerateWhereParam(dataModel interface{}, companyId int, projectId int, searchParam map[string]interface{}, queryParam []mysqlmodel.QueryParam, orderParam string) *gorm.DB {
	//空接口转map，以便判断查询字段是否在模型
	dataMap := make(map[string]interface{})
	byteList, _ := json.Marshal(dataModel)
	json.Unmarshal(byteList, &dataMap)

	db := obj.GetDb()
	db = obj.GenerateCompanyParam(db, companyId, projectId) //组织公司ID参数
	db = obj.GenerateSearchParam(db, searchParam, dataMap)  //组织Search参数
	db = obj.GenerateQueryParam(db, queryParam, dataMap)    //组织query参数
	db = obj.GenerateOrderParam(db, orderParam)             //组织排序参数

	return db
}

// 查询
func (obj *MySqlApi) GetItemList(dataModel interface{}, listParam *mysqlmodel.ListParam, dataList interface{}) error {
	val := reflect.ValueOf(dataModel)
	if val.Kind() != reflect.Ptr {
		return errors.New("查询数据失败，函数参数检查错误，应为指针类型")
	}
	val = val.Elem()
	if val.Kind() != reflect.Struct {
		return errors.New("查询数据失败，函数参数检查错误，应为结构体指针类型")
	}

	//组织查询条件
	db := obj.GenerateWhereParam(dataModel, listParam.CompanyID, listParam.ProjectID, listParam.Search, listParam.Query, listParam.OrderBy)

	//查询数据
	if err := db.Find(dataList).Error; err != nil {
		return errors.New("查询数据失败")
	}

	return nil
}

// 分页查询
func (obj *MySqlApi) GetPageItemList(dataModel interface{}, listParam *mysqlmodel.PageListParam, dataList interface{}) (mysqlmodel.PageListData, error) {
	pageListData := mysqlmodel.PageListData{}
	pageListData.PageNum = listParam.PageNum
	pageListData.PageSize = listParam.PageSize

	offset := (listParam.PageNum - 1) * listParam.PageSize
	pageSize := listParam.PageSize

	//组织查询条件
	db := obj.GenerateWhereParam(dataModel, listParam.CompanyID, listParam.ProjectID, listParam.Search, listParam.Query, listParam.OrderBy)

	//获得分页数据
	if err := db.Limit(pageSize).Offset(offset).Find(dataList).Error; err != nil {
		return pageListData, err
	}
	pageListData.Items = dataList

	//计算总记录数
	value := reflect.ValueOf(dataList).Elem()
	if listParam.PageNum == 1 && value.Len() < listParam.PageSize { //记录数不足一页
		pageListData.TotalCount = value.Len()
	} else {
		db.Model(dataModel).Count(&pageListData.TotalCount)
	}

	return pageListData, nil
}