smart-government-management/model/staff_attendance.go

// Copyright 2019  github.com. All rights reserved.
// Use of this source code is governed by  github.com.
package model

import (
	"fmt"
	"github.com/jinzhu/gorm"
	"go.uber.org/zap"
	"strings"
	"time"
	"github.com/jaryhe/gopkgs/logger"
	"smart-government-management/errors"
)

const (
	StaffAttendanceIn = 1
	StaffAttendanceOut = 2
)

const (
	AttendanceTab = iota
	AttendanceTabMonth1
	AttendanceTabMonth2
	AttendanceTabMonth3
	AttendanceTabMonth4
	AttendanceTabMonth5
	AttendanceTabMonth6
	AttendanceTabMonth7
	AttendanceTabMonth8
	AttendanceTabMonth9
	AttendanceTabMonth10
	AttendanceTabMonth11
	AttendanceTabMonth12
	AttendanceTabDay
)

type TStaffAttendance struct {
	Id int64 `gorm:"column:id"`
	ProjectId int64 `gorm:"column:project_id"`
	WorkNo string `gorm:"column:work_no"`
	Attendance string `gorm:"column:attendance"`
	InOut int64 `gorm:"column:in_out"`
	DayTime time.Time `gorm:"column:day_time"`
	CreatedAt time.Time `gorm:"column:created_at"`
	UpdatedAt time.Time `gorm:"column:updated_at"`
	TabType int `gorm:"-"` // 用于判断表0 t_staff_attendance, 1-12 t_staff_attendance_monthx, 13 t_staff_attendance_day
}

func (p TStaffAttendance) TableName() string {
	switch p.TabType {
	case AttendanceTabDay:
		return "db_smart_staff.t_staff_attendance_day"
	default:
		if p.TabType >=AttendanceTabMonth1 && p.TabType <= AttendanceTabMonth12 {
			return fmt.Sprintf("db_smart_staff.t_staff_attendance_month%d", p.TabType)
		}
	}
	return "db_smart_staff.t_staff_attendance_day"
}

func (p *TStaffAttendance) Insert(db *gorm.DB) error {
	return db.Create(p).Error
}


func (p *TStaffAttendance) Del(db *gorm.DB, where map[string]interface{}) error {
	cond, val, err := whereBuild(where)
	if err != nil {
		return err
	}
	return db.Table(p.TableName()).Where(cond, val...).Delete(p).Error
}

func (p *TStaffAttendance) Find(db *gorm.DB, where map[string]interface{}) error {
	cond, val, err := whereBuild(where)
	if err != nil {
		return err
	}
	return db.Table(p.TableName()).Where(cond, val...).First(p).Error
}

func (p *TStaffAttendance) Update(db *gorm.DB, where map[string]interface{}, values map[string]interface{}) error {
	cond, val, err := whereBuild(where)
	if err != nil {
		return err
	}

	return db.Table(p.TableName()).Where(cond, val...).Updates(values).Error
}


func (p *TStaffAttendance) Save(db *gorm.DB) error {
	return db.Save(p).Error
}

func (p *TStaffAttendance) Count(db *gorm.DB, where map[string]interface{}) (int64, error) {
	if len(where) > 0 {
		cond, val, err := whereBuild(where)
		if err != nil {
			return 0, err
		}
		ret := int64(0)
		err = db.Table(p.TableName()).Where(cond, val...).Count(&ret).Error

		return ret, err
	}
	ret := int64(0)
	err := db.Table(p.TableName()).Count(&ret).Error
	return ret, err
}

func (p *TStaffAttendance) List(db *gorm.DB, where map[string]interface{}, page int) (list []TStaffAttendance, err error) {
	if len(where) > 0 {
		cond, val, err := whereBuild(where)
		if err != nil {
			return list, err
		}

		result := db.Table(p.TableName()).Where(cond, val...).Limit(PageSize).Offset(page).Find(&list)

		return list, result.Error
	}
	result := db.Table(p.TableName()).Limit(10).Offset(page).Find(&list)

	return list, result.Error

}


type TStaffAttendanceLast struct {
	WorkNo string
	Name string
	Photo string
	RecogTime string
	InOut int
	Attendance string
	Group string
	WorkType string
}

func (p *TStaffAttendance) Last(db *gorm.DB, projectId int64) (TStaffAttendanceLast, error) {
	result := TStaffAttendanceLast{}
	sql := fmt.Sprintf("select t1.work_no, t1.in_out, t1.attendance, t2.name, t2.photo, t2.group, t3.work_type from db_smart_staff.t_staff_attendance_day as t1 left join db_smart_staff.t_staff as t2 on t1.work_no=t2.work_no left join db_smart_staff.t_work_type_item as t3 on t2.work_type=t3.code")
	whereArray := []string{}
	if projectId > 0 {
		whereArray = append(whereArray,fmt.Sprintf("t1.project_id=%d", projectId))
	}
	where := ""
	for _, v := range whereArray {
		if where == "" {
			where = fmt.Sprintf(" where %s", v)
			continue
		}
		where = fmt.Sprintf("%s and %s", where, v)
	}
	sql = fmt.Sprintf("%s %s order by t1.updated_at desc limit 1", sql, where)

	ret := []TStaffAttendanceLast{}
	err := db.Raw(sql).Scan(&ret).Error

	if err != nil {
		logger.Error("mysql",
			zap.String("sql", sql),
			zap.String("error", err.Error()))
		return  result, errors.DataBaseError
	}

	if len(ret) == 0 {
		return result, nil
	}
	for i, v := range ret {
		array := strings.Split(v.Attendance, "/")
		length := len(array)
		if length > 0 {
			tmp := array[length - 1]
			subLength := len(tmp)
			ret[i].RecogTime = tmp
			if subLength > 2 {
				if tmp[subLength-2:] == "-1" || tmp[subLength-2:] == "-2"{
					ret[i].RecogTime = tmp[:subLength-2]
				}
			}
		}
	}
	return ret[0], nil

}


type WorkTypeStaticItem struct {
	TypeCode int32
	TypeName string
	StaffCount int
	StaffAttendanceCount int
	Id int64
}

func staffWorkTypeSql(projectId int64)(string) {
	sql := fmt.Sprintf("select t1.code as type_code, t1.id, t1.work_type as type_name, count(distinct t2.work_no) as staff_count, count(distinct t3.work_no) as staff_attendance_count from db_smart_staff.t_work_type_item as t1 left join db_smart_staff.t_staff as t2 on t1.code=t2.work_type left join db_smart_staff.t_staff_attendance_day as t3 on t3.work_no=t2.work_no and t3.day_time='%s'", time.Now().Format("2006-01-02"))
	whereArray := []string{}
	if projectId > 0 {
		sql = fmt.Sprintf("select t1.code as type_code, t1.id, t1.work_type as type_name, count(distinct t2.work_no) as staff_count, count(distinct t3.work_no) as staff_attendance_count from db_smart_staff.t_work_type_item as t1 left join db_smart_staff.t_staff as t2 on t1.code=t2.work_type and t2.project_id=%d left join db_smart_staff.t_staff_attendance_day as t3 on t3.work_no=t2.work_no and t3.day_time='%s' and t3.project_id=%d", projectId, time.Now().Format("2006-01-02"), projectId)

	}
	where := ""

	for _, v := range whereArray {
		if where == "" {
			where = fmt.Sprintf(" where %s", v)
			continue
		}
		where = fmt.Sprintf("%s and %s", where, v)
	}


	sql = fmt.Sprintf("%s %s group by t1.code", sql, where)

	return sql
}

func StaffWorkTypeStaticList(db *gorm.DB, projectId int64) ([]WorkTypeStaticItem, error) {
	ret := []WorkTypeStaticItem{}
	var err error

	sql := staffWorkTypeSql(projectId)

	err = db.Raw(sql).Scan(&ret).Error
	if err != nil {
		logger.Error("mysql",
			zap.String("sql", sql),
			zap.String("error", err.Error()))
		return  nil, errors.DataBaseError
	}

	return ret,  nil
}



type StaffTypeStaticItem struct {
	TypeCode int32
	TypeName string
	StaffCount int
	StaffAttendanceCount int
	StaffInCount int
}

func staffTypeSql(projectId int64)(string) {
	sql := fmt.Sprintf("select t1.staff_code as type_code, t1.staff_type as type_name, count(distinct t2.work_no) as staff_count, count(distinct t3.work_no) as staff_attendance_count, count(distinct t4.work_no) as staff_in_count from db_smart_staff.t_staff_type_item as t1 left join db_smart_staff.t_staff as t2 on t1.staff_code=t2.staff_type  left join db_smart_staff.t_staff_attendance_day as t3 on t3.work_no=t2.work_no and t3.day_time='%s'  left join db_smart_staff.t_staff_attendance_day as t4 on t4.work_no=t2.work_no and t4.day_time='%s' and t4.in_out=1",  time.Now().Format("2006-01-02"),  time.Now().Format("2006-01-02"))
	whereArray := []string{}
	if projectId > 0 {
		sql = fmt.Sprintf("select t1.staff_code as type_code, t1.staff_type as type_name, count(distinct t2.work_no) as staff_count, count(distinct t3.work_no) as staff_attendance_count, count(distinct t4.work_no) as staff_in_count from db_smart_staff.t_staff_type_item as t1 left join db_smart_staff.t_staff as t2 on t1.staff_code=t2.staff_type and t2.project_id=%d left join db_smart_staff.t_staff_attendance_day as t3 on t3.work_no=t2.work_no and t3.day_time='%s' and t3.project_id=%d left join db_smart_staff.t_staff_attendance_day as t4 on t4.work_no=t2.work_no and t4.day_time='%s' and t4.in_out=1 and t4.project_id=%d", projectId, time.Now().Format("2006-01-02"), projectId, time.Now().Format("2006-01-02"), projectId)
	}
	where := ""

	for _, v := range whereArray {
		if where == "" {
			where = fmt.Sprintf(" where %s", v)
			continue
		}
		where = fmt.Sprintf("%s and %s", where, v)
	}


	sql = fmt.Sprintf("%s %s group by t1.staff_code order by staff_code", sql, where)

	return sql
}

func StaffTypeStaticList(db *gorm.DB, projectId int64) ([]StaffTypeStaticItem, error) {
	ret := []StaffTypeStaticItem{}
	var err error

	sql := staffTypeSql(projectId)

	err = db.Raw(sql).Scan(&ret).Error
	if err != nil {
		logger.Error("mysql",
			zap.String("sql", sql),
			zap.String("error", err.Error()))
		return  nil, errors.DataBaseError
	}

	return ret,  nil
}


type StaffCompanyStaticItem struct {
	CompanyName string
	StaffCount int
	StaffAttendanceCount int
	StaffAttendanceLabor int
}

const (
	StaffTypeLabor = 11
)
func staffCompanySql(projectId int64)(string) {
	sql := fmt.Sprintf("select t2.labor_company as company_name, count(distinct t2.work_no) as staff_count, count(distinct t3.work_no) as staff_attendance_count, count(distinct t4.work_no) as staff_attendance_labor from db_smart_staff.t_staff as t2  left join db_smart_staff.t_staff_attendance_day as t3 on t3.work_no=t2.work_no and t3.day_time='%s' left join db_smart_staff.t_staff_attendance_day as t4 on t4.work_no=t2.work_no and t4.day_time='%s' and t2.staff_type=%d" , time.Now().Format("2006-01-02"),  time.Now().Format("2006-01-02"), StaffTypeLabor)
	whereArray := []string{}
	if projectId > 0 {
		whereArray = append(whereArray, fmt.Sprintf("t2.project_id=%d", projectId))
	}
	where := ""

	for _, v := range whereArray {
		if where == "" {
			where = fmt.Sprintf(" where %s", v)
			continue
		}
		where = fmt.Sprintf("%s and %s", where, v)
	}


	sql = fmt.Sprintf("%s %s group by t2.labor_company", sql, where)

	return sql
}

func StaffCompanyStaticList(db *gorm.DB, projectId int64) ([]StaffCompanyStaticItem, error) {
	ret := []StaffCompanyStaticItem{}
	var err error

	sql := staffCompanySql(projectId)

	err = db.Raw(sql).Scan(&ret).Error
	if err != nil {
		logger.Error("mysql",
			zap.String("sql", sql),
			zap.String("error", err.Error()))
		return  nil, errors.DataBaseError
	}

	return ret,  nil
}