package dao import ( "fmt" "time" "github.com/go-pg/pg/v10" pgTransaction "github.com/linmadan/egglib-go/transaction/pg" "gitlab.fjmaimaimai.com/allied-creation/performance/pkg/domain" ) type TaskDao struct { transactionContext *pgTransaction.TransactionContext } func NewTaskDao(options map[string]interface{}) *TaskDao { var transactionContext *pgTransaction.TransactionContext if value, ok := options["transactionContext"]; ok { transactionContext = value.(*pgTransaction.TransactionContext) } return &TaskDao{ transactionContext: transactionContext, } } func (d *TaskDao) catchTaskIdByPermission(userId int) string { sqlStr := `with -- 人员自身以及全下级 recursive t_user as ( ( select "user".id,"user".parent_id from "user" where "user".id=%d ) union ( select "child_user".id,"child_user".parent_id from "user" as "child_user" join t_user as "parent_user" on "parent_user".id="child_user".parent_id where "child_user".deleted_at isnull ) ), -- 根据任务负责人和相关人员查询 t_task_0 as ( (select task.id from task join t_user on task.leader ->>'id'=t_user.id::text where task.deleted_at isnull ) union (select task.id from task where task.related_user@>'[%d]' and task.deleted_at isnull) ), t_task_ignore as ( select * from task_ignore where task_ignore.user_id =%d ), -- 过滤取消关注的 t_task_1 as ( select t_task_0.id from t_task_0 left join t_task_ignore on t_task_0.id=t_task_ignore.task_id where t_task_ignore.id isnull )` return fmt.Sprintf(sqlStr, userId, userId, userId) } type ListTaskCondition struct { Limit int //分页 Offset int //分页 CompanyId int //公司id UserId int //谁要查看任务数据 OnlyMy bool //只查看我负责的任务 LevelName string //任务类型名称 SearchWord string } // 任务和里程碑列表 type ListTaskStage struct { TaskId int `pg:"task_id"` TaskName string `pg:"task_name"` TaskStatus int `pg:"task_status"` TaskAliasName string `pg:"task_alias_name"` LeaderName string `pg:"leader_name"` LeaderId string `pg:"leader_id"` LevelName string `pg:"level_name"` Level int `pg:"level"` Anomaly int `pg:"anomaly"` WarnFlag int `pg:"warn_flag"` UpdatedAt time.Time `pg:"updated_at"` CreatedAt time.Time `pg:"created_at"` StageId int `pg:"stage_id"` StageName string `pg:"stage_name"` StageSortBy int `pg:"stage_sort_by"` PlanCompletedAt int `pg:"plan_completed_at"` RealCompletedAt int `pg:"real_completed_at"` } // 获取任务总数,用于页面展示; 有过滤查看权限 func (d *TaskDao) CountTaskNotHrbp(param ListTaskCondition) (int, error) { task1 := d.catchTaskIdByPermission(param.UserId) withSql := task1 + `select count(*) from task join t_task_1 on task.id =t_task_1.id where 1=1 and task.deleted_at isnull and task.company_id=?` condition := []interface{}{param.CompanyId} whereSql := `` if param.OnlyMy { condition = append(condition, param.UserId) whereSql += ` and task.leader ->>'id' = '?' ` } if param.SearchWord != "" { condition = append(condition, param.SearchWord, param.SearchWord) whereSql += ` and (task.leader->>'name' like ? or task.alias like ?)` } if len(param.LevelName) > 0 { condition = append(condition, param.LevelName) whereSql += ` and task.level_name = ? ` } sqlStr := withSql + whereSql tx := d.transactionContext.PgTx var cnt int _, err := tx.QueryOne(pg.Scan(&cnt), sqlStr, condition...) return cnt, err } // 获取任务总数,用于页面展示; 无过滤查看权限 func (d *TaskDao) CountTaskByHrbp(param ListTaskCondition) (int, error) { withSql := `with t_task_ignore as ( select task_ignore.task_id,task_ignore.id from task_ignore where task_ignore.user_id =? )select count(*) from task left join t_task_ignore on t_task_ignore.task_id=task.id where 1=1 and task.deleted_at isnull and t_task_ignore.id isnull and task.company_id=?` condition := []interface{}{param.UserId, param.CompanyId} whereSql := `` if param.OnlyMy { condition = append(condition, param.UserId) whereSql += ` and task.leader ->>'id' = '?' ` } if param.SearchWord != "" { condition = append(condition, param.SearchWord, param.SearchWord) whereSql += ` and (task.leader->>'name' like ? or task.alias like ?)` } if len(param.LevelName) > 0 { condition = append(condition, param.LevelName) whereSql += ` and task.level_name = ? ` } sqlStr := withSql + whereSql tx := d.transactionContext.PgTx var cnt int _, err := tx.QueryOne(pg.Scan(&cnt), sqlStr, condition...) return cnt, err } // 统计里程碑异常的数量,以非hrbp角色统计 func (d *TaskDao) CountTaskStageAnomalyNotHrbp(param ListTaskCondition) (int, error) { task1 := d.catchTaskIdByPermission(param.UserId) withSql := task1 + ` select count(*) from task join t_task_1 on task.id =t_task_1.id where 1=1 and task.company_id =? and task.deleted_at isnull and ( cast(COALESCE(task.current_stage ->>'planCompletedAt','0') as int)<floor(extract(epoch from now())) or task.warn_flag = 1 ) ` condition := []interface{}{param.CompanyId} whereSql := `` if param.OnlyMy { condition = append(condition, param.UserId) whereSql += ` and task.leader ->>'id' = '?' ` } if param.SearchWord != "" { condition = append(condition, param.SearchWord, param.SearchWord) whereSql += ` and (task.leader->>'name' like ? or task.alias like ?)` } if len(param.LevelName) > 0 { condition = append(condition, param.LevelName) whereSql += ` and task.level_name = ? ` } sqlStr := withSql + whereSql tx := d.transactionContext.PgTx var cnt int _, err := tx.QueryOne(pg.Scan(&cnt), sqlStr, condition...) return cnt, err } // 统计里程碑异常的数量,以hrbp角色统计 func (d *TaskDao) CountTaskStageAnomalyByHrbp(param ListTaskCondition) (int, error) { withSql := `with t_task_ignore as ( select task_ignore.task_id,task_ignore.id from task_ignore where task_ignore.user_id =? )select count(*) from task left join t_task_ignore on t_task_ignore.task_id=task.id where 1=1 and task.company_id =? and task.deleted_at isnull and t_task_ignore.id isnull and ( cast(COALESCE(task.current_stage ->>'planCompletedAt','0') as int)<floor(extract(epoch from now())) or task.warn_flag = 1 )` condition := []interface{}{param.UserId, param.CompanyId} whereSql := `` if param.OnlyMy { condition = append(condition, param.UserId) whereSql += ` and task.leader ->>'id' = '?' ` } if param.SearchWord != "" { condition = append(condition, param.SearchWord, param.SearchWord) whereSql += ` and (task.leader->>'name' like ? or task.alias like ?)` } if len(param.LevelName) > 0 { condition = append(condition, param.LevelName) whereSql += ` and task.level_name = ? ` } sqlStr := withSql + whereSql tx := d.transactionContext.PgTx var cnt int _, err := tx.QueryOne(pg.Scan(&cnt), sqlStr, condition...) return cnt, err } type TaskData3 struct { TaskId int `pg:"task_id"` TaskName string `pg:"task_name"` TaskAlias string `pg:"task_alias"` LeaderName string `pg:"leader_name"` LeaderId string `pg:"leader_id"` } // 根据负责人获取超期未完成的异常的里程碑任务 func (d *TaskDao) TaskStageAnomalyByLeader(leaderId []string) ([]TaskData3, error) { sqlStr := `select task.id as task_id, task."name" as task_name , task.alias as task_alias, task.leader ->>'id' as leader_id, task.leader ->>'name' as leader_name from task where 1=1 and task.deleted_at isnull and ( cast(COALESCE(task.current_stage ->>'planCompletedAt','0') as int) between 1 and floor(extract(epoch from now())) ) and task.leader ->>'id' in(?) ` result := []TaskData3{} tx := d.transactionContext.PgTx _, err := tx.Query(&result, sqlStr, pg.In(leaderId)) return result, err } // 根据任务相关人获取超期未完成的异常的里程碑任务 func (d *TaskDao) TaskStageAnomalyByRelatedUser(relatedUserId int) ([]TaskData3, error) { sqlStr := `select task.id as task_id, task."name" as task_name , task.alias as task_alias, task.leader ->>'id' as leader_id, task.leader ->>'name' as leader_name from task where 1=1 and task.deleted_at isnull and ( cast(COALESCE(task.current_stage ->>'planCompletedAt','0') as int) between 1 and floor(extract(epoch from now())) ) and task.related_user @>? ` relatedUser := fmt.Sprintf("[%d]", relatedUserId) result := []TaskData3{} tx := d.transactionContext.PgTx _, err := tx.Query(&result, sqlStr, relatedUser) return result, err } type TaskData2 struct { Id int `pg:"id"` Name string `pg:"name"` Alias string `pg:"alias"` LeaderName string `pg:"leader_name"` Anomaly string `pg:"anomaly"` } // 根据任务负责任务获取异常的任务 func (d *TaskDao) TaskAnomalyByLeader(leaderId []string, anomaly int) ([]TaskData2, error) { sqlStr := `select task."id", task."name" , task.alias , task.leader ->>'name' as leader_name, task.anomaly from task where task.anomaly >=? and task.leader ->>'id' in(?) and task.deleted_at isnull ` result := []TaskData2{} tx := d.transactionContext.PgTx _, err := tx.Query(&result, sqlStr, anomaly, pg.In(leaderId)) return result, err } // 统计任务反馈异常的数量,以非hrbp角色统计 func (d *TaskDao) CountTaskAnomalyNotHrbp(param ListTaskCondition) (int, error) { task1 := d.catchTaskIdByPermission(param.UserId) withSql := task1 + ` select count(*) from task join t_task_1 on task.id =t_task_1.id where 1=1 and task.anomaly>0 and company_id=? and task.deleted_at isnull ` condition := []interface{}{param.CompanyId} whereSql := `` if param.OnlyMy { condition = append(condition, param.UserId) whereSql += ` and task.leader ->>'id' = '?' ` } if param.SearchWord != "" { condition = append(condition, param.SearchWord, param.SearchWord) whereSql += ` and (task.leader->>'name' like ? or task.alias like ?)` } if len(param.LevelName) > 0 { condition = append(condition, param.LevelName) whereSql += ` and task.level_name = ? ` } sqlStr := withSql + whereSql tx := d.transactionContext.PgTx var cnt int _, err := tx.QueryOne(pg.Scan(&cnt), sqlStr, condition...) return cnt, err } // 统计任务反馈异常的数量,以hrbp角色统计 func (d *TaskDao) CountTaskAnomalyByHrbp(param ListTaskCondition) (int, error) { withSql := `with t_task_ignore as ( select task_ignore.task_id,task_ignore.id from task_ignore where task_ignore.user_id =? )select count(*) from task left join t_task_ignore on t_task_ignore.task_id=task.id where 1=1 and task.anomaly>0 and task.company_id=? and t_task_ignore.id isnull and task.deleted_at isnull` condition := []interface{}{param.UserId, param.CompanyId} whereSql := `` if param.OnlyMy { condition = append(condition, param.UserId) whereSql += ` and task.leader ->>'id' = '?' ` } if param.SearchWord != "" { condition = append(condition, param.SearchWord, param.SearchWord) whereSql += ` and (task.leader->>'name' like ? or task.alias like ?) ` } if len(param.LevelName) > 0 { condition = append(condition, param.LevelName) whereSql += ` and task.level_name = ? ` } sqlStr := withSql + whereSql tx := d.transactionContext.PgTx var cnt int _, err := tx.QueryOne(pg.Scan(&cnt), sqlStr, condition...) return cnt, err } // 任务数据 type TaskData1 struct { Name string `pg:"name"` Alias string `pg:"alias"` LeaderId string `pg:"leader_id"` LeaderName string `pg:"leader_name"` LevelName string `pg:"level_name"` } // 获取任务列表 func (d *TaskDao) ListTask2(userId int) ([]TaskData1, error) { task1 := d.catchTaskIdByPermission(userId) sqlStr := task1 + ` select task."name" ,task.alias , task.leader ->>'id' as leader_id, task.leader ->>'name' as leader_name, task.level_name from task join t_task_1 on task.id =t_task_1.id where task.deleted_at isnull ` result := []TaskData1{} tx := d.transactionContext.PgTx _, err := tx.Query(&result, sqlStr) return result, err } // 获取任务列表 func (d *TaskDao) ListTask2ForHrbp(userId int, companyId int) ([]TaskData1, error) { sqlStr := `with t_task_ignore as ( select task_ignore.task_id,task_ignore.id from task_ignore where task_ignore.user_id =? )select task."name" ,task.alias , task.leader ->>'id' as leader_id, task.leader ->>'name' as leader_name, task.level_name from task left join t_task_ignore on t_task_ignore.task_id=task.id where 1=1 and task.company_id=? and task.deleted_at isnull` result := []TaskData1{} tx := d.transactionContext.PgTx _, err := tx.Query(&result, sqlStr, userId, companyId) return result, err } type TaskData4 struct { TaskId int `pg:"task_id"` TaskName string `pg:"task_name"` TaskAlias string `pg:"task_alias"` LeaderName string `pg:"leader_name"` LeaderId int `pg:"leader_id"` RelatedUser []int `pg:"related_user,type:jsonb"` } // TaskStageAnomalyAll 获取所有里程碑异常的任务, func (d TaskDao) TaskStageAnomalyAll() ([]TaskData4, error) { sqlStr := `select task.id as task_id, task."name" as task_name , task.alias as task_alias, task.leader ->>'id' as leader_id, task.leader ->>'name' as leader_name, task.related_user from task where 1=1 and task.deleted_at isnull and ( cast(COALESCE(task.current_stage ->>'planCompletedAt','0') as int) between 1 and floor(extract(epoch from now())) ) ` result := []TaskData4{} tx := d.transactionContext.PgTx _, err := tx.Query(&result, sqlStr) return result, err } type TaskData5 struct { TaskId int `pg:"task_id"` TaskName string `pg:"task_name"` TaskAlias string `pg:"task_alias"` LeaderName string `pg:"leader_name"` LeaderId int `pg:"leader_id"` RelatedUser []int `pg:"related_user,type:jsonb"` Anomaly int `pg:"anomaly"` } // TaskRecordAnomalyAll 获取所有反馈异常的任务, func (d TaskDao) TaskRecordAnomalyAll() ([]TaskData5, error) { sqlStr := `select task.id as task_id, task."name" as task_name , task.alias as task_alias, task.anomaly, task.leader ->>'id' as leader_id, task.leader ->>'name' as leader_name, task.related_user from task where 1=1 and task.deleted_at isnull and task.anomaly>0 ` result := []TaskData5{} tx := d.transactionContext.PgTx _, err := tx.Query(&result, sqlStr) return result, err } type ListTask struct { SortBy1 int `pg:"sort_by_1"` CPlanCompletedAt int `pg:"c_plan_completed_at"` SortBy2 int `pg:"sort_by_2"` TaskId int `pg:"task_id"` Alias string `pg:"alias"` LeaderName string `pg:"leader_name"` Level int `pg:"level"` LevelName string `pg:"level_name"` Anomaly int `pg:"anomaly"` Status int `pg:"status"` UpdatedAt time.Time `pg:"updated_at"` CreatedAt time.Time `pg:"created_at"` CurrentStage domain.TaskStage `pg:"current_stage"` // 当前执行的里程碑 LastStage domain.TaskStage `pg:"last_stage"` // 上一个完成的里程碑 SortBy int `pg:"sort_by"` EndTime int `pg:"end_time"` } // (新)获取任务,用于页面展示; 无过滤查看权限 // userid 谁要查看任务数据 // limit 分页 // offset 分页 func (d *TaskDao) ListTaskByHrbpV2(param ListTaskCondition) ([]ListTask, error) { sqlStr := ` with t_task_ignore as ( select task_ignore.task_id,task_ignore.id from task_ignore where task_ignore.user_id =? ), t_task as ( select cast(COALESCE(task.current_stage ->>'planCompletedAt','0') as int) as c_plan_completed_at, cast(COALESCE(task.last_stage ->>'planCompletedAt','0') as int) as l_plan_completed_at, cast(COALESCE(task.last_stage ->>'realCompletedAt','0') as int) as l_real_completed_at, floor(extract(epoch from now())) as now_time, task.id , task.alias , task.leader ->>'name' as leader_name, task."level", task.level_name , task.anomaly , task.status , task.updated_at, task.current_stage, task.last_stage, task.sort_by, task.end_time, task.created_at from task left join t_task_ignore on t_task_ignore.task_id=task.id where 1=1 and t_task_ignore.id isnull and task.company_id=? and task.deleted_at isnull %s ), t_task_1 as ( select (case when t_task.c_plan_completed_at=0 then 0 when t_task.c_plan_completed_at-now_time>0 then 0 else t_task.c_plan_completed_at-now_time end) as sort_by_1, (case when t_task.l_plan_completed_at=0 then 0 when t_task.l_plan_completed_at-t_task.l_real_completed_at>0 then 0 else t_task.l_plan_completed_at-t_task.l_real_completed_at end) as sort_by_2, t_task.c_plan_completed_at , t_task.id as task_id, t_task.alias , t_task.leader_name, t_task."level", t_task.level_name , t_task.anomaly , t_task.status , t_task.updated_at , t_task.current_stage, t_task.last_stage, t_task.sort_by, t_task.end_time, t_task.created_at from t_task where 1=1 )select * from t_task_1 order by t_task_1.status, t_task_1.sort_by_1, t_task_1.sort_by_2,t_task_1.anomaly desc, t_task_1.sort_by,t_task_1."created_at" limit ? offset ? ` condition := []interface{}{param.UserId, param.CompanyId} whereSql := `` if param.OnlyMy { condition = append(condition, param.UserId) whereSql += ` and task.leader ->>'id' = '?' ` } if param.SearchWord != "" { condition = append(condition, param.SearchWord, param.SearchWord) whereSql += ` and (task.leader->>'name' like ? or task.alias like ?)` } if len(param.LevelName) > 0 { condition = append(condition, param.LevelName) whereSql += ` and task.level_name = ? ` } strSql2 := fmt.Sprintf(sqlStr, whereSql) condition = append(condition, param.Limit, param.Offset) result := []ListTask{} tx := d.transactionContext.PgTx _, err := tx.Query(&result, strSql2, condition...) return result, err } func (d *TaskDao) ListTaskNotHrbpV2(param ListTaskCondition) ([]ListTask, error) { withsql := d.catchTaskIdByPermission(param.UserId) strSql := withsql + ` ,t_task as ( select cast(COALESCE(task.current_stage ->>'planCompletedAt','0') as int) as c_plan_completed_at, cast(COALESCE(task.last_stage ->>'planCompletedAt','0') as int) as l_plan_completed_at, cast(COALESCE(task.last_stage ->>'realCompletedAt','0') as int) as l_real_completed_at, floor(extract(epoch from now())) as now_time, task.id , task.alias , task.leader ->>'name' as leader_name, task."level", task.level_name , task.anomaly , task.current_stage, task.last_stage, task.status , task.updated_at, task.sort_by, task.end_time, task.created_at from task join t_task_1 on t_task_1.id=task.id where 1=1 and task.company_id=? and task.deleted_at isnull %s ), tt_task_1 as ( select (case when t_task.c_plan_completed_at=0 then 0 when t_task.c_plan_completed_at-now_time>0 then 0 else t_task.c_plan_completed_at-now_time end) as sort_by_1, (case when t_task.l_plan_completed_at=0 then 0 when t_task.l_plan_completed_at-t_task.l_real_completed_at>0 then 0 else t_task.l_plan_completed_at-t_task.l_real_completed_at end) as sort_by_2, t_task.c_plan_completed_at , t_task.id as task_id, t_task.alias , t_task.leader_name, t_task."level", t_task.level_name , t_task.anomaly , t_task.current_stage, t_task.last_stage, t_task.status , t_task.updated_at , t_task.sort_by, t_task.end_time, t_task.created_at from t_task where 1=1 )select * from tt_task_1 order by tt_task_1.status,tt_task_1.sort_by_1, tt_task_1.sort_by_2,tt_task_1.anomaly desc, tt_task_1.sort_by,tt_task_1."created_at" limit ? offset ? ` condition := []interface{}{param.CompanyId} whereSql := `` if param.OnlyMy { condition = append(condition, param.UserId) whereSql += ` and task.leader ->>'id' = '?' ` } if param.SearchWord != "" { condition = append(condition, param.SearchWord, param.SearchWord) whereSql += ` and (task.leader->>'name' like ? or task.alias like ?)` } if len(param.LevelName) > 0 { condition = append(condition, param.LevelName) whereSql += ` and task.level_name = ? ` } strSql2 := fmt.Sprintf(strSql, whereSql) condition = append(condition, param.Limit, param.Offset) result := []ListTask{} tx := d.transactionContext.PgTx _, err := tx.Query(&result, strSql2, condition...) return result, err } // 获取已取消关注的任务列表 func (d *TaskDao) ListTaskIgnore(param ListTaskCondition) ([]ListTask, error) { sqlStr := `with t_task_ignore as( select task_ignore.task_id from task_ignore where user_id =? )select task.id as task_id , task.alias , task.leader ->>'name' as leader_name, task."level" , task.level_name , task.anomaly , task.status , task.updated_at , task.created_at , task.current_stage , task.last_stage , task.sort_by , task.end_time from task join t_task_ignore on t_task_ignore.task_id=task.id where 1=1 and task.company_id=? and task.deleted_at isnull ` condition := []interface{}{param.UserId, param.CompanyId} whereSql := `` if param.OnlyMy { condition = append(condition, param.UserId) whereSql += ` and task.leader ->>'id' = '?' ` } if param.SearchWord != "" { condition = append(condition, param.SearchWord, param.SearchWord) whereSql += ` and (task.leader->>'name' like ? or task.alias like ?)` } if len(param.LevelName) > 0 { condition = append(condition, param.LevelName) whereSql += ` and task.level_name = ? ` } condition = append(condition, param.Limit, param.Offset) sqlStr = sqlStr + whereSql + ` limit ? offset ? ` result := []ListTask{} tx := d.transactionContext.PgTx _, err := tx.Query(&result, sqlStr, condition...) return result, err } func (d *TaskDao) CountTaskIgnore(param ListTaskCondition) (int, error) { sqlStr := `with t_task_ignore as( select task_ignore.task_id from task_ignore where user_id =? )select count(*) as cnt from task join t_task_ignore on t_task_ignore.task_id=task.id where 1=1 and task.company_id=? and task.deleted_at isnull ` condition := []interface{}{param.UserId, param.CompanyId} whereSql := `` if param.OnlyMy { condition = append(condition, param.UserId) whereSql += ` and task.leader ->>'id' = '?' ` } if param.SearchWord != "" { condition = append(condition, param.SearchWord, param.SearchWord) whereSql += ` and (task.leader->>'name' like ? or task.alias like ?)` } if len(param.LevelName) > 0 { condition = append(condition, param.LevelName) whereSql += ` and task.level_name = ? ` } condition = append(condition, param.Limit, param.Offset) sqlStr = sqlStr + whereSql + ` limit ? offset ? ` result := 0 tx := d.transactionContext.PgTx _, err := tx.Query(pg.Scan(&result), sqlStr, condition...) return result, err }