<?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.xxfc.platform.tour.mapper.TourGoodVerificationMapper">

	<!-- 可根据自己的需求，是否要使用 -->
    <resultMap type="com.xxfc.platform.tour.entity.TourGoodVerification" id="tourGoodVerificationMap">
        <result property="id" column="id"/>
        <result property="speId" column="spe_id"/>
        <result property="siteId" column="site_id"/>
        <result property="goodId" column="good_id"/>
        <result property="totalPerson" column="total_person"/>
        <result property="verificationPerson" column="verification_person"/>
        <result property="status" column="status"/>
        <result property="isDel" column="is_del"/>
    </resultMap>

    <update id="delVerification">
        update  tour_good_verification set  is_del=1
        where total_person=0 and verification_person=0
        and good_id=#{goodId}
        <if test="list!=null ">
            and id not in (
            <trim suffixOverrides=",">
                <foreach collection="list" item="id">
                    #{id},
                </foreach>
            </trim>
            )
        </if>
    </update>

    <select id="findVerificationAll" resultType="com.xxfc.platform.tour.vo.TourGoodOrderVo">
       select * from (SELECT v.id as `id`,v.total_person as `headCount`,v.status as `travelStatus`,v.verification_person as `tripOfNum`,g.name as `name`,
        g.cover as `coverUrl`,p.start_time as `startDate`,s.depart_time as `startTime`FROM tour_good_verification v
        LEFT JOIN tour_good_site s ON v.site_id=s.id
        LEFT JOIN tour_good g ON v.good_id=g.id
        LEFT JOIN tour_good_spe_price p ON v.spe_id=p.id
        WHERE <![CDATA[`total_person`<>0]]>
        <if test='companyId !=null'>
           and s.company_id=#{companyId}
        </if>
        <if test="orderStatus!=null">
            and v.status=#{orderStatus}
        </if>
        <if test="travelDate!=null">
            and p.start_time=#{travelDate}
        </if> ORDER BY p.start_time DESC ) as `goodOrder`
    </select>
    <!-- 获取旅游路线id-->
     <select id="getGoodList" resultType="com.xxfc.platform.tour.vo.TourVerificationInfoVo">
             SELECT
        v.good_id as goodId,
        g.`name` as goodName
    FROM
        tour_good_verification v
    LEFT JOIN tour_good g ON v.good_id = g.id
    WHERE
        v.is_del = 0
    <if test="list!=null and list.size != 0">
             and v.site_id  in (
             <trim suffixOverrides=",">
                 <foreach collection="list" item="item">
                     #{item.siteId},
                 </foreach>
             </trim>
             )
     </if>
    GROUP BY
        v.good_id
     </select>
    <!--获取路线id-->
    <select id="getSiteList" resultType="com.xxfc.platform.tour.vo.TourVerificationInfoVo">
        SELECT
        id as siteId,
        name as siteName,
        company_id as companyId
         FROM tour_good_site
        WHERE  is_del=0
        and type=0
        <if test="goodId!=null and goodId!='' ">
            and good_id=#{goodId}
        </if>
        <if test="companyId!=null and companyId!='' ">
        and company_id=#{companyId}
        </if>
        <if test="companyList!=null and companyList.size != 0">
            and company_id  in (
            <trim suffixOverrides=",">
                <foreach collection="companyList" item="id">
                    #{id},
                </foreach>
            </trim>
            )
        </if>
        order by  rank desc
    </select>
    <!--获取时间-->
     <select id="getSpeList" resultType="com.xxfc.platform.tour.vo.TourVerificationInfoVo">
        SELECT
         DISTINCT
         v.id as verificationId,
         v.spe_id as speId,
         p.start_time as startTime,
         p.end_time as endTime,
         CONCAT(date_format(p.start_time,'%Y-%m-%d'), '~', date_format(p.end_time, '%Y-%m-%d')) as time
         FROM
         tour_good_verification v
         LEFT JOIN  tour_good_site s   on v.site_id=s.id
         LEFT JOIN  tour_good_spe_price p ON v.spe_id=p.id
         WHERE
         v.is_del = 0 and v.good_id=#{goodId} <!--and DATEDIFF(p.start_time,NOW())>0-->
         <if test="siteId!=null and siteId!='' ">
             and v.site_id=#{siteId}
         </if>
         <if test="companyId!=null and companyId!='' ">
             and s.company_id=#{companyId}
         </if>
          ORDER BY
            p.start_time

     </select>
    <!--获取核销信息-->
    <select id="getVerification" resultType="com.xxfc.platform.tour.vo.TourVerificationInfoVo">
        SELECT
        DISTINCT
        v.id as verificationId,
        v.spe_id as speId,
        p.start_time as startTime,
        p.end_time as endTime,
        CONCAT(date_format(p.start_time,'%Y-%m-%d'), '~', date_format(p.end_time, '%Y-%m-%d')) as time,
        s.id as siteId,
        s.name as siteName,
        s.company_id as companyId,
        v.good_id as goodId,
        g.`name` as goodName
        FROM
        tour_good_verification v
        LEFT JOIN  tour_good_site s   on v.site_id=s.id
        LEFT JOIN  tour_good_spe_price p ON v.spe_id=p.id
        LEFT JOIN  tour_good g ON v.good_id=g.id
        WHERE v.id=#{verificationId}
    </select>

</mapper>