262 lines
7.0 KiB
Go
262 lines
7.0 KiB
Go
package storage
|
||
|
||
import (
|
||
"crm-go/models"
|
||
"crypto/rand"
|
||
"database/sql"
|
||
"encoding/hex"
|
||
"encoding/json"
|
||
"fmt"
|
||
"strings"
|
||
"time"
|
||
)
|
||
|
||
type mysqlCustomerStorage struct {
|
||
db *sql.DB
|
||
}
|
||
|
||
// NewMySQLCustomerStorage 创建MySQL客户存储
|
||
func NewMySQLCustomerStorage() CustomerStorage {
|
||
return &mysqlCustomerStorage{
|
||
db: GetDB(),
|
||
}
|
||
}
|
||
|
||
func (cs *mysqlCustomerStorage) GetAllCustomers() ([]models.Customer, error) {
|
||
query := `
|
||
SELECT id, created_at, customer_name, intended_product, version,
|
||
description, solution, type, module, status_progress, reporter, screenshots
|
||
FROM customers
|
||
ORDER BY created_at DESC
|
||
`
|
||
|
||
rows, err := cs.db.Query(query)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
var customers []models.Customer
|
||
for rows.Next() {
|
||
var c models.Customer
|
||
var intendedProduct, version, description, solution, typ, module, statusProgress, reporter, screenshots sql.NullString
|
||
|
||
err := rows.Scan(
|
||
&c.ID, &c.CreatedAt, &c.CustomerName,
|
||
&intendedProduct, &version, &description,
|
||
&solution, &typ, &module, &statusProgress, &reporter, &screenshots,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
c.IntendedProduct = intendedProduct.String
|
||
c.Version = version.String
|
||
c.Description = description.String
|
||
c.Solution = solution.String
|
||
c.Type = typ.String
|
||
c.Module = module.String
|
||
c.StatusProgress = statusProgress.String
|
||
c.Reporter = reporter.String
|
||
// 解析 screenshots JSON 数组
|
||
if screenshots.Valid && screenshots.String != "" {
|
||
// 尝试解析为 JSON 数组
|
||
var screenshotArray []string
|
||
if err := json.Unmarshal([]byte(screenshots.String), &screenshotArray); err == nil {
|
||
c.Screenshots = screenshotArray
|
||
} else {
|
||
// 向后兼容：如果不是 JSON，尝试逗号分隔（旧格式）
|
||
c.Screenshots = strings.Split(screenshots.String, ",")
|
||
}
|
||
} else {
|
||
c.Screenshots = []string{}
|
||
}
|
||
|
||
customers = append(customers, c)
|
||
}
|
||
|
||
return customers, rows.Err()
|
||
}
|
||
|
||
func (cs *mysqlCustomerStorage) GetCustomerByID(id string) (*models.Customer, error) {
|
||
query := `
|
||
SELECT id, created_at, customer_name, intended_product, version,
|
||
description, solution, type, module, status_progress, reporter, screenshots
|
||
FROM customers
|
||
WHERE id = ?
|
||
`
|
||
|
||
var c models.Customer
|
||
var intendedProduct, version, description, solution, typ, module, statusProgress, reporter, screenshots sql.NullString
|
||
|
||
err := cs.db.QueryRow(query, id).Scan(
|
||
&c.ID, &c.CreatedAt, &c.CustomerName,
|
||
&intendedProduct, &version, &description,
|
||
&solution, &typ, &module, &statusProgress, &reporter, &screenshots,
|
||
)
|
||
|
||
if err == sql.ErrNoRows {
|
||
return nil, nil
|
||
}
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
c.IntendedProduct = intendedProduct.String
|
||
c.Version = version.String
|
||
c.Description = description.String
|
||
c.Solution = solution.String
|
||
c.Type = typ.String
|
||
c.Module = module.String
|
||
c.StatusProgress = statusProgress.String
|
||
c.Reporter = reporter.String
|
||
|
||
// 解析 screenshots JSON 数组
|
||
if screenshots.Valid && screenshots.String != "" {
|
||
// 尝试解析为 JSON 数组
|
||
var screenshotArray []string
|
||
if err := json.Unmarshal([]byte(screenshots.String), &screenshotArray); err == nil {
|
||
c.Screenshots = screenshotArray
|
||
} else {
|
||
// 向后兼容：如果不是 JSON，尝试逗号分隔（旧格式）
|
||
c.Screenshots = strings.Split(screenshots.String, ",")
|
||
}
|
||
} else {
|
||
c.Screenshots = []string{}
|
||
}
|
||
|
||
return &c, nil
|
||
}
|
||
|
||
func (cs *mysqlCustomerStorage) CreateCustomer(customer models.Customer) error {
|
||
if customer.ID == "" {
|
||
customer.ID = generateMySQLUUID()
|
||
}
|
||
if customer.CreatedAt.IsZero() {
|
||
customer.CreatedAt = time.Now()
|
||
}
|
||
|
||
query := `
|
||
INSERT INTO customers (id, created_at, customer_name, intended_product, version,
|
||
description, solution, type, module, status_progress, reporter, screenshots)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||
`
|
||
|
||
// 将 screenshots 数组序列化为 JSON
|
||
screenshotsJSON, err := json.Marshal(customer.Screenshots)
|
||
if err != nil {
|
||
return fmt.Errorf("failed to marshal screenshots: %v", err)
|
||
}
|
||
|
||
_, err = cs.db.Exec(query,
|
||
customer.ID, customer.CreatedAt, customer.CustomerName,
|
||
customer.IntendedProduct, customer.Version, customer.Description,
|
||
customer.Solution, customer.Type, customer.Module,
|
||
customer.StatusProgress, customer.Reporter, string(screenshotsJSON),
|
||
)
|
||
|
||
return err
|
||
}
|
||
|
||
func (cs *mysqlCustomerStorage) UpdateCustomer(id string, updates models.UpdateCustomerRequest) error {
|
||
// 首先获取现有客户
|
||
existing, err := cs.GetCustomerByID(id)
|
||
if err != nil || existing == nil {
|
||
return err
|
||
}
|
||
|
||
// 应用更新
|
||
if updates.CustomerName != nil {
|
||
existing.CustomerName = *updates.CustomerName
|
||
}
|
||
if updates.IntendedProduct != nil {
|
||
existing.IntendedProduct = *updates.IntendedProduct
|
||
}
|
||
if updates.Version != nil {
|
||
existing.Version = *updates.Version
|
||
}
|
||
if updates.Description != nil {
|
||
existing.Description = *updates.Description
|
||
}
|
||
if updates.Solution != nil {
|
||
existing.Solution = *updates.Solution
|
||
}
|
||
if updates.Type != nil {
|
||
existing.Type = *updates.Type
|
||
}
|
||
if updates.Module != nil {
|
||
existing.Module = *updates.Module
|
||
}
|
||
if updates.StatusProgress != nil {
|
||
existing.StatusProgress = *updates.StatusProgress
|
||
}
|
||
if updates.Reporter != nil {
|
||
existing.Reporter = *updates.Reporter
|
||
}
|
||
if updates.Screenshots != nil {
|
||
existing.Screenshots = *updates.Screenshots
|
||
}
|
||
|
||
query := `
|
||
UPDATE customers
|
||
SET customer_name = ?, intended_product = ?, version = ?,
|
||
description = ?, solution = ?, type = ?,
|
||
module = ?, status_progress = ?, reporter = ?, screenshots = ?
|
||
WHERE id = ?
|
||
`
|
||
|
||
// 将 screenshots 数组序列化为 JSON
|
||
screenshotsJSON, err := json.Marshal(existing.Screenshots)
|
||
if err != nil {
|
||
return fmt.Errorf("failed to marshal screenshots: %v", err)
|
||
}
|
||
|
||
_, err = cs.db.Exec(query,
|
||
existing.CustomerName, existing.IntendedProduct, existing.Version,
|
||
existing.Description, existing.Solution, existing.Type,
|
||
existing.Module, existing.StatusProgress, existing.Reporter, string(screenshotsJSON),
|
||
id,
|
||
)
|
||
|
||
return err
|
||
}
|
||
|
||
func (cs *mysqlCustomerStorage) DeleteCustomer(id string) error {
|
||
query := `DELETE FROM customers WHERE id = ?`
|
||
_, err := cs.db.Exec(query, id)
|
||
if err != nil {
|
||
if strings.Contains(err.Error(), "command denied") {
|
||
return fmt.Errorf("数据库权限不足：无法执行删除操作，请联系管理员")
|
||
}
|
||
return err
|
||
}
|
||
return nil
|
||
}
|
||
|
||
func (cs *mysqlCustomerStorage) SaveCustomers(customers []models.Customer) error {
|
||
// MySQL版本不需要使用此方法，保留接口兼容
|
||
return nil
|
||
}
|
||
|
||
func (cs *mysqlCustomerStorage) LoadCustomers() ([]models.Customer, error) {
|
||
return cs.GetAllCustomers()
|
||
}
|
||
|
||
func (cs *mysqlCustomerStorage) CustomerExists(customer models.Customer) (bool, error) {
|
||
query := `SELECT COUNT(*) FROM customers WHERE description = ?`
|
||
var count int
|
||
err := cs.db.QueryRow(query, customer.Description).Scan(&count)
|
||
if err != nil {
|
||
return false, err
|
||
}
|
||
return count > 0, nil
|
||
}
|
||
|
||
func generateMySQLUUID() string {
|
||
bytes := make([]byte, 16)
|
||
rand.Read(bytes)
|
||
bytes[6] = (bytes[6] & 0x0f) | 0x40 // Version 4
|
||
bytes[8] = (bytes[8] & 0x3f) | 0x80 // Variant
|
||
return hex.EncodeToString(bytes)
|
||
}
|