<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.changhu.module.superManagement.mapper.AssessmentRecordMapper">
    <select id="pager" resultType="com.changhu.module.superManagement.pojo.vo.AssessmentRecordPagerVo">
        select car.*,
        eu.name as 'enterprisesUnitName',
        cp.name as 'ckProjectName',
        cp.total_score,
        cp.type,
        cp.remark as 'ckProjectRemark',
        pu.name as 'policeUnitName',
        mpu.name as 'createUserName',
        sum(cs.deduction_points) as 'deductionPointsTotal'
        from ck_assessment_record car
        left join enterprises_unit eu on car.enterprises_unit_id = eu.snow_flake_id and eu.delete_flag = 0
        left join ck_project cp on car.ck_project_id = cp.snow_flake_id and cp.delete_flag = 0
        left join mini_program_user mpu on mpu.identity = 'police' and car.create_by = mpu.snow_flake_id
        left join police_unit pu on mpu.unit_id = pu.snow_flake_id
        left join ck_assessment_record_details card on car.snow_flake_id = card.ck_assessment_record_id and
        card.delete_flag = 0
        left join ck_standard cs on card.ck_standard_id = cs.snow_flake_id
        where car.delete_flag = 0
        <if test="policeUnitId!=null">
            and eu.police_unit_id = #{policeUnitId}
        </if>
        <if test="params.type!=null">
            and cp.type = #{params.type.value}
        </if>
        <if test="params.policeUnitName!=null and params.policeUnitName!=''">
            and pu.name like concat('%',#{params.policeUnitName},'%')
        </if>
        <if test="params.enterprisesUnitName!=null and params.enterprisesUnitName!=''">
            and eu.name like concat('%',#{params.enterprisesUnitName},'%')
        </if>
        <if test="params.assessmentUserName!=null and params.assessmentUserName!=''">
            and mpu.name like concat('%',#{params.assessmentUserName},'%')
        </if>
        group by car.snow_flake_id
        order by car.create_time desc
    </select>
</mapper>