<?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.ruoyi.cms.mapper.AppUserMapper">
    
    <resultMap type="AppUser" id="AppUserResult">
        <result property="userId"    column="user_id"    />
        <result property="name"    column="name"    />
        <result property="age"    column="age"    />
        <result property="sex"    column="sex"    />
        <result property="birthDate"    column="birth_date"    />
        <result property="education"    column="education"    />
        <result property="politicalAffiliation"    column="political_affiliation"    />
        <result property="phone"    column="phone"    />
        <result property="avatar"    column="avatar"    />
        <result property="salaryMin"    column="salary_min"    />
        <result property="salaryMax"    column="salary_max"    />
        <result property="area"    column="area"    />
        <result property="status"    column="status"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="loginIp"    column="login_ip"    />
        <result property="loginDate"    column="login_date"    />
        <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="jobTitleId"    column="job_title_id"    />
        <result property="isRecommend"    column="is_recommend"    />
        <result property="idCard"    column="id_card"    />
        <result property="workExperience"    column="work_experience"    />
        <result property="isCompanyUser"    column="is_company_user"    />
        <result property="dwUserid"    column="dw_userid"    />
    </resultMap>

    <sql id="selectAppUserVo">
        select user_id, name, age, sex, birth_date, education, political_affiliation, phone, avatar, salary_min, salary_max, area, status, del_flag, login_ip, login_date, create_by, create_time, update_by, update_time, remark,job_title_id,is_recommend,id_card,work_experience,is_company_user,dw_userid from app_user
    </sql>

    <select id="selectAppUserList" parameterType="AppUser" resultMap="AppUserResult">
        <include refid="selectAppUserVo"/>
        <where>  del_flag = '0'
            <if test="name != null  and name != ''"> and name like concat('%', cast(#{name, jdbcType=VARCHAR} as varchar), '%')</if>
            <if test="age != null  and age != ''"> and age = #{age}</if>
            <if test="sex != null  and sex != ''"> and sex = #{sex}</if>
            <if test="birthDate != null "> and birth_date = #{birthDate}</if>
            <if test="education != null  and education != ''"> and education = #{education}</if>
            <if test="politicalAffiliation != null  and politicalAffiliation != ''"> and political_affiliation = #{politicalAffiliation}</if>
            <if test="phone != null  and phone != ''"> and phone = #{phone}</if>
            <if test="avatar != null  and avatar != ''"> and avatar = #{avatar}</if>
            <if test="salaryMin != null  and salaryMin != ''"> and salary_min = #{salaryMin}</if>
            <if test="salaryMax != null  and salaryMax != ''"> and salary_max = #{salaryMax}</if>
            <if test="area != null  and area != ''"> and area = #{area}</if>
            <if test="status != null  and status != ''"> and status = #{status}</if>
            <if test="loginIp != null  and loginIp != ''"> and login_ip = #{loginIp}</if>
            <if test="loginDate != null "> and login_date = #{loginDate}</if>
            <if test="experience != null "> and experience = #{experience}</if>
            <if test="idCard != null  and idCard != ''"> and id_card like concat('%', CAST(#{idCard} AS VARCHAR), '%')</if>
            <if test="userId != null"> and user_id = #{userId}</if>
        </where>
    </select>
    <select id="selectByJobId" resultType="com.ruoyi.common.core.domain.entity.AppUser" parameterType="java.lang.Long">
        SELECT * FROM app_user WHERE user_id IN (
            select DISTINCT au.USER_ID  from APP_USER au
                                                 INNER JOIN JOB_APPLY ja ON ja.USER_ID  = au.USER_ID
            WHERE au.DEL_FLAG = '0' AND ja.DEL_FLAG = '0'  AND ja.JOB_Id = #{jobId})
    </select>

    <select id="selectByOpenid" resultType="com.ruoyi.common.core.domain.entity.AppUser">
        <include refid="selectAppUserVo"/> WHERE DEL_FLAG = '0' and openid=#{openid} and is_company_user=#{userType} LIMIT 1
    </select>

    <insert id="insertSysUserRole" parameterType="java.util.Map">
        insert into sys_user_role (user_id,role_id) values (#{userId},#{roleId})
    </insert>

    <insert id="insertSysUser" parameterType="com.ruoyi.common.core.domain.entity.SysUser" useGeneratedKeys="true" keyProperty="userId">
        insert into sys_user(
        <if test="userId != null and userId != 0">user_id,</if>
        <if test="deptId != null and deptId != 0">dept_id,</if>
        <if test="userName != null and userName != ''">user_name,</if>
        <if test="nickName != null and nickName != ''">nick_name,</if>
        <if test="email != null and email != ''">email,</if>
        <if test="avatar != null and avatar != ''">avatar,</if>
        <if test="phonenumber != null and phonenumber != ''">phonenumber,</if>
        <if test="sex != null and sex != ''">sex,</if>
        <if test="password != null and password != ''">password,</if>
        <if test="status != null and status != ''">status,</if>
        <if test="createBy != null and createBy != ''">create_by,</if>
        <if test="remark != null and remark != ''">remark,</if>
        <if test="idCard != null and idCard != ''">id_card,</if>
        create_time
        )values(
        <if test="userId != null and userId != ''">#{userId},</if>
        <if test="deptId != null and deptId != ''">#{deptId},</if>
        <if test="userName != null and userName != ''">#{userName},</if>
        <if test="nickName != null and nickName != ''">#{nickName},</if>
        <if test="email != null and email != ''">#{email},</if>
        <if test="avatar != null and avatar != ''">#{avatar},</if>
        <if test="phonenumber != null and phonenumber != ''">#{phonenumber},</if>
        <if test="sex != null and sex != ''">#{sex},</if>
        <if test="password != null and password != ''">#{password},</if>
        <if test="status != null and status != ''">#{status},</if>
        <if test="createBy != null and createBy != ''">#{createBy},</if>
        <if test="remark != null and remark != ''">#{remark},</if>
        <if test="idCard != null and idCard != ''">#{idCard},</if>
        sysdate()
        )
    </insert>

    <select id="getMyTj" resultType="com.ruoyi.common.core.domain.entity.MyChart">
        SELECT t1.yzj,t2.ysc,t3.ytd,0 AS yyy FROM
        (SELECT COUNT(user_id) AS yzj FROM app_review_job WHERE user_id = #{userId} AND del_flag = '0') t1
            CROSS JOIN (SELECT COUNT(user_id) AS ysc FROM job_collection WHERE user_id = #{userId} AND del_flag = '0') t2
            CROSS JOIN (SELECT COUNT(user_id) AS ytd FROM job_apply WHERE user_id = #{userId} AND del_flag = '0') t3
    </select>

    <select id="selectSysUserIdcard" resultType="com.ruoyi.common.core.domain.entity.SysUser">
        select * from sys_user where del_flag='0'
        <if test="idCard!=null and idCard!=''">
            and id_card=#{idCard}
        </if>
        limit 1
    </select>

    <select id="selectUserApplyList" parameterType="AppUser" resultType="com.ruoyi.common.core.domain.entity.AppUserShow">
        select a.user_id , a.name , a.age , a.sex , get_birth_date_from_id_card ( a.id_card ) as birth_date ,
        a.education , a.political_affiliation , a.phone , a.avatar , c.min_salary salaryMin ,c.max_salary salaryMax,a.area ,a.id_card ,
        b.job_id ,c.job_title,e.name as company_name,b.hire,b.hire_source,b.create_time,e.company_id,b.id as apply_id
        from app_user a
        inner join job_apply b on  a.user_id = b.user_id and a.del_flag = '0' and b.del_flag = '0'
        INNER join job c on b.job_id=c.job_id and c.del_flag='0'
        INNER join company e on c.company_id=e.company_id and e.del_flag='0'
        <where>
            <if test="name != null  and name != ''"> and a.name like concat('%', cast(#{name, jdbcType=VARCHAR} as varchar), '%')</if>
            <if test="birthDate != null "> and get_birth_date_from_id_card(a.id_card) = #{birthDate}</if>
            <if test="education != null  and education != ''"> and a.education = #{education}</if>
            <if test="politicalAffiliation != null  and politicalAffiliation != ''"> and a.political_affiliation = #{politicalAffiliation}</if>
            <if test="phone != null  and phone != ''"> and a.phone = #{phone}</if>
            <if test="idCard != null  and idCard != ''"> and a.id_card like concat('%', CAST(#{idCard} AS VARCHAR), '%')</if>
            <if test="userId != null"> and a.user_id = #{userId}</if>
        </where>
        order by b.create_time desc
    </select>

</mapper>