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+"%")
	}
	//加入排序
	sqlStr += ` order by convert_to(t_staff_assess_1.target_user_name,'GBK'),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
	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_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 
	),
	-- 部门主管
	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
	),
	-- 如果是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
		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
		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{}{
		companyId, 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) {
	sqlString := `
	set time zone 'PRC';
	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
	),
	-- 如果是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{}{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_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
	),
	-- 如果是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{}{companyId, 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_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
	),
	-- 如果是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{}{companyId, 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 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_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
	),
	-- 如果是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{}{companyId, 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 AssessCycleDayMe struct {
	BeginDay  string
	CycleId   string
	CycleName string
	EndTime   string
	BeginTime string
}

// 根据评估的人执行人id，搜索 executorId参与的评估周期
func (d *StaffAssessDao) SearchAssessCycleMe(executorId int, companyId int, limit int, offset int) ([]AssessCycleDayMe, 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 := []AssessCycleDayMe{}
	_, 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  //指标分类
	Name           string  //指标名称
	Weight         float64 //指标权重
	CycleId        string  //周期id
	CycleName      string  //周期名称
	TargetUserId   string  //评估的目标员工id
	TargetUserName string  //评估的目标员工名称
	Cnt            int     //排序
}

// 员工绩效-综合管理-导出绩效指标
// 抽取出评估的指标
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 ,
		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,
		sum(
			case
				when staff_assess_content.value isnull then 0
				when staff_assess_content.value='' then  0
				ELSE 1
			END) as cnt
	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 staff_assess_content.category,
	staff_assess_content."name" ,
	t_staff_assess_1.cycle_id ,
	t_staff_assess_1.cycle_name,
	staff_assess_content.weight ,
	target_user_id,target_user_name
	order by cnt desc,target_user_id `
	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
}