<?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.diagbot.mapper.ServiceTokenMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.diagbot.entity.ServiceToken">
        <id column="id" property="id"/>
        <result column="is_deleted" property="isDeleted"/>
        <result column="gmt_create" property="gmtCreate"/>
        <result column="gmt_modified" property="gmtModified"/>
        <result column="creator" property="creator"/>
        <result column="modifier" property="modifier"/>
        <result column="product_service_id" property="productServiceId"/>
        <result column="app_key_id" property="appKeyId"/>
        <result column="app_key_secret" property="appKeySecret"/>
        <result column="type" property="type"/>
        <result column="status" property="status"/>
        <result column="starting_date" property="startingDate"/>
        <result column="expiring_date" property="expiringDate"/>
        <result column="remark" property="remark"/>
    </resultMap>



    <select id="getByAppkeyAndSecret" parameterType="java.util.Map" resultType="com.diagbot.entity.ServiceTokenWithUser">
        SELECT a.*, c.user_id user_id FROM `diag_service_token` a, diag_product_service b, diag_service_info c
        where a.is_deleted = 'N' and b.is_deleted = 'N' and c.is_deleted = 'N'
        and a.app_key_id = #{appkey} and a.app_key_secret = #{secret} and a.product_service_id = b.id and b.service_id = c.id
    </select>

    <select id="getTryTokenCount" parameterType="java.util.Map" resultType="Integer">
        SELECT COUNT(a.id) FROM `diag_service_token` a, diag_product_service b, diag_service_info c
        where a.is_deleted = 'N' and b.is_deleted = 'N' and c.is_deleted = 'N'
		and a.product_service_id = b.id and b.service_id = c.id and a.type = 3
        and c.user_id = #{userId} and b.product_id = #{productId}
    </select>
    
 	<select id="getTryTokenByUserIdAndProductId" parameterType="java.util.Map" resultMap="BaseResultMap">
        SELECT  
        	a.*
        FROM `diag_service_token` a, diag_product_service b, diag_service_info c
        where a.is_deleted = 'N' and b.is_deleted = 'N' and c.is_deleted = 'N'
		and a.product_service_id = b.id and b.service_id = c.id and a.type = 3
        and c.user_id = #{userId} and b.product_id = #{productId}
    </select>

	

    <select id="getByAppkeyAndSecretAndProduct" parameterType="java.util.Map" resultType="com.diagbot.entity.wrapper.ServiceTokenWrapper">
        SELECT
            st.*, ps.product_id, lp.service_status, si.user_id
        FROM
            diag_service_token st,
            diag_product_service ps,
            diag_service_info si,
            diag_lantone_product lp
        WHERE
            st.product_service_id = ps.id
        AND ps.service_id = si.id
        AND ps.product_id = lp.id
        AND st.is_deleted = 'N'
        AND ps.is_deleted = 'N'
        AND si.is_deleted = 'N'
        AND lp.is_deleted = 'N'
        AND ps.product_id = #{productId}
        AND st.app_key_id = #{appkey} and st.app_key_secret = #{secret}
    </select>
</mapper>
