package dao import ( "fmt" "strconv" "time" "github.com/go-pg/pg/v10" pgTransaction "github.com/linmadan/egglib-go/transaction/pg" "gitlab.fjmaimaimai.com/allied-creation/performance/pkg/domain" ) type StaffAssessDao struct { transactionContext *pgTransaction.TransactionContext } func NewStaffAssessDao(options map[string]interface{}) *StaffAssessDao { var transactionContext *pgTransaction.TransactionContext if value, ok := options["transactionContext"]; ok { transactionContext = value.(*pgTransaction.TransactionContext) } return &StaffAssessDao{ transactionContext: transactionContext, } } type CountData struct { TargetUserId int InviteTotal int InviteCompleted int } // 获取员工邀请的人完成360评估的数量 func (d *StaffAssessDao) CountInviteAssessByTargetUser(userIds []int, assessTaskId int) ([]CountData, error) { sqlStr := `SELECT staff_assess.target_user->>'userId' as target_user_id, count(staff_assess."id") AS invite_total, sum( case WHEN staff_assess.status='completed' THEN 1 ELSE 0 END ) as invite_completed FROM staff_assess WHERE staff_assess.target_user->>'userId' IN(?) AND staff_assess.types IN ('invite_same_super','invite_diff_super') AND staff_assess.staff_assess_task_id = ? GROUP BY target_user_id` userIdList := []string{} for _, v := range userIds { uid := strconv.Itoa(v) userIdList = append(userIdList, uid) } condition := []interface{}{ pg.In(userIdList), assessTaskId, } tx := d.transactionContext.PgTx result := []CountData{} _, err := tx.Query(&result, sqlStr, condition...) return result, err } func (d *StaffAssessDao) CountTargetUserInviteAssess1(userIds []int, cycleId int, beginDay string) ([]CountData, error) { sqlStr := `SELECT staff_assess.target_user->>'userId' as target_user_id, count(staff_assess."id") AS invite_total, sum( case WHEN staff_assess.status='completed' THEN 1 ELSE 0 END ) as invite_completed FROM staff_assess WHERE staff_assess.target_user->>'userId' IN(?) AND staff_assess.types IN ('invite_same_super','invite_diff_super') and to_char(staff_assess.begin_time at time zone 'PRC','YYYY-MM-DD') = ? and staff_assess.cycle_id =? GROUP BY target_user_id` userIdStr := []string{} for _, v := range userIds { str := strconv.Itoa(v) userIdStr = append(userIdStr, str) } condition := []interface{}{ pg.In(userIdStr), beginDay, cycleId, } tx := d.transactionContext.PgTx result := []CountData{} _, err := tx.Query(&result, sqlStr, condition...) return result, err } // 根据评估的人执行人id,搜索 executorId参与的评估任务 // func (d *StaffAssessDao) SearchAssessTaskMe(executorId int, companyId int, limit int, offset int) ([]*domain.StaffAssessTask, error) { // if limit < 0 { // limit = 20 // } // if offset < 0 { // offset = 0 // } // sqlStr := `SELECT DISTINCT staff_assess_task.* FROM staff_assess_task // JOIN staff_assess ON staff_assess_task."id" = staff_assess."staff_assess_task_id" // WHERE staff_assess.company_id=? // and staff_assess_task.deleted_at isnull // and staff_assess.executor->>'userId'='?' // order by staff_assess_task.id desc // limit ? offset ? // ` // tx := d.transactionContext.PgTx // condition := []interface{}{ // companyId, executorId, limit, offset, // } // result := []*domain.StaffAssessTask{} // _, err := tx.Query(&result, sqlStr, condition...) // return result, err // } // 搜索 executorId 参与的评估任务 // func (d *StaffAssessDao) CountAssessTaskMe(executorId int, companyId int) (int, error) { // sqlStr := `SELECT count( DISTINCT staff_assess_task."id") FROM staff_assess_task // JOIN staff_assess ON staff_assess_task."id" = staff_assess."staff_assess_task_id" // WHERE staff_assess.company_id=? // and staff_assess_task.deleted_at isnull // and staff_assess.executor->>'userId'='?' // ` // tx := d.transactionContext.PgTx // condition := []interface{}{ // companyId, executorId, // } // result := 0 // _, err := tx.QueryOne(pg.Scan(&result), sqlStr, condition...) // return result, err // } // 获取所以已经执行的评估周期 type AssessCycle struct { CycleId string `json:"cycleId"` //周期id CompanyId string `json:"companyId"` CycleName string `json:"cycleName"` //周期名称 } // 获取所以已经执行的评估周期 func (d *StaffAssessDao) AllAssessCycleList(companyId int) ([]AssessCycle, error) { sqlStr := `select distinct staff_assess_task.cycle_id , staff_assess_task.company_id , staff_assess_task.cycle_name from staff_assess_task where staff_assess_task.company_id = ? and staff_assess_task.deleted_at isnull order by staff_assess_task.cycle_id desc ` tx := d.transactionContext.PgTx condition := []interface{}{ companyId, } result := []AssessCycle{} _, err := tx.Query(&result, sqlStr, condition...) return result, err } // 获取评估周期中的绩效考核日期 type AssessCycleDay struct { BeginDay string `json:"beginDay"` CycleId int `json:"cycleId"` CycleName string `json:"cycleName"` CompanyId string `json:"companyId"` } // 获取评估周期中的绩效考核日期 func (d *StaffAssessDao) AllAssessCycleDayList(companyId int, cycleId int) ([]AssessCycleDay, error) { sqlStr := `select distinct staff_assess_task.begin_day , staff_assess_task.cycle_id , staff_assess_task.cycle_name, staff_assess_task.company_id from staff_assess_task where staff_assess_task.cycle_id = ? and company_id =? and staff_assess_task.deleted_at isnull order by staff_assess_task.begin_day desc ` tx := d.transactionContext.PgTx condition := []interface{}{ cycleId, companyId, } result := []AssessCycleDay{} _, err := tx.Query(&result, sqlStr, condition...) return result, err } // 获取员工填写评估内容 type UserAssessContent struct { AssessId string `json:"assessId"` ContentId int `json:"contentId"` TargetUserId string `json:"targetUserId"` //被评估人的id TargetUserName string `json:"targetUserName"` //被评估人的名称 BeginDay string `json:"beginDay"` //评估的日期 EvaluationProjectId string `json:"evaluationProjectId"` //项目id Value string `json:"value"` //评估填写的值 LevelValue string `json:"levelValue"` //评级的值 Rule domain.EvaluationRule `json:"rule"` //规则 SortBy int `json:"sortBy"` //评估项顺序 Category string `json:"category"` //评估项分类 ContentName string `json:"contentName"` //评估项名称 Weight float64 `json:"weight"` //权重 CycleId string `json:"cycleId"` //周期id } type SearchConditin1 struct { CompanyId int //公司id CycleId int //周期id BeginDay string //评估的日期 TargetUserName string //被评估人的名称 TargetUserId []string //查询指定的人 Limit int //分页 Offset int //分页 OperaterId int //用户的id是谁在搜索数据 Hrbp int // } // 项目管理-成员列表 根据周期的id和日期获取员工填写的评估内容 // companyId int 公司id // cycleId int, 评估周期id // userId int, 用户id,谁要查看数据 // beginDay string, 周期中执行项目的时间 // hrbp 是否搜索HRBP角色的用户可以查看,1:是;-1:否 // limit int, 分页条数 // offset int 分页偏移 func (d *StaffAssessDao) SearchUserAssessContent(param SearchConditin1) ([]UserAssessContent, error) { if param.Offset < 0 { param.Offset = 0 } if param.Limit < 0 { param.Limit = 20 } sqlStr := ` 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,staff_assess_content.id as content_id, staff_assess_content.value ,staff_assess_content.sort_by ,t_staff_assess_1.cycle_id, staff_assess_content.category ,staff_assess_content."name" as content_name , staff_assess_content.weight,staff_assess_content.level_value,staff_assess_content."rule" from t_staff_assess_1 left join staff_assess_content on t_staff_assess_1.assess_id = staff_assess_content.staff_assess_id where 1=1 ` condition := []interface{}{} if len(param.TargetUserName) > 0 { sqlStr += ` and t_staff_assess_1.target_user_name like ? ` condition = append(condition, "%"+param.TargetUserName+"%") } condition = append(condition, param.Limit, param.Offset) //加入排序 sqlStr += ` order by convert_to(t_staff_assess_1.target_user_name,'GBK'),staff_assess_content.sort_by limit ? offset ?` //获取前置sql语句 sqlStr0 := d.useTStaffAssess(param.CompanyId, param.CycleId, param.OperaterId, param.BeginDay, param.Hrbp, 99999, 0, string(domain.AssessSelf)) sqlStr = sqlStr0 + sqlStr tx := d.transactionContext.PgTx result := []UserAssessContent{} _, err := tx.Query(&result, sqlStr, condition...) return result, err } type UserAssessContentItem struct { Category string `pg:"category"` //指标分类 Name string `pg:"name"` //指标名称 } // 项目管理-成员列表 根据周期的id和日期获取员工的评估指标 // companyId int 公司id // cycleId int, 评估周期id // userId int, 用户id,谁要查看数据 // beginDay string, 周期中执行项目的时间 // hrbp 是否搜索HRBP角色的用户可以查看,1:是;-1:否 // limit int, 分页条数 不需要 // offset int 分页偏移 不需要 func (d *StaffAssessDao) SearchUserAssessContentItem(param SearchConditin1) ([]UserAssessContentItem, error) { param.Offset = 0 param.Limit = 10000 sqlStr := `select staff_assess_content.category, staff_assess_content."name" from staff_assess_content join t_staff_assess_1 on staff_assess_content.staff_assess_id = t_staff_assess_1.assess_id group by staff_assess_content.category, staff_assess_content."name" ` //获取前置sql语句 sqlStr0 := d.useTStaffAssess(param.CompanyId, param.CycleId, param.OperaterId, param.BeginDay, param.Hrbp, param.Limit, param.Offset, string(domain.AssessSelf)) sqlStr = sqlStr0 + sqlStr tx := d.transactionContext.PgTx result := []UserAssessContentItem{} _, err := tx.Query(&result, sqlStr) return result, err } // 项目管理-成员列表 根据周期的id和日期获取员工填写的评估内容,数量统计 // companyId int 公司id // cycleId int, 评估周期id // userId int, 用户id,谁要查看数据 // beginDay string, 周期中执行项目的时间 // hrbp 是否搜索HRBP角色的用户可以查看,1:是;-1:否 // limit int, 分页条数 // offset int 分页偏移 func (d *StaffAssessDao) CountUserAssess(param SearchConditin1) (int, error) { sqlStr := ` select count( distinct t_staff_assess_1.target_user_id) cnt from t_staff_assess_1 where 1=1 ` condition := []interface{}{} if len(param.TargetUserName) > 0 { sqlStr += ` and t_staff_assess_1.target_user_name like ? ` condition = append(condition, "%"+param.TargetUserName+"%") } //获取前置sql语句 sqlStr0 := d.useTStaffAssess(param.CompanyId, param.CycleId, param.OperaterId, param.BeginDay, param.Hrbp, 5000, 0, string(domain.AssessSelf)) sqlStr = sqlStr0 + sqlStr tx := d.transactionContext.PgTx var result int _, err := tx.QueryOne(pg.Scan(&result), sqlStr, condition...) return result, err } // 生成的sql 根据用户的查看权限 ,获取可查看的评估任务, // companyId int 公司id (必填) // cycleId int, 评估周期id (必填) // userId int, 用户id,谁要查看数据 (必填) // beginDay string, 周期中执行项目的时间 (选填) // hrbp 是否搜索HRBP角色的用户可以查看,1:是;-1:否 (必填) // limit int, 分页条数 (必填) // offset int 分页偏移 (必填) // assessType string 评估的类型 (选填) func (d *StaffAssessDao) useTStaffAssess(companyId int, cycleId int, userId int, beginDay string, hrbp int, limit int, offset int, assessType string) string { sqlstr := ` set time zone 'PRC'; with t_user_1 as ( select "user".id::text as user_id from "user" where "user".parent_id =%d and "user".deleted_at isnull ), -- 如果是hrbp t_project_1 as( select evaluation_project.id as project_id from evaluation_project where evaluation_project.cycle_id =%d and evaluation_project.hr_bp = %d and evaluation_project.deleted_at isnull ), -- 如果的项目管理员 t_project_2 as( select evaluation_project.id as project_id from evaluation_project where evaluation_project.cycle_id =%d and evaluation_project.pmp =1 and evaluation_project.pmp_ids @>'["%d"]' and evaluation_project.deleted_at isnull ), -- 合并数据 t_project_3 as ( select t_project_2.project_id from t_project_2 union select t_project_1.project_id from t_project_1 ), -- 初步过滤数据 t_staff_assess_0 as ( select staff_assess.id as assess_id, staff_assess.cycle_id, staff_assess.cycle_name, staff_assess.target_user->>'userId' as target_user_id, staff_assess.target_user->>'userName' as target_user_name, to_char(staff_assess.begin_time,'YYYY-MM-DD') as begin_day, staff_assess.evaluation_project_id from staff_assess join staff_assess_task on staff_assess.staff_assess_task_id = staff_assess_task.id and staff_assess_task.deleted_at isnull where staff_assess.cycle_id = %d %s -- 根据条件拼接查询条件 ), -- 根据查看权限过滤合并数据 t_staff_assess_1 as ( (select t_staff_assess_0.assess_id, t_staff_assess_0.target_user_id, t_staff_assess_0.target_user_name, t_staff_assess_0.begin_day, t_staff_assess_0.cycle_name, t_staff_assess_0.cycle_id, t_staff_assess_0.evaluation_project_id from t_staff_assess_0 join t_project_3 on t_staff_assess_0.evaluation_project_id = t_project_3.project_id ) union (select t_staff_assess_0.assess_id, t_staff_assess_0.target_user_id, t_staff_assess_0.target_user_name, t_staff_assess_0.begin_day, t_staff_assess_0.cycle_name, t_staff_assess_0.cycle_id, t_staff_assess_0.evaluation_project_id from t_staff_assess_0 join t_user_1 on t_staff_assess_0.target_user_id=t_user_1.user_id ) limit %d offset %d ) ` //根据条件拼接查询条件 staffAssessWhere := map[string]string{ "beginDay": ` and to_char(staff_assess.begin_time,'YYYY-MM-DD') like '%s' `, "assessType": ` and staff_assess."types" ='%s' `, } params := []interface{}{ userId, cycleId, hrbp, cycleId, userId, cycleId, } extWhere := "" if len(beginDay) > 0 { str := fmt.Sprintf(staffAssessWhere["beginDay"]+"\n", beginDay) extWhere += str } if len(assessType) > 0 { str := fmt.Sprintf(staffAssessWhere["assessType"]+"\n", assessType) extWhere += str } params = append(params, extWhere, limit, offset) sqlstr = fmt.Sprintf(sqlstr, params...) return sqlstr } type SummaryAssess struct { AssessId string `json:"assessId"` TargetUserId string `json:"targetUserId"` // 被评估人的id TargetUserName string `json:"targetUserName"` // 被评估人的名称 BeginDay string `json:"beginDay"` // 评估的开始日期 EndTime time.Time `json:"endTime"` // 评估的截止日期 EvaluationProjectId string `json:"evaluationProjectId"` // 项目id CycleId string `json:"cycleId"` // 周期id Types domain.StaffAssessType `json:"types"` // 评估类型 Status domain.StaffAssessStatus `json:"status"` // 评估状态 } func (d *StaffAssessDao) SummaryAssess(companyId int, operatorId int, cycleId int, beginDay string, hrbp int) ([]SummaryAssess, error) { //` //with t_user_department as ( // select "user".id as user_id ,jsonb_array_elements_text ("user".department_id) as depart_id from "user" // where "user".company_id= %d and "user".deleted_at isnull //), //t_department as ( // select department.id::text as depart_id from department where charge_user_ids @>'[%d]' // and "department".deleted_at isnull //), //-- 部门主管(所有下级用户ID) //t_user_1 as ( // select t_user_department.user_id::text from t_user_department // join t_department on t_user_department.depart_id = t_department.depart_id //),` sqlString := ` set time zone 'PRC'; with t_user_1 as ( select "user".id::text as user_id from "user" where "user".parent_id =%d ), -- 如果是HRBP t_project_1 as( select evaluation_project.id as project_id from evaluation_project where evaluation_project.cycle_id =%d and evaluation_project.hr_bp = %d and evaluation_project.deleted_at isnull ), -- 如果的项目管理员 t_project_2 as( select evaluation_project.id as project_id from evaluation_project where evaluation_project.cycle_id =%d and evaluation_project.pmp =1 and evaluation_project.pmp_ids @>'["%d"]' and evaluation_project.deleted_at isnull ), -- 合并数据 t_project_3 as ( select t_project_2.project_id from t_project_2 union select t_project_1.project_id from t_project_1 ), -- 初步过滤数据 t_staff_assess_0 as ( select staff_assess.id as assess_id, staff_assess.cycle_id, staff_assess.target_user->>'userId' as target_user_id, staff_assess.target_user->>'userName' as target_user_name, to_char(staff_assess.begin_time,'YYYY-MM-DD') as begin_day, staff_assess.evaluation_project_id, staff_assess.types, staff_assess.status, staff_assess.end_time from staff_assess join staff_assess_task on staff_assess.staff_assess_task_id = staff_assess_task.id and staff_assess_task.deleted_at isnull where staff_assess.cycle_id = %d and to_char(staff_assess.begin_time,'YYYY-MM-DD')='%s' ), -- 根据查看权限过滤合并数据 t_staff_assess_1 as ( ( select t_staff_assess_0.assess_id, t_staff_assess_0.target_user_id, t_staff_assess_0.target_user_name, t_staff_assess_0.begin_day, t_staff_assess_0.cycle_id, t_staff_assess_0.evaluation_project_id, t_staff_assess_0.types, t_staff_assess_0.status, t_staff_assess_0.end_time from t_staff_assess_0 join t_project_3 on t_staff_assess_0.evaluation_project_id = t_project_3.project_id ) union ( select t_staff_assess_0.assess_id, t_staff_assess_0.target_user_id, t_staff_assess_0.target_user_name, t_staff_assess_0.begin_day, t_staff_assess_0.cycle_id, t_staff_assess_0.evaluation_project_id, t_staff_assess_0.types, t_staff_assess_0.status, t_staff_assess_0.end_time from t_staff_assess_0 join t_user_1 on t_staff_assess_0.target_user_id = t_user_1.user_id ) ) ` params := []interface{}{operatorId, cycleId, hrbp, cycleId, operatorId, cycleId, beginDay} //params := []interface{}{companyId, operatorId, cycleId, hrbp, cycleId, operatorId, cycleId, beginDay} sqlString = fmt.Sprintf(sqlString, params...) sqlString = sqlString + ` 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.cycle_id, t_staff_assess_1.assess_id, t_staff_assess_1.evaluation_project_id, t_staff_assess_1.types, t_staff_assess_1.status, t_staff_assess_1.end_time from t_staff_assess_1 where 1=1 ` tx := d.transactionContext.PgTx var result = make([]SummaryAssess, 0) _, err := tx.Query(&result, sqlString) return result, err } type MemberSummaryAssess struct { TargetUserId string `json:"targetUserId"` // 被评估人的id TargetUserName string `json:"targetUserName"` // 被评估人名称 SelfCompleted int `json:"selfCompleted"` // 自评完成数量 SupperCompleted int `json:"supperCompleted"` // 上级评价完成数量 InviteCompleted int `json:"inviteCompleted"` // 邀请完成数量 InviteTotal int `json:"inviteTotal"` // 邀请总数 } func (d *StaffAssessDao) MemberSummaryList(likeUserName string, companyId int, operatorId int, cycleId int, hrbp int, pageNumber int, pageSize int) (int, []MemberSummaryAssess, error) { limit := pageSize if limit < 0 { limit = 20 } offset := limit * (pageNumber - 1) if offset < 0 { offset = 0 } sqlString := ` set time zone 'PRC'; with t_user_1 as ( select "user".id::text as user_id from "user" where "user".parent_id =%d ), -- 如果是HRBP t_project_1 as( select evaluation_project.id as project_id from evaluation_project where evaluation_project.cycle_id =%d and evaluation_project.hr_bp = %d and evaluation_project.deleted_at isnull ), -- 如果的项目管理员 t_project_2 as( select evaluation_project.id as project_id from evaluation_project where evaluation_project.cycle_id =%d and evaluation_project.pmp =1 and evaluation_project.pmp_ids @>'["%d"]' and evaluation_project.deleted_at isnull ), -- 合并数据 t_project_3 as ( select t_project_2.project_id from t_project_2 union select t_project_1.project_id from t_project_1 ), -- 初步过滤数据 t_staff_assess_0 as ( select staff_assess.id as assess_id, staff_assess.cycle_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.types, staff_assess.status from staff_assess join staff_assess_task on staff_assess.staff_assess_task_id = staff_assess_task.id and staff_assess_task.deleted_at isnull where staff_assess.cycle_id = %d ), -- 根据查看权限过滤合并数据 merge_assess as ( ( select t_staff_assess_0.assess_id, t_staff_assess_0.target_user_id, t_staff_assess_0.target_user_name, t_staff_assess_0.cycle_id, t_staff_assess_0.evaluation_project_id, t_staff_assess_0.types, t_staff_assess_0.status from t_staff_assess_0 join t_project_3 on t_staff_assess_0.evaluation_project_id = t_project_3.project_id ) union ( select t_staff_assess_0.assess_id, t_staff_assess_0.target_user_id, t_staff_assess_0.target_user_name, t_staff_assess_0.cycle_id, t_staff_assess_0.evaluation_project_id, t_staff_assess_0.types, t_staff_assess_0.status from t_staff_assess_0 join t_user_1 on t_staff_assess_0.target_user_id = t_user_1.user_id ) ) ` params := []interface{}{operatorId, cycleId, hrbp, cycleId, operatorId, cycleId} // 筛选结果的所有评估数据 sqlString = fmt.Sprintf(sqlString, params...) // 根据用户ID进行分组 groupString := ` SELECT merge_assess.target_user_id, merge_assess.target_user_name, SUM ( CASE WHEN merge_assess.types = 'self' AND merge_assess.status = 'completed' THEN 1 ELSE 0 END ) AS self_completed, SUM ( CASE WHEN merge_assess.types = 'supper' AND merge_assess.status = 'completed' THEN 1 ELSE 0 END ) AS supper_completed, SUM ( CASE WHEN ( merge_assess.types = 'invite_diff_super' OR merge_assess.types = 'invite_same_super' ) AND merge_assess.status = 'completed' THEN 1 ELSE 0 END ) AS invite_completed, SUM ( CASE WHEN merge_assess.types = 'invite_diff_super' OR merge_assess.types = 'invite_same_super' THEN 1 ELSE 0 END ) AS invite_total ` whereFrom := ` FROM merge_assess WHERE 1=1 ` if len(likeUserName) > 0 { whereFrom += fmt.Sprintf(" AND merge_assess.target_user_name LIKE '%s'", "%"+likeUserName+"%") } groupBy := ` GROUP BY merge_assess.target_user_id, merge_assess.target_user_name ORDER BY convert_to( merge_assess.target_user_name, 'GBK' ) ` groupString += whereFrom groupString += groupBy groupString = fmt.Sprintf("%s limit %d offset %d", groupString, limit, offset) // 查询所有目标用户数量 countSql := ` SELECT COUNT ( DISTINCT merge_assess.target_user_id ) ` + whereFrom tx := d.transactionContext.PgTx var result = make([]MemberSummaryAssess, 0) _, err := tx.Query(&result, sqlString+groupString) if err != nil { return 0, result, err } var total int // 获取总数量 _, err = tx.QueryOne(pg.Scan(&total), sqlString+countSql) if err != nil { return 0, result, err } return total, result, err } type PerformanceIndicatorAssess struct { AssessId int `json:"assessId"` // ID TargetUserId string `json:"targetUserId"` // 被评估人的id TargetUserName string `json:"targetUserName"` // 被评估人的名称 BeginDay string `json:"beginDay"` // 评估的开始日期 CycleId int `json:"cycleId"` // 周期ID ContentId int `json:"contentId"` // 评估内容ID ContentCategory string `json:"contentCategory"` // 评估内容分类 ContentName string `json:"contentName"` // 评估内容名称 SortBy int `json:"sort_by"` // 评估内容排序 } func (d *StaffAssessDao) MemberPerformanceIndicator(likeUserName string, companyId int, operatorId int, cycleId int, hrbp int, assessType string, userIds []string) ([]PerformanceIndicatorAssess, error) { sqlString := ` set time zone 'PRC'; with t_user_1 as ( select "user".id::text as user_id from "user" where "user".parent_id =%d ), -- 如果是HRBP t_project_1 as( select evaluation_project.id as project_id from evaluation_project where evaluation_project.cycle_id =%d and evaluation_project.hr_bp = %d and evaluation_project.deleted_at isnull ), -- 如果的项目管理员 t_project_2 as( select evaluation_project.id as project_id from evaluation_project where evaluation_project.cycle_id =%d and evaluation_project.pmp =1 and evaluation_project.pmp_ids @>'["%d"]' and evaluation_project.deleted_at isnull ), -- 合并数据 t_project_3 as ( select t_project_2.project_id from t_project_2 union select t_project_1.project_id from t_project_1 ), -- 初步过滤数据 t_staff_assess_0 as ( select staff_assess.id as assess_id, staff_assess.cycle_id, staff_assess.target_user->>'userId' as target_user_id, staff_assess.target_user->>'userName' as target_user_name, to_char(staff_assess.begin_time,'YYYY-MM-DD') as begin_day, staff_assess.evaluation_project_id from staff_assess join staff_assess_task on staff_assess.staff_assess_task_id = staff_assess_task.id and staff_assess_task.deleted_at isnull where staff_assess.cycle_id = %d and staff_assess.types ='%s' ), -- 根据查看权限过滤合并数据 t_staff_assess_1 as ( ( select t_staff_assess_0.assess_id, t_staff_assess_0.target_user_id, t_staff_assess_0.target_user_name, t_staff_assess_0.begin_day, t_staff_assess_0.cycle_id from t_staff_assess_0 join t_project_3 on t_staff_assess_0.evaluation_project_id = t_project_3.project_id ) union ( select t_staff_assess_0.assess_id, t_staff_assess_0.target_user_id, t_staff_assess_0.target_user_name, t_staff_assess_0.begin_day, t_staff_assess_0.cycle_id from t_staff_assess_0 join t_user_1 on t_staff_assess_0.target_user_id = t_user_1.user_id ) ) ` params := []interface{}{operatorId, cycleId, hrbp, cycleId, operatorId, cycleId, assessType} sqlString = fmt.Sprintf(sqlString, params...) sqlString += ` 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.cycle_id, t_staff_assess_1.assess_id, staff_assess_content.id as content_id, staff_assess_content.name as content_name, staff_assess_content.category as content_category, staff_assess_content.sort_by from t_staff_assess_1 left join staff_assess_content on t_staff_assess_1.assess_id = staff_assess_content.staff_assess_id where 1=1 -- AND staff_assess_content.id NOTNULL 部分脏数据 ` condition := make([]interface{}, 0) if len(likeUserName) > 0 { sqlString += ` and t_staff_assess_1.target_user_name like ? ` condition = append(condition, "%"+likeUserName+"%") } if len(userIds) > 0 { sqlString += ` and t_staff_assess_1.target_user_id in (?) ` condition = append(condition, pg.In(userIds)) } sqlString += ` order by convert_to(t_staff_assess_1.target_user_name,'GBK'), staff_assess_content.sort_by` tx := d.transactionContext.PgTx var result = make([]PerformanceIndicatorAssess, 0) _, err := tx.Query(&result, sqlString, condition...) return result, err } type IndicatorUserProject struct { AssessId int `json:"assessId"` // ID TargetUserId int `json:"targetUserId"` // 目标用户ID TargetUserName string `json:"targetUserName"` // 目标用户名称 EvaluationProjectId int `json:"evaluationProjectId"` // 项目ID } func (d *StaffAssessDao) MemberAllProjectId(companyId int, likeUserName string, operatorId int, cycleId int, hrbp int) ([]IndicatorUserProject, error) { sqlStr := ` select t_staff_assess_1.evaluation_project_id, t_staff_assess_1.target_user_id, t_staff_assess_1.target_user_name, t_staff_assess_1.assess_id from t_staff_assess_1 where 1=1 ` var condition []interface{} if len(likeUserName) > 0 { sqlStr += ` and t_staff_assess_1.target_user_name like ? ` condition = append(condition, "%"+likeUserName+"%") } // 获取前置sql语句 sqlStr0 := d.useTStaffAssess(companyId, cycleId, operatorId, "", hrbp, 5000, 0, string(domain.AssessSelf)) sqlStr = sqlStr0 + sqlStr tx := d.transactionContext.PgTx var result []IndicatorUserProject _, err := tx.Query(&result, sqlStr, condition...) return result, err } type ExportPerformanceIndicator struct { AssessId int `json:"assessId"` // ID TargetUserId string `json:"targetUserId"` // 被评估人的id TargetUserName string `json:"targetUserName"` // 被评估人的名称 BeginDay string `json:"beginDay"` // 评估的开始日期 CycleId int `json:"cycleId"` // 周期ID ContentId int `json:"contentId"` // 评估内容ID ContentCategory string `json:"contentCategory"` // 评估内容分类 ContentName string `json:"contentName"` // 评估内容名称 SortBy int `json:"sort_by"` // 评估内容排序 Remark []domain.AssessContemtRemark `json:"remark"` // 评估内容填写反馈内容 } func (d *StaffAssessDao) ExportPerformanceIndicator( companyId int, operatorId int, cycleId int, beginDays []string, hrbp int, assessType string, userIds []string) ([]ExportPerformanceIndicator, error) { sqlString := ` set time zone 'PRC'; with t_user_1 as ( select "user".id::text as user_id from "user" where "user".parent_id =%d ), -- 如果是HRBP t_project_1 as( select evaluation_project.id as project_id from evaluation_project where evaluation_project.cycle_id =%d and evaluation_project.hr_bp = %d and evaluation_project.deleted_at isnull ), -- 如果的项目管理员 t_project_2 as( select evaluation_project.id as project_id from evaluation_project where evaluation_project.cycle_id =%d and evaluation_project.pmp =1 and evaluation_project.pmp_ids @>'["%d"]' and evaluation_project.deleted_at isnull ), -- 合并数据 t_project_3 as ( select t_project_2.project_id from t_project_2 union select t_project_1.project_id from t_project_1 ), -- 初步过滤数据 t_staff_assess_0 as ( select staff_assess.id as assess_id, staff_assess.cycle_id, staff_assess.target_user->>'userId' as target_user_id, staff_assess.target_user->>'userName' as target_user_name, to_char(staff_assess.begin_time,'YYYY-MM-DD') as begin_day, staff_assess.evaluation_project_id from staff_assess join staff_assess_task on staff_assess.staff_assess_task_id = staff_assess_task.id and staff_assess_task.deleted_at isnull where staff_assess.cycle_id = %d and staff_assess.types ='%s' ), -- 根据查看权限过滤合并数据 t_staff_assess_1 as ( ( select t_staff_assess_0.assess_id, t_staff_assess_0.target_user_id, t_staff_assess_0.target_user_name, t_staff_assess_0.begin_day, t_staff_assess_0.cycle_id from t_staff_assess_0 join t_project_3 on t_staff_assess_0.evaluation_project_id = t_project_3.project_id ) union ( select t_staff_assess_0.assess_id, t_staff_assess_0.target_user_id, t_staff_assess_0.target_user_name, t_staff_assess_0.begin_day, t_staff_assess_0.cycle_id from t_staff_assess_0 join t_user_1 on t_staff_assess_0.target_user_id = t_user_1.user_id ) ) ` params := []interface{}{operatorId, cycleId, hrbp, cycleId, operatorId, cycleId, assessType} sqlString = fmt.Sprintf(sqlString, params...) sqlString += ` 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.cycle_id, t_staff_assess_1.assess_id, staff_assess_content.id as content_id, staff_assess_content.name as content_name, staff_assess_content.category as content_category, staff_assess_content.remark, staff_assess_content.sort_by from t_staff_assess_1 left join staff_assess_content on t_staff_assess_1.assess_id = staff_assess_content.staff_assess_id where 1=1 -- AND staff_assess_content.id NOTNULL 部分脏数据 ` condition := make([]interface{}, 0) //if len(likeUserName) > 0 { // sqlString += ` and t_staff_assess_1.target_user_name like ? ` // condition = append(condition, "%"+likeUserName+"%") //} if len(userIds) > 0 { sqlString += ` and t_staff_assess_1.target_user_id in (?) ` condition = append(condition, pg.In(userIds)) } if len(beginDays) > 0 { sqlString += ` and t_staff_assess_1.begin_day in (?) ` condition = append(condition, pg.In(beginDays)) } //sqlString += ` order by convert_to(t_staff_assess_1.target_user_name,'GBK'), staff_assess_content.sort_by` sqlString += ` order by t_staff_assess_1.begin_day` tx := d.transactionContext.PgTx var result = make([]ExportPerformanceIndicator, 0) _, err := tx.Query(&result, sqlString, condition...) return result, err } type ExportData1 struct { AssessId string ContentId int CycleId string //周期id CycleName string //周期名称 TargetUserId string //被评估人的id TargetUserName string //被评估人的名称 BeginDay string //评估的日期 EvaluationProjectId string //项目id Value string //评估填写的值 SortBy int //评估项顺序 Category string //评估项分类 ContentName string //评估项名称 Weight float64 //权重 PromptText string //评估标准 Remark []domain.AssessContemtRemark } // 项目管理-成员列表 导出数据 func (d *StaffAssessDao) ExportDataUserAssess(param SearchConditin1) ([]ExportData1, error) { if param.Offset < 0 { param.Offset = 0 } if param.Limit < 0 { param.Limit = 5000 } sqlStr := ` 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, t_staff_assess_1.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 t_staff_assess_1 left join staff_assess_content on t_staff_assess_1.assess_id = staff_assess_content.staff_assess_id where 1=1 ` condition := []interface{}{} if len(param.TargetUserName) > 0 { sqlStr += ` and t_staff_assess_1.target_user_name like ? ` condition = append(condition, "%"+param.TargetUserName+"%") } if len(param.TargetUserId) > 0 { sqlStr += ` and t_staff_assess_1.target_user_id in (?) ` condition = append(condition, pg.In(param.TargetUserId)) } //加入排序 sqlStr += ` order by convert_to(t_staff_assess_1.target_user_name,'GBK'),t_staff_assess_1.begin_day,staff_assess_content.sort_by ` //获取前置sql语句 sqlStr0 := d.useTStaffAssess(param.CompanyId, param.CycleId, param.OperaterId, param.BeginDay, param.Hrbp, param.Limit, param.Offset, string(domain.AssessSelf)) sqlStr = sqlStr0 + sqlStr tx := d.transactionContext.PgTx var result []ExportData1 _, err := tx.Query(&result, sqlStr, condition...) return result, err } type AssessCycleDayExecutor struct { BeginDay string `pg:"begin_day"` CycleId string `pg:"cycle_id"` CycleName string `pg:"cycle_name"` EndTime string `pg:"end_time"` BeginTime string `pg:"begin_time"` } // 根据评估的人执行人id,搜索 executorId参与的评估周期 // func (d *StaffAssessDao) SearchAssessCycleMe(executorId int, companyId int, limit int, offset int) ([]AssessCycleDayExecutor, error) { // if limit < 0 { // limit = 20 // } // if offset < 0 { // offset = 0 // } // sqlStr := ` // SELECT // distinct on(staff_assess_task.cycle_id,staff_assess_task.begin_day) // staff_assess_task.cycle_id,staff_assess_task.cycle_name , // staff_assess_task.begin_day, // to_char(staff_assess_task.end_time at time zone 'PRC','YYYY-MM-DD HH24:MI:SS') as end_time, // to_char(staff_assess_task.begin_time at time zone 'PRC','YYYY-MM-DD HH24:MI:SS') as begin_time // FROM staff_assess_task // JOIN staff_assess ON staff_assess_task."id" = staff_assess."staff_assess_task_id" // WHERE staff_assess.company_id=? // and staff_assess_task.deleted_at isnull // and staff_assess.executor->>'userId'='?' // order by staff_assess_task.begin_day desc // limit ? offset ? // ` // tx := d.transactionContext.PgTx // condition := []interface{}{ // companyId, executorId, limit, offset, // } // result := []AssessCycleDayExecutor{} // _, err := tx.Query(&result, sqlStr, condition...) // return result, err // } // 根据评估的人执行人id,统计executorId参与的评估周期 func (d *StaffAssessDao) CountAssessCycleMe(executorId int, companyId int) (int, error) { sqlStr := ` select count(DISTINCT (staff_assess_task.cycle_id,staff_assess_task.begin_day )) as cnt FROM staff_assess_task JOIN staff_assess ON staff_assess_task."id" = staff_assess."staff_assess_task_id" WHERE staff_assess.company_id=? and staff_assess_task.deleted_at isnull and staff_assess.executor->>'userId'='?' ` tx := d.transactionContext.PgTx condition := []interface{}{ companyId, executorId, } var result int _, err := tx.QueryOne(pg.Scan(&result), sqlStr, condition...) return result, err } // 评估的指标 type ContentCategoryName struct { Category string `pg:"category"` //指标分类 Name string `pg:"name"` //指标名称 Weight float64 `pg:"weight"` //指标权重 CycleId string `pg:"cycle_id"` //周期id CycleName string `pg:"cycle_name"` //周期名称 SortBy int `pg:"sort_by"` // TargetUserId string `pg:"target_user_id"` //评估的目标员工id TargetUserName string `pg:"target_user_name"` //评估的目标员工名称 //Cnt int `pg:"cnt"` //排序 } // 员工绩效-综合管理-导出绩效指标 // 抽取出评估的指标 func (d *StaffAssessDao) SearchContentCategoryName(companyId int, cycleId int, userId int, hrbp int, exportUserIds []string) ([]ContentCategoryName, error) { sqlStr := ` select staff_assess_content.category, staff_assess_content."name" , staff_assess_content.weight , staff_assess_content.sort_by , t_staff_assess_1.cycle_id , t_staff_assess_1.cycle_name, t_staff_assess_1.target_user_id, t_staff_assess_1.target_user_name from staff_assess_content join t_staff_assess_1 on staff_assess_content.staff_assess_id = t_staff_assess_1.assess_id where 1=1 ` condition := []interface{}{} if len(exportUserIds) > 0 { condition = append(condition, pg.In(exportUserIds)) sqlStr += ` and t_staff_assess_1.target_user_id in(?) ` } sqlStr += ` group by category,"name" ,cycle_id ,cycle_name,weight , target_user_id,target_user_name,sort_by order by target_user_id,sort_by ` sqlStr0 := d.useTStaffAssess(companyId, cycleId, userId, "", hrbp, 5000, 0, string(domain.AssessSelf)) sqlStr = sqlStr0 + sqlStr tx := d.transactionContext.PgTx result := []ContentCategoryName{} _, err := tx.Query(&result, sqlStr, condition...) return result, err } type ExportData2 struct { CycleId string `pg:"cycle_id"` //周期id CycleName string `pg:"cycle_name"` //周期名称 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"` } // 员工绩效-综合管理-导出绩效-个人 // companyId int 公司id // cycleId int, 评估周期id // userId int, 用户id,谁要查看数据 // hrbp 是否搜索HRBP角色的用户可以查看,1:是;-1:否 // exportUserIds 只导出选中的员工的数据 func (d *StaffAssessDao) ExportDataUserAssess2(companyId int, cycleId int, operaterId int, hrbp int, exportUserIds []string) ([]*ExportData2, error) { sqlStr := ` 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.cycle_id, t_staff_assess_1.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 t_staff_assess_1 left join staff_assess_content on t_staff_assess_1.assess_id = staff_assess_content.staff_assess_id where 1=1 ` condition := []interface{}{} if len(exportUserIds) > 0 { condition = append(condition, pg.In(exportUserIds)) sqlStr += ` and t_staff_assess_1.target_user_id in(?) ` } //加入排序 sqlStr += ` order by t_staff_assess_1.begin_day` //获取前置sql语句 sqlStr0 := d.useTStaffAssess(companyId, cycleId, operaterId, "", hrbp, 5000, 0, string(domain.AssessSelf)) sqlStr = sqlStr0 + sqlStr tx := d.transactionContext.PgTx result := []*ExportData2{} _, err := tx.Query(&result, sqlStr, condition...) return result, err } type ContentValue struct { StaffAssessId int `pg:"staff_assess_id"` Value string `pg:"value"` } func (d *StaffAssessDao) SearchContentValueByAssessId(assessId []int) map[int][]string { if len(assessId) == 0 { return map[int][]string{} } sqlStr := `select staff_assess_content.staff_assess_id , staff_assess_content.value from staff_assess_content where staff_assess_content.staff_assess_id in (?)` tx := d.transactionContext.PgTx result := []ContentValue{} condition := []interface{}{pg.In(assessId)} _, err := tx.Query(&result, sqlStr, condition...) if err != nil { return map[int][]string{} } valueMap := map[int][]string{} for _, v := range result { if _, ok := valueMap[v.StaffAssessId]; !ok { valueMap[v.StaffAssessId] = []string{} } if len(v.Value) > 0 { valueMap[v.StaffAssessId] = append(valueMap[v.StaffAssessId], v.Value) } } return valueMap } type AssessContentLevelCode struct { Cnt int `pg:"cnt"` //数量 LevelValue string `pg:"level_value"` //评估的等级值 Category string `pg:"category"` //评估的分类 Name string `pg:"name"` //名称 } func (d *StaffAssessDao) CountAssessContentLevelCode(projectId int, targetUserId int, assessType domain.StaffAssessType, cycleId int) ([]AssessContentLevelCode, error) { sqlStr := ` select count(staff_assess_content.level_value) as cnt , staff_assess_content.level_value , staff_assess_content.category , staff_assess_content."name" from staff_assess_content join staff_assess on staff_assess_content.staff_assess_id = staff_assess.id where 1=1 and staff_assess.deleted_at isnull and target_user ->>'userId'='?' and staff_assess."types" = ? and staff_assess_content.level_value notnull and staff_assess.cycle_id =? and staff_assess.evaluation_project_id=? group by level_value,category,"name" ` var result []AssessContentLevelCode condition := []interface{}{ targetUserId, string(assessType), cycleId, projectId, } tx := d.transactionContext.PgTx _, err := tx.Query(&result, sqlStr, condition...) return result, err } type CountUncompletedSelfAssess struct { EvaluationProjectId int TargetUserId int Cnt int } // CountUncompletedSelfAssess 统计整个项目周期内,相关人自评未完成数量 func (d *StaffAssessDao) CountUncompletedSelfAssess(companyId int, projectIds []int) ([]CountUncompletedSelfAssess, error) { sqlStr := ` SELECT staff_assess.evaluation_project_id, staff_assess.target_user ->> 'userId' AS target_user_id, COUNT ( staff_assess.ID ) AS cnt FROM staff_assess WHERE staff_assess.company_id = ? AND staff_assess.types = 'self' AND staff_assess.evaluation_project_id IN (?) AND staff_assess.status = 'uncompleted' AND staff_assess.end_time < now() GROUP BY evaluation_project_id, target_user_id` condition := []interface{}{companyId, pg.In(projectIds)} tx := d.transactionContext.PgTx result := make([]CountUncompletedSelfAssess, 0) _, err := tx.Query(&result, sqlStr, condition...) return result, err } // SearchExecutorAssesBeforeNow func (d *StaffAssessDao) SearchExecutorAssessBeforeNow(executorId int, companyId int, limit int, offset int) ([]AssessCycleDayExecutor, int, error) { sqlStr1 := `select distinct on(cycle_id ,begin_day) staff_assess.cycle_id , staff_assess.cycle_name , to_char(staff_assess.begin_time at time zone 'PRC','YYYY-MM-DD') as begin_day, to_char(staff_assess.end_time at time zone 'PRC','YYYY-MM-DD HH24:MI:SS') as end_time, to_char(staff_assess.begin_time at time zone 'PRC','YYYY-MM-DD HH24:MI:SS') as begin_time from staff_assess join staff_assess_task on staff_assess.staff_assess_task_id = staff_assess_task.id WHERE staff_assess.company_id=? and staff_assess.deleted_at isnull and staff_assess_task.deleted_at isnull and ( staff_assess.end_time < now() ) and staff_assess.executor ->> 'userId'='?' order by begin_day desc limit ? offset ?` condition1 := []interface{}{ companyId, executorId, limit, offset, } sqlStr2 := `select count( distinct (staff_assess.cycle_id ,to_char(staff_assess.begin_time at time zone 'PRC','YYYY-MM-DD')) ) as cnt from staff_assess join staff_assess_task on staff_assess.staff_assess_task_id = staff_assess_task.id WHERE staff_assess.company_id=? and staff_assess.deleted_at isnull and ( staff_assess.end_time < now() ) and staff_assess_task.deleted_at isnull and staff_assess.executor ->> 'userId'='?' ` condition2 := []interface{}{ companyId, executorId, } tx := d.transactionContext.PgTx result := []AssessCycleDayExecutor{} _, err := tx.Query(&result, sqlStr1, condition1...) if err != nil { return nil, 0, err } var cnt int _, err = tx.QueryOne(pg.Scan(&cnt), sqlStr2, condition2...) if err != nil { return nil, 0, err } return result, cnt, nil } // SearchExecutorAssessAfterNow SearchExecutorAssesAfterNow func (d *StaffAssessDao) SearchExecutorAssessAfterNow(executorId int, companyId int) ([]AssessCycleDayExecutor, error) { sqlStr := `select distinct on(cycle_id ,begin_day) staff_assess.cycle_id , staff_assess.cycle_name , to_char(staff_assess.begin_time at time zone 'PRC','YYYY-MM-DD') as begin_day, to_char(staff_assess.end_time at time zone 'PRC','YYYY-MM-DD HH24:MI:SS') as end_time, to_char(staff_assess.begin_time at time zone 'PRC','YYYY-MM-DD HH24:MI:SS') as begin_time from staff_assess join staff_assess_task on staff_assess.staff_assess_task_id = staff_assess_task.id WHERE staff_assess.company_id=? and staff_assess.deleted_at isnull and ( staff_assess.end_time >= now() ) and staff_assess_task.deleted_at isnull and staff_assess.executor ->> 'userId'='?' order by begin_day` condition1 := []interface{}{ companyId, executorId, } tx := d.transactionContext.PgTx result := []AssessCycleDayExecutor{} _, err := tx.Query(&result, sqlStr, condition1...) return result, err } type AssessSelfContentRemark struct { Category string `pg:"category"` //评估的分类 Name string `pg:"name"` //名称 Remark []domain.AssessContemtRemark `pg:"remark"` BeginDay string `pg:"begin_day"` //开始的天数 LevelValue string `pg:"level_value"` } func (d *StaffAssessDao) SearchAssessSelfContentRemark(projectId int, targetUserId int, category string, name string, levalvalue string) ([]AssessSelfContentRemark, error) { sqlStr := ` select staff_assess_content.level_value , staff_assess_content.category , staff_assess_content."name" , staff_assess_content.remark , to_char(staff_assess.begin_time at time zone 'PRC', 'YYYY-MM-DD') as begin_day from staff_assess_content join staff_assess on staff_assess_content.staff_assess_id = staff_assess.id where 1=1 and staff_assess.deleted_at isnull and staff_assess_content.category =? and staff_assess_content."name" = ? and staff_assess."types" ='self' and staff_assess_content.level_value =? and staff_assess.evaluation_project_id=? and target_user ->>'userId'='?' ORDER BY staff_assess.begin_time ` var result []AssessSelfContentRemark condition := []interface{}{ category, name, levalvalue, projectId, targetUserId, } tx := d.transactionContext.PgTx _, err := tx.Query(&result, sqlStr, condition...) return result, err }