package db
|
|
import (
|
"basic.com/valib/logger.git"
|
"errors"
|
"gorm.io/gorm"
|
)
|
|
// 查询小区表
|
func GetCommunityIDs() ([]string, error) {
|
db, err := ConnectDB()
|
if err != nil {
|
return nil, err
|
}
|
|
// 查询数据
|
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 GetAllData() ([]PersonnelStatusRule, error) {
|
db, err := ConnectDB()
|
if err != nil {
|
return nil, err
|
}
|
|
var rules []PersonnelStatusRule
|
if err := db.Find(&rules).Error; err != nil {
|
return nil, err
|
}
|
|
return rules, nil
|
}
|
|
// 查询住户时间数据
|
func GetResidentData(status, communityID string) ([]Resident, error) {
|
db, err := ConnectDB()
|
if err != nil {
|
return nil, err
|
}
|
|
var residents []Resident
|
|
// 执行查询
|
rows, err := db.Table("person_status").
|
Select("person_status.documentNumber", "person_status.communityID", "snapshot_count_summary.last_appearance_time", "snapshot_count_summary.created_at").
|
Joins("INNER JOIN snapshot_count_summary ON person_status.documentNumber = snapshot_count_summary.document_number AND person_status.communityID = snapshot_count_summary.community_id").
|
Where("person_status.status = ? AND person_status.communityID = ?", status, communityID).
|
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 {
|
return nil, err
|
}
|
residents = append(residents, resident)
|
}
|
if err := rows.Err(); err != nil {
|
return nil, err
|
}
|
|
return residents, nil
|
}
|
|
// 查询人物属性
|
func GetDBPersonStatusData(id string) ([]PersonStatus, error) {
|
db, err := ConnectDB()
|
if err != nil {
|
return nil, err
|
}
|
|
// 查询数据
|
var personStatusList []PersonStatus
|
if err := db.Table("person_status").
|
Select("documentNumber, status, frequentAddress").
|
Where("communityID = ?", id).
|
Find(&personStatusList).Error; err != nil {
|
return nil, err
|
}
|
|
return personStatusList, nil
|
}
|
|
// 根据社区id和住户属性查询住户档案编号
|
func GetDocNumberFromPersonStatus(id, status string) ([]string, error) {
|
db, err := ConnectDB()
|
if err != nil {
|
return nil, err
|
}
|
|
// 查询数据
|
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) {
|
db, err := ConnectDB()
|
if err != nil {
|
return nil, err
|
}
|
|
// 查询数据
|
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
|
}
|
|
// UpdatePersonInfo 更新或插入多个人员信息
|
func UpdateMoveInout(personsMoveInout []MoveInout) error {
|
// 数据库连接信息
|
db, err := ConnectDB()
|
if err != nil {
|
return err
|
}
|
// 遍历人员信息
|
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 != "" {
|
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 {
|
// 如果记录不存在，则插入新记录
|
err := db.Create(&personsMoveInout).Error
|
if err != nil {
|
return err
|
}
|
}
|
|
}
|
|
return nil
|
}
|
|
// UpdatePersonInfo 更新或插入多个人员身份信息
|
func UpdateDBPersonLabel(personsIdentity []Identity) error {
|
// 数据库连接信息
|
db, err := ConnectDB()
|
if err != nil {
|
return err
|
}
|
// 遍历人员信息
|
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 {
|
// 数据库连接信息
|
db, err := ConnectDB()
|
if err != nil {
|
return err
|
}
|
// 遍历人员信息
|
for _, person := range persons {
|
|
// 检查记录是否存在
|
var existingPerson PersonStatus
|
err := db.Where("documentNumber = ? AND communityID = ?", person.DocumentNumber, communityID).First(&existingPerson).Error
|
if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) {
|
logger.Error("Query person error:", err, person.DocumentNumber, communityID)
|
//fmt.Println("asdasfasfasf")
|
continue
|
//return err
|
}
|
|
// 如果记录存在，则更新
|
if existingPerson.DocumentNumber != "" {
|
err := db.Model(&PersonStatus{}).
|
Where("documentNumber = ? AND communityID = ?", person.DocumentNumber, communityID).
|
Updates(map[string]interface{}{
|
"status": person.Status,
|
"frequentAddress": person.FrequentAddress,
|
}).Error
|
if err != nil {
|
return err
|
}
|
} else {
|
// 如果记录不存在，则插入新记录
|
err := db.Create(&person).Error
|
if err != nil {
|
return err
|
}
|
}
|
|
}
|
|
return nil
|
}
|
