<?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.pnkx.mapper.PxBookkeepingClassificationMapper">

    <resultMap type="PxBookkeepingClassification" id="PxBookkeepingClassificationResult">
        <result property="id" column="id"/>
        <result property="version" column="version"/>
        <result property="typeIcon" column="type_icon"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="remark" column="remark"/>
        <result property="orderNum" column="order_num"/>
        <result property="typeName" column="type_name"/>
        <result property="typeLevel" column="type_level"/>
        <result property="typeParentId" column="type_parent_id"/>
        <result property="typeDifference" column="type_difference"/>
        <result property="statistics" column="statistics"/>
    </resultMap>

    <sql id="selectPxBookkeepingClassificationVo">
        SELECT id,
               version,
               type_icon,
               create_by,
               create_time,
               update_by,
               update_time,
               remark,
               order_num,
               type_name,
               type_level,
               type_parent_id,
               type_difference,
               CASE

                   WHEN type_level = '0' THEN
                       (
                           COALESCE((
                                        SELECT SUM(money)
                                        FROM px_bookkeeping_record r
                                                 LEFT JOIN px_bookkeeping_classification c ON r.type = c.id
                                        WHERE c.type_parent_id = cf.id
                                          and r.del_flag = 0
                                    ),
                                    0
                               ))
                   ELSE (
                       COALESCE(
                               (SELECT SUM(money) FROM px_bookkeeping_record r WHERE r.type = cf.id and r.del_flag = 0),
                               0))
                   END statistics
        FROM px_bookkeeping_classification cf

    </sql>


    <select id="selectPxBookkeepingClassificationList" parameterType="PxBookkeepingClassification"
            resultMap="PxBookkeepingClassificationResult">
        <include refid="selectPxBookkeepingClassificationVo"/>
        <where>
            del_flag = 0
            <if test="typeIcon != null  and typeIcon != ''">and type_icon = #{typeIcon}</if>
            <if test="createBy != null  and createBy != ''">and create_by = #{createBy}</if>
            <if test="typeName != null  and typeName != ''">and type_name like concat('%', #{typeName}, '%')</if>
            <if test="typeLevel != null  and typeLevel != ''">and type_level = #{typeLevel}</if>
            <if test="typeParentId != null  and typeParentId != ''">and type_parent_id = #{typeParentId}</if>
            <if test="typeDifference != null  and typeDifference != ''">and type_difference = #{typeDifference}</if>
            <if test="statistics != null  and statistics != ''">and statistics = #{statistics}</if>
        </where>
        order by order_num
    </select>

    <select id="selectPxBookkeepingClassificationById" parameterType="Long"
            resultMap="PxBookkeepingClassificationResult">
        <include refid="selectPxBookkeepingClassificationVo"/>
        where id = #{id} and del_flag = 0
    </select>

    <!--查询最近使用分类列表-->
    <select id="getLatelyTypeList" parameterType="com.pnkx.domain.po.PxBookkeepingClassification"
            resultMap="PxBookkeepingClassificationResult">
        SELECT c.id,
               type_icon,
               type_name
        FROM px_bookkeeping_record r
                 LEFT JOIN px_bookkeeping_classification c ON r.type = c.id
        WHERE c.type_difference = #{typeDifference}
        ORDER BY r.create_time DESC
    </select>

    <select id="getTypeListByParentId" parameterType="Long" resultType="Long">
        select id
        from px_bookkeeping_classification
        where type_parent_id = #{parentId}
    </select>

    <insert id="insertPxBookkeepingClassification" parameterType="PxBookkeepingClassification" useGeneratedKeys="true"
            keyProperty="id">
        insert into px_bookkeeping_classification
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="version != null">version,</if>
            <if test="typeIcon != null">type_icon,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="remark != null">remark,</if>
            <if test="orderNum != null">order_num,</if>
            <if test="typeName != null">type_name,</if>
            <if test="typeLevel != null">type_level,</if>
            <if test="typeParentId != null">type_parent_id,</if>
            <if test="typeDifference != null">type_difference,</if>
            <if test="statistics != null">statistics,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="version != null">#{version},</if>
            <if test="typeIcon != null">#{typeIcon},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="remark != null">#{remark},</if>
            <if test="orderNum != null">#{orderNum},</if>
            <if test="typeName != null">#{typeName},</if>
            <if test="typeLevel != null">#{typeLevel},</if>
            <if test="typeParentId != null">#{typeParentId},</if>
            <if test="typeDifference != null">#{typeDifference},</if>
            <if test="statistics != null">#{statistics},</if>
        </trim>
    </insert>

    <update id="updatePxBookkeepingClassification" parameterType="PxBookkeepingClassification">
        update px_bookkeeping_classification
        <trim prefix="SET" suffixOverrides=",">
            <if test="version != null">version = #{version},</if>
            <if test="typeIcon != null">type_icon = #{typeIcon},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="orderNum != null">order_num = #{orderNum},</if>
            <if test="typeName != null">type_name = #{typeName},</if>
            <if test="typeLevel != null">type_level = #{typeLevel},</if>
            <if test="typeParentId != null">type_parent_id = #{typeParentId},</if>
            <if test="typeDifference != null">type_difference = #{typeDifference},</if>
            <if test="statistics != null">statistics = #{statistics},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deletePxBookkeepingClassificationById" parameterType="Long">
        update px_bookkeeping_classification
        set del_flag = 1
        where id = #{id}
    </delete>

    <delete id="deletePxBookkeepingClassificationByIds" parameterType="String">
        update px_bookkeeping_classification set del_flag = 1 where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
</mapper>
