package models

import (
	"fmt"
	"opp/internal/utils"
	"time"

	"github.com/astaxie/beego/orm"
)

type Chance struct {
	Id                  int64     `orm:"column(id);pk" description:"id 主键"`
	UserId              int64     `orm:"column(user_id)" description:"表user.id  用户id"`
	CompanyId           int64     `orm:"column(company_id)" description:"表company.id 公司id"`
	DepartmentId        int64     `orm:"column(department_id)" description:"表department.id 部门id"`
	ChanceTypeId        int       `orm:"column(chance_type_id)" description:"表chance_type.id 机会类型 "`
	AuditTemplateId     int64     `orm:"column(audit_template_id)" description:"表audit_template.id 所属审批模板编号"`
	AuditTemplateConfig string    `orm:"column(audit_template_config);size(255);null" description:"模板配置 （存旧的配置信息，对新改动的不影响）"`
	Content             string    `orm:"column(content)" description:"格式化后的文本内容"`
	SourceContent       string    `orm:"column(source_content)" description:"原始表单内容 json"`
	ViewTotal           int       `orm:"column(view_total)" description:"查看总数"`
	CommentTotal        int       `orm:"column(comment_total)" description:"评论总数"`
	ZanTotal            int       `orm:"column(zan_total)" description:"点赞总数"`
	ReviewStatus        int8      `orm:"column(review_status)" description:"审核状态 0:待处理 1:待审核 2:被退回 3:已通过 "`
	EnableStatus        int8      `orm:"column(enable_status)" description:"有效状态 0:无效 1:有效 "`
	UpdateAt            time.Time `orm:"column(update_at);type(timestamp)" description:"更新时间"`
	CreateAt            time.Time `orm:"column(create_at);type(timestamp)" description:"创建时间"`
	BasicScore          float64   `orm:"column(basic_score);null;digits(4);decimals(1)" description:"基础评分"`
	ExtraScore          float64   `orm:"column(extra_score);null;digits(4);decimals(1)" description:"附加评分"`
	ValueScore          float64   `orm:"column(value_score);null;digits(4);decimals(1)" description:"价值评分"`
	DiscoveryScore      float64   `orm:"column(discovery_score);null;digits(4);decimals(1)" description:"发现得分（发现得分=基础评分*系数 + 附加评分*系数 + 价值评分*系数）"`
	PublishStatus       int       `orm:"column(publish_status)" description:"公开状态 0未公开、1部门公开、2公司公开"`
	ApproveData         string    `orm:"column(approve_data)" description:"审核数据 冗余"`
	AuditLevel          int       `orm:"column(audit_level)" description:"当前审批步骤"`
	ApproveTime         time.Time `orm:"column(approve_time);type(timestamp)" description:"审批时间"`
	Code                string    `orm:"column(code)" description:"机会编码 一级编码+二级编码"`
	Status              int8      `orm:"column(status)" description:"状态 1:开启 2:关闭 "`
	SelfChecks          string    `orm:"column(self_checks);size(1000);null" description:"自查内容"`
	CheckTime           time.Time `orm:"column(check_time);type(timestamp)" description:"时间"`
	CheckResultStatus   int       `orm:"column(check_result_status)" description:"机会筛选状态【1：待处理】【2：通过】【3：不通过】"`
	CheckResult         string    `orm:"column(check_result);null" description:"筛选结果"`
}

const (
	ChanceStatusOpen  = 1 //开启
	ChanceStatusClose = 2 //关闭
)

var (
	SqlGetChanceSelfChecks = `select user_id,review_status,self_checks from chance where id =?` //机会自查数据
)

func (t *Chance) TableName() string {
	return "chance"
}

func init() {
	orm.RegisterModel(new(Chance))
}

// AddChance insert a new Chance into database and returns
// last inserted Id on success.
func AddChance(m *Chance) (id int64, err error) {
	o := orm.NewOrm()
	id, err = o.Insert(m)
	return
}

// GetChanceById retrieves Chance by Id. Returns error if
// Id doesn't exist
func GetChanceById(id int64) (v *Chance, err error) {
	o := orm.NewOrm()
	v = &Chance{Id: id}
	if err = o.Read(v); err == nil {
		return v, nil
	}
	return nil, err
}

func GetChanceByIdAndEnable(id int64) (v *Chance, err error) {
	o := orm.NewOrm()
	v = &Chance{Id: id, EnableStatus: 1}
	if err = o.Read(v); err == nil {
		return v, nil
	}
	return nil, err
}

// UpdateChance updates Chance by Id and returns error if
// the record to be updated doesn't exist
func UpdateChanceById(m *Chance) (err error) {
	o := orm.NewOrm()
	v := Chance{Id: m.Id}
	// ascertain id exists in the database
	if err = o.Read(&v); err == nil {
		var num int64
		if num, err = o.Update(m); err == nil {
			fmt.Println("Number of records updated in database:", num)
		}
	}
	return
}

// DeleteChance deletes Chance by Id and returns error if
// the record to be deleted doesn't exist
func DeleteChance(id int64) (err error) {
	o := orm.NewOrm()
	v := Chance{Id: id}
	// ascertain id exists in the database
	if err = o.Read(&v); err == nil {
		var num int64
		if num, err = o.Delete(&Chance{Id: id}); err == nil {
			fmt.Println("Number of records deleted in database:", num)
		}
	}
	return
}

func GetChanceMyChance(uid, cid int64, reviewStatus []int8, lastId int64, pageSize int, v interface{}) (total int, err error) {
	sql := `select a.*,b.images,speechs,videos 
from (
select id chance_id,user_id chance_user_id,create_at,update_at,update_at chance_approve_time,source_content,approve_data chance_approve_data,review_status,enable_status,status from chance
where user_id=? and company_id=? and review_status in (?)  and (?=0 or unix_timestamp(update_at)<?) and status=1 and enable_status=1
) a left JOIN chance_data b on a.chance_id =b.chance_id
order by update_at desc
limit ? 
` //approve_time
	//update_at
	sqlCount := fmt.Sprintf(`select count(0) from (
select id,user_id,create_at,source_content from chance
where user_id=? and company_id=? and review_status in (%v) and status=1 and enable_status=1
) a left JOIN chance_data b on a.id =b.chance_id`, utils.JoinInt8s(reviewStatus, ","))
	if err = utils.ExecuteQueryOne(&total, sqlCount, uid, cid); err != nil {
		return
	}
	if v != nil {
		if err = utils.ExecuteQueryAll(v, sql, uid, cid, utils.JoinInt8s(reviewStatus, ","), lastId, lastId, pageSize); err != nil {
			return
		}
	}
	return
}

func GetChanceMyApproveChance(uid, cid int64, reviewStatus []int8, lastId int64, pageSize int, v interface{}) (total int, err error) {
	sql := fmt.Sprintf(`select a.*,b.images,b.speechs,b.videos from (
select a.*,b.user_id chance_user_id,b.source_content,b.enable_status,b.review_status,b.create_at,b.update_at,b.approve_time chance_approve_time,b.status from  (
select id,approve_time,approve_data,uid,chance_id,approve_message,update_at  process_create_time
from audit_flow_process where uid=? and review_status in (%v) 
)a left outer join chance b on a.chance_id = b.id
)a left outer join chance_data b on a.chance_id =b.chance_id
where (?=0 or unix_timestamp(a.update_at)<?) 
order by a.update_at desc
LIMIT ?`, utils.JoinInt8s(reviewStatus, ",")) //备注:and enable_status =1 //就算已经删除了,列表上面还是要体现已经删除

	sqlCount := fmt.Sprintf(`select count(0) 
from audit_flow_process where uid=?  and review_status in (%v) `, utils.JoinInt8s(reviewStatus, ",")) //and enable_status =1
	if err = utils.ExecuteQueryOne(&total, sqlCount, uid); err != nil {
		return
	}
	if v != nil {
		if err = utils.ExecuteQueryAll(v, sql, uid, lastId, lastId, pageSize); err != nil {
			return
		}
	}
	return
}

//获取有效的机会
func GetChanceMyApproveChanceEnable(uid, cid int64, reviewStatus []int8, lastId int64, pageSize int, v interface{}) (total int, err error) {
	sql := fmt.Sprintf(`select a.*,b.images,b.speechs,b.videos from (
select a.*,b.user_id chance_user_id,b.source_content,b.enable_status,b.review_status,b.create_at,b.update_at,b.approve_time chance_approve_time,b.status,b.update_at process_create_time,b.self_checks  from  (
select id,approve_time,approve_data,uid,chance_id,approve_message
from audit_flow_process where uid=? and review_status in (%v) and enable_status =1 
)a left outer join chance b on a.chance_id = b.id
)a left outer join chance_data b on a.chance_id =b.chance_id
where (?=0 or unix_timestamp(a.update_at)<?)
order by a.update_at desc
LIMIT ?`, utils.JoinInt8s(reviewStatus, ",")) //,update_at  process_create_time

	sqlCount := fmt.Sprintf(`select count(0) 
from audit_flow_process where uid=?  and review_status in (%v) and enable_status =1`, utils.JoinInt8s(reviewStatus, ",")) //and enable_status =1
	if err = utils.ExecuteQueryOne(&total, sqlCount, uid); err != nil {
		return
	}
	if v != nil {
		if err = utils.ExecuteQueryAll(v, sql, uid, lastId, lastId, pageSize); err != nil {
			return
		}
	}
	return
}

func GetChanceCollect(uid int64, lastId int64, pageSize int, v interface{}) (total int, err error) {
	sql := fmt.Sprintf(`select a.*,b.images,b.speechs,b.videos from (
select a.*,b.user_id chance_user_id,b.create_at,b.source_content,b.enable_status,b.review_status,b.audit_template_id,b.chance_type_id,comment_total,zan_total,view_total,b.publish_status,b.status from (
select id collect_id,source_id,update_at,collect_time,chance_id from chance_favorite where (0=? or unix_timestamp(collect_time)<?) and user_id =? and enable_status=1 
and source_type=1 
and  (mark_flag&2)>0  
)a  left outer join chance b on a.source_id = b.id
)a left outer join chance_data b on a.source_id =b.chance_id
order by collect_time desc
limit ?`)

	sqlCount := `select count(0) from chance_favorite where user_id =? and enable_status=1 and (mark_flag&2)>0`
	if err = utils.ExecuteQueryOne(&total, sqlCount, uid); err != nil {
		return
	}
	if v != nil {
		if err = utils.ExecuteQueryAll(v, sql, lastId, lastId, uid, pageSize); err != nil {
			return
		}
	}
	return
}

func GetChanceThumbUp(uid int64, lastId int64, pageSize int, v interface{}) (total int, err error) {
	sql := fmt.Sprintf(`select a.*,b.images,b.speechs,b.videos from (
select a.*,b.user_id chance_user_id,b.id chance_id,b.create_at,b.source_content,b.enable_status,b.review_status,b.audit_template_id,b.chance_type_id,comment_total,zan_total,view_total,b.publish_status,b.status from (
select id collect_id,source_id,update_at,zan_time from chance_favorite where (0=? or unix_timestamp(zan_time)<?) and user_id =? and enable_status=1 
and source_type=1 
and  (mark_flag&1)>0  
)a  left outer join chance b on a.source_id = b.id
)a left outer join chance_data b on a.source_id =b.chance_id
order by zan_time desc
limit ?`)

	sqlCount := `select count(0) from chance_favorite where user_id =? and enable_status=1 and (mark_flag&1)>0 and source_type=1`
	if err = utils.ExecuteQueryOne(&total, sqlCount, uid); err != nil {
		return
	}
	if v != nil {
		if err = utils.ExecuteQueryAll(v, sql, lastId, lastId, uid, pageSize); err != nil {
			return
		}
	}
	return
}

//我点赞的
func GetChanceThumbup(uid int64, lastId int64, pageSize int, v interface{}) (total int, err error) {
	sqlCount := `select count(0) from chance_favorite where user_id =? and enable_status=1 and (mark_flag&1)>0 and source_type=1`
	if err = utils.ExecuteQueryOne(&total, sqlCount, uid); err != nil {
		return
	}
	return
}

//我的评论
func GetChanceComment(uid int64, lastId int64, pageSize int, v interface{}) (total int, err error) {
	sql := fmt.Sprintf(`select a.*,b.content commented_content,b.create_at commented_time,b.user_id commented_user_id 
from (
select a.*,b.images,b.speechs,b.videos from (
select a.*,b.id chance_id,b.source_content,b.enable_status,b.user_id chance_user_id,b.create_at,b.review_status,b.approve_data,b.publish_status,b.status from (
select id,content,view_total,zan_total,comment_total,source_type,source_id,create_at comment_time from comment 
where user_id =? and  (?=0 or id<?)
)a left outer join chance b on a.source_id = b.id and source_type=1
)a left outer join chance_data b on a.source_id = b.chance_id and source_type = 1
)a left outer join comment b on a.source_id = b.id and a.source_type=2
order by comment_time desc
limit ?`)

	sqlCount := `select count(0) from comment
where user_id =?`
	if err = utils.ExecuteQueryOne(&total, sqlCount, uid); err != nil {
		return
	}
	if v != nil {
		if err = utils.ExecuteQueryAll(v, sql, uid, lastId, lastId, pageSize); err != nil {
			return
		}
	}
	return
}

//type4 查看所有机会
func GetChancePoolAll(uid, cid int64, chanceTypeId int, dIds []int, lastId int64, pageSize int, v interface{}) (total int, err error) {
	var filterDepartment string = getFilterSqlByDIds(dIds)
	sql := fmt.Sprintf(`select a.*,b.images,speechs,videos 
from (
select id,user_id,approve_time create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total from chance
where  company_id=? and review_status=3 and (?=0 or chance_type_id =?)  and (?=0 or unix_timestamp(approve_time)<?) and enable_status=1 and status=1 %v
) a left JOIN chance_data b on a.id =b.chance_id
order by create_at desc
limit ? 
`, filterDepartment)

	//if public==protocol.pu

	sqlCount := fmt.Sprintf(`select count(0) from (
select id from chance
where  company_id=? and review_status=3 and (%v=0 or chance_type_id =%v) and enable_status=1  and status=1 %v
) a left JOIN chance_data b on a.id =b.chance_id`, chanceTypeId, chanceTypeId, filterDepartment)
	if err = utils.ExecuteQueryOne(&total, sqlCount, cid); err != nil {
		return
	}
	if v != nil {
		if err = utils.ExecuteQueryAll(v, sql, cid, chanceTypeId, chanceTypeId, lastId, lastId, pageSize); err != nil {
			return
		}
	}
	return
}

//type3 特定部门机会
func GetChancePoolSpecialDepartment(uid, cid int64, chanceTypeId int, dIds []int, lastId int64, pageSize int, v interface{}, departmentIds []int64) (total int, err error) {
	var filterDepartment string = getFilterSqlByDIds(dIds)
	sql := fmt.Sprintf(`
select a.*,b.images,speechs,videos from (
select * from (
	##公司公开的机会
	select id,user_id,approve_time create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from chance
	where  company_id = %v and publish_status = 2 and review_status=3
	UNION

	##指定部门-机会提交的部门
	select id,user_id,approve_time create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from chance
	where  department_id in (%v) and review_status=3
	UNION
	
	##本人
	select id,user_id,approve_time create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from chance
	where  user_id = %v and review_status=3
	UNION 
	
	##我审核
	select id,user_id,approve_time create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from (
	select DISTINCT chance_id from audit_flow_process where  uid =%v 
	) a inner join chance b on a.chance_id = b.id
	
) a where review_status=3 and (0=%v or chance_type_id =%v)  and (0=%v or unix_timestamp(create_at)<%v) and a.enable_status=1 and status=1 %v
) a left JOIN chance_data b on a.id =b.chance_id
order by create_at desc
limit  %v
`, cid, utils.JoinInt64s(departmentIds, ","), uid, uid, chanceTypeId, chanceTypeId, lastId, lastId, filterDepartment, pageSize)

	sqlCount := fmt.Sprintf(`
select count(0) from (
	##公司公开的机会
	select id,user_id,create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from chance
	where  company_id = %v and publish_status = 2 and review_status=3
	UNION

	##指定部门-机会提交的部门
	select id,user_id,create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from chance
	where  department_id in (%v) and review_status=3
	UNION
	
	##本人
	select id,user_id,create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from chance
	where  user_id = %v  and review_status=3
	UNION 
	
	##我审核
	select id,user_id,create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from (
	select DISTINCT chance_id from audit_flow_process where  uid =%v
	) a inner join chance b on a.chance_id = b.id
	
) a where review_status=3 and (0=%v or chance_type_id =%v)  and a.enable_status=1 and status=1 %v
`, cid, utils.JoinInt64s(departmentIds, ","), uid, uid, chanceTypeId, chanceTypeId, filterDepartment)
	if err = utils.ExecuteQueryOne(&total, sqlCount); err != nil {
		return
	}
	if v != nil {
		if err = utils.ExecuteQueryAll(v, sql); err != nil {
			return
		}
	}
	return
}

//type32 特定部门机会 - 多角色时包含levl2 部门公开
func GetChancePoolDepartment(uid, cid int64, chanceTypeId int, dIds []int, lastId int64, pageSize int, v interface{}, departmentIds []int64, userDepartmetIds []int64) (total int, err error) {
	var filterDepartment string = getFilterSqlByDIds(dIds)
	sql := fmt.Sprintf(`
select a.*,b.images,speechs,videos from (
select * from (
	##指定部门-机会公开的部门
	select id,user_id,approve_time create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from (
	select DISTINCT chance_id from chance_department where department_id in (%v)
	)a inner join chance b on a.chance_id = b.id
	where review_status=3

	UNION

	##公司公开的机会
	select id,user_id,approve_time create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from chance
	where  company_id = %v and publish_status = 2  and review_status=3
	UNION

	##指定部门-机会提交的部门
	select id,user_id,approve_time create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from chance
	where  department_id in (%v) and review_status=3
	UNION
	
	##本人
	select id,user_id,approve_time create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from chance
	where  user_id = %v and review_status=3
	UNION 
	
	##我审核
	select id,user_id,approve_time create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from (
	select DISTINCT chance_id from audit_flow_process where  uid =%v 
	) a inner join chance b on a.chance_id = b.id
	
) a where review_status=3 and (0=%v or chance_type_id =%v)  and (0=%v or unix_timestamp(create_at)<%v) and a.enable_status=1 and status=1 %v
) a left JOIN chance_data b on a.id =b.chance_id
order by create_at desc
limit  %v
`, utils.JoinInt64s(userDepartmetIds, ","), cid, utils.JoinInt64s(departmentIds, ","), uid, uid, chanceTypeId, chanceTypeId, lastId, lastId, filterDepartment, pageSize)

	sqlCount := fmt.Sprintf(`
select count(0) from (
	##指定部门-机会公开的部门
	select id,user_id,create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from (
	select DISTINCT chance_id from chance_department where department_id in (%v)
	)a inner join chance b on a.chance_id = b.id
	where review_status=3

	UNION

	##公司公开的机会
	select id,user_id,create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from chance
	where  company_id = %v and publish_status = 2 and review_status=3
	UNION

	##指定部门-机会提交的部门
	select id,user_id,create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from chance
	where  department_id in (%v) and review_status=3
	UNION
	
	##本人
	select id,user_id,create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from chance
	where  user_id = %v and review_status=3
	UNION 
	
	##我审核
	select id,user_id,create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from (
	select DISTINCT chance_id from audit_flow_process where  uid =%v 
	) a inner join chance b on a.chance_id = b.id
	
) a where review_status=3 and (0=%v or chance_type_id =%v)  and a.enable_status=1 and status=1 %v
`, utils.JoinInt64s(userDepartmetIds, ","), cid, utils.JoinInt64s(departmentIds, ","), uid, uid, chanceTypeId, chanceTypeId, filterDepartment)
	if err = utils.ExecuteQueryOne(&total, sqlCount); err != nil {
		return
	}
	if v != nil {
		if err = utils.ExecuteQueryAll(v, sql); err != nil {
			return
		}
	}
	return
}

//type2 对我所在部门公开的机会  公司公开的机会
func GetChancePoolPublicCompany(uid, cid int64, chanceTypeId int, dIds []int, lastId int64, pageSize int, v interface{}, departmentIds []int64) (total int, err error) {
	var filterDepartment string = getFilterSqlByDIds(dIds)
	sql := fmt.Sprintf(`
select a.*,b.images,speechs,videos from (
select * from (
	##公司公开的机会
	select id,user_id,approve_time create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from chance
	where  company_id = %v and publish_status = 2 and review_status=3

	UNION
	##指定部门-机会公开的部门
	select id,user_id,approve_time create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from (
	select DISTINCT chance_id from chance_department where department_id in (%v)
	)a inner join chance b on a.chance_id = b.id
	where review_status=3

	UNION
	
	##本人
	select id,user_id,approve_time create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from chance
	where  user_id = %v and review_status=3
	UNION 
	
	##我审核
	select id,user_id,approve_time create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from (
	select DISTINCT chance_id from audit_flow_process where  uid =%v 
	) a inner join chance b on a.chance_id = b.id
	
) a where review_status=3 and (0=%v or chance_type_id =%v)  and (0=%v or unix_timestamp(create_at)<%v) and a.enable_status=1 and status=1  %v
) a left JOIN chance_data b on a.id =b.chance_id
order by create_at desc
limit  %v
`, cid, utils.JoinInt64s(departmentIds, ","), uid, uid, chanceTypeId, chanceTypeId, lastId, lastId, filterDepartment, pageSize)

	sqlCount := fmt.Sprintf(`
select count(0) from (
	##公司公开的机会
	select id,user_id,create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from chance
	where  company_id = %v and publish_status = 2  and review_status=3

	UNION
	##指定部门-机会公开的部门
	select id,user_id,create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from (
	select DISTINCT chance_id from chance_department where department_id in (%v)
	)a inner join chance b on a.chance_id = b.id
	where review_status=3

	UNION
	
	##本人
	select id,user_id,create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from chance
	where  user_id = %v and review_status=3
	UNION 
	
	##我审核
	select id,user_id,create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from (
	select DISTINCT chance_id from audit_flow_process where  uid =%v
	) a inner join chance b on a.chance_id = b.id
	
) a where review_status=3 and (0=%v or chance_type_id =%v)  and a.enable_status=1 and status=1 %v
`, cid, utils.JoinInt64s(departmentIds, ","), uid, uid, chanceTypeId, chanceTypeId, filterDepartment)
	if err = utils.ExecuteQueryOne(&total, sqlCount); err != nil {
		return
	}
	if v != nil {
		if err = utils.ExecuteQueryAll(v, sql); err != nil {
			return
		}
	}
	return
}
func getFilterSqlByDIds(dIds []int) string {
	if len(dIds) == 0 {
		return ""
	}
	return fmt.Sprintf(" and department_id in(%v) ", utils.JoinInts(dIds, ","))
}

//type1 禁止查看所有机会
func GetChancePoolMyself(uid, cid int64, chanceTypeId int, dIds []int, lastId int64, pageSize int, v interface{}) (total int, err error) {
	var filterDepartment string = getFilterSqlByDIds(dIds)
	sql := fmt.Sprintf(`
select a.*,b.images,speechs,videos from (
select * from (
	##本人
	select id,user_id,approve_time create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from chance
	where  user_id = %v and review_status=3
	UNION 
	
	##我审核
	select id,user_id,approve_time create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from (
	select DISTINCT chance_id from audit_flow_process where  uid =%v
	) a inner join chance b on a.chance_id = b.id
	
) a where review_status=3 and (0=%v or chance_type_id =%v)  and (0=%v or unix_timestamp(create_at)<%v) and a.enable_status=1 and status=1 %v
) a left JOIN chance_data b on a.id =b.chance_id
order by create_at desc
limit  %v
`, uid, uid, chanceTypeId, chanceTypeId, lastId, lastId, filterDepartment, pageSize)

	sqlCount := fmt.Sprintf(`
select count(0) from (
	##本人
	select id,user_id,create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from chance
	where  user_id = %v and review_status=3
	UNION 
	
	##我审核
	select id,user_id,create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total,enable_status,department_id,status from (
	select DISTINCT chance_id from audit_flow_process where  uid =%v
	) a inner join chance b on a.chance_id = b.id
	
) a where review_status=3 and (0=%v or chance_type_id =%v)  and a.enable_status=1 and status=1 %v
`, uid, uid, chanceTypeId, chanceTypeId, filterDepartment)
	if err = utils.ExecuteQueryOne(&total, sqlCount); err != nil {
		return
	}
	if v != nil {
		if err = utils.ExecuteQueryAll(v, sql); err != nil {
			return
		}
	}
	return
}

//type4 查看所有机会
func ValidChanceAll(uid, cid int64, chanceTypeId int, lastId int64, pageSize int, v interface{}) (total int, err error) {

	//	sql := `select a.*,b.images,speechs,videos
	//from (
	//select id,user_id,create_at,source_content,review_status,audit_template_id,chance_type_id,comment_total,zan_total,view_total from chance
	//where  company_id=? and review_status=3 and (?=0 or chance_type_id =?)  and (?=0 or id<?) and enable_status=1
	//order by create_at desc
	//limit ?
	//) a left JOIN chance_data b on a.id =b.chance_id`
	//	//if public==protocol.pu
	//
	//	sqlCount := fmt.Sprintf(`select count(0) from (
	//select id from chance
	//where  company_id=? and review_status=3 and (%v=0 or chance_type_id =%v) and enable_status=1
	//order by create_at desc
	//) a left JOIN chance_data b on a.id =b.chance_id`, chanceTypeId, chanceTypeId)
	//	if err = utils.ExecuteQueryOne(&total, sqlCount, cid); err != nil {
	//		return
	//	}
	//	if v != nil {
	//		if err = utils.ExecuteQueryAll(v, sql, cid, chanceTypeId, chanceTypeId, lastId, lastId, pageSize); err != nil {
	//			return
	//		}
	//	}
	return
}

//type3 特定部门机会
func ValidChanceSpecialDepartment(uid, chanceIds string, v interface{}) (err error) {

	sql := fmt.Sprintf(`
select * from (
	##指定部门-机会提交的部门
	##select chance_id from chance
	##where  id in (v) and department_id in (v)
	##UNION
	
	##本人
	##select chance_id from chance
    ##where  user_id = v and id in (v)
	##UNION 
	
	##我审核
	select DISTINCT chance_id from audit_flow_process where  uid =%v and chance_id in (%v)
)
`, chanceIds)

	if v != nil {
		if err = utils.ExecuteQueryAll(v, sql); err != nil {
			return
		}
	}
	return
}

//是否存在按发布机会部门编号查询
func ExitsChanceByDeparmentIds(chanceId int64, departmentIds []int64) (v *Chance, err error) {
	sql := fmt.Sprintf(`select id from chance where id=%v and department_id in (%v)`,
		chanceId, utils.JoinInt64s(departmentIds, ","))
	o := orm.NewOrm()
	if err = o.Raw(sql).QueryRow(&v); err != nil {
		return
	}
	return
}

//是否存在发布机会的部门编号查询
func ExitsChanceByPublicDeparmentIds(chanceId int64, departmentIds []int64) (v *Chance, err error) {
	sql := fmt.Sprintf(`select chance_id from chance_department where chance_id=%v and department_id in (%v)`,
		chanceId, utils.JoinInt64s(departmentIds, ","))
	o := orm.NewOrm()
	if err = o.Raw(sql).QueryRow(&v); err != nil {
		return
	}
	return
}

//是否存在机会 - 审核人
func ExitsChanceByAuditUser(chanceId int64, auditUserId int64) (v *Chance, err error) {
	sql := fmt.Sprintf(`select * from audit_flow_process where chance_id =%v and uid =%v and enable_status=1 limit 1`,
		chanceId, auditUserId)
	o := orm.NewOrm()
	if err = o.Raw(sql).QueryRow(&v); err != nil {
		return
	}
	return
}

//机会统计-按部门
//@departmentIds 按部门编号查询
func GetChanceStatisticByDepartment(companyId int64, departmentIds []int, reviewStatus int) (v int, err error) {
	sql := fmt.Sprintf(`select count(0) from chance where company_id=%v 
and review_status=%v
and status=1 
and enable_status=1
and department_id in (%v)`,
		companyId, reviewStatus, utils.JoinInts(departmentIds, ","))
	o := orm.NewOrm()
	if err = o.Raw(sql).QueryRow(&v); err != nil {
		return
	}
	return
}

//获取筛选中的机会
//@submitStatus 提交状态:已提交 未提交
//@checkResultStatus 筛选结果状态:通过 / 不通过
func GetSiftingChance(uid int64, submitStatus int, checkResultStatus int, offset int, pageSize int, v interface{}) (total int, err error) {
	var where string
	if submitStatus == 1 {
		where = fmt.Sprintf(`order by a.submit_check_time desc`)
	} else {
		where = fmt.Sprintf(`order by a.chance_approve_time desc`)
	}
	sql := fmt.Sprintf(`select a.*,b.images,b.speechs,b.videos 
from (
	select a.*,b.user_id chance_user_id,b.source_content,b.enable_status,b.review_status,b.audit_template_id,b.chance_type_id,b.create_at,
	b.update_at,b.approve_time chance_approve_time,b.status,b.self_checks,b.check_result_status,b.check_time  
	from  (
		select max(id) id,approve_time,approve_data,uid,chance_id,submit_check_status,submit_check_time,level
		from audit_flow_process a,(select MAX(id) AS max_id from audit_flow_process as t where t.uid=%v  group by chance_id)  b
		where a.uid=%v and b.max_id=a.id and a.submit_check_status=%v and level>0
		GROUP BY chance_id
	)a left outer join chance b on a.chance_id = b.id
	where b.review_status=3 and b.enable_status=1 and b.status=1 and b.check_result_status =%v and  a.level=b.audit_level and length(b.self_checks)>5 
)a left outer join chance_data b on a.chance_id =b.chance_id
%v
limit %v,%v`, uid, uid, submitStatus, checkResultStatus, where, offset, pageSize) //,update_at  process_create_time

	sqlCount := fmt.Sprintf(`select count(0) 
from (
	select a.*,b.user_id chance_user_id,b.source_content,b.enable_status,b.review_status,b.create_at,
	b.update_at,b.approve_time chance_approve_time,b.status,b.self_checks,b.check_result_status  
	from  (
		select max(id) id,approve_time,approve_data,uid,chance_id,submit_check_status,submit_check_time,level
		from audit_flow_process a,(select MAX(id) AS max_id from audit_flow_process as t where t.uid=%v  group by chance_id)  b
		where a.uid=%v and b.max_id=a.id and a.submit_check_status=%v and level>0
		GROUP BY chance_id
	)a left outer join chance b on a.chance_id = b.id
	where b.review_status=3 and b.enable_status=1 and b.status=1 and b.check_result_status =%v and  a.level=b.audit_level and length(b.self_checks)>5 
)a  
`, uid, uid, submitStatus, checkResultStatus) //and enable_status =1
	if err = utils.ExecuteQueryOne(&total, sqlCount); err != nil {
		return
	}
	if v != nil {
		if err = utils.ExecuteQueryAll(v, sql); err != nil {
			return
		}
	}
	return
}

//获取筛选结果的机会
//@submitStatus 提交状态:已提交 未提交
//@checkResultStatus 筛选结果状态:通过 / 不通过
func GetSiftingResults(uid int64, submitStatus, checkResultStatus int, offset int, pageSize int, v interface{}) (total int, err error) {
	sql := fmt.Sprintf(`select a.*,b.images,b.speechs,b.videos 
from (
	select a.*,b.user_id chance_user_id,b.source_content,b.enable_status,b.review_status,b.audit_template_id,b.chance_type_id,b.create_at,
	b.update_at,b.approve_time chance_approve_time,b.status,b.self_checks,b.check_result_status,b.check_time  
	from  (
		select max(id) id,approve_time,approve_data,uid,chance_id,submit_check_status,submit_check_time,level
		from audit_flow_process a,(select MAX(id) AS max_id from audit_flow_process as t where t.uid=%v  group by chance_id)  b
		where a.uid=%v and b.max_id=a.id and a.submit_check_status=%v and level>0
		GROUP BY chance_id
	)a left outer join chance b on a.chance_id = b.id
	where b.review_status=3 and b.enable_status=1 and b.status=1 and b.check_result_status =%v and  a.level=b.audit_level 
)a left outer join chance_data b on a.chance_id =b.chance_id
order by a.check_time desc
limit %v,%v`, uid, uid, submitStatus, checkResultStatus, offset, pageSize) //,update_at  process_create_time

	sqlCount := fmt.Sprintf(`select count(0) 
from (
	select a.*,b.user_id chance_user_id,b.source_content,b.enable_status,b.review_status,b.create_at,
	b.update_at,b.approve_time chance_approve_time,b.status,b.self_checks,b.check_result_status  
	from  (
		select max(id) id,approve_time,approve_data,uid,chance_id,submit_check_status,submit_check_time,level
		from audit_flow_process a,(select MAX(id) AS max_id from audit_flow_process as t where t.uid=%v  group by chance_id)  b
		where a.uid=%v and b.max_id=a.id and a.submit_check_status=%v and level>0
		GROUP BY chance_id
	)a left outer join chance b on a.chance_id = b.id
	where b.review_status=3 and b.enable_status=1 and b.status=1 and b.check_result_status =%v and  a.level=b.audit_level 
)a  
`, uid, uid, submitStatus, checkResultStatus) //and enable_status =1
	if err = utils.ExecuteQueryOne(&total, sqlCount); err != nil {
		return
	}
	if v != nil {
		if err = utils.ExecuteQueryAll(v, sql); err != nil {
			return
		}
	}
	return
}