<?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.bonus.gs.sub.evaluate.outsourceEnterprise.dao.ViolationDao" >
    <insert id="addOutsourceEnterprise">
        insert into project_assignment(
        enterpriseName,
        socialCreditCode,
        setTime,
        corporateName,
        idCard,
        phone,
        inThoseDays,
        currentScore,
        create_time,
        update_time,
        is_active
        )
        values(
        #{enterpriseName},
        #{socialCreditCode},
        #{setTime},
        #{corporateName},
        #{idCard},
        #{phone},
        #{inThoseDays},
        #{currentScore},
        now(),
        now(),
        '1'
        )
    </insert>
    <insert id="addShopBaseInfo">
        insert into project_assignment(
            enterpriseName,
            socialCreditCode,
            setTime,
            corporateName,
            idCard,
            phone,
            inThoseDays,
            currentScore,
            create_time,
            update_time,
            is_active
        )
        values(
            #{enterpriseName},
            #{socialCreditCode},
            #{setTime},
            #{corporateName},
            #{idCard},
            #{phone},
            YEAR(NOW()),
            '12',
            now(),
            now(),
            '1'
            )
    </insert>
    <update id="updateOutsourceEnterprise">
        update project_assignment set
        enterpriseName = #{enterpriseName},
        socialCreditCode = #{socialCreditCode},
        setTime = #{setTime},
        corporateName = #{corporateName},
        idCard = #{idCard},
        phone = #{phone},
        inThoseDays = #{inThoseDays},
        currentScore = #{currentScore},
        update_time = now()
        where id = #{id}
    </update>
    <delete id="outsourceEnterpriseDelete">
        update project_assignment set is_active ='0' WHERE id in(
        <foreach item="id" collection="idArr" open="" separator="," close="">
            #{id}
        </foreach>
        )
    </delete>
    <select id="outsourceEnterpriseByPage" resultType="com.bonus.gs.sub.evaluate.outsourceEnterprise.beans.ViolationBean">
        SELECT
        pa.id,
        pa.enterpriseName as enterpriseName,
        pa.socialCreditCode as socialCreditCode,
        pa.setTime as setTime,
        pa.corporateName as corporateName,
        pa.idCard as idCard,pro.num proNum,
        pa.phone as phone,
        pa.inThoseDays as inThoseDays,
        if(sum(pvd.deductionOfPoints) IS NULL, '0', sum(pvd.deductionOfPoints)) as currentScore,
        COUNT(pvd.id) as violationCount
        FROM project_assignment pa
        LEFT JOIN pm_violation_details pvd ON pa.id = pvd.Violation_record and pvd.is_active = '1' and pvd.identification = '2'
        LEFT JOIN(
        SELECT count(1) num ,lk_id  FROM pm_org_info GROUP BY lk_id
        )pro on pro.lk_id = pa.id
        WHERE pa.is_active = '1'
        <if test="params.keyWord != null and params.keyWord != ''">
            and pa.enterpriseName LIKE CONCAT("%",#{params.keyWord},"%")
        </if>
        <if test="params.socialCreditCode != null and params.socialCreditCode != ''">
            and pa.socialCreditCode LIKE CONCAT("%",#{params.socialCreditCode},"%")
        </if>
        <if test="params.corporateName != null and params.corporateName != ''">
            and pa.corporateName LIKE CONCAT("%",#{params.corporateName},"%")
        </if>
        GROUP BY pa.id
    </select>
    <select id="toViewOutsourceEnterprise" resultType="com.bonus.gs.sub.evaluate.outsourceEnterprise.beans.ViolationBean">
        SELECT pa.id,
               pa.enterpriseName   as enterpriseName,
               pa.socialCreditCode as socialCreditCode,
               pa.setTime          as setTime,
               pa.corporateName    as corporateName,
               pa.idCard           as idCard,
               pa.phone            as phone
          FROM project_assignment pa
                   LEFT JOIN(SELECT count(1) num, lk_id
                               FROM pm_org_info
                              GROUP BY lk_id) pro
                            on pro.lk_id = pa.id
         WHERE pa.is_active = '1'
           and pa.id = #{id}
    </select>
    <select id="exportOutsourceEnterprise" resultType="com.bonus.gs.sub.evaluate.outsourceEnterprise.beans.ViolationBean">
        SELECT
            pa.id,
            pa.enterpriseName as enterpriseName,
            pa.socialCreditCode as socialCreditCode,
            pa.setTime as setTime,
            pa.corporateName as corporateName,
            pa.idCard as idCard,
            pa.phone as phone,
            ifnull(pro.num,'0') as proNum
        FROM project_assignment pa
        LEFT JOIN(
        SELECT count(1) num ,lk_id  FROM pm_org_info GROUP BY lk_id
        )pro on pro.lk_id = pa.id
        WHERE pa.is_active = '1'
        <if test="keyWord != null and keyWord != ''">
            and pa.enterpriseName LIKE CONCAT("%",#{keyWord},"%")
        </if>
        <if test="socialCreditCode != null and socialCreditCode != ''">
            and pa.socialCreditCode LIKE CONCAT("%",#{socialCreditCode},"%")
        </if>
        <if test="corporateName != null and corporateName != ''">
            and pa.corporateName LIKE CONCAT("%",#{corporateName},"%")
        </if>
        GROUP BY pa.id
    </select>
    <select id="encoding" resultType="java.lang.Integer">
        SELECT
            COUNT(1)
        FROM
            pm_violation
        WHERE
            violation_number = #{value} and is_active = '1'
    </select>
    <select id="personnelViolationByPage" resultType="com.bonus.gs.sub.evaluate.outsourceEnterprise.beans.ViolationBean">
        SELECT
            pvd.Violation_record as id,
            pu.NAME as name,
            case pu.SEX
                when '1' then '男'
                when '0' then '女'
                else '其他'
            end as sex,
            pu.LOGIN_NAME as idCard,
            pu.TELPHONE as phone,
            pd.NAME as deptName,
            YEAR(pvd.create_time) as inThoseDays,
            SUM(pvd.deductionOfPoints) as deductionOfPoints,
            COUNT(pvd.Violation_record) as violationCount
        FROM pm_violation_details pvd
        LEFT JOIN pm_user pu ON pvd.Violation_record = pu.id and pu.is_active = '1'
        LEFT JOIN pm_dept pd ON pu.dept_id = pd.id
        WHERE pvd.is_active = '1' AND pvd.identification = '1' AND YEAR(pvd.create_time) = YEAR(CURDATE())
        <if test="params.keyWord != null and params.keyWord != ''">
            and (
                pu.NAME LIKE CONCAT("%",#{params.keyWord},"%") or
                pu.LOGIN_NAME LIKE CONCAT("%",#{params.keyWord},"%") or
                pu.TELPHONE LIKE CONCAT("%",#{params.keyWord},"%")
            )
        </if>
        GROUP BY pvd.Violation_record
    </select>
    <select id="violationSubpageByPage" resultType="com.bonus.gs.sub.evaluate.outsourceEnterprise.beans.ViolationBean" parameterType="com.bonus.gs.sub.evaluate.outsourceEnterprise.beans.ViolationBean">
        SELECT
        pv.id as id,
        pd.PARENT_NAME as violator,
        pv.violation_number as violationNumber,
        case pv.type_violation
        when '1' then '管理违章'
        when '2' then '行为违章'
        when '3' then '装置违章'
        when '4' then '操作违章'
        else '其他'
        end as typeViolation,
        case pv.violation_level
        when '1' then '一般违章'
        when '2' then '一类严重违章'
        when '3' then '二类严重违章'
        when '4' then '三类严重违章'
        else '其他'
        end as violationLevel,
        pu1.NAME as personInCharge,
        pv.penalty_score as penaltyScore,
        pd1.enterpriseName as affiliatedEnterprise,
        pv.enterprise_penalty as enterprisePenalty,
        pu.NAME as jointLiability,
        pv.joint_penalty_score as jointPenaltyScore,
        pv.illegal_content as illegalContent,
        pu2.NAME as inspector,
        pd2.PARENT_NAME as inspectorUnit,
        pv.inspectionTime,
        DATE_FORMAT(pv.create_time, '%Y-%m-%d %H:%i:%s') as createTime,
        pv.update_time as updateTime,
        pd3.NAME as operatorId
        FROM
        pm_violation pv
        left join pm_dept pd on pv.violator = pd.id
        left join project_assignment pd1 on pv.affiliated_enterprise = pd1.id
        left join pm_dept pd2 on pv.inspector_unit = pd2.id
        left join pm_user pd3 on pv.operator = pd3.id
        left join pm_user pu on pv.jointLiability = pu.id
        left join pm_user pu1 on pv.personInCharge = pu1.id
        left join pm_user pu2 on pv.inspector = pu2.id
        WHERE
        pv.is_active = '1'
        <if test="params.identification == '1' or params.identification == 1">
            and (
                pv.personInCharge = #{params.id} or
                pv.jointLiability = #{params.id}
            )
        </if>
        <if test="params.identification == '2' or params.identification == 2">
            and pv.affiliated_enterprise = #{params.id}
        </if>
    </select>
    <select id="exportSubpageList" resultType="com.bonus.gs.sub.evaluate.outsourceEnterprise.beans.ViolationBean">
        SELECT
        pvd.Violation_record as id,
        pu.NAME as name,
        case pu.SEX
        when '1' then '男'
        when '0' then '女'
        else '其他'
        end as sex,
        pu.LOGIN_NAME as idCard,
        pu.TELPHONE as phone,
        pd.NAME as deptName,
        YEAR(pvd.create_time) as inThoseDays,
        12 - SUM(pvd.deductionOfPoints) as deductionOfPoints,
        COUNT(pvd.Violation_record) as violationCount
        FROM pm_violation_details pvd
        LEFT JOIN pm_user pu ON pvd.Violation_record = pu.id and pu.is_active = '1'
        LEFT JOIN pm_dept pd ON pu.dept_id = pd.id
        WHERE pvd.is_active = '1' AND pvd.identification = '1' AND YEAR(pvd.create_time) = YEAR(CURDATE())
        <if test="keyWord != null and keyWord != ''">
            and (
            pu.NAME LIKE CONCAT("%",#{keyWord},"%") or
            pu.LOGIN_NAME LIKE CONCAT("%",#{keyWord},"%") or
            pu.TELPHONE LIKE CONCAT("%",#{keyWord},"%")
            )
        </if>
        GROUP BY pvd.Violation_record
    </select>
    <select id="getOutSourceNumById" resultType="java.lang.Integer">
        select  count(1)
        from  project_assignment
        WHERE is_active=1
        <if test="enterpriseName!=null and enterpriseName!=''">
            and enterpriseName=#{enterpriseName}
        </if>
        <if test="socialCreditCode!=null and socialCreditCode!=''">
            and socialCreditCode=#{socialCreditCode}
        </if>
        <if test="idCard!=null and idCard!=''">
            and idCard=#{idCard}
        </if>
        <if test="phone!=null and phone!=''">
            and phone=#{phone}
        </if>
        <if test="id!=null and id!=''">
            and id!=#{id}
        </if>
    </select>
    <select id="getOutsourceEnterpriseByPage"
            resultType="com.bonus.gs.sub.evaluate.outsourceEnterprise.beans.ViolationBean">
        SELECT
        pa.id,
        pa.enterpriseName as enterpriseName,
        pa.socialCreditCode as socialCreditCode,
        pa.setTime as setTime,
        pa.corporateName as corporateName,
        pa.idCard as idCard,ifnull(pro.num,'0') proNum,
        pa.phone as phone
        FROM project_assignment pa
        LEFT JOIN(
        SELECT count(1) num ,lk_id  FROM pm_org_info GROUP BY lk_id
        )pro on pro.lk_id = pa.id
        WHERE pa.is_active = '1'
        <if test="keyWord != null and keyWord != ''">
            and pa.enterpriseName LIKE CONCAT("%",#{keyWord},"%")
        </if>
        <if test="socialCreditCode != null and socialCreditCode != ''">
            and pa.socialCreditCode LIKE CONCAT("%",#{socialCreditCode},"%")
        </if>
        <if test="corporateName != null and corporateName != ''">
            and pa.corporateName LIKE CONCAT("%",#{corporateName},"%")
        </if>
        GROUP BY pa.id
    </select>
    <select id="formValidation" resultType="java.lang.Integer">
        SELECT
            count(1)
          FROM project_assignment
         WHERE
             is_active = '1'
           AND
             enterpriseName=#{enterpriseName}
        <if test="id != '0' or id != 0">
            and id!=#{id}
        </if>
    </select>
</mapper>