postgres.go

package postgres

import (
	"ai_developer_admin/models"
	"encoding/json"
	"fmt"
	"github.com/beego/beego/orm"
	"github.com/beego/beego/v2/core/logs"
	beego "github.com/beego/beego/v2/server/web"
	_ "github.com/lib/pq"
)

var ormpost orm.Ormer

func GetOrmer() orm.Ormer {
	return ormpost
}

func init() {
	//return
	logs.Debug("postgres lib init")
	orm.Debug = true
	if err := orm.RegisterDriver("postgres", orm.DRPostgres); err != nil {
		logs.Error(err.Error())
	}

	//orm.RegisterModel(new(models.Funds))
	//orm.RegisterModel(new(models.Tasks))
	//orm.RegisterModel(new(models.ChargeRecord))
	//orm.RegisterModel(new(models.UserLevel))
	//orm.RegisterModel(new(models.ApiKey))
	//orm.RegisterModel(new(models.JwtToken))

	logs.Debug("AppConfig", beego.AppConfig)

	dbuser, _ := beego.AppConfig.String("postgresuser")
	dbpass, _ := beego.AppConfig.String("postgrespass")
	dbhost, _ := beego.AppConfig.String("postgreshost")
	dbport, _ := beego.AppConfig.Int("postgresport")
	dbname, _ := beego.AppConfig.String("postgresdb")
	logs.Debug("postgres info", dbuser, dbpass, dbhost, dbport, dbname)

	dbURL := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", dbhost, dbport, dbuser, dbpass, dbname)
	logs.Debug("Will connect to postgres url", dbURL)

	if err := orm.RegisterDataBase(dbname, "postgres", dbURL); err != nil {
		logs.Error(err.Error())
		panic(err.Error())
	}
	orm.SetMaxIdleConns(dbname, 10)
	orm.SetMaxOpenConns(dbname, 100)

	db, err := orm.GetDB(dbname)
	if err != nil {
		panic(err)
	}
	ormpost, err = orm.NewOrmWithDB("postgres", dbname, db)
	if err != nil {
		panic(err)
	}
	createFundTable()
}

func createFundTable() {
	sql := "CREATE TABLE IF NOT EXISTS \n'funds' (\n  id LONG ,\n  status int,\n  uid  int,\n  order_id SYMBOL CAPACITY 128 CACHE INDEX CAPACITY 8192,\n  trade_flow int,\n  trade_time TIMESTAMP,\n  trade_type INT,\n  trade_channel int,\n  channel_serial SYMBOL CAPACITY 128 CACHE INDEX CAPACITY 8192,\n  amount LONG,\n  balance LONG,\n  remark SYMBOL CAPACITY 128 CACHE INDEX CAPACITY 8192\n) timestamp (trade_time) PARTITION BY DAY WAL;"
	qs := ormpost.Raw(sql)
	_, err := qs.Exec()
	if err != nil {
		logs.Debug("createFundTable", err.Error())
	}
}

func QueryTset(sql string, args ...interface{}) ([]models.Bills, error) {
	logs.Debug("QueryBills = ", sql)
	qs := ormpost.Raw(sql, args)
	var params []orm.Params
	_, err := qs.Values(&params)
	if err != nil {
		return nil, err
	}
	arr, err := json.Marshal(params)
	if err != nil {
		return nil, err
	}
	var containers []models.Bills
	logs.Debug("QueryBills = ", string(arr))
	err = json.Unmarshal(arr, &containers)
	if err != nil {
		return nil, err
	}

	return containers, nil
}

func QueryBills(sql string) ([]models.Bills, error) {
	logs.Debug("QueryBills = ", sql)
	qs := ormpost.Raw(sql)
	var params []orm.Params
	_, err := qs.Values(&params)
	if err != nil {
		return nil, err
	}
	arr, err := json.Marshal(params)
	if err != nil {
		return nil, err
	}
	var containers []models.Bills
	logs.Debug("QueryBills = ", string(arr))
	err = json.Unmarshal(arr, &containers)
	if err != nil {
		return nil, err
	}

	return containers, nil
}

func QueryFunds(sql string) ([]models.Funds, error) {
	logs.Debug("QueryFunds = ", sql)
	qs := ormpost.Raw(sql)
	var params []orm.Params
	_, err := qs.Values(&params)
	if err != nil {
		return nil, err
	}
	arr, err := json.Marshal(params)
	if err != nil {
		return nil, err
	}
	var containers []models.Funds
	logs.Debug("QueryFunds = ", string(arr))
	err = json.Unmarshal(arr, &containers)
	if err != nil {
		return nil, err
	}

	return containers, nil
}

func CountFunds(sql string) ([]models.IncomeAndExpenseRsponse, error) {
	logs.Debug("CountFunds = ", sql)
	qs := ormpost.Raw(sql)
	var params []orm.Params
	_, err := qs.Values(&params)
	if err != nil {
		return nil, err
	}
	arr, err := json.Marshal(params)
	if err != nil {
		return nil, err
	}
	var containers []models.IncomeAndExpenseRsponse
	logs.Debug("CountFunds = ", string(arr))
	err = json.Unmarshal(arr, &containers)
	if err != nil {
		return nil, err
	}

	return containers, nil
}

func QueryTotal(sql string) (int64, error) {
	logs.Debug("QueryBills = ", sql)
	var count int64
	_ = ormpost.Raw(sql).QueryRow(&count)

	//_, err := qs.Values(&params)
	//if err != nil {
	//	return 0, err
	//}
	//arr, err := json.Marshal(params)
	//if err != nil {
	//	return 0, err
	//}
	//var containers models.TotalType
	//logs.Debug("QueryTotal = ", string(arr))
	//err = json.Unmarshal(arr, &containers)
	//if err != nil {
	//	return 0, err
	//}
	//dataType := reflect.TypeOf(containers.Count)
	//
	//count := int64(0)
	// 判断变量的类型是否为字符串
	//if dataType.Kind() == reflect.String {
	//	num, err := strconv.ParseInt(containers.Count, 10, 64)
	//	if err == nil {
	//		count = num
	//	}
	//}
	return count, nil
}

func InsertFunds(funds *models.Funds) (int64, error) {
	return ormpost.Insert(funds)
}

func InsertWithSql(sql string) (int64, error) {
	logs.Debug("InsertWithSql = %s", sql)
	_, err := ormpost.Raw(sql).Exec()
	if err != nil {
		return 0, err
	}
	return 0, nil
}

func CountTasks(sql string) ([]models.TaskCount, error) {
	logs.Debug("CountTasks = ", sql)
	qs := ormpost.Raw(sql)
	var params []orm.Params
	_, err := qs.Values(&params)
	if err != nil {
		return nil, err
	}
	arr, err := json.Marshal(params)
	if err != nil {
		return nil, err
	}
	var containers []models.TaskCount
	err = json.Unmarshal(arr, &containers)
	if err != nil {
		return nil, err
	}

	return containers, nil
}