log-server/service/rentComputer/rent_computer.go

package rentComputer

import (
	"context"
	"errors"
	"github.com/xuri/excelize/v2"
	"go.uber.org/zap"
	"gorm.io/gorm"
	"log-server/global"
	"log-server/model/rentComputer"
	"log-server/model/rentComputer/request"
	"log-server/model/rentComputer/response"
	"math"
	"os"
	"strconv"
	"time"
)

type ServiceRentComputer struct {
}

func (s *ServiceRentComputer) RentComputerList(ctx context.Context, api rentComputer.RentComputer, info request.PageInfo, order string, desc bool) (interface{}, int64, error) {

	db := global.GVA_DB.Model(&rentComputer.RentComputer{})
	db = db.Select("rent_computer.id,rent_computer.update_time,rent_computer.create_time,rent_computer.pc_num,rent_computer.set_meal_id," +
		"rent_computer.pc_name,rent_computer.shop_id,rent_computer.rent_start,rent_computer.rent_duration,rent_computer.is_off_shelf,rent_computer.rent_end," +
		"rent_computer.remark,rent_computer.todesk_id,rent_computer.todesk_password,rent_computer.sunflower_id,rent_computer.sunflower_password,rent_computer.rent_price_used,rent_computer.is_expire," +
		"s.name as shop_name, r.name as set_meal_name,r.rent_price,r.price_type,r.rent_price_day")
	db = db.Joins("left join rent_computer_shop s on s.id = rent_computer.shop_id")
	db = db.Joins("left join rent_set_meal r on r.id = rent_computer.set_meal_id")

	if api.PcNum != "" {
		db = db.Where("rent_computer.pc_num = ?", api.PcNum)
	}
	if api.ShopId != 0 {
		db = db.Where("rent_computer.shop_id = ?", api.ShopId)
	}
	if api.SetMealId != 0 {
		db = db.Where("rent_computer.set_meal_id = ?", api.SetMealId)
	}
	//global.GVA_LOG.Info(strconv.Itoa(int(api.IsOffShelf)))
	//global.GVA_LOG.Info(strconv.Itoa(int(api.IsExpire)))
	if api.IsOffShelf != -1 {
		db = db.Where("rent_computer.is_off_shelf = ?", api.IsOffShelf)
	}
	if api.IsExpire != -1 {
		db = db.Where("rent_computer.is_expire = ?", api.IsExpire)
	}

	var total int64
	err := db.Count(&total).Error
	//if err != nil {
	//	return nil, 0, err
	//}
	limit := info.PageSize
	offset := info.PageSize * (info.Page - 1)
	//var statisticsLogs []*log.LogComputer
	var statisticscomputers []*response.ComputerStatisticsReply2
	db = db.Limit(limit).Offset(offset)
	if order != "" {
		var OrderStr string
		// 设置有效排序key 防止sql注入
		// 感谢 Tom4t0 提交漏洞信息
		orderMap := make(map[string]bool, 3)
		orderMap["pc_num"] = true
		//orderMap["game_id"] = true
		//orderMap["operator"] = true
		if orderMap[order] {
			if desc {
				OrderStr = order + " desc"
			} else {
				OrderStr = order
			}
		} else { // didn't matched any order key in `orderMap`
			global.GVA_LOG.Error("获取失败!", zap.Error(err))
			return statisticscomputers, total, err
		}
		err = db.Order(OrderStr).Find(&statisticscomputers).Error
	} else {
		err = db.Order("id").Find(&statisticscomputers).Error
	}
	if err != nil {
		return nil, 0, err
	}
	var statisticsLogsComputer []*response.ComputerStatisticsReply2

	for _, statisticsLog := range statisticscomputers {

		statisticscomputer := new(response.ComputerStatisticsReply2)
		statisticscomputer.Id = statisticsLog.Id
		statisticscomputer.UpdateTime = statisticsLog.UpdateTime
		statisticscomputer.CreateTime = statisticsLog.CreateTime
		statisticscomputer.PcNum = statisticsLog.PcNum
		statisticscomputer.PcName = statisticsLog.PcName
		statisticscomputer.ShopId = statisticsLog.ShopId
		statisticscomputer.RentStart = statisticsLog.RentStart
		statisticscomputer.RentDuration = statisticsLog.RentDuration
		statisticscomputer.RentEnd = statisticsLog.RentEnd
		statisticscomputer.Remark = statisticsLog.Remark
		statisticscomputer.TodeskId = statisticsLog.TodeskId
		statisticscomputer.TodeskPassword = statisticsLog.TodeskPassword
		statisticscomputer.SunflowerId = statisticsLog.SunflowerId
		statisticscomputer.SunflowerPassword = statisticsLog.SunflowerPassword
		statisticscomputer.RentPrice = statisticsLog.RentPrice
		statisticscomputer.RentPriceDay = statisticsLog.RentPriceDay
		statisticscomputer.RentPriceUsed = statisticsLog.RentPriceUsed
		statisticscomputer.IsExpire = statisticsLog.IsExpire
		statisticscomputer.SetMealId = statisticsLog.SetMealId
		statisticscomputer.PriceType = statisticsLog.PriceType
		statisticscomputer.IsOffShelf = statisticsLog.IsOffShelf
		statisticscomputer.ShopName = statisticsLog.ShopName
		statisticscomputer.SetMealName = statisticsLog.SetMealName
		statisticsLogsComputer = append(statisticsLogsComputer, statisticscomputer)
	}
	return statisticsLogsComputer, total, err
}

func (s *ServiceRentComputer) GetRentComputerNum() int64 {
	var total int64

	db := global.GVA_DB.Model(&rentComputer.RentComputer{})
	db = db.Distinct("id")
	db = db.Where("is_off_shelf = 0 and is_expire != 1")
	_ = db.Count(&total).Error
	return total
}

func (s *ServiceRentComputer) AddRentComputer(requestCoding request.ComputerRequest) (err error) {
	if !errors.Is(global.GVA_DB.Where("pc_num = ? and shop_id = ?", requestCoding.PcNum, requestCoding.ShopId).First(&rentComputer.RentComputer{}).Error, gorm.ErrRecordNotFound) {
		return errors.New("租机编号已存在")
	}
	computer := new(rentComputer.RentComputer)
	//computer.Id = requestCoding.Id
	computer.PcNum = requestCoding.PcNum
	computer.PcName = requestCoding.PcName
	computer.ShopId = requestCoding.ShopId
	computer.RentStart = requestCoding.RentStart
	computer.RentDuration = requestCoding.RentDuration
	computer.RentEnd = requestCoding.RentEnd
	computer.Remark = requestCoding.Remark
	computer.TodeskId = requestCoding.TodeskId
	computer.TodeskPassword = requestCoding.TodeskPassword
	computer.SunflowerId = requestCoding.SunflowerId
	computer.SunflowerPassword = requestCoding.SunflowerPassword
	computer.RentPriceUsed = requestCoding.RentPriceUsed
	computer.IsExpire = requestCoding.IsExpire
	computer.SetMealId = requestCoding.SetMealId
	computer.IsOffShelf = requestCoding.IsOffShelf
	computer.CreateTime = time.Now().Format("2006-01-02 15:04:05")
	computer.UpdateTime = time.Now().Format("2006-01-02 15:04:05")
	//key := codeListCacheKey
	//_ = s.cache.DelBatheHsCache(context.Background(), key)
	return global.GVA_DB.Create(&computer).Error
}

func (s *ServiceRentComputer) GetRentComputerById(id int) (computerShop response.ComputerStatisticsReply2, err error) {
	db := global.GVA_DB.Model(&rentComputer.RentComputer{})
	var api response.ComputerStatisticsReply2
	db = db.Select("rent_computer.id,rent_computer.update_time,rent_computer.create_time,rent_computer.pc_num,rent_computer.set_meal_id," +
		"rent_computer.pc_name,rent_computer.shop_id,rent_computer.rent_start,rent_computer.rent_duration,rent_computer.is_off_shelf,rent_computer.rent_end," +
		"rent_computer.remark,rent_computer.todesk_id,rent_computer.todesk_password,rent_computer.sunflower_id,rent_computer.sunflower_password,rent_computer.rent_price_used,rent_computer.is_expire," +
		"s.name as shop_name, r.name as set_meal_name,r.rent_price,r.price_type,r.rent_price_day")
	db = db.Joins("left join rent_computer_shop s on s.id = rent_computer.shop_id")
	db = db.Joins("left join rent_set_meal r on r.id = rent_computer.set_meal_id")
	err = db.Where("rent_computer.id = ?", id).Order(id).Limit(1).Find(&api).Error
	if err != nil {
		return
	}
	computerShop.Id = api.Id
	computerShop.PcNum = api.PcNum
	computerShop.PcName = api.PcName
	computerShop.RentStart = api.RentStart
	computerShop.RentDuration = api.RentDuration
	computerShop.RentEnd = api.RentEnd
	computerShop.TodeskId = api.TodeskId
	computerShop.TodeskPassword = api.TodeskPassword
	computerShop.SunflowerId = api.SunflowerId
	computerShop.SunflowerPassword = api.SunflowerPassword
	computerShop.RentPrice = api.RentPrice
	computerShop.RentPriceDay = api.RentPriceDay
	computerShop.RentPriceUsed = api.RentPriceUsed
	computerShop.IsExpire = api.IsExpire
	computerShop.SetMealId = api.SetMealId
	computerShop.PriceType = api.PriceType
	computerShop.IsOffShelf = api.IsOffShelf
	global.GVA_LOG.Info(strconv.Itoa(api.IsOffShelf))
	global.GVA_LOG.Info(strconv.Itoa(computerShop.IsOffShelf))
	computerShop.ShopName = api.ShopName
	computerShop.SetMealName = api.SetMealName
	computerShop.Remark = api.Remark
	computerShop.ShopId = api.ShopId
	computerShop.UpdateTime = api.UpdateTime
	computerShop.CreateTime = api.CreateTime
	return
}

func (s *ServiceRentComputer) EditRentComputer(computer rentComputer.RentComputer) (err error) {

	err = global.GVA_DB.Save(computer).Error
	return err
}

func (s *ServiceRentComputer) DeleteRentComputerByIds(ids request.IdsReq) (err error) {
	err = global.GVA_DB.Delete(&[]rentComputer.RentComputer{}, "id in ?", ids.Ids).Error
	return err
}

func (s *ServiceRentComputer) DeleteRentComputerById(api rentComputer.RentComputer) (err error) {
	var entity rentComputer.RentComputer
	//global.GVA_LOG.Info(strconv.Itoa(int(api.Id)))
	err = global.GVA_DB.Where("id = ?", api.Id).First(&entity).Error // 根据id查询api记录
	if errors.Is(err, gorm.ErrRecordNotFound) {                      // api记录不存在
		return err
	}
	return global.GVA_DB.Delete(&entity).Error
}

func (exa *ServiceRentComputer) ParseExcel2InfoList(filePath string) (err error) {
	skipHeader := true
	fixedHeader := []string{"租机编码", "供应商", "所属套餐", "套餐类型", "起租日", "到期日", "todesk号", "todesk密码", "向日葵号", "向日葵密码"}
	file, err := excelize.OpenFile(filePath)
	if err != nil {
		return err
	}
	var insertComputers []*rentComputer.RentComputer
	//var updateComputers []*rentComputer.RentComputer
	rows, err := file.Rows("Sheet1")
	if err != nil {
		return err
	}
	//查询数据库里的套餐与供应商=====================================
	var setMeals []*response.SetMealStatisticsReply1
	db := global.GVA_DB.Model(&rentComputer.RentSetMeal{})
	db = db.Select("rent_set_meal.id, rent_set_meal.name," +
		"rent_set_meal.price_type,rent_set_meal.shop_id,s.name as shop_name")
	db = db.Joins("left join rent_computer_shop s on s.id = rent_set_meal.shop_id")
	err = db.Order("id").Find(&setMeals).Error
	if err != nil {
		return err
	}
	// =========================================================
	index := 0
	for rows.Next() {
		index++
		row, err := rows.Columns()
		if err != nil {
			return err
		}
		if skipHeader {
			if exa.compareStrSlice(row, fixedHeader) {
				skipHeader = false
				continue
			} else {
				return errors.New("Excel格式错误")
			}
		}

		c := new(rentComputer.RentComputer)
		c.PcNum = row[0]
		c.PcName = row[0]
		flagShopId := false
		flagSetMealName := false
		for _, setMeal := range setMeals {
			if row[1] == setMeal.ShopName {
				flagShopId = true
				c.ShopId = setMeal.ShopId
				//break
			}
			priceType := 0
			if row[3] == "天卡" {
				priceType = 0
			} else if row[3] == "周卡" {
				priceType = 1
			} else {
				priceType = 2
			}
			if row[2] == setMeal.Name && priceType == setMeal.PriceType {
				flagSetMealName = true
				c.SetMealId = setMeal.Id
				break
			}
		}
		if flagShopId == false || flagSetMealName == false {
			return errors.New("供应商错误")
		}

		rentStart, _ := time.ParseInLocation("2006-01-02 15:04:05", row[4]+" 00:00:00", time.Local)
		rentEnd, _ := time.ParseInLocation("2006-01-02 15:04:05", row[5]+" 00:00:00", time.Local)
		//global.GVA_LOG.Info(strconv.FormatInt(rentStart.Unix(), 10))
		//global.GVA_LOG.Info(strconv.FormatInt(rentEnd.Unix(), 10))
		c.RentStart = row[4] + " 00:00:00"
		c.RentEnd = row[5] + " 00:00:00"
		c.RentDuration = int(math.Abs(float64(SubDays(rentStart, rentEnd)))) + 1
		c.IsExpire = 0
		c.IsOffShelf = 0
		// 获取工作表中指定单元格的值
		todeskId, _ := file.GetCellValue("Sheet1", "G"+strconv.Itoa(index))
		todeskPassword, _ := file.GetCellValue("Sheet1", "H"+strconv.Itoa(index))
		sunflowerId, _ := file.GetCellValue("Sheet1", "I"+strconv.Itoa(index))
		sunflowerPassword, _ := file.GetCellValue("Sheet1", "J"+strconv.Itoa(index))
		c.TodeskId = todeskId                   //todesk账号密码
		c.TodeskPassword = todeskPassword       //todesk账号密码
		c.SunflowerId = sunflowerId             //向日葵账号密码
		c.SunflowerPassword = sunflowerPassword //向日葵账号密码

		c.CreateTime = time.Now().Format("2006-01-02 15:04:05")
		c.UpdateTime = time.Now().Format("2006-01-02 15:04:05")
		// 租机编号已存在,存入更新数组中
		if !errors.Is(global.GVA_DB.Where("pc_num = ? and shop_id = ?", c.PcNum, c.ShopId).First(&rentComputer.RentComputer{}).Error, gorm.ErrRecordNotFound) {
			// 租机编号已存在,存入更新数组中
			//updateComputers = append(updateComputers, c)
			continue
		} else {
			insertComputers = append(insertComputers, c)
		}

	}
	defer func() {
		global.GVA_LOG.Info("defer 删除文件")
		err = os.RemoveAll(filePath)
		if err != nil {
			return
		}
	}()

	//if updateComputers != nil {
	//	err = global.GVA_DB.Updates(updateComputers).Error
	//	if err != nil {
	//		return
	//	}
	//}
	if insertComputers != nil {
		err = global.GVA_DB.Create(insertComputers).Error
		if err != nil {
			return
		}
	}

	//var cp log.Computer
	//err = cp.DelAllOnlinePcCodeCache()
	return
}

func (exa *ServiceRentComputer) compareStrSlice(a, b []string) bool {
	if len(a) != len(b) {
		return false
	}
	if (b == nil) != (a == nil) {
		return false
	}
	for key, value := range a {
		if value != b[key] {
			return false
		}
	}
	return true
}

// CheckIsExpire 定时检查是否有电脑到期，修改租机状态/*
func (s *ServiceRentComputer) CheckIsExpire() {
	global.GVA_LOG.Info("检查是否有电脑到期开始执行:" + time.Now().Format("2006-01-02 15:04:05"))
	db := global.GVA_DB.Model(&rentComputer.RentComputer{})
	//SELECT pc_num,rent_start,rent_end,is_expire FROM shuyou_rent_computer
	db = db.Where("is_off_shelf = 0")
	var computers []*rentComputer.RentComputer
	err := db.Order("id").Find(&computers).Error
	if err != nil {
		global.GVA_LOG.Error("检查是否有电脑到期执行失败:" + time.Now().Format("2006-01-02 15:04:05"))
		return
	}
	const overtime = 24 * 60 * 60
	nowUnix := time.Now().Unix()

	//global.GVA_LOG.Info(strconv.FormatInt(nowUnix, 10))
	//global.GVA_LOG.Info("---------------------------------------------------------------------------------")
	computer := new(rentComputer.RentComputer)
	for _, c := range computers {
		endUnix, _ := time.ParseInLocation("2006-01-02 15:04:05", c.RentEnd, time.Local)
		//global.GVA_LOG.Info(strconv.FormatInt(endUnix.Unix(), 10))
		if endUnix.Unix() < nowUnix {
			computer.IsExpire = 1
		} else if endUnix.Unix()-nowUnix <= overtime {
			computer.IsExpire = 2
		} else {
			computer.IsExpire = 0
		}
		if c.IsExpire == computer.IsExpire {
			continue
		}
		computer.Id = c.Id
		computer.UpdateTime = c.UpdateTime
		computer.CreateTime = c.CreateTime
		computer.PcNum = c.PcNum
		computer.PcName = c.PcName
		computer.ShopId = c.ShopId
		computer.RentStart = c.RentStart
		computer.RentDuration = c.RentDuration
		computer.RentEnd = c.RentEnd
		computer.Remark = c.Remark
		computer.TodeskId = c.TodeskId
		computer.TodeskPassword = c.TodeskPassword
		computer.SunflowerId = c.SunflowerId
		computer.SunflowerPassword = c.SunflowerPassword
		computer.RentPriceUsed = c.RentPriceUsed
		computer.SetMealId = c.SetMealId
		computer.IsOffShelf = c.IsOffShelf

		err = global.GVA_DB.Save(computer).Error
	}
	global.GVA_LOG.Info("检查是否有电脑到期执行成功:" + time.Now().Format("2006-01-02 15:04:05"))
	return
}

// SubDays 计算日期相差多少天
// 返回值day>0, t1晚于t2; day<0, t1早于t2
func SubDays(t1, t2 time.Time) (day int) {
	swap := false
	if t1.Unix() < t2.Unix() {
		t_ := t1
		t1 = t2
		t2 = t_
		swap = true
	}
	//global.GVA_LOG.Info(strconv.Itoa(int(t1.Unix())))
	//global.GVA_LOG.Info(strconv.Itoa(int(t2.Unix())))
	day = int(t1.Sub(t2).Hours() / 24)
	// 计算在被24整除外的时间是否存在跨自然日
	if int(t1.Sub(t2).Milliseconds())%86400000 > int(86400000-t2.Unix()%86400000) {
		day += 1
	}
	if swap {
		day = -day
	}
	//global.GVA_LOG.Info(strconv.Itoa(day))
	return
}