staff_assess_dao_2.go
10.5 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
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 := `
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
}