SELECT 
project.id as id, project.create_name as create_name, project.create_by create_by, project.create_date create_date, 
project.update_name update_name, project.update_by update_by, project.update_date update_date, project.code code, 
project.name name, pm.realname as leader, project.zone zone, project.customer customer, project.status status, 
project.activetime activetime, project.addr addr, project.currentprocessstart currentprocessstart, project.currentprocessend currentprocessend, 
project.abbreviation abbreviation, project.type type, project.defnum1 defnum1, project.defnum2 defnum2, project.defnum3 defnum3, 
project.defnum4 defnum4, project.defnum5 defnum5, project.defnum6 defnum6, project.defnum7 defnum7, project.defnum8 defnum8, 
project.defnum9 defnum9, project.defnum10 defnum10, project.defstr1 defstr1, project.defstr2 defstr2, project.defstr3 defstr3,
project.defstr4 defstr4, project.defstr5 defstr5, project.defstr6 defstr6, project.defstr7 defstr7, project.defstr8 defstr8, 
project.defstr9 defstr9, project.defstr10 defstr10, project.leadername leadername, project.customername customername, 
case when ufavorite.projectid is null then '0' else '1' end as favorite
FROM t_b_project AS project
LEFT JOIN t_b_project_favorite AS ufavorite ON ufavorite.projectid = project.id and ufavorite.userid = :paramDto.userid
left join (select 
	proj.id as projectid ,
	GROUP_CONCAT(user.realname) as realname,
	GROUP_CONCAT(user.userid) as userid
from t_s_user_org uo
left join t_s_depart dept on uo.org_id = dept.id 
left join t_b_project proj  on proj.id = dept.projectid 
inner join ( 
	select bu.id as userid,realname 
	from t_s_role_user ru 
	left join t_s_base_user bu on ru.userid = bu.id 
	left join t_s_role role on role.id = ru.roleid 
	where role.rolecode = 'P_XMJL') user on user.userid = uo.user_id 
WHERE LENGTH(dept.org_code) = 6 and proj.id is not null
group by projectid) PM on pm.projectid = project.id
WHERE 
project.id = project.id

<#if paramDto.id ?exists && paramDto.id ?length gt 0>
	AND project.id = :paramDto.id 
</#if>
<#if paramDto.code ?exists && paramDto.code ?length gt 0>
	AND project.code LIKE CONCAT('%', :paramDto.code ,'%')
</#if>
<#if paramDto.name ?exists && paramDto.name ?length gt 0>
	AND project.name LIKE CONCAT('%', :paramDto.name ,'%')
</#if>
<#if paramDto.zone ?exists && paramDto.zone ?length gt 0>
	AND project.zone = :paramDto.zone
</#if>
<#if paramDto.customer ?exists && paramDto.customer ?length gt 0>
	AND project.customer = :paramDto.customer
</#if>
<#if paramDto.status ?exists && paramDto.status ?length gt 0>
	AND project.status = :paramDto.status
</#if>
<#if paramDto.type ?exists && paramDto.type ?length gt 0>
	AND project.type = :paramDto.type
</#if>
<#if paramDto.abbreviation ?exists && paramDto.abbreviation ?length gt 0>
	AND project.abbreviation LIKE CONCAT('%', :paramDto.abbreviation ,'%')
</#if>
<#if authSql ?exists && authSql ?length gt 0>
	${authSql}
</#if>
<#if sortorder ?exists && sortorder ?length gt 0>
	 order by ${sortorder}
</#if>


