fix db colummn

zhangzengfei

2024-09-28 b1d7efd8c4ab9c4bf56f62e636a358a5182c09bf

fix db colummn

 .idea/workspace.xml

@@ -2,7 +2,12 @@
<project version="4">
  <component name="ChangeListManager">
    <list default="true" id="919111b8-f2aa-4154-8db0-88e76c9af55d" name="Default Changelist" comment="">
      <change beforePath="$PROJECT_DIR$/cache/device.go" beforeDir="false" afterPath="$PROJECT_DIR$/cache/device.go" afterDir="false" />
      <change beforePath="$PROJECT_DIR$/.idea/workspace.xml" beforeDir="false" afterPath="$PROJECT_DIR$/.idea/workspace.xml" afterDir="false" />
      <change beforePath="$PROJECT_DIR$/db/database.go" beforeDir="false" afterPath="$PROJECT_DIR$/db/database.go" afterDir="false" />
      <change beforePath="$PROJECT_DIR$/db/models.go" beforeDir="false" afterPath="$PROJECT_DIR$/db/models.go" afterDir="false" />
      <change beforePath="$PROJECT_DIR$/db/repository.go" beforeDir="false" afterPath="$PROJECT_DIR$/db/repository.go" afterDir="false" />
      <change beforePath="$PROJECT_DIR$/rule/engine.go" beforeDir="false" afterPath="$PROJECT_DIR$/rule/engine.go" afterDir="false" />
      <change beforePath="$PROJECT_DIR$/rule/service.go" beforeDir="false" afterPath="$PROJECT_DIR$/rule/service.go" afterDir="false" />
    </list>
    <option name="SHOW_DIALOG" value="false" />
    <option name="HIGHLIGHT_CONFLICTS" value="true" />
@@ -192,12 +197,13 @@
    <property name="settings.editor.selected.configurable" value="watcher.settings" />
  </component>
  <component name="RecentsManager">
    <key name="MoveFile.RECENT_KEYS">
      <recent name="F:\workspace\golang\cloud_ai\ruleModelEngine\cache" />
    </key>
    <key name="CopyFile.RECENT_KEYS">
      <recent name="F:\workspace\golang\cloud_ai\ruleModelEngine" />
    </key>
    <key name="MoveFile.RECENT_KEYS">
      <recent name="E:\Basic\ruleModelEngine\db" />
      <recent name="F:\workspace\golang\cloud_ai\ruleModelEngine\cache" />
    </key>
  </component>
  <component name="RunManager" selected="Go Build.go build main.go">
    <configuration name="go build elastic.go" type="GoApplicationRunConfiguration" factoryName="Go Application" temporary="true" nameIsGenerated="true">

 db/database.go

@@ -1,10 +1,11 @@
package db

import (
    "time"

    "gorm.io/driver/mysql"
    "gorm.io/gorm"
    "ruleModelEngine/config"
    "time"
)

var DB *gorm.DB
@@ -35,7 +36,11 @@
    sqlDb.SetConnMaxLifetime(time.Duration(120) * time.Second)
    sqlDb.SetConnMaxIdleTime(time.Duration(1800) * time.Second)

    db.AutoMigrate(&PublicHouse{})
    db.AutoMigrate(
        &PublicHouse{},
        &PersonnelStatusRule{},
        &Task{},
    )

    DB = db
    return nil

 db/device.go

New file
@@ -0,0 +1,35 @@
package db

type Device struct {
    DeviceCode       string       `gorm:"column:device_code"`        // 设备编码
    Name             string       `gorm:"column:name"`               // 设备名称
    Longitude        string       `gorm:"column:longitude"`          // 经度
    Latitude         string       `gorm:"column:latitude"`           // 纬度
    CaptureNum       int          `gorm:"column:capture_num"`        // 抓拍次数
    Building         string       `gorm:"column:building"`           // 楼号
    BuildingType     BuildingType `gorm:"column:building_type"`      // 楼号
    Unit             string       `gorm:"column:unit"`               // 单元号
    Position         string       `gorm:"column:position"`           // 安装位置
    Floor            string       `gorm:"column:floor"`              // 楼层号
    Type             int          `gorm:"column:type"`               // 设备类型: 0 摄像机, 1 设备箱
    Scene            int          `gorm:"column:scene"`              // 安装场景: 0 室内, 1 室外
    Direction        string       `gorm:"column:direction"`          // 拍摄方向: in, out
    Entrance         string       `gorm:"column:entrance"`           // 入口的位置: "1F,-1F,-2F"
    CommunityID      string       `gorm:"column:community_id"`       // 常驻小区 domain unit ID
    OrgID            string       `gorm:"column:org_id"`             // 所属派出所 domain unit ID
    MaxBuildingFloor int          `gorm:"column:max_building_floor"` //最高楼层号
    MinBuildingFloor int          `gorm:"column:min_building_floor"` //最低楼层号
    Disabled         int          `gorm:"column:disabled"`           // 禁用
}

// 查询设备表
func GetDeviceData() ([]Device, error) {
    // 查询数据
    var db = DB
    var device []Device
    result := db.Table("device").Where("community_id != ?", "").Find(&device)
    if result.Error != nil {
        return nil, result.Error
    }
    return device, nil
}

 db/label.go

New file
@@ -0,0 +1,29 @@
package db

type LabelManage struct {
    ID        string `gorm:"column:id"`
    Name      string `gorm:"column:name"`
    Comment   string `gorm:"column:comment"`
    Type      int    `gorm:"column:type"`
    ValidDays int    `gorm:"column:valid_days"`
}

func (LabelManage) TableName() string {
    return "label"
}

// 查询人物身份属性表
func GeIdentityLabels() ([]LabelManage, error) {
    var db = DB

    // 查询数据
    var labelManageIdentity []LabelManage
    if err := db.Table("label").
        Select("id, name, valid_days").
        Where("type = 2").
        Find(&labelManageIdentity).Error; err != nil {
        return nil, err
    }

    return labelManageIdentity, nil
}

 db/models.go

@@ -1,18 +1,21 @@
package db

import (
    "time"
type BuildingType int

const (
    BuildingTypeResidential    BuildingType = 1 //住宅楼
    BuildingTypeMixedUse       BuildingType = 2 //商住楼
    BuildingTypePublicRental   BuildingType = 3 //公租房
    BuildingTypeLowIncome      BuildingType = 4 //廉租房
    BuildingTypeOldResidential BuildingType = 5 //老旧小区
    BuildingTypeOffice         BuildingType = 6 //写字楼
)

type PersonnelStatusRule struct {
    ID                  int    `gorm:"column:id"`
    Name                string `gorm:"column:name"`
    DetectionCountStart int    `gorm:"column:detectionCountStart"`
    DetectionCountEnd   int    `gorm:"column:detectionCountEnd"`
    DetectionDaysStart  int    `gorm:"column:detectionDaysStart"`
    DetectionDaysEnd    int    `gorm:"column:detectionDaysEnd"`
    Status              string `gorm:"column:status"`
}
const (
    StatusStranger int = 1
    StatusVisitor  int = 2
    StatusResident int = 3
)

type AlarmRule struct {
    RuleId     string `json:"ruleId"`
@@ -29,120 +32,6 @@
    TargetType     string `json:"targetType"`
    Floor          string `json:"floor"`
}

type BuildingType int

const (
    BuildingTypeResidential    BuildingType = 1 //住宅楼
    BuildingTypeMixedUse       BuildingType = 2 //商住楼
    BuildingTypePublicRental   BuildingType = 3 //公租房
    BuildingTypeLowIncome      BuildingType = 4 //廉租房
    BuildingTypeOldResidential BuildingType = 5 //老旧小区
    BuildingTypeOffice         BuildingType = 6 //写字楼
)

//type status int

const (
    StatusStranger int = 1
    StatusVisitor  int = 2
    StatusResident int = 3
)

type Device struct {
    DeviceCode       string       `gorm:"column:deviceCode" json:"deviceCode" example:"J83762"`                                 //设备编码
    AreaID           string       `json:"areaID" gorm:"index;column:communityID;type:varchar(299);"`                            //常住小区 domain unit ID
    BuildingType     BuildingType `gorm:"column:building_type;type:tinyint(1);not null;default:0" json:"buildingType"`          //楼宇类型
    MaxBuildingFloor int          `gorm:"column:max_building_floor;type:tinyint(1);not null;default:0" json:"maxBuildingFloor"` //最高楼层号
}

type Task struct {
    Id   int    `gorm:"column:id"`
    Name string `gorm:"name"`
}

func (Task) TableName() string {
    return "task"
}

type MoveInout struct {
    //RecordId       int        `gorm:"column:record_id"`
    DocumentNumber string     `gorm:"column:document_number"`
    CommunityID    string     `gorm:"column:community_id"`
    MoveInDate     time.Time  `gorm:"column:move_in_date"`
    MoveOutDate    *time.Time `gorm:"column:move_out_date"`
    Status         string     `gorm:"column:status"`
    MoveType       string     `gorm:"column:move_type"`
}

func (MoveInout) TableName() string {
    return "move_inout"
}

type Resident struct {
    CommunityId        string `gorm:"column:community_id"`
    DocumentNumber     string `gorm:"column:document_number"`
    LastAppearanceTime int64  `gorm:"column:last_appearance_time"`
    CreateAt           string `gorm:"column:create_at"`
}

type PersonStatus struct {
    Id                       uint   `gorm:"column:id;primary_key;auto_increment;not null;"`
    OrgId                    string `gorm:"column:org_id;type:varchar(299);not null;default:''"`                                                                                            // 派出所id
    CommunityID              string `gorm:"uniqueIndex:idx_document_number_community_id;index:community_id_last_appearance_time;column:community_id;type:varchar(299);not null;default:''"` // 小区id
    DocumentNumber           string `gorm:"uniqueIndex:idx_document_number_community_id;column:document_number;type:varchar(299);not null;default:''"`                                      // 档案编号
    DaysAppeared             int    `gorm:"column:days_appeared;type:int(11);not null;default:0" json:"daysAppeared"`                                                                       // 出现天数
    Count                    int    `gorm:"column:count;type:int;not null;default:0"`                                                                                                       // 抓拍次数
    Status                   string `gorm:"column:status;type:varchar(255);not null;default:''"`                                                                                            //标签
    LastAppearanceTime       int64  `gorm:"index:community_id_last_appearance_time;column:last_appearance_time;type:int;not null;default:0" json:"lastAppearanceTime"`                      //最后出现时间
    LastAppearanceStatusTime int64  `gorm:"column:last_appearance_status_time"`
    LastLocation             string `gorm:"column:last_location;type:varchar(255);not null;default:''" json:"lastLocation"`       //最后出现地点
    FrequentAddress          string `gorm:"column:frequent_address;type:varchar(255);not null;default:''" json:"frequentAddress"` //常出现地点
    CreatedAt                time.Time
    UpdatedAt                time.Time
    NewStatus                string `gorm:"-"`
    ////OrgId           string `gorm:"column:org_id"`
    //CommunityID     string `gorm:"column:communityID"`
    //DocumentNumber  string `gorm:"column:documentNumber"`
    //Status          string `gorm:"column:status"`
    //FrequentAddress string `gorm:"column:frequentAddress"`
}

func (PersonStatus) TableName() string {
    return "snapshot_count_summary"
}

type LabelManage struct {
    Id        int    `gorm:"id"`
    Name      string `gorm:"name"`
    ValidDays int    `gorm:"valid_days"`
}

func (LabelManage) TableName() string {
    return "label_manage"
}

type Identity struct {
    CreatedAt      time.Time
    UpdatedAt      time.Time
    CommunityID    string `gorm:"column:community_id"`
    DocumentNumber string `gorm:"column:dbtablepersons_id"`
    LabelId        int    `gorm:"column:label_id"`
    ExpireTime     int64  `gorm:"column:expire_time"`
}

func (Identity) TableName() string {
    return "dbtablepersons_label"
}

//
//type ModelMatix struct {
//    CommunityID     string
//    DocumentNumber  string
//    CaptureDate     string
//    Status          string
//    FrequentAddress string
//}

type StatusPersonMapping struct {
    Status string
@@ -163,19 +52,4 @@
    CaptureDate    string `json:"captureDate"`
    CaptureAddress string `json:"captureAddress"`
    Direction      string `json:"direction"`
}

type PublicHouse struct {
    ID              int    `gorm:"primaryKey;autoIncrement" json:"id"`                        // 主键ID
    CommunityID     string `gorm:"column:community_id;type:varchar(255)" json:"community_id"` // 小区ID
    Applicant       string `gorm:"column:applicant;type:varchar(255)" json:"applicant"`       // 申请人
    IdCard          string `gorm:"column:id_card;type:varchar(50)" json:"id_card"`            // 证件编号
    PhoneNumber     string `gorm:"column:phone_number;type:varchar(20)" json:"phone_number"`  // 联系电话
    Address         string `gorm:"column:address;type:varchar(255)" json:"address"`           // 房屋地址
    ApplicationTime string `gorm:"column:application_time" json:"application_time"`           // 申请时间
    EndTime         string `gorm:"column:end_time" json:"end_time"`                           // 结束时间
}

func (PublicHouse) TableName() string {
    return "public_house"
}

 db/moveInOut.go

New file
@@ -0,0 +1,77 @@
package db

import (
    "basic.com/valib/logger.git"
    "errors"
    "gorm.io/gorm"
    "time"
)

type MoveInout struct {
    ID                 string `gorm:"column:id;type:varchar(255);" json:"id"`
    OrgId              string `gorm:"column:org_id;type:varchar(299);not null;default:''"`                                                                                            // 派出所id
    CommunityID        string `gorm:"uniqueIndex:idx_document_number_community_id;index:community_id_last_appearance_time;column:community_id;type:varchar(299);not null;default:''"` // 小区id
    DocumentNumber     string `gorm:"uniqueIndex:idx_document_number_community_id;column:document_number;type:varchar(299);not null;default:''"`                                      //最后出现地点
    MoveInDate         string `gorm:"column:move_in_date;type:date;" json:"moveInDate"`                                                                                               //搬入日期
    MoveOutDate        string `gorm:"column:move_out_date;type:date;" json:"moveOutDate"`                                                                                             //搬出日期
    Status             string `gorm:"column:status;"`
    MoveType           string `gorm:"column:move_type;"`
    DaysAppeared       int    `gorm:"column:days_appeared;type:int(11);not null;default:0" json:"daysAppeared"`                                                  // 出现天数
    LastAppearanceTime int64  `gorm:"index:community_id_last_appearance_time;column:last_appearance_time;type:int;not null;default:0" json:"lastAppearanceTime"` //最后出现时间
    LastLocation       string `gorm:"column:last_location;type:varchar(255);not null;default:''" json:"lastLocation"`                                            //最后出现地点
    CreatedAt          time.Time
    UpdatedAt          time.Time
    DeletedAt          gorm.DeletedAt `gorm:"index" json:"-"`
}

// UpdatePersonInfo 更新或插入多个人员信息
func UpdateMoveInout(personsMoveInout []MoveInout) error {
    var db = DB
    // 遍历人员信息
    for _, personMoveInout := range personsMoveInout {

        // 检查记录是否存在
        var existingPerson MoveInout
        err := db.Where("document_number = ? AND community_id = ?", personMoveInout.DocumentNumber, personMoveInout.CommunityID).First(&existingPerson).Error
        if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) {
            logger.Error("Query person error:", err, personMoveInout.DocumentNumber, personMoveInout.CommunityID)
            continue
        }

        // 如果记录存在，则更新
        if existingPerson.DocumentNumber != "" {
            //fmt.Println("existingPerson.DocumentNumber: ", existingPerson.DocumentNumber)
            if existingPerson.Status != "Verified" {
                err := db.Model(&MoveInout{}).
                    Where("document_number = ? AND community_id = ?", personMoveInout.DocumentNumber, personMoveInout.CommunityID).
                    Updates(map[string]interface{}{
                        "status":        personMoveInout.Status,
                        "move_out_date": personMoveInout.MoveOutDate,
                    }).Error
                if err != nil {
                    return err
                }
            } else {
                err := db.Model(&MoveInout{}).
                    Where("document_number = ? AND community_id = ?", personMoveInout.DocumentNumber, personMoveInout.CommunityID).
                    Updates(map[string]interface{}{
                        "move_out_date": personMoveInout.MoveOutDate,
                    }).Error
                if err != nil {
                    return err
                }
            }
        } else {
            // 如果记录不存在，则插入新记录
            //fmt.Println("插入记录失败")
            //fmt.Println("data", &personMoveInout)
            err := db.Create(&personMoveInout).Error
            if err != nil {
                return err
            }
        }

    }

    return nil
}

 db/person.go

New file
@@ -0,0 +1,46 @@
package db

import "strconv"

// 根据dbtablepersons表id查询目标档案年龄
func QueryAgeById(id string) (int, error) {
    var db = DB
    var age string
    err := db.Table("person").
        Select("age").
        Where("id = ?", id).
        Scan(&age).Error
    if err != nil {
        return 0, err
    }

    return strconv.Atoi(age)
}

// 查询人物年龄
func GetAgeById(id string) (int, error) {
    var db = DB
    // 查询数据
    var age string
    if err := db.Table("person").
        Select("age").
        Where("id = ?", id).
        Find(&age).Error; err != nil {
        return 0, err
    }

    return strconv.Atoi(age)
}

// 查询人物身份证号
func GetIdCardById(id string) string {
    var db = DB
    // 查询数据
    var idCard string
    db.Table("person").
        Select("id_card").
        Where("id = ?", id).
        Find(&idCard)

    return idCard
}

 db/personLabel.go

New file
@@ -0,0 +1,73 @@
package db

import (
    "basic.com/valib/logger.git"
    "errors"
    "gorm.io/gorm"
    "time"
)

type Identity struct {
    ID             string `gorm:"primary_key;column:id;type:varchar(255);" json:"id"`
    CreatedAt      time.Time
    UpdatedAt      time.Time
    DeletedAt      gorm.DeletedAt `gorm:"index" json:"-"`
    CommunityID    string         `gorm:"column:community_id"`
    DocumentNumber string         `gorm:"column:person_id"`
    LabelId        string         `gorm:"column:label_id"`
    ExpireTime     int64          `gorm:"column:expire_time"`
}

func (Identity) TableName() string {
    return "person_label"
}

// UpdatePersonInfo 更新或插入多个人员身份信息
func UpdateDBPersonLabel(personsIdentity []Identity) error {
    var db = DB
    // 遍历人员信息
    for _, personIdentity := range personsIdentity {

        // 检查记录是否存在
        var existingPerson Identity
        err := db.Where("person_id = ? AND community_id = ? AND label_id = ?",
            personIdentity.DocumentNumber,
            personIdentity.CommunityID,
            personIdentity.LabelId,
        ).First(&existingPerson).Error
        if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) {
            logger.Error("Query person error:", err,
                personIdentity.DocumentNumber,
                personIdentity.CommunityID,
                personIdentity.LabelId)
            //fmt.Println("asdasfasfasf")
            continue
            //return err
        }

        // 如果记录存在，则更新
        if existingPerson.DocumentNumber != "" {
            err := db.Model(&Identity{}).
                Where("person_id = ? AND community_id = ? AND label_id = ?",
                    personIdentity.DocumentNumber,
                    personIdentity.CommunityID,
                    personIdentity.LabelId,
                ).
                Updates(map[string]interface{}{
                    "expire_time": personIdentity.ExpireTime,
                }).Error
            if err != nil {
                return err
            }
        } else {
            // 如果记录不存在，则插入新记录
            err := db.Create(&personIdentity).Error
            if err != nil {
                return err
            }
        }

    }

    return nil
}

 db/personStatusRule.go

New file
@@ -0,0 +1,27 @@
package db

type PersonnelStatusRule struct {
    ID                  int    `gorm:"column:id"`
    Name                string `gorm:"column:name"`
    DetectionCountStart int    `gorm:"column:capture_count_start"`
    DetectionCountEnd   int    `gorm:"column:capture_count_end"`
    DetectionDaysStart  int    `gorm:"column:capture_days_start"`
    DetectionDaysEnd    int    `gorm:"column:capture_days_end"`
    Status              string `gorm:"column:status"`
}

func (psr *PersonnelStatusRule) TableName() string {
    return "person_status_rule"
}

// 查询全部数据
func GetAllData() ([]PersonnelStatusRule, error) {
    var db = DB

    var rules []PersonnelStatusRule
    if err := db.Find(&rules).Error; err != nil {
        return nil, err
    }

    return rules, nil
}

 db/publicHouse.go

New file
@@ -0,0 +1,27 @@
package db

type PublicHouse struct {
    ID              int    `gorm:"primaryKey;autoIncrement" json:"id"`                        // 主键ID
    CommunityID     string `gorm:"column:community_id;type:varchar(255)" json:"community_id"` // 小区ID
    Applicant       string `gorm:"column:applicant;type:varchar(255)" json:"applicant"`       // 申请人
    IdCard          string `gorm:"column:id_card;type:varchar(50)" json:"id_card"`            // 证件编号
    PhoneNumber     string `gorm:"column:phone_number;type:varchar(20)" json:"phone_number"`  // 联系电话
    Address         string `gorm:"column:address;type:varchar(255)" json:"address"`           // 房屋地址
    ApplicationTime string `gorm:"column:application_time" json:"application_time"`           // 申请时间
    EndTime         string `gorm:"column:end_time" json:"end_time"`                           // 结束时间
}

func (PublicHouse) TableName() string {
    return "public_house"
}

func GetPublicHouseData() ([]PublicHouse, error) {
    // 查询数据
    var db = DB
    var ph []PublicHouse
    result := db.Table("public_house").Where("1 = 1").Find(&ph)
    if result.Error != nil {
        return nil, result.Error
    }
    return ph, nil
}

 db/repository.go

@@ -1,330 +1,14 @@
package db

import (
    "basic.com/valib/logger.git"
    "errors"
    "gorm.io/gorm"
    "strconv"
)

// 查询小区表
func GetCommunityIDs() ([]string, error) {
    // 查询数据
    var db = DB
    var communityIDs []string
    result := db.Table("domain_unit").Where("domainType = ?", 1).Pluck("id", &communityIDs)
    result := db.Table("domain_unit").Where("domain_type = ?", 1).Pluck("id", &communityIDs)
    if result.Error != nil {
        return nil, result.Error
    }

    return communityIDs, nil
}

// 查询设备表
func GetDeviceData() ([]Device, error) {
    // 查询数据
    var db = DB
    var device []Device
    result := db.Table("device").Where("communityID != ?", "").Find(&device)
    if result.Error != nil {
        return nil, result.Error
    }
    return device, nil
}

func GetPublicHouseData() ([]PublicHouse, error) {
    // 查询数据
    var db = DB
    var ph []PublicHouse
    result := db.Table("public_house").Where("1 = 1").Find(&ph)
    if result.Error != nil {
        return nil, result.Error
    }
    return ph, nil
}

// 查询全部数据
func GetAllTaskData() ([]Task, error) {
    var db = DB
    var task []Task
    if err := db.Find(&task).Error; err != nil {
        return nil, err
    }

    return task, nil
}

// 查询全部数据
func GetAllData() ([]PersonnelStatusRule, error) {
    var db = DB
    var rules []PersonnelStatusRule
    if err := db.Find(&rules).Error; err != nil {
        return nil, err
    }

    return rules, nil
}

// 查询住户时间数据
func GetResidentData(status, communityID string) ([]Resident, error) {
    var residents []Resident
    //var db = DB.Debug()
    var db = DB
    // 执行查询
    rows, err := db.Table("snapshot_count_summary").
        Select("document_number", "community_id", "last_appearance_time", "created_at").
        Where("status = ? AND community_id = ?", status, communityID).
        //Where("snapshot_count_summary.created_at is not null").
        Rows()
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    // 遍历查询结果
    for rows.Next() {
        var resident Resident
        err := rows.Scan(&resident.DocumentNumber, &resident.CommunityId, &resident.LastAppearanceTime, &resident.CreateAt)
        if err != nil {
            logger.Error("err: ", err)
            return nil, err
        }
        //fmt.Println("resident111: ", resident)
        residents = append(residents, resident)
    }
    if err := rows.Err(); err != nil {
        return nil, err
    }

    return residents, nil
}

// 查询人物属性
func GetDBPersonStatus(id, communityId string) string {
    var db = DB

    // 查询数据
    var personStatus string
    db.Table("snapshot_count_summary").
        Select("status").
        Where("community_id = ? and document_number = ?", communityId, id).
        Find(&personStatus)

    return personStatus
}

// 查询小区档案表 (原查询任务属性)
func QueryPersonStatusWithPagination(community_id string, timeThreshold int64) ([]*PersonStatus, error) {
    var db = DB
    var personStatusList []*PersonStatus
    result := db.Select("document_number, status, frequent_address, last_appearance_time, last_appearance_status_time").
        Where("community_id = ? AND last_appearance_time != last_appearance_status_time AND last_appearance_time > ?", community_id, timeThreshold).
        Find(&personStatusList)
    if result.Error != nil {
        logger.Error(result.Error)
        return nil, result.Error
    }
    return personStatusList, nil
}

// 查询人物年龄
func GetAgeById(id string) (int, error) {
    var db = DB
    // 查询数据
    var age string
    if err := db.Table("dbtablepersons").
        Select("age").
        Where("id = ?", id).
        Find(&age).Error; err != nil {
        return 0, err
    }

    return strconv.Atoi(age)
}

// 查询人物身份证号
func GetIdCardById(id string) string {
    var db = DB
    // 查询数据
    var idCard string
    db.Table("dbtablepersons").
        Select("idCard").
        Where("id = ?", id).
        Find(&idCard)

    return idCard
}

//// 根据社区id和住户属性查询住户档案编号
//func GetDocNumberFromPersonStatus(id, status string) ([]string, error) {
//    var db = DB
//    // 查询数据
//    var personStatusList []PersonStatus
//    if err := db.Table("person_status").
//        Select("documentNumber, status, frequentAddress").
//        Where("communityID = ? AND status = ?", id, status).
//        Find(&personStatusList).Error; err != nil {
//        return nil, err
//    }
//
//    docNum := make([]string, 0)
//    for _, ps := range personStatusList {
//        docNum = append(docNum, ps.DocumentNumber)
//    }
//
//    return docNum, nil
//}

// 查询人物身份属性表
func GetLabelManageIdentity(IdentityType int) ([]LabelManage, error) {
    var db = DB
    // 查询数据
    var labelManageIdentity []LabelManage
    if err := db.Table("label_manage").
        Select("id, name, valid_days").
        Where("type = ?", IdentityType).
        Find(&labelManageIdentity).Error; err != nil {
        return nil, err
    }

    return labelManageIdentity, nil
}

// 根据dbtablepersons表id查询目标档案年龄
func QueryAgeById(id string) (int, error) {
    var db = DB
    var age string
    err := db.Table("dbtablepersons").
        Select("age").
        Where("id = ?", id).
        Scan(&age).Error
    if err != nil {
        return 0, err
    }

    return strconv.Atoi(age)
}

// UpdatePersonInfo 更新或插入多个人员信息
func UpdateMoveInout(personsMoveInout []MoveInout) error {
    var db = DB
    // 遍历人员信息
    for _, personMoveInout := range personsMoveInout {

        // 检查记录是否存在
        var existingPerson MoveInout
        err := db.Where("document_number = ? AND community_id = ?", personMoveInout.DocumentNumber, personMoveInout.CommunityID).First(&existingPerson).Error
        if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) {
            logger.Error("Query person error:", err, personMoveInout.DocumentNumber, personMoveInout.CommunityID)
            //fmt.Println("asdasfasfasf")
            continue
            //return err
        }

        // 如果记录存在，则更新
        if existingPerson.DocumentNumber != "" {
            //fmt.Println("existingPerson.DocumentNumber: ", existingPerson.DocumentNumber)
            if existingPerson.Status != "Verified" {
                err := db.Model(&MoveInout{}).
                    Where("document_number = ? AND community_id = ?", personMoveInout.DocumentNumber, personMoveInout.CommunityID).
                    Updates(map[string]interface{}{
                        "status":        personMoveInout.Status,
                        "move_out_date": personMoveInout.MoveOutDate,
                    }).Error
                if err != nil {
                    return err
                }
            } else {
                err := db.Model(&MoveInout{}).
                    Where("document_number = ? AND community_id = ?", personMoveInout.DocumentNumber, personMoveInout.CommunityID).
                    Updates(map[string]interface{}{
                        "move_out_date": personMoveInout.MoveOutDate,
                    }).Error
                if err != nil {
                    return err
                }
            }
        } else {
            // 如果记录不存在，则插入新记录
            //fmt.Println("插入记录失败")
            //fmt.Println("data", &personMoveInout)
            err := db.Create(&personMoveInout).Error
            if err != nil {
                return err
            }
        }

    }

    return nil
}

// UpdatePersonInfo 更新或插入多个人员身份信息
func UpdateDBPersonLabel(personsIdentity []Identity) error {
    var db = DB
    // 遍历人员信息
    for _, personIdentity := range personsIdentity {

        // 检查记录是否存在
        var existingPerson Identity
        err := db.Where("dbtablepersons_id = ? AND community_id = ? AND label_id = ?",
            personIdentity.DocumentNumber,
            personIdentity.CommunityID,
            personIdentity.LabelId,
        ).First(&existingPerson).Error
        if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) {
            logger.Error("Query person error:", err,
                personIdentity.DocumentNumber,
                personIdentity.CommunityID,
                personIdentity.LabelId)
            //fmt.Println("asdasfasfasf")
            continue
            //return err
        }

        // 如果记录存在，则更新
        if existingPerson.DocumentNumber != "" {
            err := db.Model(&Identity{}).
                Where("dbtablepersons_id = ? AND community_id = ? AND label_id = ?",
                    personIdentity.DocumentNumber,
                    personIdentity.CommunityID,
                    personIdentity.LabelId,
                ).
                Updates(map[string]interface{}{
                    "expire_time": personIdentity.ExpireTime,
                }).Error
            if err != nil {
                return err
            }
        } else {
            // 如果记录不存在，则插入新记录
            err := db.Create(&personIdentity).Error
            if err != nil {
                return err
            }
        }

    }

    return nil
}

// UpdatePersonInfo 更新或插入多个人员信息
func UpdatePersonInfo(persons []PersonStatus, communityID string) error {
    var db = DB
    // 遍历人员信息
    for _, person := range persons {
        err := db.Model(&PersonStatus{}).
            Where("document_number = ? AND community_id = ?", person.DocumentNumber, communityID).
            Updates(map[string]interface{}{
                "status":                   person.Status,
                "frequent_address":         person.FrequentAddress,
                "LastAppearanceStatusTime": person.LastAppearanceStatusTime,
            }).Error
        if err != nil {
            return err
        }
    }

    return nil
}

 db/summary.go

New file
@@ -0,0 +1,117 @@
package db

import (
    "basic.com/valib/logger.git"
    "gorm.io/gorm"
    "time"
)

type PersonStatus struct {
    ID                       string `gorm:"primary_key;column:id;type:varchar(255);" json:"id"`
    CreatedAt                time.Time
    UpdatedAt                time.Time
    DeletedAt                gorm.DeletedAt `gorm:"index" json:"-"`
    OrgId                    string         `gorm:"column:org_id"`                      // 派出所id
    CommunityID              string         `gorm:"column:community_id"`                // 小区id
    DocumentNumber           string         `gorm:"column:document_number"`             // 档案编号
    DaysAppeared             int            `gorm:"column:days_appeared"`               // 出现天数
    Count                    int            `gorm:"column:count"`                       // 抓拍次数
    Status                   string         `gorm:"column:status"`                      // 标签
    LastAppearanceTime       int64          `gorm:"column:last_appearance_time"`        // 最后出现时间
    LastAppearanceStatusTime int64          `gorm:"column:last_appearance_status_time"` // 最后出现时间
    LastLocation             string         `gorm:"column:last_location"`               // 最后出现地点
    FrequentAddress          string         `gorm:"column:frequent_address"`            // 常住地址
}

func (PersonStatus) TableName() string {
    return "snapshot_count_summary"
}

type Resident struct {
    CommunityId        string `gorm:"column:community_id"`
    DocumentNumber     string `gorm:"column:document_number"`
    LastAppearanceTime int64  `gorm:"column:last_appearance_time"`
    CreateAt           string `gorm:"column:create_at"`
}

// 查询住户时间数据
func GetResidentData(status, communityID string) ([]Resident, error) {
    var residents []Resident
    //var db = DB.Debug()
    var db = DB
    // 执行查询
    rows, err := db.Table("snapshot_count_summary").
        Select("document_number", "community_id", "last_appearance_time", "created_at").
        Where("status = ? AND community_id = ?", status, communityID).
        //Where("snapshot_count_summary.created_at is not null").
        Rows()
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    // 遍历查询结果
    for rows.Next() {
        var resident Resident
        err := rows.Scan(&resident.DocumentNumber, &resident.CommunityId, &resident.LastAppearanceTime, &resident.CreateAt)
        if err != nil {
            logger.Error("err: ", err)
            return nil, err
        }
        //fmt.Println("resident111: ", resident)
        residents = append(residents, resident)
    }
    if err := rows.Err(); err != nil {
        return nil, err
    }

    return residents, nil
}

// 查询人物属性
func GetDBPersonStatus(id, communityId string) string {
    var db = DB

    // 查询数据
    var personStatus string
    db.Table("snapshot_count_summary").
        Select("status").
        Where("community_id = ? and document_number = ?", communityId, id).
        Find(&personStatus)

    return personStatus
}

// 查询小区档案表 (原查询任务属性)
func QueryPersonStatusWithPagination(community_id string, timeThreshold int64) ([]*PersonStatus, error) {
    var db = DB
    var personStatusList []*PersonStatus
    result := db.Select("document_number, status, frequent_address, last_appearance_time, last_appearance_status_time").
        Where("community_id = ? AND last_appearance_time != last_appearance_status_time AND last_appearance_time > ?", community_id, timeThreshold).
        Find(&personStatusList)
    if result.Error != nil {
        logger.Error(result.Error)
        return nil, result.Error
    }
    return personStatusList, nil
}

// UpdatePersonInfo 更新或插入多个人员信息
func UpdatePersonInfo(persons []PersonStatus, communityID string) error {
    var db = DB
    // 遍历人员信息
    for _, person := range persons {
        err := db.Model(&PersonStatus{}).
            Where("document_number = ? AND community_id = ?", person.DocumentNumber, communityID).
            Updates(map[string]interface{}{
                "status":                   person.Status,
                "frequent_address":         person.FrequentAddress,
                "LastAppearanceStatusTime": person.LastAppearanceStatusTime,
            }).Error
        if err != nil {
            return err
        }
    }

    return nil
}

 db/task.go

New file
@@ -0,0 +1,30 @@
package db

import (
    "gorm.io/gorm"
    "time"
)

type Task struct {
    Id        int    `gorm:"column:id"`
    Name      string `gorm:"column:name"`
    disabled  bool   `gorm:"column:disabled,default:0"`
    CreatedAt time.Time
    UpdatedAt time.Time
    DeletedAt gorm.DeletedAt `gorm:"index" json:"-"`
}

func (Task) TableName() string {
    return "surveillance_task"
}

// 查询全部数据
func GetAllTaskData() ([]Task, error) {
    var db = DB
    var task []Task
    if err := db.Find(&task).Error; err != nil {
        return nil, err
    }

    return task, nil
}

 rule/engine.go

@@ -17,19 +17,17 @@

    // 遍历Resident结构体切片
    for _, resident := range residents {
        //fmt.Println("gogogogo!!!!!!!!!!!!!!!!!!!!!!!!!!!")
        // 将字符串类型的时间转换为time.Time类型，并只保留年月日
        lastAppearanceTime := time.Unix(resident.LastAppearanceTime, 0)
        lastAppearanceDate := time.Date(lastAppearanceTime.Year(), lastAppearanceTime.Month(), lastAppearanceTime.Day(), 0, 0, 0, 0, lastAppearanceTime.Location())
        //lastAppearanceTime, err := time.Parse("2006-01-02", resident.LastAppearanceTime)
        datePart := strings.Split(resident.CreateAt, "T")[0]
        createdAt, err := time.Parse("2006-01-02", datePart)
        createdAt, err := time.Parse("2006-01-02", resident.CreateAt)
        if err != nil {
            fmt.Println(err)
            // 处理时间解析错误
            // 可以选择跳过该条数据或者记录日志
            continue
        }

        // 计算LastAppearanceTime和CreateAt距离当前日期的天数
        daysSinceLastAppearance := currentDate.Sub(lastAppearanceDate).Hours() / 24
        daysSinceCreateAt := currentDate.Sub(createdAt).Hours() / 24
@@ -63,8 +61,8 @@
        moveInout = append(moveInout, db.MoveInout{
            DocumentNumber: resident.DocumentNumber,
            CommunityID:    resident.CommunityId,
            MoveInDate:     createdAt,           // 存储年月日
            MoveOutDate:    &lastAppearanceDate, // 存储年月日
            MoveInDate:     createdAt.Format("2006-01-02"),          // 存储年月日
            MoveOutDate:    lastAppearanceDate.Format("2006-01-02"), // 存储年月日
            MoveType:       moveType,
            Status:         status,
        })
@@ -84,7 +82,7 @@
//    return true, nil
//}

func CreateLinearModel(personInfos []db.CaptureInfo, communityID string, threshold float64, validDays int, labelId int) ([]db.Identity, []db.CaptureInfo) {
func CreateLinearModel(personInfos []db.CaptureInfo, communityID string, threshold float64, validDays int, labelId string) ([]db.Identity, []db.CaptureInfo) {
    identity := make([]db.Identity, 0)
    captureInfo := make([]db.CaptureInfo, 0)
    for _, info := range personInfos {
@@ -203,21 +201,21 @@
                identity = append(identity, db.Identity{
                    CommunityID:    communityID,
                    DocumentNumber: info.DocumentNumber,
                    LabelId:        labelManage[lIndex].Id,
                    LabelId:        labelManage[lIndex].ID,
                    ExpireTime:     GetCurrentDateAddDaysTimestamp(labelManage[lIndex].ValidDays)})
            } else if info.Age >= 23 && info.Age <= 60 {
                lIndex := labelIndexMap["上班族"]
                identity = append(identity, db.Identity{
                    CommunityID:    communityID,
                    DocumentNumber: info.DocumentNumber,
                    LabelId:        labelManage[lIndex].Id,
                    LabelId:        labelManage[lIndex].ID,
                    ExpireTime:     GetCurrentDateAddDaysTimestamp(labelManage[lIndex].ValidDays)})
            } else if info.Age > 60 {
                lIndex := labelIndexMap["离退休人员"]
                identity = append(identity, db.Identity{
                    CommunityID:    communityID,
                    DocumentNumber: info.DocumentNumber,
                    LabelId:        labelManage[lIndex].Id,
                    LabelId:        labelManage[lIndex].ID,
                    ExpireTime:     GetCurrentDateAddDaysTimestamp(labelManage[lIndex].ValidDays)})
            }
            //fmt.Println("上班族", info.DocumentNumber, info.Age, countMaxRows, colS, colE)
@@ -230,21 +228,21 @@
                    identity = append(identity, db.Identity{
                        CommunityID:    communityID,
                        DocumentNumber: info.DocumentNumber,
                        LabelId:        labelManage[lIndex].Id,
                        LabelId:        labelManage[lIndex].ID,
                        ExpireTime:     GetCurrentDateAddDaysTimestamp(labelManage[lIndex].ValidDays)})
                } else if info.Age >= 23 && info.Age <= 60 {
                    lIndex := labelIndexMap["上班族"]
                    identity = append(identity, db.Identity{
                        CommunityID:    communityID,
                        DocumentNumber: info.DocumentNumber,
                        LabelId:        labelManage[lIndex].Id,
                        LabelId:        labelManage[lIndex].ID,
                        ExpireTime:     GetCurrentDateAddDaysTimestamp(labelManage[lIndex].ValidDays)})
                } else if info.Age > 60 {
                    lIndex := labelIndexMap["离退休人员"]
                    identity = append(identity, db.Identity{
                        CommunityID:    communityID,
                        DocumentNumber: info.DocumentNumber,
                        LabelId:        labelManage[lIndex].Id,
                        LabelId:        labelManage[lIndex].ID,
                        ExpireTime:     GetCurrentDateAddDaysTimestamp(labelManage[lIndex].ValidDays)})
                }
                //fmt.Println("上班族", info.DocumentNumber, info.Age, countMaxRows, colS, colE)

 rule/service.go

@@ -87,7 +87,7 @@

    cameraIds := make([]string, 0)
    for _, deviceInfo := range cache.Device {
        if deviceInfo.AreaID == communityId && deviceInfo.BuildingType == db.BuildingTypeMixedUse {
        if deviceInfo.CommunityID == communityId && deviceInfo.BuildingType == db.BuildingTypeMixedUse {
            cameraIds = append(cameraIds, deviceInfo.DeviceCode)
        }
    }
@@ -137,7 +137,7 @@
    if err != nil {
        logger.Error("GetCommunityIDs Error", err)
    }
    labeManage, err := db.GetLabelManageIdentity(2)
    labeManage, err := db.GeIdentityLabels()
    if err != nil {
        logger.Error("GetDBPersonStatusData Error", err)
    }
@@ -223,7 +223,7 @@
        for _, identity := range labeManage {
            switch identity.Name {
            case "服务人员":
                identity, attribute := CreateLinearModel(captureInfos, communityID, 2.68, identity.ValidDays, identity.Id)
                identity, attribute := CreateLinearModel(captureInfos, communityID, 2.68, identity.ValidDays, identity.ID)

                errIdentity := db.UpdateDBPersonLabel(identity)
                if errIdentity != nil {