<?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.goafanti.common.dao.AchievementMapper">
  <resultMap id="BaseResultMap" type="com.goafanti.common.model.Achievement">
    <id column="id" jdbcType="VARCHAR" property="id" />
    <result column="serial_number" jdbcType="INTEGER" property="serialNumber" />
    <result column="data_category" jdbcType="INTEGER" property="dataCategory" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="keyword" jdbcType="VARCHAR" property="keyword" />
    <result column="category" jdbcType="INTEGER" property="category" />
    <result column="summary" jdbcType="VARCHAR" property="summary" />
    <result column="introduction" jdbcType="LONGVARCHAR" property="introduction" />
    <result column="technical_picture_url" jdbcType="VARCHAR" property="technicalPictureUrl" />
    <result column="field_a" jdbcType="INTEGER" property="fieldA" />
    <result column="field_b" jdbcType="INTEGER" property="fieldB" />
    <result column="field_c" jdbcType="INTEGER" property="fieldC" />
    <result column="maturity" jdbcType="INTEGER" property="maturity" />
    <result column="maturity_picture_url" jdbcType="LONGVARCHAR" property="maturityPictureUrl" />
    <result column="maturity_text_file_url" jdbcType="VARCHAR" property="maturityTextFileUrl" />
    <result column="maturity_video_url" jdbcType="VARCHAR" property="maturityVideoUrl" />
    <result column="innovation" jdbcType="INTEGER" property="innovation" />
    <result column="owner_id" jdbcType="VARCHAR" property="ownerId" />
    <result column="owner_name" jdbcType="VARCHAR" property="ownerName" />
    <result column="owner_type" jdbcType="INTEGER" property="ownerType" />
    <result column="owner_id_number" jdbcType="VARCHAR" property="ownerIdNumber" />
    <result column="owner_mobile" jdbcType="VARCHAR" property="ownerMobile" />
    <result column="owner_email" jdbcType="VARCHAR" property="ownerEmail" />
    <result column="owner_postal_address" jdbcType="VARCHAR" property="ownerPostalAddress" />
    <result column="cooperation_mode" jdbcType="INTEGER" property="cooperationMode" />
    <result column="transfer_mode" jdbcType="INTEGER" property="transferMode" />
    <result column="bargaining_mode" jdbcType="INTEGER" property="bargainingMode" />
    <result column="transfer_price" jdbcType="DECIMAL" property="transferPrice" />
    <result column="technical_scene" jdbcType="VARCHAR" property="technicalScene" />
    <result column="breakthrough" jdbcType="VARCHAR" property="breakthrough" />
    <result column="patent_case" jdbcType="VARCHAR" property="patentCase" />
    <result column="awards" jdbcType="VARCHAR" property="awards" />
    <result column="team_des" jdbcType="VARCHAR" property="teamDes" />
    <result column="parameter" jdbcType="VARCHAR" property="parameter" />
    <result column="tech_plan_url" jdbcType="VARCHAR" property="techPlanUrl" />
    <result column="business_plan_url" jdbcType="VARCHAR" property="businessPlanUrl" />
    <result column="org_id" jdbcType="VARCHAR" property="orgId" />
    <result column="org_name" jdbcType="VARCHAR" property="orgName" />
    <result column="org_address" jdbcType="VARCHAR" property="orgAddress" />
    <result column="org_email" jdbcType="VARCHAR" property="orgEmail" />
    <result column="org_contacts" jdbcType="VARCHAR" property="orgContacts" />
    <result column="org_contacts_mobile" jdbcType="VARCHAR" property="orgContactsMobile" />
    <result column="manager_id" jdbcType="VARCHAR" property="managerId" />
    <result column="salesman_id" jdbcType="VARCHAR" property="salesmanId" />
    <result column="status" jdbcType="INTEGER" property="status" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="release_status" jdbcType="INTEGER" property="releaseStatus" />
    <result column="release_date" jdbcType="TIMESTAMP" property="releaseDate" />
    <result column="deleted_sign" jdbcType="INTEGER" property="deletedSign" />
    <result column="audit_status" jdbcType="INTEGER" property="auditStatus" />
    <result column="tech_broker_id" jdbcType="VARCHAR" property="techBrokerId" />
    <result column="country_name_zh" jdbcType="VARCHAR" property="countryNameZh" />
    <result column="international_flag" jdbcType="INTEGER" property="internationalFlag" />
    <result column="jmrh_flag" jdbcType="INTEGER" property="jmrhFlag" />
    <result column="boutique" jdbcType="INTEGER" property="boutique" />
    <result column="contacts" jdbcType="VARCHAR" property="contacts" />
    <result column="contact_mobile" jdbcType="VARCHAR" property="contactMobile" />
  </resultMap>
  <sql id="Base_Column_List">
    id, serial_number, data_category, name, keyword, category, summary, introduction, 
    technical_picture_url, field_a, field_b, field_c, maturity, maturity_picture_url, 
    maturity_text_file_url, maturity_video_url, innovation, owner_id, owner_name, owner_type, owner_id_number, 
    owner_mobile, owner_email, owner_postal_address, cooperation_mode, transfer_mode, 
    bargaining_mode, transfer_price, technical_scene, breakthrough, patent_case, awards, 
    team_des, parameter, tech_plan_url, business_plan_url, org_id, org_name, org_address, 
    org_email, org_contacts, org_contacts_mobile, contacts, manager_id, salesman_id, 
    status, create_time, release_status, release_date, deleted_sign, audit_status, tech_broker_id,
    country_name_zh,international_flag,jmrh_flag,boutique
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.String" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from achievement
    where id = #{id,jdbcType=VARCHAR}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.String">
    delete from achievement
    where id = #{id,jdbcType=VARCHAR}
  </delete>
  <insert id="insert" parameterType="com.goafanti.common.model.Achievement">
    insert into achievement (id, serial_number, data_category, 
      name, keyword, category, 
      summary, introduction, technical_picture_url, 
      field_a, field_b, field_c, 
      maturity, innovation, owner_id, owner_postal_address, cooperation_mode, 
      transfer_mode, bargaining_mode, transfer_price,  patent_case, 
      awards, team_des, parameter,  status, 
      create_time, release_status, release_date, 
      deleted_sign, audit_status,international_flag,boutique,is_hot,info_sources)
    values (#{id,jdbcType=VARCHAR}, #{serialNumber,jdbcType=INTEGER}, #{dataCategory,jdbcType=INTEGER}, 
      #{name,jdbcType=VARCHAR}, #{keyword,jdbcType=VARCHAR}, #{category,jdbcType=INTEGER}, 
      #{summary,jdbcType=VARCHAR}, #{introduction,jdbcType=LONGVARCHAR}, #{technicalPictureUrl,jdbcType=VARCHAR}, 
      #{fieldA,jdbcType=INTEGER}, #{fieldB,jdbcType=INTEGER}, #{fieldC,jdbcType=INTEGER}, 
      #{maturity,jdbcType=INTEGER}, #{innovation,jdbcType=INTEGER}, #{ownerId,jdbcType=VARCHAR}
      , #{ownerPostalAddress,jdbcType=VARCHAR}, #{cooperationMode,jdbcType=INTEGER}, 
      #{transferMode,jdbcType=INTEGER}, #{bargainingMode,jdbcType=INTEGER}, #{transferPrice,jdbcType=DECIMAL}, 
     #{patentCase,jdbcType=VARCHAR}, 
      #{awards,jdbcType=VARCHAR}, #{teamDes,jdbcType=VARCHAR}, #{parameter,jdbcType=VARCHAR}, 
    #{status,jdbcType=INTEGER}, 
      #{createTime,jdbcType=TIMESTAMP}, #{releaseStatus,jdbcType=INTEGER}, #{releaseDate,jdbcType=TIMESTAMP}, 
      #{deletedSign,jdbcType=INTEGER}, #{auditStatus,jdbcType=INTEGER},
       #{internationalFlag,jdbcType=INTEGER},#{boutique,jdbcType=INTEGER},#{isHot,jdbcType=INTEGER},#{infoSources,jdbcType=INTEGER})
  </insert>
  <insert id="insertSelective" parameterType="com.goafanti.common.model.Achievement">
    insert into achievement
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id != null">
        id,
      </if>
      <if test="serialNumber != null">
        serial_number,
      </if>
      <if test="dataCategory != null">
        data_category,
      </if>
      <if test="name != null">
        name,
      </if>
      <if test="keyword != null">
        keyword,
      </if>
      <if test="category != null">
        category,
      </if>
      <if test="summary != null">
        summary,
      </if>
      <if test="introduction != null">
        introduction,
      </if>
      <if test="technicalPictureUrl != null">
        technical_picture_url,
      </if>
      <if test="fieldA != null">
        field_a,
      </if>
      <if test="fieldB != null">
        field_b,
      </if>
      <if test="fieldC != null">
        field_c,
      </if>
      <if test="maturity != null">
        maturity,
      </if>
      <if test="maturityPictureUrl != null">
        maturity_picture_url,
      </if>
      <if test="maturityTextFileUrl != null">
        maturity_text_file_url,
      </if>
      <if test="maturityVideoUrl != null">
        maturity_video_url,
      </if>
      <if test="innovation != null">
        innovation,
      </if>
      <if test="ownerId != null">
        owner_id,
      </if>
      <if test="ownerName != null">
        owner_name,
      </if>
      <if test="ownerType != null">
        owner_type,
      </if>
      <if test="ownerIdNumber != null">
        owner_id_number,
      </if>
      <if test="ownerMobile != null">
        owner_mobile,
      </if>
      <if test="ownerEmail != null">
        owner_email,
      </if>
      <if test="ownerPostalAddress != null">
        owner_postal_address,
      </if>
      <if test="cooperationMode != null">
        cooperation_mode,
      </if>
      <if test="transferMode != null">
        transfer_mode,
      </if>
      <if test="bargainingMode != null">
        bargaining_mode,
      </if>
      <if test="transferPrice != null">
        transfer_price,
      </if>
      <if test="technicalScene != null">
        technical_scene,
      </if>
      <if test="breakthrough != null">
        breakthrough,
      </if>
      <if test="patentCase != null">
        patent_case,
      </if>
      <if test="awards != null">
        awards,
      </if>
      <if test="teamDes != null">
        team_des,
      </if>
      <if test="parameter != null">
        parameter,
      </if>
      <if test="techPlanUrl != null">
        tech_plan_url,
      </if>
      <if test="businessPlanUrl != null">
        business_plan_url,
      </if>
      <if test="orgId != null">
        org_id,
      </if>
      <if test="orgName != null">
        org_name,
      </if>
      <if test="orgAddress != null">
        org_address,
      </if>
      <if test="orgEmail != null">
        org_email,
      </if>
      <if test="orgContacts != null">
        org_contacts,
      </if>
      <if test="orgContactsMobile != null">
        org_contacts_mobile,
      </if>
      <if test="contacts != null">
        contacts,
      </if>
      <if test="managerId != null">
        manager_id,
      </if>
      <if test="salesmanId != null">
        salesman_id,
      </if>
      <if test="status != null">
        status,
      </if>
      <if test="createTime != null">
        create_time,
      </if>
      <if test="releaseStatus != null">
        release_status,
      </if>
      <if test="releaseDate != null">
        release_date,
      </if>
      <if test="deletedSign != null">
        deleted_sign,
      </if>
       <if test="auditStatus != null">
        audit_status,
      </if>
      <if test="techBrokerId != null">
        tech_broker_id,
      </if>
      <if test="countryNameZh != null">
      	country_name_zh,
      </if>
      <if test="internationalFlag != null">
      	international_flag,
      </if>
       <if test="jmrhFlag != null">
      	jmrh_flag,
      </if>
      <if test="boutique !=null">
      	boutique,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="id != null">
        #{id,jdbcType=VARCHAR},
      </if>
      <if test="serialNumber != null">
        #{serialNumber,jdbcType=INTEGER},
      </if>
      <if test="dataCategory != null">
        #{dataCategory,jdbcType=INTEGER},
      </if>
      <if test="name != null">
        #{name,jdbcType=VARCHAR},
      </if>
      <if test="keyword != null">
        #{keyword,jdbcType=VARCHAR},
      </if>
      <if test="category != null">
        #{category,jdbcType=INTEGER},
      </if>
      <if test="summary != null">
        #{summary,jdbcType=VARCHAR},
      </if>
      <if test="introduction != null">
        #{introduction,jdbcType=LONGVARCHAR},
      </if>
      <if test="technicalPictureUrl != null">
        #{technicalPictureUrl,jdbcType=VARCHAR},
      </if>
      <if test="fieldA != null">
        #{fieldA,jdbcType=INTEGER},
      </if>
      <if test="fieldB != null">
        #{fieldB,jdbcType=INTEGER},
      </if>
      <if test="fieldC != null">
        #{fieldC,jdbcType=INTEGER},
      </if>
      <if test="maturity != null">
        #{maturity,jdbcType=INTEGER},
      </if>
      <if test="maturityPictureUrl != null">
        #{maturityPictureUrl,jdbcType=LONGVARCHAR},
      </if>
      <if test="maturityTextFileUrl != null">
        #{maturityTextFileUrl,jdbcType=VARCHAR},
      </if>
      <if test="maturityVideoUrl != null">
        #{maturityVideoUrl,jdbcType=VARCHAR},
      </if>
      <if test="innovation != null">
        #{innovation,jdbcType=INTEGER},
      </if>
      <if test="ownerId != null">
        #{ownerId,jdbcType=VARCHAR},
      </if>
      <if test="ownerName != null">
        #{ownerName,jdbcType=VARCHAR},
      </if>
      <if test="ownerType != null">
        #{ownerType,jdbcType=INTEGER},
      </if>
      <if test="ownerIdNumber != null">
        #{ownerIdNumber,jdbcType=VARCHAR},
      </if>
      <if test="ownerMobile != null">
        #{ownerMobile,jdbcType=VARCHAR},
      </if>
      <if test="ownerEmail != null">
        #{ownerEmail,jdbcType=VARCHAR},
      </if>
      <if test="ownerPostalAddress != null">
        #{ownerPostalAddress,jdbcType=VARCHAR},
      </if>
      <if test="cooperationMode != null">
        #{cooperationMode,jdbcType=INTEGER},
      </if>
      <if test="transferMode != null">
        #{transferMode,jdbcType=INTEGER},
      </if>
      <if test="bargainingMode != null">
        #{bargainingMode,jdbcType=INTEGER},
      </if>
      <if test="transferPrice != null">
        #{transferPrice,jdbcType=DECIMAL},
      </if>
      <if test="technicalScene != null">
        #{technicalScene,jdbcType=VARCHAR},
      </if>
      <if test="breakthrough != null">
        #{breakthrough,jdbcType=VARCHAR},
      </if>
      <if test="patentCase != null">
        #{patentCase,jdbcType=VARCHAR},
      </if>
      <if test="awards != null">
        #{awards,jdbcType=VARCHAR},
      </if>
      <if test="teamDes != null">
        #{teamDes,jdbcType=VARCHAR},
      </if>
      <if test="parameter != null">
        #{parameter,jdbcType=VARCHAR},
      </if>
      <if test="techPlanUrl != null">
        #{techPlanUrl,jdbcType=VARCHAR},
      </if>
      <if test="businessPlanUrl != null">
        #{businessPlanUrl,jdbcType=VARCHAR},
      </if>
      <if test="orgId != null">
        #{orgId,jdbcType=VARCHAR},
      </if>
      <if test="orgName != null">
        #{orgName,jdbcType=VARCHAR},
      </if>
      <if test="orgAddress != null">
        #{orgAddress,jdbcType=VARCHAR},
      </if>
      <if test="orgEmail != null">
        #{orgEmail,jdbcType=VARCHAR},
      </if>
      <if test="orgContacts != null">
        #{orgContacts,jdbcType=VARCHAR},
      </if>
      <if test="orgContactsMobile != null">
        #{orgContactsMobile,jdbcType=VARCHAR},
      </if>
      <if test="contacts != null">
        #{contacts,jdbcType=INTEGER},
      </if>
      <if test="managerId != null">
        #{managerId,jdbcType=VARCHAR},
      </if>
      <if test="salesmanId != null">
        #{salesmanId,jdbcType=VARCHAR},
      </if>
      <if test="status != null">
        #{status,jdbcType=INTEGER},
      </if>
      <if test="createTime != null">
        #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="releaseStatus != null">
        #{releaseStatus,jdbcType=INTEGER},
      </if>
      <if test="releaseDate != null">
        #{releaseDate,jdbcType=TIMESTAMP},
      </if>
      <if test="deletedSign != null">
        #{deletedSign,jdbcType=INTEGER},
      </if>
       <if test="auditStatus != null">
        #{auditStatus,jdbcType=INTEGER},
      </if>
      <if test="techBrokerId != null">
        #{techBrokerId,jdbcType=VARCHAR},
      </if>
      <if test="countryNameZh != null">
      	#{countryNameZh,jdbcType=VARCHAR},
      </if>
      <if test="internationalFlag != null">
      	#{internationalFlag,jdbcType=INTEGER},
      </if>
      <if test="jmrhFlag != null">
      	#{jmrhFlag,jdbcType=INTEGER},
      </if>
      <if test="boutique != null">
      	#{boutique,jdbcType=INTEGER}
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.goafanti.common.model.Achievement">
    update achievement
    <set>
      <if test="serialNumber != null">
        serial_number = #{serialNumber,jdbcType=INTEGER},
      </if>
      <if test="dataCategory != null">
        data_category = #{dataCategory,jdbcType=INTEGER},
      </if>
      <if test="name != null">
        name = #{name,jdbcType=VARCHAR},
      </if>
      <if test="keyword != null">
        keyword = #{keyword,jdbcType=VARCHAR},
      </if>
      <if test="category != null">
        category = #{category,jdbcType=INTEGER},
      </if>
      <if test="summary != null">
        summary = #{summary,jdbcType=VARCHAR},
      </if>
      <if test="introduction != null">
        introduction = #{introduction,jdbcType=LONGVARCHAR},
      </if>
      <if test="technicalPictureUrl != null">
        technical_picture_url = #{technicalPictureUrl,jdbcType=VARCHAR},
      </if>
      <if test="fieldA != null">
        field_a = #{fieldA,jdbcType=INTEGER},
      </if>
      <if test="fieldB != null">
        field_b = #{fieldB,jdbcType=INTEGER},
      </if>
      <if test="fieldC != null">
        field_c = #{fieldC,jdbcType=INTEGER},
      </if>
      <if test="maturity != null">
        maturity = #{maturity,jdbcType=INTEGER},
      </if>
      <if test="innovation != null">
        innovation = #{innovation,jdbcType=INTEGER},
      </if>
      <if test="ownerId != null">
        owner_id = #{ownerId,jdbcType=VARCHAR},
      </if>
      <if test="ownerPostalAddress != null">
        owner_postal_address = #{ownerPostalAddress,jdbcType=VARCHAR},
      </if>
      <if test="cooperationMode != null">
        cooperation_mode = #{cooperationMode,jdbcType=INTEGER},
      </if>
      <if test="transferMode != null">
        transfer_mode = #{transferMode,jdbcType=INTEGER},
      </if>
      <if test="bargainingMode != null">
        bargaining_mode = #{bargainingMode,jdbcType=INTEGER},
      </if>
      <if test="transferPrice != null">
        transfer_price = #{transferPrice,jdbcType=DECIMAL},
      </if>
      <if test="patentCase != null">
        patent_case = #{patentCase,jdbcType=VARCHAR},
      </if>
      <if test="awards != null">
        awards = #{awards,jdbcType=VARCHAR},
      </if>
      <if test="teamDes != null">
        team_des = #{teamDes,jdbcType=VARCHAR},
      </if>
      <if test="parameter != null">
        parameter = #{parameter,jdbcType=VARCHAR},
      </if>
      <if test="status != null">
        status = #{status,jdbcType=INTEGER},
      </if>
      <if test="createTime != null">
        create_time = #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="releaseStatus != null">
        release_status = #{releaseStatus,jdbcType=INTEGER},
      </if>
      <if test="releaseDate != null">
        release_date = #{releaseDate,jdbcType=TIMESTAMP},
      </if>
      <if test="deletedSign != null">
        deleted_sign = #{deletedSign,jdbcType=INTEGER},
      </if>
      <if test="auditStatus != null">
        audit_status = #{auditStatus,jdbcType=INTEGER},
      </if>
      <if test="countryNameZh != null">
      	country_name_zh = #{countryNameZh,jdbcType=VARCHAR},
      </if>
       <if test="internationalFlag != null">
      	international_flag = #{internationalFlag,jdbcType=INTEGER},
      </if>
       <if test="boutique != null">
        boutique = #{boutique,jdbcType=INTEGER},
      </if>
       <if test="isHot != null">
        is_hot = #{isHot,jdbcType=INTEGER},
      </if>
       <if test="auditInfo != null">
        audit_info = #{auditInfo,jdbcType=VARCHAR},
      </if>
    </set>
    where id = #{id,jdbcType=VARCHAR}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.goafanti.common.model.Achievement">
    update achievement
    set serial_number = #{serialNumber,jdbcType=INTEGER},
      data_category = #{dataCategory,jdbcType=INTEGER},
      name = #{name,jdbcType=VARCHAR},
      keyword = #{keyword,jdbcType=VARCHAR},
      category = #{category,jdbcType=INTEGER},
      summary = #{summary,jdbcType=VARCHAR},
      introduction = #{introduction,jdbcType=LONGVARCHAR},
      technical_picture_url = #{technicalPictureUrl,jdbcType=VARCHAR},
      field_a = #{fieldA,jdbcType=INTEGER},
      field_b = #{fieldB,jdbcType=INTEGER},
      field_c = #{fieldC,jdbcType=INTEGER},
      maturity = #{maturity,jdbcType=INTEGER},
      innovation = #{innovation,jdbcType=INTEGER},
      owner_id = #{ownerId,jdbcType=VARCHAR},
      owner_postal_address = #{ownerPostalAddress,jdbcType=VARCHAR},
      transfer_mode = #{transferMode,jdbcType=INTEGER},
      bargaining_mode = #{bargainingMode,jdbcType=INTEGER},
      transfer_price = #{transferPrice,jdbcType=DECIMAL},
      patent_case = #{patentCase,jdbcType=VARCHAR},
      team_des = #{teamDes,jdbcType=VARCHAR},
      parameter = #{parameter,jdbcType=VARCHAR},
      release_status = #{releaseStatus,jdbcType=INTEGER},
      release_date = #{releaseDate,jdbcType=TIMESTAMP},
      deleted_sign = #{deletedSign,jdbcType=INTEGER},
      audit_status = #{auditStatus,jdbcType=INTEGER},
      country_name_zh = #{countryNameZh,jdbcType=VARCHAR},
      international_flag = #{internationalFlag,jdbcType=INTEGER},
      boutique = #{boutique,jdbcType=INTEGER}
    where id = #{id,jdbcType=VARCHAR}
  </update>
  
  <update id="batchDeleteByPrimaryKey" parameterType="java.util.List">
  	update achievement set deleted_sign = 1
  	where  id in 
	<foreach item="item"  collection="list" open="(" separator="," close=")">
		#{item}
	</foreach>
  </update>
  <select id="findAchievementListWithPid" resultType="com.goafanti.portal.bo.AchievementObject">
  select a.name,a.id,release_date as releaseDate,technical_picture_url as technicalPictureUrl,data_category as dataCategory,is_hot as isHot 
  ,field_a as fieldA,field_b as fieldB,ic.name as fieldAS,icc.name as fieldBS
  from achievement a
  left join industry_category ic on ic.id=a.field_a
  left join industry_category icc on icc.id=a.field_b
  where info_sources=1 and audit_status=2
  <if test="fieldA!=null">
 and a.field_a =#{fieldA,jdbcType=INTEGER}
  </if>
  <if test="fieldB!=null">
  and a.field_b=#{fieldB,jdbcType=INTEGER}
  </if>
  <if test="name !=null">
  and a.name like concat("%",#{name,jdbcType=VARCHAR},"%")
  </if>
  order by a.release_date
  <if test="page_sql !=null">
  	${page_sql}
  </if>
  </select>
  <select id="findAchievementCountWithPid" resultType="java.lang.Integer">
   select
   count(0)
    from achievement a
  where info_sources=1 and audit_status=2
  <if test="fieldA!=null">
 and a.field_a =#{fieldA,jdbcType=INTEGER}
  </if>
  <if test="fieldB!=null">
  and a.field_b=#{fieldB,jdbcType=INTEGER}
  </if>
  <if test="name !=null">
  and a.name like concat("%",#{name,jdbcType=VARCHAR},"%")
  </if>
  </select>
   <select id="findAppAchievementListByPage" parameterType="String" resultType="com.goafanti.achievement.bo.AchievementListBo">
  	
	select 
  		a.id,a.name, a.keyword, a.introduction,a.field_a as fieldA,a.field_b as fieldB,a.data_category as dataCategory,
  		a.status, a.release_date as releaseDate, a.audit_status as auditStatus,a.maturity,
		f.name as field1,f2.name as field2,ifnull(b.countInterest,0) as countInterest,if(isnull(c.achievement_id),0,1) as interested  
  	from achievement a
  	left join (select count(0)  as countInterest,achievement_id from achievement_interest 
  		group by achievement_id)b on a.id = b.achievement_id
	left join (select achievement_id  from achievement_interest where uid=#{uid,jdbcType=VARCHAR})c
	on a.id=c.achievement_id
  	left join field_glossory f on a.field_a=f.id
  	left join field_glossory f2 on a.field_b=f2.id
  	where a.deleted_sign = 0 and a.audit_status = 2 and a.info_sources=1 and a.release_status=1
  	<if test="ownerId != null">
  	and a.owner_id = #{ownerId,jdbcType=VARCHAR}
  	</if>
  	<if test="serialNumber != null">
		and  a.serial_number = #{serialNumber,jdbcType=INTEGER}
	</if>
	<if test="auditStatus != null">
		and  a.audit_status = #{auditStatus,jdbcType=INTEGER}
	</if>
	<if test="name != null">
		and  a.name like CONCAT('%',#{name,jdbcType=VARCHAR},'%')
	</if>
	<if test="keyword != null">
		and  a.keyword like CONCAT('%',#{keyword,jdbcType=VARCHAR},'%')
	</if>
	<if test="dataCategory != null and dataCategory != ''">
  		and a.data_category = #{dataCategory,jdbcType=INTEGER}
  	</if>
  	<if test="category != null">
  		and a.category = #{category,jdbcType=INTEGER}
  	</if>
	<if test="ownerType != null">
		and  a.owner_type = #{ownerType,jdbcType=INTEGER}
	</if>
	<if test="fieldA != null">
		and a.field_a  = #{fieldA,jdbcType=VARCHAR}
	</if>
	<if test="status != null">
		and  a.status = #{status,jdbcType=INTEGER}
	</if>
	<if test="rStart != null">
       and	a.release_date <![CDATA[ >= ]]> #{rStart,jdbcType=TIMESTAMP}
	</if>
	<if test="rEnd != null">
	   and  a.release_date <![CDATA[ < ]]> #{rEnd,jdbcType=TIMESTAMP}
	</if>
	<if test="releaseStatus != null">
	   and  a.release_status = #{releaseStatus,jdbcType=INTEGER}
	</if>
	
	order by a.boutique desc
	<if test="page_sql!=null">
			${page_sql}
	</if>
  </select>
  
  <select id="findAppAchievementCount" parameterType="String" resultType="java.lang.Integer">
  	select 
  		count(1)
  from achievement a
  	where a.deleted_sign = 0 and a.audit_status = 2 and a.info_sources=1 and a.release_status=1
	<if test="ownerId != null">
  	and a.owner_id = #{ownerId,jdbcType=VARCHAR}
  	</if>
  	<if test="serialNumber != null">
		and  a.serial_number = #{serialNumber,jdbcType=INTEGER}
	</if>
	<if test="auditStatus != null">
		and  a.audit_status = #{auditStatus,jdbcType=INTEGER}
	</if>
	<if test="name != null">

		and  a.name = #{name,jdbcType=VARCHAR}

	</if>
	<if test="keyword != null">

		and  a.keyword like "%"#{keyword,jdbcType=VARCHAR}"%"

	</if>
	<if test="dataCategory != null and dataCategory != ''">
  		and a.data_category = #{dataCategory,jdbcType=INTEGER}
  	</if>
  	<if test="category != null">
  		and a.category = #{category,jdbcType=INTEGER}
  	</if>
	<if test="ownerType != null">
		and  a.owner_type = #{ownerType,jdbcType=INTEGER}
	</if>
	<if test="fieldA != null">
		and a.field_a  = #{fieldA,jdbcType=VARCHAR}
	</if>
	<if test="status != null">
		and  a.status = #{status,jdbcType=INTEGER}
	</if>
	<if test="rStart != null">
       and	a.release_date <![CDATA[ >= ]]> #{rStart,jdbcType=TIMESTAMP}
	</if>
	<if test="rEnd != null">
	   and  a.release_date <![CDATA[ < ]]> #{rEnd,jdbcType=TIMESTAMP}
	</if>
	<if test="releaseStatus != null">
	   and  a.release_status = #{releaseStatus,jdbcType=INTEGER}
	</if>
	</select>
  
  <select id="selectUserOwnerDetail" parameterType="java.lang.String" resultType="com.goafanti.achievement.bo.AchievementUserOwnerDetailBo">
		select  
		  	a.id, a.serial_number as serialNumber, a.data_category as dataCategory, 
		  	a.name, a.keyword, a.category, 
		  	a.summary, a.introduction, a.technical_picture_url as technicalPictureUrl, 
		  	a.field_a as fieldA, a.field_b as fieldB, a.field_c as fieldC, f.name as field1, f2.name as field2,
		  	a.maturity, a.maturity_picture_url as maturityPictureUrl, a.maturity_text_file_url as maturityTextFileUrl, 
		  	a.maturity_video_url as maturityVideoUrl, a.innovation, a.owner_id as ownerId, 
		  	a.owner_name as ownerName, a.owner_type as ownerType, a.owner_id_number as ownerIdNumber, 
		    a.owner_mobile as ownerMobile, a.owner_email as ownerEmail, a.owner_postal_address as ownerPostalAddress, 
		    a.cooperation_mode as cooperationMode, a.transfer_mode as transferMode, a.bargaining_mode as bargainingMode, 
		    a.transfer_price as transferPrice, a.technical_scene as technicalScene, a.breakthrough, 
		    a.patent_case as patentCase, a.awards, a.team_des as teamDes, 
		    a.parameter, a.tech_plan_url as techPlanUrl, a.business_plan_url as businessPlanUrl, 
		    a.contacts, a.manager_id as managerId, a.salesman_id as salesmanId, 
		    a.create_time as createTime, a.release_status as releaseStatus, 
		    a.release_date as releaseDate, a.audit_status as auditStatus, a.tech_broker_id as techBrokerId,
		    ui.username as iOwnerName, ui.province, ui.city, ui.area, u.email as iOwnerEmail,
			ui.id_number as iOwnerIdNumber, u.mobile as iOwnerMobile,j.easemob_name as easemobName,a.remark,
			ifnull(t2.x,0) as orderCount,a.boutique,a.contacts,a.contact_mobile as contactMobile, 
			a.international_flag as internationalFlag
	from achievement a
    left join user_identity ui on a.owner_id = ui.uid
    left join field_glossory f on a.field_a=f.id
  	left join field_glossory f2 on a.field_b=f2.id
    left join (select count(0) as x,tl.commodity_id from t_order tr
				left join t_order_detail tl on tl.order_no=tr.order_no
				where tr.order_status != 4 and tr.order_status != 0
				group by  tl.commodity_id) t2 on a.id=t2.commodity_id 
    left join jpush_easemob_account j on a.owner_id=j.uid
    left join user u on u.id = a.owner_id
  where a.id =#{id,jdbcType=VARCHAR}
  </select>
  
   <select id="selectOrgOwnerDetail" parameterType="java.lang.String" resultType="com.goafanti.achievement.bo.AchievementOrgOwnerDetailBo">
  	select  
		  	a.id, a.serial_number as serialNumber, a.data_category as dataCategory, 
		  	a.name, a.keyword, a.category, 
		  	a.summary, a.introduction, a.technical_picture_url as technicalPictureUrl, 
		  	a.field_a as fieldA, a.field_b as fieldB, a.field_c as fieldC, 
		  	a.maturity, a.maturity_picture_url as maturityPictureUrl, a.maturity_text_file_url as maturityTextFileUrl, 
		  	a.maturity_video_url as maturityVideoUrl, a.innovation, a.owner_id as ownerId, 
		  	a.owner_name as ownerName, a.owner_type as ownerType, a.owner_id_number as ownerIdNumber, 
		    a.owner_mobile as ownerMobile, a.owner_email as ownerEmail, a.owner_postal_address as ownerPostalAddress, 
		    a.cooperation_mode as cooperationMode, a.transfer_mode as transferMode, a.bargaining_mode as bargainingMode, 
		    a.transfer_price as transferPrice, a.technical_scene as technicalScene, a.breakthrough, 
		    a.patent_case as patentCase, a.awards, a.team_des as teamDes, 
		    a.parameter, a.tech_plan_url as techPlanUrl, a.business_plan_url as businessPlanUrl, 
		    a.contacts, a.manager_id as managerId, a.salesman_id as salesmanId,  a.international_flag as internationalFlag,
		    a.create_time as createTime, a.release_status as releaseStatus,
		    a.release_date as releaseDate, a.audit_status as auditStatus, a.tech_broker_id as techBrokerId,
		    oi.unit_name as iOwnerName, oi.postal_address as iOwnerPostalAddress, u.email as iOwnerEmail,
		    oi.org_code as iOwnerIdNumber, u.mobile as iOwnerMobile,a.boutique,ifnull(mm.effective,0) hot,mm.cover_img as coverImg,a.remark,a.contacts,a.contact_mobile as contactMobile
    from achievement a
    left join organization_identity oi on a.owner_id = oi.uid
    left join user u on u.id = a.owner_id
    left join marketing_management mm on a.id = mm.product_id
    where a.id = #{id,jdbcType=VARCHAR}
  </select>
  
   <insert id="insertBatch" parameterType="com.goafanti.common.model.Achievement">
    insert into achievement
    	(id, deleted_sign, audit_status, 
    	 create_time, release_status, data_category,
    	 name, keyword, category,
    	 owner_name, owner_type, owner_id_number,
    	 owner_mobile, owner_email, owner_postal_address,
    	 cooperation_mode, introduction, tech_broker_id,international_flag
    	 )
    values 
    <foreach item="item" index="index" collection="list" separator=",">
      (
      	#{item.id,jdbcType=VARCHAR}, #{item.deletedSign,jdbcType=INTEGER}, #{item.auditStatus,jdbcType=INTEGER}, 
      	#{item.createTime,jdbcType=TIMESTAMP}, #{item.releaseStatus,jdbcType=INTEGER}, #{item.dataCategory,jdbcType=INTEGER},
      	#{item.name,jdbcType=VARCHAR}, #{item.keyword,jdbcType=VARCHAR}, #{item.category,jdbcType=INTEGER},
      	#{item.ownerName,jdbcType=VARCHAR}, #{item.ownerType,jdbcType=VARCHAR}, #{item.ownerIdNumber,jdbcType=VARCHAR},
      	#{item.ownerMobile,jdbcType=VARCHAR}, #{item.ownerEmail,jdbcType=VARCHAR}, #{item.ownerPostalAddress,jdbcType=VARCHAR},
      	#{item.cooperationMode,jdbcType=INTEGER}, #{item.introduction,jdbcType=LONGVARCHAR},#{item.techBrokerId,jdbcType=VARCHAR},
      	#{item.internationalFlag,jdbcType=INTEGER}
      )
    </foreach>
  </insert>

  <select id="selectAchievementDetail"  resultType="com.goafanti.portal.bo.AchievementDetailBo">
  	select 
  		a.id, 
  		a.owner_name as ownerName,
  		a.serial_number as serialNumber, 
  		a.keyword, 
  		a.name, 
  		a.data_category as dataCategory, 
  		a.category,
  		ui.username, 
  		oi.unit_name as unitName, 
  		a.release_date as releaseDate,
  		a.owner_type as ownerType, 
  		a.field_a as fieldA, 
  		a.field_b as fieldB,
  		a.transfer_price as transferPrice, 
  		a.maturity, 
  		a.transfer_mode as transferMode,
  		a.bargaining_mode as bargainingMode, 
  		a.summary, 
  		a.introduction,
  		a.technical_picture_url as technicalPictureUrl, 
  		a.maturity_picture_url as maturityPictureUrl, 
  		a.maturity_text_file_url as maturityTextFileUrl,
  		a.maturity_video_url as maturityVideoUrl, 
  		a.innovation, 
  		a.cooperation_mode as cooperationMode,
  		a.bargaining_mode as bargainingMode, 
  		a.technical_scene as technicalScene, 
  		a.breakthrough,
  		a.patent_case as patentCase, 
  		a.awards, 
  		a.team_des as teamDes, 
  		a.parameter, 
  		a.tech_plan_url as techPlanUrl, 
  		a.business_plan_url as businessPlanUrl,
  		ai.id as achievementInterestId
  	from achievement a
  	left join user_identity ui on ui.uid = a.owner_id
  	left join organization_identity oi on oi.uid = a.owner_id
  	left join achievement_interest ai on ai.achievement_id = a.id and ai.uid = #{0}
  	where a.id = #{1}
  </select>
  
  <select id="findPartnerAchievementListByPage" parameterType="java.lang.String" resultType="com.goafanti.achievement.bo.AchievementPartnerListBo">
  	SELECT
		id,
		serial_number AS serialNumber,
		data_category AS dataCategory,
		NAME,
		keyword,
		category,
		owner_type as ownerType,
		release_date AS releaseDate,
		field_a as fieldA,
		maturity,
		introduction,
		technical_picture_url as technicalPictureUrl,
		bargaining_mode as bargainingMode,
		transfer_price as transferPrice
	FROM
		achievement
	WHERE
		audit_status = 3
	AND release_status = 1
	AND deleted_sign = 0
	AND owner_id = #{ownerId,jdbcType=VARCHAR}
  </select>
  
  <select id="findPartnerAchievementCount" parameterType="java.lang.String" resultType="java.lang.Integer">
  	SELECT
		count(1)
	FROM
		achievement
	WHERE
		audit_status = 3
	AND release_status = 1
	AND deleted_sign = 0
	AND owner_id = #{ownerId,jdbcType=VARCHAR}
  </select>
  
  <select id="findUserPortalAchievementDetail" parameterType="java.lang.String" resultType="com.goafanti.portal.bo.AchievementPortalDetailBo">
  	SELECT
		a.id,
		a.name,
		a.keyword,
		a.category,
		a.introduction,
		a.maturity,
		a.transfer_price AS transferPrice,
		a.patent_case AS patentCase,
		a.field_a as fieldA,
		fg.name AS fieldAS,
		fgg.name AS fieldBS,
		a.release_date AS releaseDate,
		dg.name as province,
		dgg.name as city,
		a.technical_picture_url AS technicalPictureUrl,
		a.transfer_mode as transferMode,
		interest.id as interestId,
		u.lvl as level,
		a.owner_name as ownerName,
		a.owner_mobile as ownerMobile,
		a.bargaining_mode as bargainingMode,
		ui.city as location
	FROM
		achievement a
	LEFT JOIN <![CDATA[ user ]]> u on u.id = a.owner_id
	LEFT JOIN user_identity ui on ui.uid = a.owner_id
	LEFT JOIN district_glossory dg on dg.id = ui.province
	LEFT JOIN district_glossory dgg on dgg.id = ui.city
	LEFT JOIN field_glossory fg on fg.id = a.field_a
	LEFT JOIN field_glossory fgg on fgg.id = a.field_b
	LEFT JOIN achievement_interest interest on interest.achievement_id = a.id and interest.uid = #{uid,jdbcType=VARCHAR}
	WHERE
		a.id = #{id,jdbcType=VARCHAR}
  </select>
  
   <select id="findOrgPortalAchievementDetail" parameterType="java.lang.String" resultType="com.goafanti.portal.bo.AchievementPortalDetailBo">
  	SELECT
		a.id,
		a.name,
		a.keyword,
		a.category,
		a.introduction,
		a.maturity,
		a.transfer_price AS transferPrice,
		a.patent_case AS patentCase,
		a.field_a as fieldA,
		fg.name AS fieldAS,
		fgg.name AS fieldBS,
		a.release_date AS releaseDate,
		dg.name as province,
		dgg.name as city,
		a.technical_picture_url AS technicalPictureUrl,
		a.transfer_mode as transferMode,
		interest.id as interestId,
		u.lvl as level,
		a.owner_name as ownerName,
		a.bargaining_mode as bargainingMode
	FROM
		achievement a
	LEFT JOIN <![CDATA[ user ]]> u on u.id = a.owner_id
	LEFT JOIN organization_identity oi on oi.uid = a.owner_id
	LEFT JOIN district_glossory dg on dg.id = oi.licence_province
	LEFT JOIN district_glossory dgg on dgg.id = oi.licence_city
	LEFT JOIN field_glossory fg on fg.id = a.field_a
	LEFT JOIN field_glossory fgg on fgg.id = a.field_b
	LEFT JOIN achievement_interest interest on interest.achievement_id = a.id and interest.uid = #{uid,jdbcType=VARCHAR}
	WHERE
		a.id = #{id,jdbcType=VARCHAR}
  </select>
 
  <select id="listAchievement" resultType="com.goafanti.achievement.bo.AchievementListBo">
  		select 
  		a.id, a.name, a.keyword,a.introduction,a.category,
  		a.serial_number as serialNumber, 
  		a.data_category as dataCategory, 
  	    a.owner_type as ownerType, 
  		a.owner_name as ownerName,
  		a.field_a as fieldA, 
  		a.transfer_price as transferPrice,
  		a.transfer_mode as transfermode,
  		a.maturity,
  		a.technical_picture_url as technicalpictureurl
  		from achievement a 
  		where 
  	    a.deleted_sign = 0 
		and a.category = 0
  	    and a.release_status = 1
  	    and a.audit_status = 3 
  	    order by a.release_date desc limit 0,12
  </select>
 
  <select id="countInterest" resultType="Integer">
  	select count(0)
  	from achievement_interest 
  	where achievement_id=#{achievementId,jdbcType=VARCHAR}
  	</select>
  	
  	<select id="achievementInterest" resultType="com.goafanti.achievement.bo.AchievementInterestBo">
  	select
		a.id,a.name,a.create_time as createTime
	from achievement_interest ai
	LEFT  join achievement a on ai.achievement_id=a.id
	where uid=#{uid,jdbcType=VARCHAR}
  	</select>
  	
  <select id="findMyAchievementListByPage" parameterType="String" resultType="com.goafanti.achievement.bo.AchievementListBo">
  	select 
	  a.id, a.boutique,a.name,a.category, a.audit_status as auditStatus,ifnull(t.x,0) as buyIntention,
	  a.release_date as releaseDate,a.create_time as createTime,ifnull(i.y,0) as countInterest
	from  achievement a 
	left join (select count(0) as x, tl.commodity_id from t_order tr
				left join t_order_detail tl on tr.order_no=tl.order_no
				where tr.order_status != 4
				group by  tl.commodity_id ) t on a.id=t.commodity_id 
	left join (select count(0) as y,achievement_id from achievement_interest
				group by achievement_id ) i on a.id=i.achievement_id	
	where a.deleted_sign = 0
	<if test="ownerId != null">
  	and a.owner_id = #{ownerId,jdbcType=VARCHAR}
  	</if>
  	order by a.create_time desc
  	<if test="page_sql!=null">
			${page_sql}
	</if>
  </select>
  
  <select id="findMyAchievementCount" parameterType="String" resultType="java.lang.Integer">
  	select 
  		count(1)
  	from  achievement a 
	where a.deleted_sign = 0
	<if test="ownerId != null">
  	and  a.owner_id  = #{ownerId,jdbcType=VARCHAR}
  	</if>
  </select>

  <select id="getAppHomeAchievement" resultType="com.goafanti.app.bo.HomeAchievemend">
  select ap.achievement_id as id ,a.name as name ,a.technical_picture_url as img, 
		ap.if_top,ap.top_number,ap.show_number
		from achievement_publish ap
		left join achievement a on ap.achievement_id=a.id
		where ap.publish_client='1'
		and ap.publish_page='W03'
		order by ap.if_top,ap.top_number,ap.show_number asc
		limit 0,4
  </select>
 <select id="getAppHomeRecommended" resultType="com.goafanti.achievement.bo.AchievementRecommended">
 	select ap.achievement_id as id,a.name,a.introduction,a.technical_picture_url as technicalpictureUrl,
	a.field_a,a.field_b,f1.name as fieldA,f2.name as fieldB,a.boutique
	from achievement_publish ap 
	left join achievement a on ap.achievement_id = a.id
	left join field_glossory f1 on a.field_a=f1.id
	left join field_glossory f2 on a.field_b=f2.id
where
	ap.publish_client = '1'
	and ap.publish_page = 'W01'
order by
	ap.if_top,
	ap.top_number,
	ap.show_number asc limit 0,
	6
</select> 	

<select id="getRecentAchievements" resultType="com.goafanti.portal.bo.AchievementObject">
 select
	d.name,
	d.id ,
	data_category as dataCategory,
	release_date as releaseDate,
	category,
	d.audit_status ,
	fg.name as industryCategory1,
	fgg.name as industryCategory2,
	d.technical_picture_url as technicalPictureUrl
from
	achievement d
left join industry_category fg on
	fg.id = d.field_a
left join industry_category fgg on
	fgg.id = d.field_b
left join branch_information branchInfo on
	instr( #{1},branchInfo.domain_name )>0
left join achievement_publish dp on
	dp.achievement_id = d.id
	and dp.publish_platform = branchInfo.id
where
	audit_status = 2
	and dp.achievement_id = d.id
	and dp.publish_platform = branchInfo.id
	and instr( dp.publish_page, #{2} )>0
	and info_sources = 1
order by
	release_date desc limit #{0}
</select>

<select id="getHotAchievements" resultType="com.goafanti.portal.bo.AchievementObject">
select 
a.name as name ,
a.id as id,
a.technical_picture_url as technicalPictureUrl,
b.ct as interestCount,
a.category,
a.data_category as dataCategory,
	fg.name as fieldAS,
	fgg.name as fieldBS,
	a.introduction
from achievement a
left join branch_information branchInfo 
on INSTR(#{1},branchInfo.domain_name)>0
left join achievement_publish ap
 on ap.achievement_id=a.id
 left join industry_category fg on
	fg.id = a.field_a
left join industry_category fgg on
	fgg.id = a.field_b
left join 
(select  
count(0) as ct,
achievement_id as interest_aid
from achievement_interest
group by
achievement_id
) b 
on b.interest_aid=a.id
where a.audit_status=2
and ap.achievement_id=a.id
and branchInfo.id = ap.publish_platform
and instr(ap.publish_page,#{2})>0
and info_sources=1
order by a.release_date desc limit #{0}

</select>

<select id="getPatentList" resultType="com.goafanti.portal.bo.AchievementObject">
select
	a.id,
	a.name,
	a.technical_picture_url as technicalPictureUrl,
	a.category,
	data_category as dataCategory,
	release_date as releaseDate
from
	achievement a
left join branch_information bi on instr(#{1},bi.domain_name)>0
left join achievement_publish ap on ap.achievement_id=a.id
where audit_status=2
and bi.id =ap.publish_platform
and category=0
and instr(ap.publish_page,#{2})>0
and info_sources=1
order by release_date desc limit #{0}
</select>

<select id="getPracticalTechnologys" resultType="com.goafanti.portal.bo.AchievementObject">
select
	a.id,
	a.name,
	a.technical_picture_url as technicalPictureUrl,
	a.is_hot as isHot,
	a.category,
	release_date as releaseDate,
	a.data_category as dataCategory,
	transfer_mode as transferMode,
	fg.name as fieldAS,
	fgg.name as fieldBS
from
	achievement a
left join branch_information bi on instr(#{1},bi.domain_name)>0
left join achievement_publish ap on ap.achievement_id=a.id
left join industry_category fg on
	fg.id = a.field_a
left join industry_category fgg on
	fgg.id = a.field_b
where audit_status=2
and bi.id =ap.publish_platform
and data_category=1
and instr(ap.publish_page,#{2})>0
and info_sources=1
order by release_date desc limit #{0}
</select>

<select id="getIntellectualProperty" resultType="com.goafanti.common.bo.PolicyEntity" >
select p.id,p.title,p.title_img as titleImg,p.summary,dg1.name as provinceS,dg2.name as cityS,edit_time as editTime ,create_time as createTime,p.id,p.source,p.source_url as sourceUrl  from policy p 
left join district_glossory dg1 on dg1.id=p.province
left join district_glossory dg2 on dg2.id=p.city
where audit_status = 2
order by edit_time
limit #{0}
</select>

<select id="getAchievementDetail" resultType="com.goafanti.portal.bo.AchievementObject" >
select
	a.name as name,
	a.transfer_mode as transferMode,
	a.technical_picture_url as technicalPictureUrl,
	a.maturity,
	a.category,
	a.owner_postal_address as city,
	fg.name as fieldAS,
	fgg.name as fieldBS,
	a.release_date as releaseDate,
	a.introduction,
	u.identify_name as ownerName
from
	achievement a
left join industry_category fg on
	fg.id = a.field_a
left join industry_category fgg on
	fgg.id = a.field_b
left join user u on u.id=a.owner_id
where
	a.id = #{0}
</select>

<select id="getBoutiquePatent" resultType="com.goafanti.portal.bo.AchievementObject">
select
	a.technical_picture_url as technicalPictureUrl,
	name,
	id,
	data_category as dataCategory,
	summary
from
	achievement a
where
	a.info_sources = 1
	and audit_status=2
	and a.boutique = 1
	order by a.release_date desc
limit #{0}
</select>

<select id="guessYouLike" resultType="com.goafanti.portal.bo.AchievementObject">
select
	a.id,
	a.data_category as dataCategory,
	a.name as name,
	a.transfer_mode as transferMode,
	a.technical_picture_url as technicalPictureUrl,
	a.maturity,
	a.category,
	fg.name as fieldAS,
	fgg.name as fieldBS,
	release_date as releaseDate,
	owner_type as ownerType,
	introduction
from
	achievement a
left join industry_category fg on
	fg.id = a.field_a
left join industry_category fgg on
	fgg.id = a.field_b
where
audit_status=2
and info_sources=1
order by release_date limit #{0}
</select>
<select id="listMyAchievementByPage"   resultType="com.goafanti.portal.bo.AchievementObject">
	select 
  		a.id, 
  		a.serial_number as serialNumber,
  		a.category,
  		a.data_category as dataCategory,
  		a.name, 
  		a.release_date as releaseDate, 
  		a.audit_status as auditStatus,
  		a.boutique,
  		if(isnull(c.interestCount),0,c.interestCount) as interestCount,
  		if(isnull(d.achievement_id),0,1) as ifInterest,
  		b.identify_name as ownerName
  	from achievement a
  	left join user b on a.owner_id = b.id left join (select achievement_id,count(*)as interestCount from achievement_interest group by achievement_id) c
  	on a.id=c.achievement_id left join (select achievement_id from achievement_interest where uid= #{ownerId,jdbcType=VARCHAR} )d  on a.id=d.achievement_id
  	where a.deleted_sign = 0 and info_sources=1
	<if test="ownerId != null">
  		and a.owner_id = #{ownerId,jdbcType=VARCHAR}
  	</if>
  	<if test="name != null">
		and a.name like concat('%',#{name,jdbcType=VARCHAR},'%')
	</if>
	<if test="ownerName != null">
		and b.identify_name =#{ownerName,jdbcType=VARCHAR}
	</if>
	<if test="auditStatus != null">
		and  a.audit_status = #{auditStatus,jdbcType=INTEGER}
	</if>
	<if test="category != null">
		and a.category = #{category,jdbcType=INTEGER}
	</if>
	<if test="dataCategory != null ">
  		and a.data_category = #{dataCategory,jdbcType=INTEGER}
  	</if>
	<if test="startDate != null">
       and	a.release_date <![CDATA[ >= ]]> #{startDate,jdbcType=TIMESTAMP}
	</if>
	<if test="endDate != null">
	   and  a.release_date <![CDATA[ <= ]]> #{endDate,jdbcType=TIMESTAMP}
	</if>
	order by a.release_date desc
	<if test="page_sql!=null">
			${page_sql}
	</if>
</select>
<select id="listMyAchievementCount" resultType="java.lang.Integer">
	select 
  		count(0)
  	from achievement a
  	left join user b on a.owner_id = b.id left join (select achievement_id,count(*)as interestCount from achievement_interest group by achievement_id) c
  	on a.id=c.achievement_id left join (select achievement_id from achievement_interest where uid='1' )d  on a.id=d.achievement_id
  	where a.deleted_sign = 0 and info_sources=1
	<if test="ownerId != null">
  		and a.owner_id = #{ownerId,jdbcType=VARCHAR}
  	</if>
  	<if test="name != null">
		and a.name like concat('%',#{name,jdbcType=VARCHAR},'%')
	</if>
	<if test="ownerName != null">
		and b.identify_name=#{ownerName,jdbcType=VARCHAR}
	</if>
	<if test="auditStatus != null">
		and  a.audit_status = #{auditStatus,jdbcType=INTEGER}
	</if>
	<if test="category != null">
		and a.category = #{category,jdbcType=INTEGER}
	</if>
	<if test="dataCategory != null ">
  		and a.data_category = #{dataCategory,jdbcType=INTEGER}
  	</if>
	<if test="startDate != null">
       and	a.release_date <![CDATA[ >= ]]> #{startDate,jdbcType=TIMESTAMP}
	</if>
	<if test="endDate != null">
	   and  a.release_date <![CDATA[ =< ]]> #{endDate,jdbcType=TIMESTAMP}
	</if>
</select>
<select id="getAchievementDetailById" resultType="com.goafanti.portal.bo.AchievementResultObject" >
select
a.id,
	a.name as name,
	a.transfer_mode as transferMode,
	a.technical_picture_url as technicalPictureUrl,
	a.maturity,
	a.category,
	a.data_category as dataCategory,
	a.owner_postal_address as city,
	fg.name as fieldAS,
	fgg.name as fieldBS,
	a.release_date as releaseDate,
	a.introduction,
	a.patent_case as patentCase,
	a.parameter,
	a.team_des as teamDes,
	a.field_a as fieldA,
	a.field_b as fieldB,
	a.innovation,
	a.transfer_mode as transferMode,
	a.bargaining_mode as bargainingMode,
	a.boutique,
	a.is_hot as isHot,
	a.audit_status as auditStatus,
	a.audit_info as auditInfo
from
	achievement a
left join industry_category fg on
	fg.id = a.field_a
left join industry_category fgg on
	fgg.id = a.field_b
where
	a.id = #{0}
</select>
<select id="listInterestedAchievement" resultType="com.goafanti.portal.bo.AchievementObject">
	select 
  		a.id, 
  		a.serial_number as serialNumber,
  		a.category,
  		a.data_category as dataCategory,
  		a.name, 
  		a.release_date as releaseDate, 
  		a.audit_status as auditStatus,
  		a.technical_picture_url as technicalPictureUrl,
  		a.boutique,
  		a.release_date as releaseDate,
  		introduction
  	from achievement a
  	left join achievement_interest ai on ai.achievement_id=a.id
  	where ai.uid=#{uid,jdbcType=VARCHAR}
  	<if test="page_sql!=null">
			${page_sql}
	</if>
  	</select>
  	<select id="listInterestedAchievementCount" resultType="java.lang.Integer">
	select 
  		count(0)
  	from achievement a
  	left join achievement_interest ai on ai.achievement_id=a.id
  	where ai.uid=#{uid,jdbcType=VARCHAR}
  	</select>
  	<select id="selectAppAchuevementDetail" resultType="com.goafanti.achievement.bo.AchievementEntity">
  	select a.id,a.name,a.keyword,a.data_category,a.summary,a.introduction,a.transfer_mode as transferMode,a.status,a.transfer_price as transferPrice ,a.innovation,a.maturity,a.audit_status,a.technical_picture_url as technicalPictureUrl,
  a.field_a as fieldA,a.field_b as fieldB,a.field_c as fieldC,f.name as field1,f2.name as field2,f3.name as field3,a.release_status as releaseStatus,a.audit_status as auditStatus,a.boutique,a.field_a as fieldA,a.is_hot as isHot,a.release_date as releaseDate,if(isnull(b.count),0,b.count) as interestCount,
  if(isnull(d.achievement_id),0,1) interest,ifnull(t.x,0) as orderCount
   from achievement a left join industry_category f on a.field_a=f.id
  left join industry_category f2 on a.field_b=f2.id
  left join industry_category f3 on a.field_c=f3.id
  left join ( select count(0) as x,tl.commodity_id from t_order tr
  	left join t_order_detail tl on tl.order_no=tr.order_no
	where tr.order_status != 4 and tr.order_status != 0
	group by  tl.commodity_id) t on a.id=t.commodity_id 
  left join (select achievement_id,count(*) as count from achievement_interest group by achievement_id ) b 
  on a.id=b.achievement_id left join (select achievement_id from achievement_interest where uid= #{uid,jdbcType=VARCHAR} )d  on a.id=d.achievement_id
  where a.deleted_sign = 0 and a.info_sources=1
  AND  a.id =#{id,jdbcType=VARCHAR}
  	</select>
  	<select id="myCollectionAchievementList" resultType="com.goafanti.common.bo.MyCollection">
  	select 
  		a.id, a.name, a.introduction, b.name as fieidA, ai.uid, a.maturity as textA
  	from achievement a
  	left join achievement_interest ai on ai.achievement_id=a.id
  	left join field_glossory b on a.field_a= b.id
  	where ai.uid= #{uid,jdbcType=VARCHAR}
  	order by ai.create_time desc
  	<if test="page_sql!=null">
			${page_sql}
	</if>
  	</select>
  	<select id="myCollectionAchievementCount" resultType="java.lang.Integer">
	select 
  		count(0)
  	from achievement a
  	left join achievement_interest ai on ai.achievement_id=a.id
  	where ai.uid=#{uid,jdbcType=VARCHAR}
  	</select>
</mapper>