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 
		)
	),
	-- 根据任务负责人和相关人员查询
	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 //只查看我负责的任务
	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"`
}

// (弃用) 获取任务以及里程碑列表，用于页面展示; 有过滤查看权限
// userid 谁要查看任务数据
// limit 分页
// offset 分页
func (d *TaskDao) ListTaskStageNotHrbp(param ListTaskCondition) ([]ListTaskStage, error) {
	task1 := d.catchTaskIdByPermission(param.UserId)
	withSql := task1 + `,
	-- 获取的里程碑数据，以及排序
	t_task_tage_1 as(
		select 
		task.id as task_id,
		task."name" as task_name,
		task."alias" as task_alias_name,
		task.leader ->>'name' as leader_name,
		task.leader ->>'id' as leader_id,
		task.level_name ,
		task.anomaly ,
		task.updated_at ,
		task.created_at ,
		task."level" ,
		task."warn_flag",
		task."status" as task_status,
		task_stage."id" as stage_id,
		task_stage."name" as stage_name,
		task_stage.sort_by as stage_sort_by,
		task_stage.plan_completed_at,
		task_stage.real_completed_at,
		(case 
			when task_stage.real_completed_at =0 
			then task_stage.plan_completed_at - floor( extract(epoch from now()))
			else task_stage.plan_completed_at - task_stage.real_completed_at
		end) as diff_time
		from  task 
		join  t_task_1 on task.id=t_task_1.id
		join task_stage on task.id =task_stage.task_id 
		where 1=1 and task.company_id=?
		order by diff_time,task."level",task.created_at desc
	),
	-- 按任务数据分页获取
	t_task_page as (
		select distinct t_task_tage_1.task_id 
		from t_task_tage_1 
		where 1=1
		%s
		limit ? offset ?
	)
	select 
	    t_task_tage_1.task_id,
		t_task_tage_1.task_name,
		t_task_tage_1.task_alias_name,
		t_task_tage_1.leader_name,
		t_task_tage_1.level_name ,
		t_task_tage_1.anomaly ,
		t_task_tage_1.updated_at ,
		t_task_tage_1.created_at ,
		t_task_tage_1."level" ,
		t_task_tage_1.plan_completed_at,
		t_task_tage_1.real_completed_at,
		t_task_tage_1.stage_name,
		t_task_tage_1.stage_id,
		t_task_tage_1.stage_sort_by,
		t_task_tage_1."warn_flag"
	from t_task_tage_1
	where t_task_tage_1.task_id in(
	 select t_task_page.task_id from t_task_page
	)`
	condition := []interface{}{param.CompanyId}
	whereSql := ``
	if param.OnlyMy {
		condition = append(condition, param.UserId)
		whereSql += ` and t_task_tage_1.leader_id = '?' `
	}
	if param.SearchWord != "" {
		condition = append(condition, param.SearchWord, param.SearchWord)
		whereSql += ` and (t_task_tage_1.leader_name like ? or t_task_tage_1.task_alias_name like ?)`
	}
	condition = append(condition, param.Limit, param.Offset)
	sqlStr := fmt.Sprintf(withSql, whereSql)
	result := []ListTaskStage{}
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, sqlStr, condition...)
	return result, err
}

// 获取任务总数，用于页面展示; 有过滤查看权限
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 ?)`
	}

	sqlStr := withSql + whereSql
	tx := d.transactionContext.PgTx
	var cnt int
	_, err := tx.QueryOne(pg.Scan(&cnt), sqlStr, condition...)
	return cnt, err
}

// (弃用)获取任务以及里程碑列表，用于页面展示; 无过滤查看权限
// userid 谁要查看任务数据
// limit 分页
// offset 分页
func (d *TaskDao) ListTaskStageByHrbp(param ListTaskCondition) ([]ListTaskStage, error) {
	withSql := `with 
	t_task_ignore as (
			select task_ignore.task_id,task_ignore.id  from  task_ignore where task_ignore.user_id =?
	),
	-- 获取的里程碑数据，以及排序
	t_task_tage_1 as(
		select 
		task.id as task_id,
		task."name" as task_name,
		task."alias" as task_alias_name,
		task."status" as task_status,
		task.leader ->>'name' as leader_name,
		task.leader ->>'id' as leader_id,
		task.level_name ,
		task.anomaly ,
		task.updated_at ,
		task."warn_flag",
		task.created_at ,
		task."level" ,
		task_stage."id" as stage_id,
		task_stage."name" as stage_name,
		task_stage.sort_by as stage_sort_by,
		task_stage.plan_completed_at,
		task_stage.real_completed_at,
		(case 
			when task_stage.real_completed_at =0 
			then task_stage.plan_completed_at - floor( extract(epoch from now()))
			else task_stage.plan_completed_at - task_stage.real_completed_at
		end) as diff_time
		from  task 
		join task_stage on task.id =task_stage.task_id 
		where 1=1 and task.company_id=?
		order by diff_time,task."level",task.created_at desc
	),
	-- 按任务数据分页获取
	t_task_page as (
		select distinct t_task_tage_1.task_id 
		from t_task_tage_1
		left join  t_task_ignore on t_task_ignore.task_id=t_task_tage_1.task_id
		where t_task_ignore.id isnull 
		%s
		limit ? offset ?
	)
	select 
		t_task_tage_1.task_id,
		t_task_tage_1.task_name,
		t_task_tage_1.task_alias_name,
		t_task_tage_1.task_status,
		t_task_tage_1.leader_name,
		t_task_tage_1.level_name ,
		t_task_tage_1.anomaly ,
		t_task_tage_1.updated_at ,
		t_task_tage_1.created_at ,
		t_task_tage_1."level" ,
		t_task_tage_1.plan_completed_at,
		t_task_tage_1.real_completed_at,
		t_task_tage_1.stage_name,
		t_task_tage_1.stage_id,
		t_task_tage_1."warn_flag",
		t_task_tage_1.stage_sort_by
	from t_task_tage_1
	where t_task_tage_1.task_id in(
	 select t_task_page.task_id from t_task_page
	)`
	condition := []interface{}{param.UserId, param.CompanyId}
	whereSql := ``
	if param.OnlyMy {
		condition = append(condition, param.UserId)
		whereSql += ` and t_task_tage_1.leader_id = '?' `
	}
	if param.SearchWord != "" {
		condition = append(condition, param.SearchWord, param.SearchWord)
		whereSql += ` and (t_task_tage_1.leader_name like ? or t_task_tage_1.task_alias_name like ?) `
	}
	condition = append(condition, param.Limit, param.Offset)
	sqlStr := fmt.Sprintf(withSql, whereSql)
	result := []ListTaskStage{}
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, sqlStr, condition...)
	return result, 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 ?)`
	}
	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 ->>'plan_completed_at','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 ?)`
	}
	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 ->>'plan_completed_at','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 ?)`
	}

	sqlStr := withSql + whereSql
	tx := d.transactionContext.PgTx
	var cnt int
	_, err := tx.QueryOne(pg.Scan(&cnt), sqlStr, condition...)
	return cnt, err
}

func (d *TaskDao) IncreaseAnomaly(id []int, incr int) error {
	if len(id) == 0 {
		return nil
	}
	sqlStr := `update  task set anomaly=task.anomaly+?
	where  id in(?)`
	tx := d.transactionContext.PgTx
	_, err := tx.Exec(sqlStr, incr, pg.In(id))
	return err
}

// TaskStageData
// type TaskStageData struct {
// 	TaskStageId   int    `pg:"task_stage_id"`
// 	TaskStageName string `pg:"task_stage_name"`
// 	LeaderName    string `pg:"leader_name"`
// 	TaskAlias     string `pg:"task_alias"`
// }

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 distinct 
		task.id as task_id,
		task.leader ->>'name' as leader_name,
		task.leader ->>'id' as leader_id,
		task.alias as task_alias,
		task.name as task_name
	from task
	join task_stage  on task.id=task_stage.task_id 
	where 1=1
	and task_stage.real_completed_at=0 and task_stage.plan_completed_at<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 distinct 
	task.id as task_id,
	task.leader ->>'name' as leader_name,
	task.leader ->>'id' as leader_id,
	task.alias as task_alias,
	task.name as task_name
	from task
	join task_stage  on task.id=task_stage.task_id 
	where 1=1
	and task_stage.real_completed_at=0 and task_stage.plan_completed_at<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(?) `
	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>1 `
	condition := []interface{}{}
	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 ?)`
	}
	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>1 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 ?)`
	}
	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
	`
	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=? `
	result := []TaskData1{}
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, sqlStr, userId, companyId)
	return result, err

}

// 获取异常任务对应的公司
func (d *TaskDao) TaskStageAnomalyForCompany() ([]int, error) {
	var companyIds []struct {
		CompanyId int `pg:"company_id"`
	}
	sqlStr := `select  distinct company_id 
	from task 
	where 1=1 
	and (task.warn_flag >0)`
	result := []TaskData1{}
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, sqlStr)
	if err != nil {
		return nil, err
	}
	var ids []int
	for _, v := range companyIds {
		ids = append(ids, v.CompanyId)
	}
	return ids, nil
}

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"`
}

// TaskStageAnomalyAll 获取所有异常的里程碑任务
func (d TaskDao) TaskStageAnomalyAll(companyId int) ([]TaskData4, error) {
	sqlStr := `select distinct  
	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 
	join task_stage on task.id=task_stage.task_id
	where 1=1
	and task_stage.real_completed_at=0
	and task_stage.plan_completed_at<extract(epoch from now())
	and task.company_id =?
	`
	result := []TaskData4{}
	tx := d.transactionContext.PgTx
	_, err := tx.Query(&result, sqlStr, companyId)
	return result, err
}

type ListTask struct {
	SortBy1          int              `pg:"sort_by_1"`
	CPlanCompletedAt int              `pg:"c_plan_completed_at"`
	LastStageDiff    int              `pg:"last_stage_diff"`
	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"`    // 上一个完成的里程碑
}

// (新)获取任务，用于页面展示; 无过滤查看权限
// 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 ->>'plan_completed_at','0') as int)  as c_plan_completed_at,
		cast(COALESCE(task.last_stage ->>'plan_completed_at','0') as int)  as l_plan_completed_at,
		cast(COALESCE(task.last_stage ->>'real_completed_at','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.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 1
			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,
		t_task.c_plan_completed_at ,
		t_task.l_plan_completed_at-t_task.l_real_completed_at as last_stage_diff,
		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.created_at
		from t_task  
		where 1=1
	)select *
	from t_task_1
	order by t_task_1.sort_by_1,t_task_1.last_stage_diff,
	t_task_1."level",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 ?)`
	}
	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 ->>'plan_completed_at','0') as int)  as c_plan_completed_at,
		cast(COALESCE(task.last_stage ->>'plan_completed_at','0') as int)  as l_plan_completed_at,
		cast(COALESCE(task.last_stage ->>'real_completed_at','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.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 1
			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,
		t_task.c_plan_completed_at ,
		t_task.l_plan_completed_at-t_task.l_real_completed_at as last_stage_diff,
		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.created_at
		from t_task  
		where 1=1
	)select *
	from tt_task_1
	order by tt_task_1.sort_by_1,tt_task_1.last_stage_diff,
	tt_task_1."level",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 ?)`
	}
	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
}