<?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>

    <select id="findById" resultType="com.xxfc.platform.tour.vo.TourGoodOrderVo">
       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
	v.id = #{id}
    </select>
</mapper>