property-garden/model/charge_bind.go

// Copyright 2019 getensh.com. All rights reserved.
// Use of this source code is governed by getensh.com.

package model

import (
	"database/sql"
	"fmt"
	"git.getensh.com/common/gopkgs/logger"
	"git.getensh.com/common/gopkgs/util"
	"go.uber.org/zap"
	"gorm.io/gorm"
	"time"
)

type TChargeBind struct {
	ID int64 `gorm:"column:id;PRIMARY_KEY" json:"id"`
	ObjId int64 `gorm:"column:obj_id" json:"obj_id"`
	ObjName string `gorm:"column:obj_name" json:"obj_name"`
	HouseId int64 `gorm:"column:house_id" json:"house_id"`
	ChargeId int64 `gorm:"column:charge_id" json:"charge_id"`
	Start int64 `gorm:"column:start" json:"start"`
	End int64 `gorm:"column:end" json:"end"`
	CreatedAt time.Time `gorm:"column:created_at" json:"created_at"`
	UpdatedAt time.Time `gorm:"column:updated_at" json:"updated_at"`
	ChargeType int32 `gorm:"column:charge_type" json:"charge_type"`
	UniqFlag sql.NullInt32 `gorm:"column:uniq_flag" json:"uniq_flag"`
	ObjType int32 `gorm:"column:obj_type" json:"obj_type"`
	CustomFee int64 `gorm:"column:custom_fee" json:"custom_fee"`
	BillLastTime int64 `gorm:"column:bill_last_time" json:"bill_last_time"`
	ObjArea float64 `gorm:"column:obj_area" json:"obj_area"`
	//ChargeBasis int32 `gorm:"column:charge_basis" json:"charge_basis"`
	table string
}

func (p *TChargeBind) TableName() string {
	return p.table
}

func NewChargeBind(database string) *TChargeBind {
	return &TChargeBind{table: fmt.Sprintf("%s.%s", database, "t_charge_bind")}
}
func (p *TChargeBind) SetTable(database string)  {
	p.table = fmt.Sprintf("%s.%s", database, "t_charge_bind")
}

func (p *TChargeBind) CreateTable(db *gorm.DB) error {
	sql := "CREATE TABLE IF NOT EXISTS " + p.TableName() + "(" +
	"	`id` bigint(11) NOT NULL AUTO_INCREMENT,"+
	"	`obj_id` bigint(11) NOT NULL COMMENT '房间id/车位id/车辆id',"+
	"`obj_name` varchar(16) COLLATE utf8mb4_bin NOT NULL COMMENT '房屋号/车位/车牌',"+
	"`house_id` bigint(11) NOT NULL COMMENT '房屋id',"+
	"`charge_id` int(10) NOT NULL COMMENT '费用项目id',"+
	"`start` bigint(11) NOT NULL COMMENT '计费开始时间戳',"+
	"`end` bigint(11) NOT NULL COMMENT '结束时间 车辆收费会用到',"+
	"`created_at` datetime NOT NULL,"+
	"	`updated_at` datetime NOT NULL,"+
	"	`charge_type` int(10) NOT NULL COMMENT '费用类型1 物业费 2 水费 3 电费 4 气费 99其他',"+
	"`uniq_flag` tinyint(1) DEFAULT NULL COMMENT '和其他字段做联合唯一索引1 不能重复 null 可以重复',"+
	"`obj_type` tinyint(1) NOT NULL COMMENT '1 房屋 2 车位 3 车辆',"+
	"`custom_fee` int(10) NOT NULL COMMENT '自定义费用',"+
	"`bill_last_time` bigint(11) NOT NULL COMMENT '账单上次生成时间',"+
	"`obj_area` decimal(18,2) NOT NULL COMMENT '房屋或车位面积',"+
		//"`charge_basis` tinyint(1) NOT NULL COMMENT '1 按房屋面积 2 按使用面积 3 按车位面积 4 按使用量 5 固定费用 6 自定义',"+
	"PRIMARY KEY (`id`) USING BTREE,"+
	"UNIQUE KEY `house_charge_id` (`obj_id`,`charge_id`) USING BTREE COMMENT '一个房屋和一个项目不能重复绑定',"+
	"UNIQUE KEY `house_charge_type` (`obj_id`,`charge_type`,`uniq_flag`) USING BTREE COMMENT ' 水电气，物业费，一个房屋和缴费类型不能重复绑定',"+
	"KEY `charge_id` (`charge_id`) USING BTREE"+
	") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPACT COMMENT='房屋的缴费项目';"
	err := db.Exec(sql).Error
	if err != nil {
		logger.Error("mysql",
			zap.String("sql", "create table "+p.TableName()),
			zap.String("fields", ""),
			zap.String("error", err.Error()))
	}
	return err
}

func (p *TChargeBind) Find(db *gorm.DB, where map[string]interface{}) error {
	cond, val, err := whereBuild(where)
	if err != nil {
		fields, _ := util.MarshalToString(where)
		logger.Error("mysql",
			zap.String("sql", "select from "+p.TableName()),
			zap.String("fields", fields),
			zap.String("error", err.Error()))
		return err
	}
	err = db.Table(p.TableName()).Where(cond, val...).First(p).Error
	if err != nil {
		fields, _ := util.MarshalToString(where)
		logger.Error("mysql",
			zap.String("sql", "select from "+p.TableName()),
			zap.String("fields", fields),
			zap.String("error", err.Error()))
	}

	return err
}

func (p *TChargeBind) AllNot(db *gorm.DB, not map[string]interface{}) (list []TChargeBind,err error) {
	err = db.Table(p.TableName()).Not(not).Find(&list).Error
	if err != nil {
		fields, _ := util.MarshalToString(not)
		logger.Error("mysql",
			zap.String("sql", "select from where not"+p.TableName()),
			zap.String("fields", fields),
			zap.String("error", err.Error()))
	}

	return list ,err
}

func (p *TChargeBind) Last(db *gorm.DB) error {
	err := db.Table(p.TableName()).Last(p).Error
	if err != nil {
		logger.Error("mysql",
			zap.String("sql", "select last from "+p.TableName()),
			zap.String("fields", ""),
			zap.String("error", err.Error()))
	}

	return err
}

// Insert 插入一条记录
func (p *TChargeBind) Insert(db *gorm.DB) error {
	err := db.Table(p.TableName()).Create(p).Error
	if err != nil {
		fields, _ := util.MarshalToString(*p)
		logger.Error("mysql",
			zap.String("sql", "insert into "+p.TableName()),
			zap.String("fields", fields),
			zap.String("error", err.Error()))
	}
	return err
}

func (p *TChargeBind) InsertMulti(db *gorm.DB, values interface{}) error {
	err := db.Table(p.TableName()).Create(values).Error
	if err != nil {
		fields, _ := util.MarshalToString(*p)
		logger.Error("mysql",
			zap.String("sql", "insert into "+p.TableName()),
			zap.String("fields", fields),
			zap.String("error", err.Error()))
	}
	return err
}

func (p *TChargeBind) Delete(db *gorm.DB, filter map[string]interface{}) error {
	cond, val, err := whereBuild(filter)
	if err != nil {
		return err
	}
	return db.Table(p.TableName()).Where(cond, val...).Delete(p).Error
}

func (p *TChargeBind) Update(db *gorm.DB, where map[string]interface{}, values map[string]interface{}) error {
	cond, val, err := whereBuild(where)
	if err != nil {
		if err != nil {
			fields, _ := util.MarshalToString(values)
			logger.Error("mysql",
				zap.String("sql", "update "+p.TableName()),
				zap.String("fields", fields),
				zap.String("error", err.Error()))
		}
		return err
	}

	return db.Table(p.TableName()).Where(cond, val...).Updates(values).Error
}

func (p *TChargeBind) UpdateByModel(db *gorm.DB) error {
	err := db.Model(p).Updates(p).Error
	if err != nil {
		fields, _ := util.MarshalToString(*p)
		logger.Error("mysql",
			zap.String("sql", "update "+p.TableName()),
			zap.String("fields", fields),
			zap.String("error", err.Error()))
	}
	return err
}

func (p *TChargeBind) Count(db *gorm.DB, where map[string]interface{}, or map[string]interface{}) (int64, error) {

	cond, val, err := whereBuildAndOr(where, or)
	if err != nil {
		return 0, err
	}
	ret := int64(0)
	err = db.Table(p.TableName()).Where(cond, val...).Count(&ret).Error
	if err != nil {
		fields, _ := util.MarshalToString(where)
		logger.Error("mysql",
			zap.String("sql", "select count "+p.TableName()),
			zap.String("fields", fields),
			zap.String("error", err.Error()))
	}

	return ret, err
}

type IdResult struct {
	Id int64
}

func (p *TChargeBind) GetIds(db *gorm.DB, where map[string]interface{}, or map[string]interface{}) ([]IdResult, error) {
	cond, val, err := whereBuildAndOr(where, or)
	if err != nil {
		return nil, err
	}

	array := []IdResult{}
	err = db.Table(p.TableName()).Select("id").Where(cond, val...).Find(&array).Error
	if err != nil {
		fields, _ := util.MarshalToString(where)
		logger.Error("mysql",
			zap.String("sql", "select id "+p.TableName()),
			zap.String("fields", fields),
			zap.String("error", err.Error()))
	}
	return array, nil
}

func (p *TChargeBind) List(db *gorm.DB, where map[string]interface{}, or map[string]interface{}, page int, pageSize int) (list []TChargeBind, err error) {

	if page >= 0 {
		return p.ListByJoin(db, where, or, page, pageSize)
	}
	cond, val, err := whereBuildAndOr(where, or)
	if err != nil {
		return list, err
	}

	result := db.Table(p.TableName()).Where(cond, val...).Find(&list)
	if result.Error != nil {
		wherefields, _ := util.MarshalToString(where)
		logger.Error("mysql",
			zap.String("sql", "select * from "+p.TableName()),
			zap.String("where", wherefields),
			zap.String("error", result.Error.Error()))
	}

	return list, result.Error

}

func (p *TChargeBind) ListByJoin(db *gorm.DB, where map[string]interface{}, or map[string]interface{}, page int, pageSize int) (list []TChargeBind, err error) {
	cond, val, err := whereBuildAndOr(where, or)
	if err != nil {
		return list, err
	}

	offset := (page - 1) * pageSize
	sql := fmt.Sprintf("select * from %s t1 join (select id from %s limit %d offset %d) t2 on t1.id=t2.id", p.TableName(), p.TableName(), pageSize, offset)
	if cond != "" {
		sql = fmt.Sprintf("select * from %s t1 join (select id from %s where %s limit %d offset %d) t2 on t1.id=t2.id", p.TableName(), p.TableName(), cond, pageSize, offset)
	}

	result := db.Raw(sql, val...).Scan(&list)
	if result.Error != nil {
		wherefields, _ := util.MarshalToString(where)
		logger.Error("mysql",
			zap.String("sql", "select * from "+p.TableName()),
			zap.String("where", wherefields),
			zap.String("error", result.Error.Error()))
	}

	return list, result.Error
}

func (p *TChargeBind)GetObjIds(db *gorm.DB, where map[string]interface{})([]int64, error) {
	cond, val, err := whereBuildAndOr(where, nil)
	if err != nil {
		return nil, err
	}
	type Result struct {
		ObjId int64
	}
	array := []Result{}
	err = db.Table(p.TableName()).Select("obj_id").Where(cond, val...).Find(&array).Error
	if err != nil {
		wherefields, _ := util.MarshalToString(where)
		logger.Error("mysql",
			zap.String("sql", "select house_id from "+p.TableName()),
			zap.String("where", wherefields),
			zap.String("error", err.Error()))
	}
	ret := make([]int64, len(array))
	for i, v := range array {
		ret[i] = v.ObjId
	}
	return ret, err
}