<?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.canteen.core.account.mapper.AccInfoMapper">
    <resultMap type="com.bonus.canteen.core.account.domain.AccountInfo" id="AccInfoResult">
        <result property="accountId"    column="account_id"    />
        <result property="accountName"    column="account_name"    />
        <result property="userId"    column="user_id"    />
        <result property="scope"    column="scope"    />
        <result property="endDate"    column="end_date"    />
        <result property="accountStatus"    column="account_status"    />
        <result property="payPwd"    column="pay_pwd"    />
        <result property="lastCreditTime"    column="last_credit_time"    />
        <result property="currCreditCount"    column="curr_credit_count"    />
        <result property="currBrushCount"    column="curr_brush_count"    />
        <result property="sameDayCount"    column="same_day_count"    />
        <result property="sameMonthCount"    column="same_month_count"    />
        <result property="currCumuAmount"    column="curr_cumu_amount"    />
        <result property="dayCumuAmount"    column="day_cumu_amount"    />
        <result property="monthSumuAmount"    column="month_sumu_amount"    />
        <result property="minWalletBalLimit"    column="min_wallet_bal_limit"    />
        <result property="minRedBalLimit"    column="min_red_bal_limit"    />
        <result property="minSubBalLimit"    column="min_sub_bal_limit"    />
        <result property="monthFullReduceAmount"    column="month_full_reduce_amount"    />
        <result property="lastFullReduceTime"    column="last_full_reduce_time"    />
        <result property="lastSubTime"    column="last_sub_time"    />
        <result property="subValidityDate"    column="sub_validity_date"    />
        <result property="lastSubAmount"    column="last_sub_amount"    />
        <result property="lastWalTime"    column="last_wal_time"    />
        <result property="lastWalAmount"    column="last_wal_amount"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
    </resultMap>

    <sql id="selectAccInfoVo">
        select account_id, account_name, user_id, scope, end_date,
               account_status, pay_pwd, last_credit_time, curr_credit_count, curr_brush_count,
               same_day_count, same_month_count, curr_cumu_amount, day_cumu_amount, month_sumu_amount,
               min_wallet_bal_limit, min_red_bal_limit, min_sub_bal_limit, month_full_reduce_amount,
               last_full_reduce_time, last_sub_time, sub_validity_date, last_sub_amount, last_wal_time, last_wal_amount,
               create_by, create_time, update_by, update_time, del_flag
        from account_info
    </sql>

    <select id="selectAccInfoList" parameterType="com.bonus.canteen.core.account.domain.AccountInfo" resultMap="AccInfoResult">
        <include refid="selectAccInfoVo"/>
        <where>
            <if test="accountId != null "> and account_id = #{accountId}</if>
            <if test="accountName != null  and accountName != ''"> and account_name like concat('%', #{accountName}, '%')</if>
            <if test="userId != null "> and user_id = #{userId}</if>
            <if test="scope != null "> and scope = #{scope}</if>
            <if test="endDate != null "> and end_date = #{endDate}</if>
            <if test="accountStatus != null "> and account_status = #{accountStatus}</if>
            <if test="payPwd != null  and payPwd != ''"> and pay_pwd = #{payPwd}</if>
            <if test="lastCreditTime != null "> and last_credit_time = #{lastCreditTime}</if>
            <if test="currCreditCount != null "> and curr_credit_count = #{currCreditCount}</if>
            <if test="currBrushCount != null "> and curr_brush_count = #{currBrushCount}</if>
            <if test="sameDayCount != null "> and same_day_count = #{sameDayCount}</if>
            <if test="sameMonthCount != null "> and same_month_count = #{sameMonthCount}</if>
            <if test="currCumuAmount != null "> and curr_cumu_amount = #{currCumuAmount}</if>
            <if test="dayCumuAmount != null "> and day_cumu_amount = #{dayCumuAmount}</if>
            <if test="monthSumuAmount != null "> and month_sumu_amount = #{monthSumuAmount}</if>
            <if test="minWalletBalLimit != null "> and min_wallet_bal_limit = #{minWalletBalLimit}</if>
            <if test="minRedBalLimit != null "> and min_red_bal_limit = #{minRedBalLimit}</if>
            <if test="minSubBalLimit != null "> and min_sub_bal_limit = #{minSubBalLimit}</if>
            <if test="monthFullReduceAmount != null "> and month_full_reduce_amount = #{monthFullReduceAmount}</if>
            <if test="lastFullReduceTime != null "> and last_full_reduce_time = #{lastFullReduceTime}</if>
            <if test="lastSubTime != null "> and last_sub_time = #{lastSubTime}</if>
            <if test="subValidityDate != null "> and sub_validity_date = #{subValidityDate}</if>
            <if test="lastSubAmount != null "> and last_sub_amount = #{lastSubAmount}</if>
            <if test="lastWalTime != null "> and last_wal_time = #{lastWalTime}</if>
            <if test="lastWalAmount != null "> and last_wal_amount = #{lastWalAmount}</if>
            <if test="delFlag != null  and delFlag != ''"> and del_flag = #{delFlag}</if>
        </where>
    </select>

    <select id="selectAccInfoVo" parameterType="com.bonus.canteen.core.account.domain.AccountInfo" resultType="com.bonus.canteen.core.account.domain.AccInfoVo">
        <include refid="selectAccInfoVo"/>
        <where>
            <if test="accountId != null "> and account_id = #{accountId}</if>
            <if test="accountName != null  and accountName != ''"> and account_name like concat('%', #{accountName}, '%')</if>
            <if test="userId != null "> and user_id = #{userId}</if>
            <if test="scope != null "> and scope = #{scope}</if>
            <if test="endDate != null "> and end_date = #{endDate}</if>
            <if test="accountStatus != null "> and account_status = #{accountStatus}</if>
            <if test="payPwd != null  and payPwd != ''"> and pay_pwd = #{payPwd}</if>
            <if test="lastCreditTime != null "> and last_credit_time = #{lastCreditTime}</if>
            <if test="currCreditCount != null "> and curr_credit_count = #{currCreditCount}</if>
            <if test="currBrushCount != null "> and curr_brush_count = #{currBrushCount}</if>
            <if test="sameDayCount != null "> and same_day_count = #{sameDayCount}</if>
            <if test="sameMonthCount != null "> and same_month_count = #{sameMonthCount}</if>
            <if test="currCumuAmount != null "> and curr_cumu_amount = #{currCumuAmount}</if>
            <if test="dayCumuAmount != null "> and day_cumu_amount = #{dayCumuAmount}</if>
            <if test="monthSumuAmount != null "> and month_sumu_amount = #{monthSumuAmount}</if>
            <if test="minWalletBalLimit != null "> and min_wallet_bal_limit = #{minWalletBalLimit}</if>
            <if test="minRedBalLimit != null "> and min_red_bal_limit = #{minRedBalLimit}</if>
            <if test="minSubBalLimit != null "> and min_sub_bal_limit = #{minSubBalLimit}</if>
            <if test="monthFullReduceAmount != null "> and month_full_reduce_amount = #{monthFullReduceAmount}</if>
            <if test="lastFullReduceTime != null "> and last_full_reduce_time = #{lastFullReduceTime}</if>
            <if test="lastSubTime != null "> and last_sub_time = #{lastSubTime}</if>
            <if test="subValidityDate != null "> and sub_validity_date = #{subValidityDate}</if>
            <if test="lastSubAmount != null "> and last_sub_amount = #{lastSubAmount}</if>
            <if test="lastWalTime != null "> and last_wal_time = #{lastWalTime}</if>
            <if test="lastWalAmount != null "> and last_wal_amount = #{lastWalAmount}</if>
            <if test="delFlag != null  and delFlag != ''"> and del_flag = #{delFlag}</if>
        </where>
    </select>

    <select id="selectAccInfoById" parameterType="Long" resultMap="AccInfoResult">
        <include refid="selectAccInfoVo"/>
        where account_id = #{accId}
    </select>

    <insert id="insertAccInfo" parameterType="com.bonus.canteen.core.account.domain.AccountInfo" useGeneratedKeys="true" keyProperty="accountId">
        insert into account_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="accountName != null and accountName != ''">account_name,</if>
            <if test="userId != null">user_id,</if>
            <if test="scope != null">scope,</if>
            <if test="endDate != null">end_date,</if>
            <if test="accountStatus != null">account_status,</if>
            <if test="payPwd != null and payPwd != ''">pay_pwd,</if>
            <if test="lastCreditTime != null">last_credit_time,</if>
            <if test="currCreditCount != null">curr_credit_count,</if>
            <if test="currBrushCount != null">curr_brush_count,</if>
            <if test="sameDayCount != null">same_day_count,</if>
            <if test="sameMonthCount != null">same_month_count,</if>
            <if test="currCumuAmount != null">curr_cumu_amount,</if>
            <if test="dayCumuAmount != null">day_cumu_amount,</if>
            <if test="monthSumuAmount != null">month_sumu_amount,</if>
            <if test="minWalletBalLimit != null">min_wallet_bal_limit,</if>
            <if test="minRedBalLimit != null">min_red_bal_limit,</if>
            <if test="minSubBalLimit != null">min_sub_bal_limit,</if>
            <if test="monthFullReduceAmount != null">month_full_reduce_amount,</if>
            <if test="lastFullReduceTime != null">last_full_reduce_time,</if>
            <if test="lastSubTime != null">last_sub_time,</if>
            <if test="subValidityDate != null">sub_validity_date,</if>
            <if test="lastSubAmount != null">last_sub_amount,</if>
            <if test="lastWalTime != null">last_wal_time,</if>
            <if test="lastWalAmount != null">last_wal_amount,</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>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="accountName != null and accountName != ''">#{accountName},</if>
            <if test="userId != null">#{userId},</if>
            <if test="scope != null">#{scope},</if>
            <if test="endDate != null">#{endDate},</if>
            <if test="accountStatus != null">#{accountStatus},</if>
            <if test="payPwd != null and payPwd != ''">#{payPwd},</if>
            <if test="lastCreditTime != null">#{lastCreditTime},</if>
            <if test="currCreditCount != null">#{currCreditCount},</if>
            <if test="currBrushCount != null">#{currBrushCount},</if>
            <if test="sameDayCount != null">#{sameDayCount},</if>
            <if test="sameMonthCount != null">#{sameMonthCount},</if>
            <if test="currCumuAmount != null">#{currCumuAmount},</if>
            <if test="dayCumuAmount != null">#{dayCumuAmount},</if>
            <if test="monthSumuAmount != null">#{monthSumuAmount},</if>
            <if test="minWalletBalLimit != null">#{minWalletBalLimit},</if>
            <if test="minRedBalLimit != null">#{minRedBalLimit},</if>
            <if test="minSubBalLimit != null">#{minSubBalLimit},</if>
            <if test="monthFullReduceAmount != null">#{monthFullReduceAmount},</if>
            <if test="lastFullReduceTime != null">#{lastFullReduceTime},</if>
            <if test="lastSubTime != null">#{lastSubTime},</if>
            <if test="subValidityDate != null">#{subValidityDate},</if>
            <if test="lastSubAmount != null">#{lastSubAmount},</if>
            <if test="lastWalTime != null">#{lastWalTime},</if>
            <if test="lastWalAmount != null">#{lastWalAmount},</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>
         </trim>
    </insert>

    <update id="updateAccInfo" parameterType="com.bonus.canteen.core.account.domain.AccountInfo">
        update account_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="accountId != null">account_id = #{accountId},</if>
            <if test="accountName != null and accountName != ''">account_name = #{accountName},</if>
            <if test="userId != null">user_id = #{userId},</if>
            <if test="scope != null">scope = #{scope},</if>
            <if test="endDate != null">end_date = #{endDate},</if>
            <if test="accountStatus != null">account_status = #{accountStatus},</if>
            <if test="payPwd != null and payPwd != ''">pay_pwd = #{payPwd},</if>
            <if test="lastCreditTime != null">last_credit_time = #{lastCreditTime},</if>
            <if test="currCreditCount != null">curr_credit_count = #{currCreditCount},</if>
            <if test="currBrushCount != null">curr_brush_count = #{currBrushCount},</if>
            <if test="sameDayCount != null">same_day_count = #{sameDayCount},</if>
            <if test="sameMonthCount != null">same_month_count = #{sameMonthCount},</if>
            <if test="currCumuAmount != null">curr_cumu_amount = #{currCumuAmount},</if>
            <if test="dayCumuAmount != null">day_cumu_amount = #{dayCumuAmount},</if>
            <if test="monthSumuAmount != null">month_sumu_amount = #{monthSumuAmount},</if>
            <if test="minWalletBalLimit != null">min_wallet_bal_limit = #{minWalletBalLimit},</if>
            <if test="minRedBalLimit != null">min_red_bal_limit = #{minRedBalLimit},</if>
            <if test="minSubBalLimit != null">min_sub_bal_limit = #{minSubBalLimit},</if>
            <if test="monthFullReduceAmount != null">month_full_reduce_amount = #{monthFullReduceAmount},</if>
            <if test="lastFullReduceTime != null">last_full_reduce_time = #{lastFullReduceTime},</if>
            <if test="lastSubTime != null">last_sub_time = #{lastSubTime},</if>
            <if test="subValidityDate != null">sub_validity_date = #{subValidityDate},</if>
            <if test="lastSubAmount != null">last_sub_amount = #{lastSubAmount},</if>
            <if test="lastWalTime != null">last_wal_time = #{lastWalTime},</if>
            <if test="lastWalAmount != null">last_wal_amount = #{lastWalAmount},</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="delFlag != null">del_flag = #{delFlag},</if>
        </trim>
        where account_id = #{accountId}
    </update>

    <delete id="deleteAccInfoByUserId" parameterType="Long">
        update account_info set del_flag = '2' where user_id = #{userId}
    </delete>

    <delete id="deleteAccInfoByIds" parameterType="String">
        update account_info set del_flag = '2' where account_id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="queryAccInfoInvalidSum" resultType="java.lang.Integer">
        SELECT count( a.account_id ) FROM account_info a
        WHERE a.account_status = #{accStatus} AND a.update_time BETWEEN #{startDateTime} and  #{endDateTime}
    </select>

    <select id="queryAccInfoDetails" resultType="com.bonus.canteen.core.account.domain.vo.AccInfoDetailsVO">
        SELECT
        t1.update_time,
        t1.account_id,
        t2.user_id,
        t2.nick_name,
        t2.phonenumber as phoneNumber,
        t6.dept_name,
        t6.dept_full_name,
        t2.user_type as userType,
        t1.scope,
        t1.account_status,
        t1.end_date,
        ac.serial_num
         FROM account_info t1
         INNER JOIN sys_user t2 ON t1.user_id = t2.user_id
         LEFT JOIN account_card ac on t2.user_id = ac.user_id
         LEFT JOIN sys_dept t6 on t6.dept_id = t2.dept_id
         <include refid="queryAccountInfo_ref"/>
        order by t1.account_id desc
     </select>

    <select id="queryAccInfoBalanceSum" resultType="com.bonus.canteen.core.account.domain.vo.AccInfoDetailsVO">
        SELECT
        SUM( t4.accBalTotal ) AS accBalTotal,
        SUM( t4.walletBal ) AS walletBal,
        SUM( t4.subsidyBal ) AS subsidyBal,
        SUM( t4.accAvailableBal ) AS accAvailableBal,
        SUM( t4.accFreezeBalTotal ) AS accFreezeBalTotal
        FROM account_info t1
        INNER JOIN sys_user t2 ON t1.user_id = t2.user_id
        LEFT JOIN
        (
        SELECT
        user_id,
        SUM( CASE WHEN wallet_type = 1 THEN wallet_bal ELSE 0 END ) AS walletBal,
        SUM( CASE WHEN wallet_type = 2 THEN wallet_bal ELSE 0 END ) AS subsidyBal,
        SUM( a.wallet_bal ) AS accAvailableBal,
        SUM( ifnull(a.frozen_balance, 0) ) AS accFreezeBalTotal,
        SUM( a.wallet_bal ) + SUM( ifnull(a.frozen_balance, 0) ) AS accBalTotal
        FROM account_wallet_info a
        GROUP BY user_id
        ) AS t4 ON t4.user_id = t1.user_id
        <include refid="queryAccountInfo_ref"/>
    </select>

    <sql id="queryAccountInfo_ref">
        <where>
            t1.del_flag = 0
            and t1.account_status in
            <foreach collection="accountInfoQueryParam.accountStatusList" item="accStatus" separator="," open="(" close=")">
                #{accStatus}
            </foreach>
            <if test="accountInfoQueryParam.searchValue != null and accountInfoQueryParam.searchValue != ''">
                and (t2.nick_name like CONCAT('%',#{accountInfoQueryParam.searchValue},'%')
                or t2.user_id like CONCAT('%',#{accountInfoQueryParam.searchValue},'%')
                or t2.phonenumber = #{encryptedSearchValue}
                )
            </if>
            <if test="accountInfoQueryParam.deptIdList != null and accountInfoQueryParam.deptIdList.size() > 0">
                and t2.dept_id in
                <foreach collection="accountInfoQueryParam.deptIdList" item="deptId" separator="," open="(" close=")">
                    #{deptId}
                </foreach>
            </if>
            <if test="accountInfoQueryParam.startDateTime != null">
                and t1.update_time <![CDATA[ >= ]]> #{accountInfoQueryParam.startDateTime}
            </if>
            <if test="accountInfoQueryParam.endDateTime != null">
                and t1.update_time <![CDATA[ <= ]]> #{accountInfoQueryParam.endDateTime}
            </if>
            <if test="accountInfoQueryParam.userType != null">
                and t2.user_type = #{accountInfoQueryParam.userType}
            </if>
            <if test="accountInfoQueryParam.walletType != null">
                AND EXISTS(
                SELECT null FROM
                (
                SELECT
                user_id,
                SUM( CASE WHEN wallet_type = 1 THEN wallet_bal ELSE 0 END ) AS walletBal,
                SUM( CASE WHEN wallet_type = 2 THEN wallet_bal ELSE 0 END ) AS subsidyBal,
                SUM( a.wallet_bal ) + SUM( ifnull(a.frozen_balance, 0) ) accBalTotal
                FROM
                account_wallet_info a
                GROUP BY
                user_id
                ) AS t4
                <where>
                    t4.user_id = t1.user_id
                    <!--
                    <if test="accountInfoQueryParam.walletMinAmount != null">
                        <if test="accountInfoQueryParam.walletType == 0">
                            and  t4.accBalTotal <![CDATA[ >= ]]> #{accountInfoQueryParam.walletMinAmount}
                        </if>
                        <if test="accountInfoQueryParam.walletType == 1">
                            and  t4.walletBal <![CDATA[ >= ]]> #{accountInfoQueryParam.walletMinAmount}
                        </if>
                        <if test="accountInfoQueryParam.walletType == 2">
                            and  t4.subsidyBal <![CDATA[ >= ]]> #{accountInfoQueryParam.walletMinAmount}
                        </if>
                    </if>
                    <if test="accountInfoQueryParam.walletMaxAmount != null">
                        <if test="accountInfoQueryParam.walletType == 0">
                            and  t4.accBalTotal <![CDATA[ <= ]]> #{accountInfoQueryParam.walletMaxAmount}
                        </if>
                        <if test="accountInfoQueryParam.walletType == 1">
                            and  t4.walletBal <![CDATA[ <= ]]> #{accountInfoQueryParam.walletMaxAmount}
                        </if>
                        <if test="accountInfoQueryParam.walletType == 2">
                            and  t4.subsidyBal <![CDATA[ <= ]]> #{accountInfoQueryParam.walletMaxAmount}
                        </if>
                    </if>
                    -->
                </where>
                )
            </if>
        </where>
    </sql>

    <update id="updateAccInfoEndDateByUserId">
        update account_info
            set end_date = #{endDate},
                update_by = #{updateBy},
                update_time = now()
        where user_id in
        <foreach item="userId" collection="userIds" open="(" separator="," close=")">
            #{userId}
        </foreach>
    </update>

    <select id="queryAccInfoByUserIds" resultType="com.bonus.canteen.core.account.domain.vo.AccInfoDetailsVO">
        SELECT ai.account_id,
               ai.user_id,
               ai.end_date,
               ai.account_status,
               su.nick_name,
               su.phonenumber,
               su.dept_id,
               co.dept_full_name,
               su.user_type
        FROM account_info ai
             LEFT JOIN sys_user su ON su.user_id = ai.user_id
             LEFT JOIN sys_dept co ON co.dept_id = su.dept_id
        WHERE ai.user_id in
        <foreach collection="userIds" item="userId" separator="," open="(" close=")">
            #{userId}
        </foreach>
    </select>

    <select id="queryAccInfoByUserId" resultType="com.bonus.canteen.core.account.domain.vo.AccInfoDetailsVO">
        SELECT ai.account_id,
        ai.user_id,
        ai.end_date,
        ai.del_flag,
        ai.account_status,
        su.nick_name,
        su.phonenumber,
        su.dept_id
        FROM account_info ai
        LEFT JOIN sys_user su ON su.user_id = ai.user_id
        LEFT JOIN sys_dept co ON co.dept_id = su.dept_id
        WHERE ai.user_id = #{userId}
    </select>
 </mapper>
