<?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.storlead.user.mapper.HrmresourceMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.storlead.user.pojo.entity.Hrmresource">
        <id column="ID" property="id"/>
        <result column="LOGINID" property="loginid"/>
        <result column="PASSWORD" property="password"/>
        <result column="LASTNAME" property="lastname"/>
        <result column="SEX" property="sex"/>
        <result column="BIRTHDAY" property="birthday"/>
        <result column="NATIONALITY" property="nationality"/>
        <result column="SYSTEMLANGUAGE" property="systemlanguage"/>
        <result column="MARITALSTATUS" property="maritalstatus"/>
        <result column="TELEPHONE" property="telephone"/>
        <result column="MOBILE" property="mobile"/>
        <result column="MOBILECALL" property="mobilecall"/>
        <result column="EMAIL" property="email"/>
        <result column="LOCATIONID" property="locationid"/>
        <result column="WORKROOM" property="workroom"/>
        <result column="HOMEADDRESS" property="homeaddress"/>
        <result column="RESOURCETYPE" property="resourcetype"/>
        <result column="STARTDATE" property="startdate"/>
        <result column="ENDDATE" property="enddate"/>
        <result column="JOBTITLE" property="jobtitle"/>
        <result column="JOBACTIVITYDESC" property="jobactivitydesc"/>
        <result column="JOBLEVEL" property="joblevel"/>
        <result column="SECLEVEL" property="seclevel"/>
        <result column="DEPARTMENTID" property="departmentid"/>
        <result column="SUBCOMPANYID1" property="subcompanyid1"/>
        <result column="COSTCENTERID" property="costcenterid"/>
        <result column="MANAGERID" property="managerid"/>
        <result column="ASSISTANTID" property="assistantid"/>
        <result column="BANKID1" property="bankid1"/>
        <result column="ACCOUNTID1" property="accountid1"/>
        <result column="RESOURCEIMAGEID" property="resourceimageid"/>
        <result column="CREATERID" property="createrid"/>
        <result column="CREATEDATE" property="createdate"/>
        <result column="LASTMODID" property="lastmodid"/>
        <result column="LASTMODDATE" property="lastmoddate"/>
        <result column="LASTLOGINDATE" property="lastlogindate"/>
        <result column="DATEFIELD1" property="datefield1"/>
        <result column="DATEFIELD2" property="datefield2"/>
        <result column="DATEFIELD3" property="datefield3"/>
        <result column="DATEFIELD4" property="datefield4"/>
        <result column="DATEFIELD5" property="datefield5"/>
        <result column="NUMBERFIELD1" property="numberfield1"/>
        <result column="NUMBERFIELD2" property="numberfield2"/>
        <result column="NUMBERFIELD3" property="numberfield3"/>
        <result column="NUMBERFIELD4" property="numberfield4"/>
        <result column="NUMBERFIELD5" property="numberfield5"/>
        <result column="TEXTFIELD1" property="textfield1"/>
        <result column="TEXTFIELD2" property="textfield2"/>
        <result column="TEXTFIELD3" property="textfield3"/>
        <result column="TEXTFIELD4" property="textfield4"/>
        <result column="TEXTFIELD5" property="textfield5"/>
        <result column="TINYINTFIELD1" property="tinyintfield1"/>
        <result column="TINYINTFIELD2" property="tinyintfield2"/>
        <result column="TINYINTFIELD3" property="tinyintfield3"/>
        <result column="TINYINTFIELD4" property="tinyintfield4"/>
        <result column="TINYINTFIELD5" property="tinyintfield5"/>
        <result column="CERTIFICATENUM" property="certificatenum"/>
        <result column="NATIVEPLACE" property="nativeplace"/>
        <result column="EDUCATIONLEVEL" property="educationlevel"/>
        <result column="BEMEMBERDATE" property="bememberdate"/>
        <result column="BEPARTYDATE" property="bepartydate"/>
        <result column="WORKCODE" property="workcode"/>
        <result column="REGRESIDENTPLACE" property="regresidentplace"/>
        <result column="HEALTHINFO" property="healthinfo"/>
        <result column="RESIDENTPLACE" property="residentplace"/>
        <result column="POLICY" property="policy"/>
        <result column="DEGREE" property="degree"/>
        <result column="HEIGHT" property="height"/>
        <result column="USEKIND" property="usekind"/>
        <result column="JOBCALL" property="jobcall"/>
        <result column="ACCUMFUNDACCOUNT" property="accumfundaccount"/>
        <result column="BIRTHPLACE" property="birthplace"/>
        <result column="FOLK" property="folk"/>
        <result column="RESIDENTPHONE" property="residentphone"/>
        <result column="RESIDENTPOSTCODE" property="residentpostcode"/>
        <result column="EXTPHONE" property="extphone"/>
        <result column="MANAGERSTR" property="managerstr"/>
        <result column="STATUS" property="status"/>
        <result column="FAX" property="fax"/>
        <result column="ISLABOUUNION" property="islabouunion"/>
        <result column="WEIGHT" property="weight"/>
        <result column="TEMPRESIDENTNUMBER" property="tempresidentnumber"/>
        <result column="PROBATIONENDDATE" property="probationenddate"/>
        <result column="COUNTRYID" property="countryid"/>
        <result column="PASSWDCHGDATE" property="passwdchgdate"/>
        <result column="NEEDUSB" property="needusb"/>
        <result column="SERIAL" property="serial"/>
        <result column="ACCOUNT" property="account"/>
        <result column="LLOGINID" property="lloginid"/>
        <result column="NEEDDYNAPASS" property="needdynapass"/>
        <result column="DSPORDER" property="dsporder"/>
        <result column="PASSWORDSTATE" property="passwordstate"/>
        <result column="ACCOUNTTYPE" property="accounttype"/>
        <result column="BELONGTO" property="belongto"/>
        <result column="DACTYLOGRAM" property="dactylogram"/>
        <result column="ASSISTANTDACTYLOGRAM" property="assistantdactylogram"/>
        <result column="PASSWORDLOCK" property="passwordlock"/>
        <result column="SUMPASSWORDWRONG" property="sumpasswordwrong"/>
        <result column="OLDPASSWORD1" property="oldpassword1"/>
        <result column="OLDPASSWORD2" property="oldpassword2"/>
        <result column="MSGSTYLE" property="msgstyle"/>
        <result column="MESSAGERURL" property="messagerurl"/>
        <result column="pinyinlastname" property="pinyinlastname"/>
        <result column="TOKENKEY" property="tokenkey"/>
        <result column="USERUSBTYPE" property="userusbtype"/>
        <result column="OUTKEY" property="outkey"/>
        <result column="ADSJGS" property="adsjgs"/>
        <result column="ADGS" property="adgs"/>
        <result column="ADBM" property="adbm"/>
        <result column="MOBILESHOWTYPE" property="mobileshowtype"/>
        <result column="USBSTATE" property="usbstate"/>
        <result column="TOTALSPACE" property="totalspace"/>
        <result column="OCCUPYSPACE" property="occupyspace"/>
        <result column="ECOLOGY_PINYIN_SEARCH" property="ecologyPinyinSearch"/>
        <result column="ISADACCOUNT" property="isadaccount"/>
        <result column="ACCOUNTNAME" property="accountname"/>
        <result column="haschangepwd" property="haschangepwd"/>
        <result column="created" property="created"/>
        <result column="creater" property="creater"/>
        <result column="modified" property="modified"/>
        <result column="modifier" property="modifier"/>
        <result column="passwordlocktime" property="passwordlocktime"/>
        <result column="mobilecaflag" property="mobilecaflag"/>
        <result column="salt" property="salt"/>
        <result column="companystartdate" property="companystartdate"/>
        <result column="workstartdate" property="workstartdate"/>
        <result column="secondaryPwd" property="secondarypwd"/>
        <result column="useSecondaryPwd" property="usesecondarypwd"/>
        <result column="classification" property="classification"/>
        <result column="uuid" property="uuid"/>
        <result column="passwordLockReason" property="passwordlockreason"/>
        <result column="companyworkyear" property="companyworkyear"/>
        <result column="workyear" property="workyear"/>
        <result column="DISMISSDATE" property="dismissdate"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        ID, LOGINID, PASSWORD, LASTNAME, SEX, BIRTHDAY, NATIONALITY, SYSTEMLANGUAGE, MARITALSTATUS, TELEPHONE, MOBILE, MOBILECALL, EMAIL, LOCATIONID, WORKROOM, HOMEADDRESS, RESOURCETYPE, STARTDATE, ENDDATE, JOBTITLE, JOBACTIVITYDESC, JOBLEVEL, SECLEVEL, DEPARTMENTID, SUBCOMPANYID1, COSTCENTERID, MANAGERID, ASSISTANTID, BANKID1, ACCOUNTID1, RESOURCEIMAGEID, CREATERID, CREATEDATE, LASTMODID, LASTMODDATE, LASTLOGINDATE, DATEFIELD1, DATEFIELD2, DATEFIELD3, DATEFIELD4, DATEFIELD5, NUMBERFIELD1, NUMBERFIELD2, NUMBERFIELD3, NUMBERFIELD4, NUMBERFIELD5, TEXTFIELD1, TEXTFIELD2, TEXTFIELD3, TEXTFIELD4, TEXTFIELD5, TINYINTFIELD1, TINYINTFIELD2, TINYINTFIELD3, TINYINTFIELD4, TINYINTFIELD5, CERTIFICATENUM, NATIVEPLACE, EDUCATIONLEVEL, BEMEMBERDATE, BEPARTYDATE, WORKCODE, REGRESIDENTPLACE, HEALTHINFO, RESIDENTPLACE, POLICY, DEGREE, HEIGHT, USEKIND, JOBCALL, ACCUMFUNDACCOUNT, BIRTHPLACE, FOLK, RESIDENTPHONE, RESIDENTPOSTCODE, EXTPHONE, MANAGERSTR, STATUS, FAX, ISLABOUUNION, WEIGHT, TEMPRESIDENTNUMBER, PROBATIONENDDATE, COUNTRYID, PASSWDCHGDATE, NEEDUSB, SERIAL, ACCOUNT, LLOGINID, NEEDDYNAPASS, DSPORDER, PASSWORDSTATE, ACCOUNTTYPE, BELONGTO, DACTYLOGRAM, ASSISTANTDACTYLOGRAM, PASSWORDLOCK, SUMPASSWORDWRONG, OLDPASSWORD1, OLDPASSWORD2, MSGSTYLE, MESSAGERURL, pinyinlastname, TOKENKEY, USERUSBTYPE, OUTKEY, ADSJGS, ADGS, ADBM, MOBILESHOWTYPE, USBSTATE, TOTALSPACE, OCCUPYSPACE, ECOLOGY_PINYIN_SEARCH, ISADACCOUNT, ACCOUNTNAME, haschangepwd, created, creater, modified, modifier, passwordlocktime, mobilecaflag, salt, companystartdate, workstartdate, secondaryPwd, useSecondaryPwd, classification, uuid, passwordLockReason, companyworkyear, workyear, DISMISSDATE
    </sql>

    <!--同步OA流程中的转正日期到OA人力资源表中-->
    <update id="updatePermanentDate">
        UPDATE hrmresource h
            INNER JOIN hrmstatushistory hh ON h.id = hh.RESOURCEID
            SET h.`datefield2` = hh.`CHANGEDATE`
        WHERE hh.type_n = 2;
    </update>

    <!--同步OA流程中的离职日期到OA人力资源表中-->
    <update id="updateResignDate">
        UPDATE hrmresource h
            INNER JOIN hrmstatushistory hh ON h.id = hh.RESOURCEID
            SET h.`datefield1` = hh.`CHANGEDATE`
        WHERE hh.type_n = 5
    </update>

    <!--查询OA全部用户转成WEB user实体返回-->
    <select id="getUserList" resultType="com.storlead.user.pojo.entity.UserEntity">
        SELECT r.ID,
               r.LOGINID                                 AS nickName,
               r.LASTNAME                                AS realName,
               r.MOBILE,
               r.DEPARTMENTID                            AS deptId,
               r.SUBCOMPANYID1                           AS subCompanyId,
               sc.COMPANYID                              AS companyId,
               r.CERTIFICATENUM                          AS idNum,
               r.companystartdate                        AS entryDate,
               r.datefield2                              AS permanentDate,
               r.datefield1                              AS resignDate,
               rel.DESCRIPTION                           AS education,
               r.NATIVEPLACE                             AS census,
               r.FOLK                                    AS ethnic,
               r.BIRTHDAY                                AS birthday,
               r.EMAIL                                   AS email,
               r.MANAGERID                               AS managerId,
               SUBSTRING(r.MANAGERSTR,2,LENGTH(r.MANAGERSTR)-2)      AS managers,
               r.JOBTITLE                                AS jobId,
               r.RESIDENTPLACE                           AS address,
               REPLACE(r.ACCOUNTID1, ' ', '')            AS bankCardNum,
               rtb.BANKNAME                              AS bankName,
               <!-- CASE
                   WHEN r.status = 0 THEN 1
                   WHEN r.status = 2 THEN 1
                   WHEN r.status = 3 THEN 1
                   WHEN r.status = 1 THEN 2
                   WHEN r.status = 5 THEN 6
                   END                                   AS status,-->
               r.status                                  AS status,
               CASE
                   WHEN r.sex = 0 THEN '男'
                   WHEN r.sex = 1 THEN '女'
                   END                                   AS sex,
               CASE
                   WHEN r.MARITALSTATUS = 0 THEN '未婚'
                   WHEN r.MARITALSTATUS = 1 THEN '已婚'
                   END                                   AS marriage
        FROM hrmresource r
                 LEFT JOIN hrmeducationlevel rel ON r.EDUCATIONLEVEL = rel.ID
                 LEFT JOIN hrmbank rtb ON rtb.id = r.BANKID1
                 LEFT JOIN hrmsubcompany sc ON sc.id = r.SUBCOMPANYID1
    </select>

</mapper>
