<?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="cn.com.countrygarden.bi.main.dao.wcx.WcxUserDtoMapper">
    <resultMap id="BaseMap" type="cn.com.countrygarden.bi.main.model.wcx.WcxUserAdmDto">
        <id column="user_id" property="userId"/>
        <result column="open_id" property="openId"/>
        <result column="head_pic_url" property="headPicUrl"/>
        <result column="nick_name" property="nickName"/>
        <result column="status" property="status"/>
        <result column="role_id" property="roleId"/>
        <result column="employee_id" property="employeeId"/>
        <result column="employee_name" property="employeeName"/>
        <result column="employee_phone" property="employeePhone"/>
        <result column="employee_card_id" property="employeeCardId"/>
        <result column="create_date" property="createDate"/>
        <result column="org_code" property="orgCode"/>
        <result column="org_name" property="orgName"/>
        <result column="comm_code" property="commCode"/>
        <result column="comm_name" property="commName"/>
        <result column="is_invalid" property="isInvalid"/>
        <result column="user_code" property="userCode"/>
    </resultMap>

    <select id="selectPageData" resultType="cn.com.countrygarden.bi.main.model.wcx.WcxUserAdmDto">
      SELECT u.user_id as userId,u.employee_name as employeeName,u.employee_card_id as employeeCardId,u.create_date as createDate,u.org_code as orgCode,
      u.org_name as orgName,u.comm_code as commCode,u.comm_name as commName,u.is_invalid as isInvalid,
        (SELECT
        COUNT(i.innovation_id)
        FROM `wcx_innovation` i WHERE i.`user_id`=u.user_id) AS `release`,u.user_code as userCode
      FROM wcx_user u
      <where>
          <if test="employeeName!=null and employeeName!=''">
              and u.employee_name like concat('%', #{employeeName}, '%')
          </if>
          <if test="orgCode!=null and orgCode!=''">
              and u.org_code=#{orgCode}
          </if>
          <if test="commCode!=null and commCode!=''">
              and u.comm_code=#{commCode}
          </if>
      </where>
        order by u.create_date DESC
        <if test="pageSize != '-1'">
            limit #{pageOffset}, #{pageSize}
        </if>

    </select>

    <select id="selectPageCount" resultType="long">
       SELECT
        count(u.user_id)
        from wcx_user u
        <where>
            <if test="employeeName!=null and employeeName!=''">
                and u.employee_name like concat('%', #{employeeName}, '%')
            </if>
            <if test="orgCode!=null and orgCode!=''">
                and u.org_code=#{orgCode}
            </if>
            <if test="commCode!=null and commCode!=''">
                and u.comm_code=#{commCode}
            </if>
        </where>
    </select>

    <select id="findRoleregionfour" resultType="cn.com.countrygarden.bi.main.dto.wcx.BdTypeDo">
          SELECT DISTINCT four as four,Four_tissue as fourTissue FROM performance_app.bd_type
    </select>
    <select id="getPojDate" resultType="cn.com.countrygarden.bi.main.dto.wcx.BdTypeDo">
         SELECT DISTINCT five as five,pk_org as pkOrg FROM performance_app.bd_type
         where four=#{organCode}
    </select>

    <select id="selectList" resultType="cn.com.countrygarden.bi.main.model.wcx.WcxUserAdmDto">
          SELECT user_id as userId,employee_card_id as employeeCardId,employee_name as employeeName FROM wcx_user
    </select>
    <select id="selectUserOne" resultType="cn.com.countrygarden.bi.main.model.wcx.WcxUserAdmDto">
         SELECT user_id as userId,employee_card_id as employeeCardId,employee_name as employeeName FROM wcx_user
         where employee_card_id=#{employeeCardId}
    </select>
    <select id="selectOne" resultType="cn.com.countrygarden.bi.main.dto.wcx.WcxRegionProjectDto">
        SELECT DISTINCT
          emp.project_name      projectName,
          rp.region_name        regionName,
          emp.employee_card_id  employeeCardId,
          (SELECT u.user_id FROM wcx_user u WHERE u.employee_card_id = #{employeeCardId} order by u.create_date desc LIMIT 1) userId,
          rp.Four as orgCode,rp.five as commCode,emp.userCode AS userCode
        FROM
          (SELECT DISTINCT d.ID employee_card_id,d.PK_ORG project_name,MAX(d.CREATIONTIME),d.`CODE` AS userCode FROM performance_app.bd_psndoc d
            LEFT JOIN  `performance_app`.hi_psnjob w  ON d.PK_PSNDOC = w.PK_PSNDOC
          WHERE d.ID = #{employeeCardId} AND w.POSTSTAT='Y'
          GROUP BY d.ID,d.PK_ORG,d.`CODE`
           LIMIT 1
          ) emp
        LEFT JOIN (
                      SELECT DISTINCT Four,Four_tissue region_name,five,pk_org project_name
                      FROM performance_app.bd_type
                    ) rp on emp.project_name = rp.project_name
    </select>
    <select id="findPropertyProjectNameByEmployeeCardId" resultType="cn.com.countrygarden.bi.main.dto.wcx.BdTypeDo">
        SELECT PK_ORG as pkOrg
        FROM performance_app.bd_psndoc
        WHERE ID = #{employeeCardId}
        ORDER BY TS DESC, BID DESC
        LIMIT 1
    </select>
    <update id="updateOrg">
         UPDATE  `wcx_user` SET
         `org_code` = #{orgCode},
         `org_name` = #{orgName},
         `comm_code` = #{commCode},
         `comm_name` =#{commName},
         `user_code` =#{userCode}
         WHERE `employee_card_id` = #{employeeCardId};
    </update>

    <select id="selectByPersonName" resultType="cn.com.countrygarden.bi.main.model.bd_psndoc">
 SELECT DISTINCT d.BID,d.`CODE` as CODE,d.`NAME`,w.pk_dept WORKDEPT,d.ID,
w.POSTSTAT AS poststat, w.PK_JOBGRADE AS pkjobrank,w.PK_POST AS pkPost
FROM `performance_app`.bd_psndoc d
 LEFT JOIN  `performance_app`.hi_psnjob w  ON d.PK_PSNDOC = w.PK_PSNDOC
 WHERE w.POSTSTAT='Y' and d.`NAME`=#{employeeName} and d.ID=#{employeeCardId}
    </select>

    <insert id="insertUser">
         INSERT INTO `biapp`.`wcx_user` (
  `user_id`,
  `open_id`,
  `role_id`,
  `employee_name`,
  `employee_card_id`,
  `status`,
  `create_date`,
  `org_code`,
  `org_name`,
  `comm_code`,
  `comm_name`,
  `is_invalid`,
  user_code
)
VALUES
  (
    #{userId},
    #{openId},
    #{roleId},
    #{employeeName},
    #{employeeCardId},
    #{status},
    #{createDate},
    #{orgCode},
    #{orgName},
    #{commCode},
    #{commName},
     #{isInvalid},
     #{userCode}
  );
    </insert>

    <update id="addInvalid">
         UPDATE  `wcx_user` SET
         `is_invalid` = #{isInvalid}
         WHERE `user_id` = #{userId};
    </update>

    <select id="getReadingNotice" resultType="cn.com.countrygarden.bi.main.model.wcx.WcxUserAdmDto">
         SELECT u.`user_id` as userId,u.`employee_name` as employeeName,u.`org_name` as orgName,u.`comm_name` as commName,r.create_date as createDate FROM `wcx_notice` n
LEFT JOIN `wcx_notice_reading` r ON r.`notice_id`=n.`notice_id`
LEFT JOIN `wcx_user` u ON u.`user_id`=r.`user_id`
WHERE n.`notice_id`=#{id}
        order by r.create_date desc
        <if test="pageSize != '-1'">
            limit #{pageOffset}, #{pageSize}
        </if>

    </select>

    <select id="getReadingNoticeCount" resultType="long">
  SELECT COUNT(u.`user_id`) FROM `wcx_notice` n
LEFT JOIN `wcx_notice_reading` r ON r.`notice_id`=n.`notice_id`
LEFT JOIN `wcx_user` u ON u.`user_id`=r.`user_id`
WHERE n.`notice_id`=#{id}
    </select>

    <select id="getReadingInnovation" resultType="cn.com.countrygarden.bi.main.model.wcx.WcxUserAdmDto">
         SELECT u.`user_id` as userId,u.`employee_name` as employeeName,u.`org_name` as orgName,u.`comm_name` as commName,r.create_date as createDate  FROM `wcx_innovation` i
LEFT JOIN `wcx_innovation_reading` r ON r.`innovation_id`=i.`innovation_id`
LEFT JOIN `wcx_user` u ON u.`user_id`=r.`user_id`
WHERE i.`innovation_id`=#{id}
        order by r.create_date desc
        <if test="pageSize != '-1'">
            limit #{pageOffset}, #{pageSize}
        </if>

    </select>

    <select id="getReadingInnovationCount" resultType="long">
            SELECT COUNT(u.`user_id`) FROM `wcx_innovation` i
LEFT JOIN `wcx_innovation_reading` r ON r.`innovation_id`=i.`innovation_id`
LEFT JOIN `wcx_user` u ON u.`user_id`=r.`user_id`
WHERE i.`innovation_id`=#{id}
    </select>

</mapper>
