<?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.PxBookkeepingAccountMapper">

    <resultMap type="PxBookkeepingAccount" id="PxBookkeepingAccountResult">
        <result property="id" column="id"/>
        <result property="version" column="version"/>
        <result property="accountType" column="account_type"/>
        <result property="createBy" column="create_by"/>
        <result property="accountIcon" column="account_icon"/>
        <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="accountName" column="account_name"/>
        <result property="balance" column="balance"/>
        <result property="inflow" column="inflow"/>
        <result property="flowOut" column="flow_out"/>
        <result property="delFlag" column="del_flag"/>
    </resultMap>

    <sql id="selectPxBookkeepingAccountVo">
        SELECT id,
               version,
               account_type,
               create_by,
               account_icon,
               create_time,
               update_by,
               update_time,
               remark,
               account_name,
               (
                                   COALESCE((
                                                SELECT SUM(cast(money as decimal(18, 2)))
                                                FROM px_bookkeeping_record r
                                                         LEFT JOIN px_bookkeeping_classification c ON r.type = c.id
                                                WHERE account = a.id
                                                  AND c.type_difference = '0'
                                                  AND r.del_flag = 0
                                            ),
                                            0
                                       ) - COALESCE((
                                                        SELECT SUM(cast(money as decimal(18, 2)))
                                                        FROM px_bookkeeping_record r
                                                                 LEFT JOIN px_bookkeeping_classification c ON r.type = c.id
                                                        WHERE account = a.id
                                                          AND c.type_difference = '1'
                                                          AND r.del_flag = 0
                                                    ),
                                                    0
                                       ) + COALESCE((
                                                        SELECT SUM(cast(money as decimal(18, 2)))
                                                        FROM px_bookkeeping_record

                                                        WHERE other_account = a.id
                                                          AND type = 0
                                                          AND del_flag = 0
                                                    ),
                                                    0
                                       ) - COALESCE((
                                                        SELECT SUM(cast(money as decimal(18, 2)))
                                                        FROM px_bookkeeping_record

                                                        WHERE account = a.id
                                                          AND type = 0
                                                          AND del_flag = 0
                                                    ),
                                                    0
                                       ) + COALESCE((
                                                        SELECT SUM(cast(money as decimal(18, 2)))
                                                        FROM px_bookkeeping_record
                                                        WHERE account = a.id
                                                          AND type = 1
                                                          AND del_flag = 0
                                                    ),
                                                    0
                                       )) balance,
               (
                       COALESCE((
                                    SELECT SUM(cast(money as decimal(18, 2)))
                                    FROM px_bookkeeping_record r
                                             LEFT JOIN px_bookkeeping_classification c ON r.type = c.id
                                    WHERE account = a.id
                                      AND c.type_difference = '0'
                                      AND r.del_flag = 0
                                ),
                                0
                           ) + COALESCE((
                                            SELECT SUM(cast(money as decimal(18, 2)))
                                            FROM px_bookkeeping_record
                                            WHERE other_account = a.id
                                              AND type = 0
                                              AND del_flag = 0
                                        ),
                                        0
                           )
                   )                      inflow,
               (
                       COALESCE((
                                    SELECT SUM(cast(money as decimal(18, 2)))
                                    FROM px_bookkeeping_record r
                                             LEFT JOIN px_bookkeeping_classification c ON r.type = c.id
                                    WHERE account = a.id
                                      AND c.type_difference = '1'
                                      AND r.del_flag = 0
                                ),
                                0
                           ) + COALESCE((
                                            SELECT SUM(cast(money as decimal(18, 2)))
                                            FROM px_bookkeeping_record
                                            WHERE account = a.id
                                              AND type = 0
                                              AND del_flag = 0
                                        ),
                                        0
                           ))             flow_out
        FROM px_bookkeeping_account a
    </sql>

    <select id="selectPxBookkeepingAccountList" parameterType="PxBookkeepingAccount"
            resultMap="PxBookkeepingAccountResult">
        <include refid="selectPxBookkeepingAccountVo"/>
        <where>
            del_flag = 0
            <if test="accountType != null  and accountType != ''">and account_type = #{accountType}</if>
            <if test="createBy != null  and createBy != ''">and create_by = #{createBy}</if>
            <if test="accountIcon != null  and accountIcon != ''">and account_icon = #{accountIcon}</if>
            <if test="accountName != null  and accountName != ''">and account_name like concat('%', #{accountName},
                '%')
            </if>
            <if test="balance != null  and balance != ''">and balance = #{balance}</if>
            <if test="inflow != null  and inflow != ''">and inflow = #{inflow}</if>
            <if test="flowOut != null  and flowOut != ''">and flow_out = #{flowOut}</if>
        </where>
    </select>

    <select id="selectPxBookkeepingAccountById" parameterType="Long" resultMap="PxBookkeepingAccountResult">
        <include refid="selectPxBookkeepingAccountVo"/>
        where id = #{id} and del_flag = 0
    </select>

    <!--查询最近使用账户列表-->
    <select id="getLatelyAccountList" parameterType="com.pnkx.domain.po.PxBookkeepingClassification"
            resultMap="PxBookkeepingAccountResult">
        SELECT a.id,
               account_icon,
               account_name
        FROM px_bookkeeping_record r
                 LEFT JOIN px_bookkeeping_account a ON r.account = a.id
                 LEFT JOIN px_bookkeeping_classification c ON r.type = c.id
        where c.type_difference = #{typeDifference}
        ORDER BY r.create_time DESC
    </select>

    <insert id="insertPxBookkeepingAccount" parameterType="PxBookkeepingAccount" useGeneratedKeys="true"
            keyProperty="id">
        insert into px_bookkeeping_account
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="version != null">version,</if>
            <if test="accountType != null">account_type,</if>
            <if test="createBy != null">create_by,</if>
            <if test="accountIcon != null">account_icon,</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="accountName != null">account_name,</if>
            <if test="balance != null">balance,</if>
            <if test="inflow != null">inflow,</if>
            <if test="flowOut != null">flow_out,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="version != null">#{version},</if>
            <if test="accountType != null">#{accountType},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="accountIcon != null">#{accountIcon},</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="accountName != null">#{accountName},</if>
            <if test="balance != null">#{balance},</if>
            <if test="inflow != null">#{inflow},</if>
            <if test="flowOut != null">#{flowOut},</if>
        </trim>
    </insert>

    <update id="updatePxBookkeepingAccount" parameterType="PxBookkeepingAccount">
        update px_bookkeeping_account
        <trim prefix="SET" suffixOverrides=",">
            <if test="version != null">version = #{version},</if>
            <if test="accountType != null">account_type = #{accountType},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="accountIcon != null">account_icon = #{accountIcon},</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="accountName != null">account_name = #{accountName},</if>
            <if test="balance != null">balance = #{balance},</if>
            <if test="inflow != null">inflow = #{inflow},</if>
            <if test="flowOut != null">flow_out = #{flowOut},</if>
        </trim>
        where id = #{id}
    </update>

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

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