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)
|
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
|
}
|
