<?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.MyAppMapper">
  <select id="countCollections" parameterType="String" resultType="org.springframework.ui.ModelMap">
  	select 
    (select count(1) from achievement_interest ai where ai.uid=#{uid,jdbcType=VARCHAR}) as achievement,
    (select count(1) from demand_interest di where di.uid=#{uid,jdbcType=VARCHAR}) as demand,
    (SELECT count(1) FROM user_interest uin left join user u on uin.to_uid=u.id where u.type=0 and uin.from_uid=#{uid,jdbcType=VARCHAR}) as expert,
    (SELECT count(1) FROM user_interest uin left join organization_info oi on uin.to_uid=oi.uid where oi.identity_type='企业' and uin.from_uid=#{uid,jdbcType=VARCHAR}) as company,
    (SELECT count(1) FROM user_interest uin left join organization_info oi on uin.to_uid=oi.uid where oi.identity_type='院校' and uin.from_uid=#{uid,jdbcType=VARCHAR}) as school,
    (SELECT count(1) FROM user_interest uin left join organization_info oi on uin.to_uid=oi.uid where oi.identity_type='研究机构' and uin.from_uid=#{uid,jdbcType=VARCHAR}) as institute
  </select>
  <select id="countOrders" parameterType="String" resultType="org.springframework.ui.ModelMap">
  	select 
    (select count(1) from org_cognizance where uid=#{uid,jdbcType=VARCHAR} and deleted_sign=0) as hightech,
    (select count(1) from tech_project where uid=#{uid,jdbcType=VARCHAR} and deleted_sign=0) as program,
    (SELECT count(1) FROM patent_info where uid=#{uid,jdbcType=VARCHAR} and deleted_sign=0) as patent,
    (SELECT count(1) FROM value_evaluation where uid=#{uid,jdbcType=VARCHAR}) as evalution,
    (SELECT count(1) FROM activity_user where uid=#{uid,jdbcType=VARCHAR}) as activity,
    (SELECT count(1) FROM achievement_order where uid=#{uid,jdbcType=VARCHAR} and deleted_sign=0) as achievement,
    (SELECT count(1) FROM demand_order where uid=#{uid,jdbcType=VARCHAR} and deleted_sign=0) as demand,
    (SELECT count(1) FROM copyright_info where uid=#{uid,jdbcType=VARCHAR} and delete_sign=0) as copyright
  </select>
  <select id="countMyTech" parameterType="String" resultType="org.springframework.ui.ModelMap">
  	select 
    (select count(1) from achievement a left join achievement_demand ad on a.id=ad.achievement_id where ad.demand_id is not null and a.owner_id=#{uid,jdbcType=VARCHAR}) as `match`,
    (select count(1) from achievement a left join achievement_order ao on a.id=ao.achievement_id where a.owner_id=#{uid,jdbcType=VARCHAR} and ao.status  &gt; 0 and ao.status &lt; 9) as `deal`,
    (select count(1) from achievement a left join achievement_order iao on a.id=iao.achievement_id where a.owner_id=#{uid,jdbcType=VARCHAR} and iao.status = 0) as `intention`
  </select>
  <select id="countMyDemand" parameterType="String" resultType="org.springframework.ui.ModelMap">
    select 
    (select count(1) from demand d left join achievement_demand ad on d.id=ad.demand_id where ad.demand_id is not null and d.employer_id=#{uid,jdbcType=VARCHAR}) as `match`,
    (select count(1) from demand d left join demand_order `do` on d.id=`do`.demand_id where d.employer_id=#{uid,jdbcType=VARCHAR} and `do`.status <![CDATA[ > ]]> 5 and (`do`.service_money <![CDATA[ > ]]> 0 or `do`.commission <![CDATA[ > ]]> 0 or `do`.intention_money <![CDATA[ > ]]> 0)) as `deal`,
    (select count(1) from demand d left join demand_order `ido` on d.id=`ido`.demand_id where d.employer_id=#{uid,jdbcType=VARCHAR} and `ido`.status <![CDATA[ < ]]> 6) as `intention`
  </select>
</mapper>