package dao import ( "fmt" "github.com/go-pg/pg/v10" "gitlab.fjmaimaimai.com/allied-creation/performance/pkg/domain" ) type DataStaffAssessContent2 struct { CycleId string `pg:"cycle_id"` //周期id CycleName string `pg:"cycle_name"` //周期名称 EvaluationProjectId string `pg:"evaluation_project_id"` // TargetUserId string `pg:"target_user_id"` //被评估人的id TargetUserName string `pg:"target_user_name"` //被评估人的名称 BeginDay string `pg:"begin_day"` //评估的日期 Value string `pg:"value"` //评估填写的值 Category string `pg:"category"` //评估项分类 ContentName string `pg:"content_name"` //评估项名称 Weight float64 `pg:"weight"` //权重 PromptText string `pg:"prompt_text"` //评估标准 Remark []domain.AssessContemtRemark `pg:"remark"` } // SearchStaffAssessContent2 用于员工绩效-综合管理-导出绩效-个人 // 获取所有评估的填写内容 // companyId 公司id // cycleId 周期id // operaterId 操作人、查看人的id // hrbp 操作人、查看人是否hrbp 1:是;-1:否 func (d *StaffAssessDao) SearchStaffAssessContent2(companyId int, cycleId int, operaterId int, hrbp int, exportUserIds []string) ( []DataStaffAssessContent2, error) { withSql := d.catchProjectIdByPermission(companyId, cycleId, operaterId, hrbp) sqlStr := ` select staff_assess.target_user ->>'userId' as target_user_id, staff_assess.evaluation_project_id, staff_assess.target_user ->>'userName' as target_user_name, to_char(staff_assess.begin_time,'YYYY-MM-DD') as begin_day, staff_assess.cycle_id, staff_assess.cycle_name, staff_assess_content.value , staff_assess_content.category , staff_assess_content."name" as content_name , staff_assess_content.weight, staff_assess_content.prompt_text, staff_assess_content.remark from staff_assess join staff_assess_content on staff_assess.id = staff_assess_content.staff_assess_id where 1=1 and staff_assess.cycle_id =? and "types" ='self' and staff_assess.evaluation_project_id in ( select t_project_4.project_id from t_project_4 ) ` condition := []interface{}{cycleId} if len(exportUserIds) > 0 { sqlStr += ` and staff_assess.target_user->>'userId' in(?) ` condition = append(condition, pg.In(exportUserIds)) } sqlStr = withSql + sqlStr + ` order by staff_assess.begin_time ` result := []DataStaffAssessContent2{} tx := d.transactionContext.PgTx _, err := tx.Query(&result, sqlStr, condition...) return result, err } type DataEvaluationItemUsed2 struct { CycleId string `pg:"cycle_id"` //周期id CycleName string `pg:"cycle_name"` //周期名称 EvaluationProjectId string `pg:"evaluation_project_id"` //评估的项目 TargetUserId string `pg:"target_user_id"` //被评估人的id TargetUserName string `pg:"target_user_name"` //被评估人的名称 BeginDay string `pg:"begin_day"` //评估的日期 Category string `pg:"category"` //评估项分类 ContentName string `pg:"content_name"` //评估项名称 Weight float64 `pg:"weight"` //权重 PromptText string `pg:"prompt_text"` //评估标准 } // SearchEvaluationItemUsed2 用于员工绩效-综合管理-导出绩效-个人 // 获取所有评估的填写项 // companyId 公司id // cycleId 周期id // operaterId 操作人、查看人的id // hrbp 操作人、查看人是否hrbp 1:是;-1:否 func (d *StaffAssessDao) SearchEvaluationItemUsed2(companyId int, cycleId int, operaterId int, hrbp int, exportUserIds []string) ( []DataEvaluationItemUsed2, error) { withSql := d.catchProjectIdByPermission(companyId, cycleId, operaterId, hrbp) sqlStr := `select staff_assess.target_user ->>'userId' as target_user_id, staff_assess.evaluation_project_id , staff_assess.target_user ->>'userName' as target_user_name, to_char(staff_assess.begin_time,'YYYY-MM-DD') as begin_day, staff_assess.cycle_id, staff_assess.cycle_name, evaluation_item_used.category , evaluation_item_used."name" as content_name , evaluation_item_used.weight, evaluation_item_used.prompt_text from staff_assess join evaluation_item_used on evaluation_item_used.evaluation_project_id = staff_assess.evaluation_project_id where 1=1 and staff_assess.cycle_id =? and "types" ='self' and staff_assess.evaluation_project_id in ( select t_project_4.project_id from t_project_4 ) ` condition := []interface{}{cycleId} if len(exportUserIds) > 0 { sqlStr += ` and staff_assess.target_user->>'userId' in(?) ` condition = append(condition, pg.In(exportUserIds)) } sqlStr = withSql + sqlStr result := []DataEvaluationItemUsed2{} tx := d.transactionContext.PgTx _, err := tx.Query(&result, sqlStr, condition...) return result, err } // sql 语句片段 // 按照人员可查看权限的过滤 // companyId int 公司id (必填) // cycleId int, 评估周期id (必填) // userId int, 用户id,谁要查看数据 (必填) // hrbp 是否搜索HRBP角色的用户可以查看,1:是;-1:否 (必填) func (d *StaffAssessDao) catchProjectIdByPermission(companyId int, cycleId int, operaterId int, hrbp int) string { withSql := ` set time zone 'PRC'; with t_project_0 as( select evaluation_project.id as project_id, jsonb_array_elements_text(evaluation_project.recipients) as target_user_id, evaluation_project.pmp, evaluation_project.pmp_ids, evaluation_project.hr_bp from evaluation_project where evaluation_project.cycle_id =%d and evaluation_project.deleted_at isnull ), -- 查我的下级员工 t_user_1 as ( select "user".id::text as user_id from "user" where "user".parent_id =%d and company_id=%d ), -- 如果是HRBP t_project_1 as( select t_project_0.project_id,t_project_0.target_user_id from t_project_0 where t_project_0.hr_bp =%d ), -- 如果的项目管理员 t_project_2 as( select t_project_0.project_id,t_project_0.target_user_id from t_project_0 where t_project_0.pmp =1 and t_project_0.pmp_ids @>'["%d"]' ), -- 如果是上级员工 t_project_3 as ( select t_project_0.project_id,t_project_0.target_user_id from t_project_0 join t_user_1 on t_user_1.user_id = t_project_0.target_user_id ), -- 合并数据 t_project_4 as ( select t_project_2.project_id,t_project_2.target_user_id from t_project_2 union select t_project_1.project_id,t_project_1.target_user_id from t_project_1 union select t_project_3.project_id,t_project_3.target_user_id from t_project_3 )` params := []interface{}{cycleId, operaterId, companyId, hrbp, operaterId} return fmt.Sprintf(withSql, params...) } type TargetUserCycleProject struct { CycleId string `pg:"cycle_id" json:"cycleId"` //周期id CycleName string `pg:"cycle_name" json:"cycleName"` //周期名称 EvaluationProjectId string `pg:"evaluation_project_id" json:"evaluationProjectId"` EvaluationProjectName string `pg:"evaluation_project_name" json:"evaluationProjectName"` TargetUserId string `pg:"target_user_id" json:"targetUserId"` } // 获取目标员工的自评周期和项目 func (d *StaffAssessDao) SearchTargetUserCycleProject(companyId int, targetUserId int, limit int, offset int) ([]TargetUserCycleProject, error) { sqlStr := `select distinct staff_assess.cycle_name, staff_assess.cycle_id , staff_assess.evaluation_project_id, staff_assess.target_user ->>'userId' as target_user_id, staff_assess.evaluation_project_name from staff_assess ,staff_assess_task where 1=1 and staff_assess_task.id =staff_assess.staff_assess_task_id and staff_assess."types" ='self' and staff_assess_task.deleted_at isnull and staff_assess.deleted_at isnull and staff_assess.target_user ->>'userId'='?' and staff_assess.company_id=? ` sqlStr += ` order by cycle_id desc limit ? offset ? ` condition := []interface{}{targetUserId, companyId, limit, offset} result := []TargetUserCycleProject{} tx := d.transactionContext.PgTx _, err := tx.Query(&result, sqlStr, condition...) return result, err } type SearchConditin2 struct { CompanyId int //公司id CycleId int //周期id BeginDayList []string //评估的日期 TargetUserName string //被评估人的名称 TargetUserId []string //查询指定的人 OperaterId int //用户的id是谁在搜索数据 Hrbp int // } // 项目管理-成员列表 导出数据 func (d *StaffAssessDao) ExportDataUserAssessV2(param SearchConditin2) ([]ExportData1, error) { withSql := d.catchProjectIdByPermission(param.CompanyId, param.CycleId, param.OperaterId, param.Hrbp) sqlStr := `select staff_assess.target_user->>'userId' as target_user_id , staff_assess.target_user->>'userName' as target_user_name , to_char(staff_assess.begin_time at time zone 'PRC','YYYY-MM-DD') as begin_day, staff_assess.id as assess_id, staff_assess.cycle_id, staff_assess.cycle_name, staff_assess_content.id as content_id, staff_assess_content.value , staff_assess_content.sort_by , staff_assess_content.category , staff_assess_content."name" as content_name , staff_assess_content.weight, staff_assess_content.prompt_text , staff_assess_content.remark from staff_assess join t_project_4 on staff_assess.evaluation_project_id =t_project_4.project_id left join staff_assess_content on staff_assess.id = staff_assess_content.staff_assess_id where 1=1 and staff_assess."types" ='self' ` condition := []interface{}{} if len(param.TargetUserName) > 0 { condition = append(condition, "%"+param.TargetUserName+"%") sqlStr += ` and staff_assess.target_user->>'userName' like ? ` } if len(param.BeginDayList) > 0 { condition = append(condition, pg.In(param.BeginDayList)) sqlStr += ` and to_char(staff_assess.begin_time at time zone 'PRC','YYYY-MM-DD') in(?) ` } if len(param.TargetUserId) > 0 { condition = append(condition, pg.In(param.TargetUserId)) sqlStr += ` and staff_assess.target_user->>'userId' in (?) ` } sqlStr = withSql + sqlStr + ` order by convert_to(staff_assess.target_user->>'userName','GBK'),begin_day,staff_assess_content.sort_by ` tx := d.transactionContext.PgTx var result []ExportData1 _, err := tx.Query(&result, sqlStr, condition...) return result, err } type ExecutorUser struct { UserId string `pg:"user_id"` UserName string `pg:"user_name"` } // 获取没有进行过360邀请的员工 func (d *StaffAssessDao) ListTargetUserNoInvite(companyId int, cycleId int, beginDay string) ([]ExecutorUser, error) { sqlStr := `with t1 as ( select staff_assess.target_user ->>'userId' as user_id, staff_assess.target_user ->>'userName' as user_name from staff_assess where 1=1 and cycle_id =? and company_id =? and to_char(staff_assess.begin_time at time zone 'PRC','YYYY-MM-DD') =? and "types" in('invite_same_super','invite_diff_super') ) select staff_assess.target_user ->>'userId' as user_id, staff_assess.target_user ->>'userName' as user_name from staff_assess left join t1 on staff_assess.target_user ->>'userId'=t1.user_id where 1=1 and staff_assess.cycle_id =? and company_id = ? and to_char(staff_assess.begin_time at time zone 'PRC','YYYY-MM-DD') =? and staff_assess."types"='self' and t1.user_id isnull ` condition := []interface{}{cycleId, companyId, beginDay, cycleId, companyId, beginDay} tx := d.transactionContext.PgTx var result []ExecutorUser _, err := tx.Query(&result, sqlStr, condition...) return result, err } type UserSelfStaffAssess struct { AssessId string `pg:"assess_id"` EvaluationProjectName string `pg:"evaluation_project_name"` //项目名称 CompanyId string `pg:"company_id"` //公司id ContentId int `pg:"content_id"` // TargetUserId string `pg:"target_user_id"` //被评估人的id TargetUserName string `pg:"target_user_name"` //被评估人的名称 BeginDay string `pg:"begin_day"` //评估的日期 EvaluationProjectId string `pg:"evaluation_project_id"` //项目id CycleId string `pg:"cycle_id"` //周期id } type SearchConditin3 struct { CompanyId int //公司id CycleId int //周期id BeginDay string //评估的日期 TargetUserName string //被评估人的名称 Limit int //分页 Offset int //分页 OperaterId int //用户的id是谁在搜索数据 Hrbp int // Status string // 评估完成状态 } // 根据周期的id和日期获取员工评估的评估任务。有进行查看权限过滤 // companyId int 公司id ,必填 // cycleId int, 评估周期id, 必填 // userId int, 用户id,谁要查看数据 // beginDay string, 周期中执行项目的时间 // hrbp 是否搜索HRBP角色的用户可以查看,1:是;-1:否 // limit int, 分页条数 // offset int 分页偏移 func (d *StaffAssessDao) SearchUserAssessContentV2(param SearchConditin3) ([]UserAssessContent, error) { withSql := d.catchProjectIdByPermission(param.CompanyId, param.CycleId, param.OperaterId, param.Hrbp) sqlStr := `select staff_assess.cycle_id , staff_assess.id as assess_id, staff_assess.target_user ->>'userId' as target_user_id, staff_assess.target_user ->>'userName' as target_user_name, staff_assess.evaluation_project_id , staff_assess.evaluation_project_name , staff_assess.company_id , to_char(staff_assess.begin_time at time zone 'PRC','YYYY-MM-DD') as begin_day from staff_assess join t_project_4 on staff_assess.evaluation_project_id =t_project_4.project_id where 1=1 and staff_assess.cycle_id =? and staff_assess.deleted_at isnull and staff_assess.types='self' ` condition := []interface{}{param.CycleId} if len(param.BeginDay) > 0 { condition = append(condition, param.BeginDay) sqlStr += ` and to_char(staff_assess.begin_time at time zone 'PRC','YYYY-MM-DD')=? ` } if len(param.TargetUserName) > 0 { condition = append(condition, "%"+param.TargetUserName+"%") sqlStr += ` and staff_assess.target_user ->>'userName' like ? ` } if len(param.Status) > 0 { condition = append(condition, param.Status) sqlStr += ` and staff_assess.status=? ` } condition = append(condition, param.Limit, param.Offset) sqlStr += ` order by convert_to(staff_assess.target_user ->>'userName','GBK') limit ? offset ? ` withSql2 := withSql + `,t_staff_assess_1 as( ` + sqlStr + ` )` sqlStr2 := ` ,t_item_used as (select t_staff_assess_1.target_user_id, t_staff_assess_1.target_user_name, t_staff_assess_1.begin_day, t_staff_assess_1.assess_id, t_staff_assess_1.cycle_id, evaluation_item_used.sort_by , evaluation_item_used.category , evaluation_item_used."name" as content_name , evaluation_item_used.weight, evaluation_item_used."rule" from t_staff_assess_1 join evaluation_item_used on t_staff_assess_1.evaluation_project_id =evaluation_item_used.evaluation_project_id ) select t_item_used.* ,staff_assess_content.value,staff_assess_content.level_value from t_item_used left join staff_assess_content on staff_assess_content.staff_assess_id=t_item_used.assess_id and t_item_used.category =staff_assess_content.category and t_item_used.content_name = staff_assess_content."name" where 1=1` sqlStr2 = withSql2 + sqlStr2 tx := d.transactionContext.PgTx var result []UserAssessContent _, err := tx.Query(&result, sqlStr2, condition...) return result, err } // 根据周期的id和日期获取员工评估的评估任务数量。有进行查看权限过滤 // companyId int 公司id // cycleId int, 评估周期id // userId int, 用户id,谁要查看数据 // beginDay string, 周期中执行项目的时间 // hrbp 是否搜索HRBP角色的用户可以查看,1:是;-1:否 func (d *StaffAssessDao) CountUserSelfStaffAssess(param SearchConditin3) (int, error) { withSql := d.catchProjectIdByPermission(param.CompanyId, param.CycleId, param.OperaterId, param.Hrbp) sqlStr := `select count(*) from staff_assess join t_project_4 on staff_assess.evaluation_project_id =t_project_4.project_id where 1=1 and staff_assess.cycle_id =? and staff_assess.deleted_at isnull and staff_assess.types='self' ` condition := []interface{}{param.CycleId} if len(param.BeginDay) > 0 { condition = append(condition, param.BeginDay) sqlStr += ` and to_char(staff_assess.begin_time at time zone 'PRC','YYYY-MM-DD')=? ` } if len(param.TargetUserName) > 0 { condition = append(condition, "%"+param.TargetUserName+"%") sqlStr += ` and staff_assess.target_user ->>'userName' like ? ` } if len(param.Status) > 0 { condition = append(condition, param.Status) sqlStr += ` and staff_assess.status=? ` } sqlStr2 := withSql + sqlStr tx := d.transactionContext.PgTx var result int _, err := tx.Query(pg.Scan(&result), sqlStr2, condition...) return result, err }