<?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="cn.com.ty.lift.ud.userCollect.mapper.UserCollectMapper">
    <resultMap id="BaseResultMap" type="cn.com.ty.lift.ud.userCollect.mapper.entity.UserCollectEntity">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="info_id" property="infoId" jdbcType="INTEGER"/>
        <result column="collect_type" property="collectType" jdbcType="VARCHAR"/>
        <result column="user_id" property="userId" jdbcType="INTEGER"/>
        <result column="descr" property="descr" jdbcType="VARCHAR"/>
        <result column="create_by" property="createBy" jdbcType="VARCHAR"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="update_by" property="updateBy" jdbcType="VARCHAR"/>
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
    </resultMap>
    <sql id="Base_Column_List">
    id, info_id, collect_type, user_id, descr,
    create_by, create_time, update_by, update_time
  </sql>


    <!--查询收藏的问题-->
    <select id="selectQuestionById" resultType="cn.com.ty.lift.ud.userCollect.mapper.entity.UserCollectQuestionVo">
        select qb.id as id , userInfo.`name` as userName , userInfo.avatar_url as avatarUrl ,qb.create_time as createTime,
        qb.like_num as likeNum,qb.browse_num as browseNum,qb.expression as expression,qb.solution as solution,qb.imgs as imgs,
        qb.title as title,lb.name as brandName,uc.id as uid,qb.statuz as statuz
        from user_collect uc
        left join question_bank qb on uc.info_id = qb.id
        left join lift_brand lb on lb.id = qb.brand_id
        left join user_info userInfo on userInfo.user_id = qb.expert
        <where>
            <if test="uId != null" >
                uc.user_id = #{uId,jdbcType=INTEGER}
            </if>
            and uc.collect_type = 1
            ${ew.sqlSegment}
        </where>
        limit #{page.pageIndex},#{page.pageSize}
    </select>

    <!--查询收藏的问题总数量-->
    <select id="countQuestionById" resultType="java.lang.Integer">
        select count(0) from user_collect uc
        left join question_bank qb on uc.info_id = qb.id
        left join user_info userInfo on userInfo.user_id = qb.expert
        <where>
            <if test="uId != null" >
                uc.user_id = #{uId,jdbcType=INTEGER}
            </if>
            and uc.collect_type = 1
            ${ew.sqlSegment}
        </where>
    </select>

    <!--查询我收藏的视频-->
    <select id="selectSchoolVideoById" resultType="cn.com.ty.lift.ud.userCollect.mapper.entity.UserCollectSchoolVideoVo">
        select sv.id as id ,sv.title as title,sv.like_num as  likeNum,sv.browse_num as  browseNum,sv.url as url,brand.name as brandName,
        sv.create_time as createTime,uc.id as uid,sv.cover as cover,sv.statuz as statuz
        from user_collect uc
        left join school_video sv on uc.info_id = sv.id
        left join lift_brand brand on sv.brand_id = brand.id
        <where>
            <if test="uId != null" >
                uc.user_id = #{uId,jdbcType=INTEGER}
            </if>
            and uc.collect_type = 2
            ${ew.sqlSegment}
        </where>
        limit #{page.pageIndex},#{page.pageSize}
    </select>

    <!--查询我收藏的视频的总数量-->
    <select id="countSchoolVideoById" resultType="java.lang.Integer">
        select count(0) from user_collect uc
        left join school_video sv on uc.info_id = sv.id
        left join lift_brand brand on sv.brand_id = brand.id
        <where>
            <if test="uId != null" >
                uc.user_id = #{uId,jdbcType=INTEGER}
            </if>
            and uc.collect_type = 2
            ${ew.sqlSegment}
        </where>
    </select>


    <!--查询我收藏的职位-->
    <select id="selectRecruitmentInfoById" resultType="cn.com.ty.lift.ud.userCollect.mapper.entity.UserCollectRecruitmentInfoVo">
        select re.id as id ,re.job as job ,re.upper_salary as upperSalary,re.lower_salary as lowerSalary,
        re.address as address,re.edu_level as eduLevel,re.upper_working as upperWorking,re.upper_working as upperWorking,
        re.create_time as createTime,com.`name` as companyName ,uc.id as uid,re.statuz as statuz,
        re.info as info ,re.province_name as provinceName ,re.city_name as cityName
        from user_collect uc
        left join recruitment_info  re on uc.info_id = re.id
        left join maintenance_company  com on com.id = re.company_id
        <where>
            <if test="uId != null" >
                uc.user_id = #{uId,jdbcType=INTEGER}
            </if>
            and uc.collect_type = 3
            ${ew.sqlSegment}
        </where>
        limit #{page.pageIndex},#{page.pageSize}
    </select>

    <!--查询我收藏的职位的总数量-->
    <select id="countRecruitmentInfoById" resultType="java.lang.Integer">
        select count(0)    from user_collect uc
        left join recruitment_info  re on uc.info_id = re.id
        left join maintenance_company  com on com.id = re.company_id
        <where>
            <if test="uId != null" >
                uc.user_id = #{uId,jdbcType=INTEGER}
            </if>
            and uc.collect_type = 3
            ${ew.sqlSegment}
        </where>
    </select>


    <!--查询我收藏的商品-->
    <select id="selectShoppingById" resultType="cn.com.ty.lift.ud.userCollect.mapper.entity.UserCollectShoppingVo">
        select sp.id as id ,sp.`name` as sName,brand.`name` as bName,sp.manufacturer as manufacturer,
        sp.price as price,sp.discount_price as discountPrice,
        sp.telephone as telephone,sp.imgs as imgs,sp.create_time as createTime,uc.id as uid,sp.statuz as statuz
        from user_collect uc
        left join shopping  sp on uc.info_id = sp.id
        left join lift_brand brand on sp.brand_id = brand.id
        <where>
            <if test="uId != null" >
                uc.user_id = #{uId,jdbcType=INTEGER}
            </if>
            and uc.collect_type = 4
            ${ew.sqlSegment}
        </where>
        limit #{page.pageIndex},#{page.pageSize}
    </select>

    <!--查询我收藏的商品的总数量-->
    <select id="countShoppingById" resultType="java.lang.Integer">
        select count(0)    from user_collect uc
        left join shopping  sp on uc.info_id = sp.id
        left join lift_brand brand on sp.brand_id = brand.id
        <where>
            <if test="uId != null" >
                uc.user_id = #{uId,jdbcType=INTEGER}
            </if>
            and uc.collect_type = 4
            ${ew.sqlSegment}
        </where>
    </select>


    <!--查询我收藏的商品-->
    <select id="selectAttachmentFileById" resultType="cn.com.ty.lift.ud.userCollect.mapper.entity.UserCollectAttachmentFileVo">
        select
          lf.`id` id,
          lf.`brand_id` brandId,
          lf.`data_table` dataTable,
          lf.`data_id` dataId,
          lf.`catagory_id` catagoryId,
          lf.`name` name,
          lf.`url` url,
          lf.`sort` sort,
          lf.`statuz` statuz,
          lf.`browse_num` browseNum,
          lf.`download_num` downloadNum,
          lf.`create_by` createBy,
          lf.`create_time` createTime,
          lf.`update_by` updateBy,
          lf.`update_time` updateTime
        from user_collect uc
        left join lift_file lf on uc.info_id = lf.id
        where uc.collect_type=5
        and uc.user_id = #{uId}
        limit #{pageIndex},#{pageSize}
    </select>

    <!--查询我收藏的商品的总数量-->
    <select id="countAttachmentFileById" resultType="java.lang.Integer">
        select
          count(0)
        from user_collect uc
        left join lift_file lf on uc.info_id = lf.id
        where uc.collect_type=5
        and uc.user_id = #{uId}
    </select>

</mapper>