xugu_license/internal/models/license_upfile_models.go

package models

import (
	"database/sql"
	"fmt"
	"time"
	"xugu_license/internal/global"

	"github.com/google/uuid"
)

type LicenseApplication struct {
	Creator                   string         // 创建人
	ApplicationDate           string         //申请日期
	AssociatedProject         string         // 关联项目
	SalesPerson               string         //销售人员
	SalesEmail                string         // 销售接收邮箱
	SupportPerson             string         //技服人员
	SupportEmail              string         // 技服接收邮箱
	TotalNodes                string         //总节点数
	Company                   string         //使用单位
	ProductName               string         // 产品名称
	Version                   string         //版本
	NodeCount                 string         //节点数
	Processor                 string         //处理器
	OperatingSystem           string         //操作系统
	MasterMacAddress          string         // 主MasterMac地址
	SecondaryMasterMacAddress sql.NullString // 副MasterMac地址

}

type LicenseInfo struct {
	ID           int          `json:"id"`
	UserID       int          `json:"user_id"`
	UpUser       string       `json:"up_user"`
	UpTime       sql.NullTime `json:"up_time"`
	DelTime      sql.NullTime `json:"del_time"`
	License1     sql.NullString
	License2     sql.NullString
	LicenseFlage string
	UniqueID     string
	LicenseApplication
}

// 删除LicenseApplication和LicenseInfo表中的数据
// func DelLicenseApplicationAndInfoRow(uniqueID string) error {
// 	fmt.Println("UniqueID12313", uniqueID)
// 	tx, err := global.XuguDB.Begin()
// 	if err != nil {
// 		return fmt.Errorf("begin transaction: %v", err)
// 	}

// 	defer func() {
// 		if err != nil {
// 			tx.Rollback()
// 		} else {
// 			err = tx.Commit()
// 		}
// 	}()
// 	//	"update LicenseApplication set DelTime = ? where UniqueID = ?",
// 	//DELETE FROM LicenseInfo WHERE LicenseApplicationUniqueID = ?
// 	// _, err = tx.Exec("DELETE FROM LicenseInfo WHERE LicenseApplicationUniqueID = ?", uniqueID)
// 	// if err != nil {
// 	// 	return err
// 	// }

// 	// _, err = tx.Exec("DELETE FROM LicenseApplication WHERE UniqueID = ?", uniqueID)
// 	// if err != nil {
// 	// 	return err
// 	// }
// 	_, err = tx.Exec("UPDATE LicenseApplication SET DelTime = ?  WHERE UniqueID = ?", time.Now(), uniqueID)
// 	if err != nil {
// 		return err
// 	}
// 	return nil
// }

func InsertLicenseApplicationAndInfoRow(licenseInfo LicenseApplication, userId int, userName string) error {
	tx, err := global.XuguDB.Begin()
	if err != nil {
		return fmt.Errorf("begin transaction: %v", err)
	}

	defer func() {
		if err != nil {
			tx.Rollback()
		} else {
			err = tx.Commit()
		}
	}()
	// 插入 LicenseApplication 表
	uniqueID := uuid.New()
	insertLicenseApplication := `
        INSERT INTO LicenseApplication (
            UniqueID, UserId, UpUser, UpTime, DelTime,
			 Creator, ApplicationDate,  AssociatedProject, SalesPerson, SalesEmail, 
			 SupportPerson, SupportEmail,TotalNodes, Company,ProductName, Version,
			NodeCount, Processor, OperatingSystem,MasterMacAddress, SecondaryMasterMacAddress
        ) VALUES (
            ?, ?, ?, ?, ?,
			?, ?, ?, ?, ?,
			?, ?, ?, ?, ?, 
			?, ?, ?, ?, ?,?
        )`
	_, err = tx.Exec(insertLicenseApplication,
		uniqueID, userId, userName, time.Now(), nil,
		licenseInfo.Creator, licenseInfo.ApplicationDate, licenseInfo.AssociatedProject,
		licenseInfo.SalesPerson, licenseInfo.SalesEmail, licenseInfo.SupportPerson,
		licenseInfo.SupportEmail, licenseInfo.TotalNodes, licenseInfo.Company, licenseInfo.ProductName,
		licenseInfo.Version, licenseInfo.NodeCount, licenseInfo.Processor,
		licenseInfo.OperatingSystem, licenseInfo.MasterMacAddress,
		licenseInfo.SecondaryMasterMacAddress)

	if err != nil {
		return err
	}

	//插入 LicenseInfo 表
	_, err = tx.Exec(`
	    INSERT INTO LicenseInfo (
	        LicenseApplicationUniqueID, LicenseFlage
	    ) VALUES (?, ?)
	`,
		uniqueID, "未生成")
	if err != nil {
		fmt.Println("InsertLicenseApplicationRow", err)
		return err
	}
	return nil
}

// 获取所有或单个license信息，或者指定某个用户能查询到的所有license信息
func GetLicenseInfoAllOrSingle(page int, pageSize int, uniqueID string, userName string) ([]LicenseInfo, int, error) {
	offset := (page - 1) * pageSize
	// 查询总记录数
	var total int
	err := global.XuguDB.QueryRow(`SELECT COUNT(*) FROM LicenseApplication WHERE DelTime IS NULL`).Scan(&total)
	if err != nil {
		return nil, 0, fmt.Errorf("count query error: %v", err)
	}
	var query string
	if uniqueID != "" {
		query = fmt.Sprintf(`SELECT 
			 la.id,
			 la.UniqueID,
			 la.UserId,
			 la.UpUser,
			 la.UpTime,
			 la.DelTime,
			 la.Creator,
			 la.ApplicationDate,
			 la.AssociatedProject,
			 la.SalesPerson,
			 la.SalesEmail,
			 la.SupportPerson,
			 la.SupportEmail,
			 la.TotalNodes,
			 la.Company,
			 la.ProductName,
			 la.Version,
			 la.NodeCount,
			 la.Processor,
			 la.OperatingSystem,
			 la.MasterMacAddress,
			 la.SecondaryMasterMacAddress,
			 li.lic1,
			 li.lic2
		 FROM 
			 LicenseApplication la
		 INNER JOIN 
			 LicenseInfo li
		 ON 
			 la.UniqueID = li.LicenseApplicationUniqueID
		  WHERE 
				 la.DelTime IS NULL
			AND la.UniqueID = '%s'  LIMIT ? OFFSET ?`, uniqueID)

	} else if userName != "" { //使用用户名查询

		err := global.XuguDB.QueryRow(`SELECT COUNT(*) FROM licenseRecordToUser 	lr WHERE lr.userUNIQUEID LIKE '%%%s%%'`, userName).Scan(&total)
		if err != nil {
			global.Logger.Errorln("查询总数失败  ", err.Error())
			return nil, 0, fmt.Errorf("查询总数失败: %v", err)
		}

		query = fmt.Sprintf(`
				SELECT la.*,li.lic1, li.lic2, lr.upTime
				FROM LicenseApplication la
				JOIN licenseRecordToUser lr ON la.UniqueID = lr.LicenseUniqueID
				JOIN LicenseInfo li ON la.UniqueID = li.LicenseApplicationUniqueID
				WHERE lr.userUNIQUEID LIKE '%%%s%%' and la.deltime is null
				LIMIT ? OFFSET ?`, userName)

		rows, err := global.XuguDB.Query(query, pageSize, offset)
		if err != nil {

			global.Logger.Errorln("查询数据失败 ", err.Error())
			return nil, 0, fmt.Errorf("query error: %v", err)
		}
		defer rows.Close()
		var results []LicenseInfo
		for rows.Next() {
			var li LicenseInfo

			err = rows.Scan(
				&li.ID,
				&li.UniqueID,
				&li.UserID,
				&li.UpUser,
				&li.UpTime,
				&li.DelTime,
				&li.LicenseApplication.Creator,
				&li.LicenseApplication.ApplicationDate,
				&li.LicenseApplication.AssociatedProject,
				&li.LicenseApplication.SalesPerson,
				&li.LicenseApplication.SalesEmail,
				&li.LicenseApplication.SupportPerson,
				&li.LicenseApplication.SupportEmail,
				&li.LicenseApplication.TotalNodes,
				&li.LicenseApplication.Company,
				&li.LicenseApplication.ProductName,
				&li.LicenseApplication.Version,
				&li.LicenseApplication.NodeCount,
				&li.LicenseApplication.Processor,
				&li.LicenseApplication.OperatingSystem,
				&li.LicenseApplication.MasterMacAddress,
				&li.LicenseApplication.SecondaryMasterMacAddress,
				&li.License1,
				&li.License2,
				&li.UpTime,
			)

			if err != nil {
				global.Logger.Errorln("数据查询失败： ", err.Error())
				//	return nil, fmt.Errorf("scan row: %v", err)
				return nil, 0, fmt.Errorf("scan row error: %v", err)
			}
			results = append(results, li)
		}
		if err = rows.Err(); err != nil {
			global.Logger.Errorln("rows数据查询失败： ", err.Error())
			return nil, 0, fmt.Errorf("rows error: %v", err)
		}

		// fmt.Printf("\n  usernaame %s\n", userName)
		// fmt.Printf("\n  LIMIT ? OFFSET ?`, %d,%d\n", pageSize, offset)
		// fmt.Printf("\nresults123 %#v\n", results)
		return results, total, nil

	} else {
		query = `
		SELECT 
			 la.id,
			 la.UniqueID,
			 la.UserId,
			 la.UpUser,
			 la.UpTime,
			 la.DelTime,
			 la.Creator,
			 la.ApplicationDate,
			 la.AssociatedProject,
			 la.SalesPerson,
			 la.SalesEmail,
			 la.SupportPerson,
			 la.SupportEmail,
			 la.TotalNodes,
			 la.Company,
			 la.ProductName,
			 la.Version,
			 la.NodeCount,
			 la.Processor,
			 la.OperatingSystem,
			 la.MasterMacAddress,
			 la.SecondaryMasterMacAddress,
			 li.Lic1,
			 li.Lic2,
			 li.LicenseFlage
		 FROM 
			 LicenseApplication la
		 INNER JOIN 
			 LicenseInfo li
		 ON 
			 la.UniqueID = li.LicenseApplicationUniqueID
		  WHERE 
				 la.DelTime IS NULL
			 LIMIT ? OFFSET ?
	 `
	}

	rows, err := global.XuguDB.Query(query, pageSize, offset)
	if err != nil {
		global.Logger.Errorln("数据查询失败： ", err.Error())
		return nil, 0, fmt.Errorf("query error: %v", err)
	}
	defer rows.Close()

	var results []LicenseInfo
	for rows.Next() {
		var li LicenseInfo
		if uniqueID != "" {
			err = rows.Scan(
				&li.ID,
				&li.UniqueID,
				&li.UserID,
				&li.UpUser,
				&li.UpTime,
				&li.DelTime,
				&li.LicenseApplication.Creator,
				&li.LicenseApplication.ApplicationDate,
				&li.LicenseApplication.AssociatedProject,
				&li.LicenseApplication.SalesPerson,
				&li.LicenseApplication.SalesEmail,
				&li.LicenseApplication.SupportPerson,
				&li.LicenseApplication.SupportEmail,
				&li.LicenseApplication.TotalNodes,
				&li.LicenseApplication.Company,
				&li.LicenseApplication.ProductName,
				&li.LicenseApplication.Version,
				&li.LicenseApplication.NodeCount,
				&li.LicenseApplication.Processor,
				&li.LicenseApplication.OperatingSystem,
				&li.LicenseApplication.MasterMacAddress,
				&li.LicenseApplication.SecondaryMasterMacAddress,

				&li.License1,
				&li.License2,
			)
		} else {
			err = rows.Scan(
				&li.ID,
				&li.UniqueID,
				&li.UserID,
				&li.UpUser,
				&li.UpTime,
				&li.DelTime,
				&li.LicenseApplication.Creator,
				&li.LicenseApplication.ApplicationDate,
				&li.LicenseApplication.AssociatedProject,
				&li.LicenseApplication.SalesPerson,
				&li.LicenseApplication.SalesEmail,
				&li.LicenseApplication.SupportPerson,
				&li.LicenseApplication.SupportEmail,
				&li.LicenseApplication.TotalNodes,
				&li.LicenseApplication.Company,
				&li.LicenseApplication.ProductName,
				&li.LicenseApplication.Version,
				&li.LicenseApplication.NodeCount,
				&li.LicenseApplication.Processor,
				&li.LicenseApplication.OperatingSystem,
				&li.LicenseApplication.MasterMacAddress,
				&li.LicenseApplication.SecondaryMasterMacAddress,

				&li.License1,
				&li.License2,
				&li.LicenseFlage,
			)
		}

		if err != nil {
			global.Logger.Errorln("数据查询失败： ", err.Error())
			return nil, 0, fmt.Errorf("scan row error: %v", err)
		}
		results = append(results, li)
	}

	if err = rows.Err(); err != nil {
		global.Logger.Errorln("数据查询失败： ", err.Error())
		return nil, 0, fmt.Errorf("rows error: %v", err)
	}

	return results, total, nil
}

// 插入生成好的lic1，2的信息
func UpdateLicenseStr(uniqueID string, lic1 []byte, lic2 []byte) error {

	tx, err := global.XuguDB.Begin()
	if err != nil {
		global.Logger.Errorln("begin transaction失败： ", err.Error())
		return fmt.Errorf("begin transaction: %v", err)
	}

	defer func() {
		if err != nil {
			tx.Rollback()
		} else {
			err = tx.Commit()
		}
	}()

	//插入 LicenseInfo 表
	if lic2 == nil {
		_, err = tx.Exec(`
UPDATE LicenseInfo SET LicenseFlage = ? ,lic1 = ?  WHERE LicenseApplicationUniqueID = ?
	`,
			"已生成", string(lic1), uniqueID)
	} else {
		_, err = tx.Exec(`
UPDATE LicenseInfo SET LicenseFlage = ? ,lic1 = ?,lic2 = ?  WHERE LicenseApplicationUniqueID = ?
	`,
			"已生成", string(lic1), string(lic2), uniqueID)
	}

	if err != nil {
		global.Logger.Errorln("插入lic1 ，2 失败：  ", err.Error())
		return err
	}
	return nil
}

// 插入分发记录表
// func InsertlicenseRecordByUserRow(LicenseUniqueID string, userUniqueID string, UserAccount string, operatorUniqueID string) error {

// 	tx, err := global.XuguDB.Begin()
// 	if err != nil {
// 		global.Logger.Errorln("begin transaction失败：  ", err.Error())
// 		return fmt.Errorf("begin transaction: %v", err)
// 	}

// 	defer func() {
// 		if err != nil {
// 			tx.Rollback()
// 		} else {
// 			err = tx.Commit()
// 		}
// 	}()
// 	_, err = tx.Exec(`
// 	Insert into  licenseRecordToUser(LicenseUniqueID ,userUniqueID,UserAccount,upTime,operatorUniqueID) values(?,?,?,?,?)`,
// 		LicenseUniqueID, userUniqueID, UserAccount, time.Now(), operatorUniqueID)
// 	if err != nil {

// 		global.Logger.Errorln("插入分发记录用户表失败： ", err.Error())
// 		return err
// 	}
// 	return nil

// }

// 插入分发记录表
// func InsertlicenseRecordByEmailRow(LicenseUniqueID string, emails string, operatorUniqueID string) error {

// 	tx, err := global.XuguDB.Begin()
// 	if err != nil {
// 		global.Logger.Errorln("begin transaction失败：  ", err.Error())
// 		return fmt.Errorf("begin transaction: %v", err)
// 	}

// 	defer func() {
// 		if err != nil {
// 			tx.Rollback()
// 		} else {
// 			err = tx.Commit()
// 		}
// 	}()
// 	_, err = tx.Exec(`
// 	Insert into  licenseRecordToEmails(LicenseUniqueID ,emails,operatorUniqueID,upTime) values(?,?,?,?)`,
// 		LicenseUniqueID, emails, operatorUniqueID, time.Now())
// 	if err != nil {

// 		global.Logger.Errorln("插入分发记录邮箱表失败： ", err.Error())
// 		return err
// 	}
// 	return nil

// }

// // 搜索分发记录表
// func GetlicenseRecordByUser(uniqueID string) ([]LicenseRecordToUser, error) {
// 	fmt.Println("搜索分发记录表 uniqueID", uniqueID)

// 	if uniqueID == "" {
// 		return nil, fmt.Errorf("uniqueID is empty")

// 	}
// 	sqlLicUser := `select LicenseUniqueID,userUNIQUEID,UserAccount,operatorUniqueID,upTime from licenseRecordToUser where LicenseUniqueID  = ? AND  deltime IS NULL`
// 	rows, err := global.XuguDB.Query(sqlLicUser, uniqueID)
// 	if err != nil {
// 		global.Logger.Errorln("query data: ", err.Error())
// 		return nil, fmt.Errorf("query error: %v", err)
// 	}
// 	defer rows.Close()
// 	fmt.Println("执行这里")
// 	var results []LicenseRecordToUser
// 	for rows.Next() {
// 		var lRU LicenseRecordToUser

// 		err = rows.Scan(
// 			&lRU.LicenseUniqueID,
// 			&lRU.UserUniqueID,
// 			&lRU.UserAccount,
// 			&lRU.OperatorUniqueID,
// 			&lRU.UpTime,
// 		)

// 		if err != nil {

// 			global.Logger.Errorln("scan row: ", err.Error())

// 			return nil, fmt.Errorf("scan row error: %v", err)
// 		}

// 		results = append(results, lRU)
// 	}
// 	fmt.Println("LicenseRecordToUser ", results)
// 	return results, nil
// }

// func GetlicenseRecordByEmail(uniqueID string) ([]LicenseRecordToEmails, error) {
// 	if uniqueID == "" {
// 		return nil, fmt.Errorf("uniqueID is empty")

// 	}

// 	sqlLicEmail := `select LicenseUniqueID,emails,operatorUniqueID ,upTime from licenseRecordToEmails  where LicenseUniqueID  = ? AND  deltime IS NULL;`
// 	rows, err := global.XuguDB.Query(sqlLicEmail, uniqueID)
// 	if err != nil {
// 		global.Logger.Errorln("query data: ", err.Error())
// 		return nil, fmt.Errorf("query error: %v", err)
// 	}

// 	fmt.Println("执行这里")
// 	var results []LicenseRecordToEmails
// 	for rows.Next() {
// 		var lRU LicenseRecordToEmails

// 		err = rows.Scan(
// 			&lRU.LicenseUniqueID,
// 			&lRU.Emails,
// 			&lRU.OperatorUniqueID,
// 			&lRU.UpTime,
// 		)

// 		if err != nil {

// 			global.Logger.Errorln("scan row: ", err.Error())

// 			return nil, fmt.Errorf("scan row error: %v", err)
// 		}

// 		results = append(results, lRU)
// 	}

// 	if err = rows.Err(); err != nil {
// 		global.Logger.Errorln("rows error: ", err.Error())
// 		return nil, fmt.Errorf("rows error: %v", err)
// 	}

// 	return results, nil
// }

// func UpdatelicenseInfoRow(license LicenseInfo) error {
// 	tx, err := global.XuguDB.Begin()
// 	if err != nil {
// 		return fmt.Errorf("begin transaction: %v", err)
// 	}

// 	defer func() {
// 		if err != nil {
// 			tx.Rollback()
// 		} else {
// 			err = tx.Commit()
// 		}
// 	}()

// 	_, err = tx.Exec(`
// 		UPDATE LICENSEAPPLICATION SET Creator =? ,ApplicationDate =? ,AssociatedProject = ? ,
// 		SalesPerson = ? , SalesEmail = ?  , SupportPerson = ? , SupportEmail = ? , TotalNodes = ? ,
// 		Company = ? , ProductName = ? , Version = ? , NodeCount = ?
// 		WHERE  UniqueID =?;
// 	`, license.Creator, license.ApplicationDate, license.AssociatedProject,
// 		license.SalesPerson, license.SalesEmail, license.SupportPerson, license.SupportEmail, license.TotalNodes,
// 		license.Company, license.ProductName, license.Version, license.NodeCount, license.UniqueID)
// 	if err != nil {
// 		global.Logger.Errorln("UpdatelicenseInfoRow失败 ", err.Error())
// 		return err
// 	}

// 	return nil
// }