<?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.dayou.mapper.PersonalMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.dayou.entity.Personal">
        <result column="id" property="id" />
        <result column="created" property="created" />
        <result column="modified" property="modified" />
        <result column="deleted" property="deleted" />
        <result column="order_id" property="orderId" />
        <result column="location" property="location" />
        <result column="evaluate_aim" property="evaluateAim" />
        <result column="purpose" property="purpose" />
        <result column="clientele_type" property="clienteleType" />
        <result column="clientele_id" property="clienteleId" />
        <result column="clientele_sub_id" property="clienteleSubId" />
        <result column="clientele_contact_id" property="clienteleContactId" />
        <result column="agent" property="agent" />
        <result column="loan_nature" property="loanNature" />
        <result column="loan_aim" property="loanAim" />
        <result column="loan_limit" property="loanLimit" />
        <result column="loan_period" property="loanPeriod" />
        <result column="loan_expire" property="loanExpire" />
        <result column="bailorA" property="bailorA" />
        <result column="bailorB" property="bailorB" />
        <result column="bailorA_tel" property="bailoraTel" />
        <result column="bailorB_tel" property="bailorbTel" />
        <result column="terminal_clientele_type" property="terminalClienteleType" />
        <result column="terminal_clientele_contact_id" property="terminalClienteleContactId" />
        <result column="terminal_clientele_id" property="terminalClienteleId" />
        <result column="credentials" property="credentials" />
        <result column="client_manager_id" property="clientManagerId" />
        <result column="outward_staff" property="outwardStaff" />
        <result column="inward_staff" property="inwardStaff" />
        <result column="pricing_staff" property="pricingStaff" />
        <result column="remark" property="remark" />
        <result column="save_file_check" property="saveFileCheck" />
        <result column="if_save_file" property="ifSaveFile" />
        <result column="save_file_date" property="saveFileDate" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id,
        created,
        modified,
        deleted,
        order_id, location, evaluate_aim, purpose, clientele_type, clientele_id, clientele_sub_id, clientele_contact_id, agent, loan_nature, loan_aim, loan_limit, loan_period, loan_expire, bailorA, bailorB, bailorA_tel, bailorB_tel, terminal_clientele_type, terminal_clientele_contact_id, terminal_clientele_id, credentials, is_online, client_manager_id, outward_staff, inward_staff, pricing_staff, remark
    </sql>

    <sql id="Common_query_sql">
        SELECT
            wf.id AS currentNodeId,
            wf.node_id,
            wf.business_id,
            wf.business_sub_id,
            wf.created AS nodeTime,
            wn.NAME as currentNodeName,
            wn.CODE currentNodeCode,
            p.id,
            p.order_id,
            p.location,
            p.agent,
            p.bailorA,
            p.bailorB,
            p.bailorA_tel,
            p.bailorB_tel,
            p.client_manager_id,
            p.remark,
            pt.acreage,
            pt.price,
            pt.amount,
            cc1.NAME AS clientName,
            cc2.NAME AS clientSubName,
            cl1.NAME AS contactName,
            cl1.mobile AS contactTel,
            u.NAME AS clientManager,
            u1.NAME AS outwardStaffName,
            u2.NAME AS inwardStaffName,
            u3.NAME AS pricingStaffName,
            u4.name as handlerName,
            u4.id as handlerId,
            p.created
        FROM
            work_flow_node_instance wf
                LEFT JOIN work_node wn ON wn.id = wf.node_id
                LEFT JOIN work_task_record wtr ON wtr.instance_id = wf.id
                LEFT JOIN user u4 ON u4.id = wtr.handler_id
                LEFT JOIN personal p ON p.id = wf.business_id
                LEFT JOIN personal_target pt ON pt.personal_id =  wf.business_id
                LEFT JOIN customer_company cc1 ON cc1.id = p.clientele_id
                LEFT JOIN customer_company cc2 ON cc2.id = p.clientele_sub_id
                LEFT JOIN customer_linkman cl1 ON cl1.id = p.clientele_contact_id
                LEFT JOIN user u ON u.id = p.client_manager_id
                LEFT JOIN user u1 ON u1.id = p.outward_staff
                LEFT JOIN user u2 ON u2.id = p.inward_staff
                LEFT JOIN user u3 ON u3.id = p.pricing_staff
    </sql>
    <select id="xSelectPage" parameterType="com.dayou.vo.PersonalVO" resultType="com.dayou.vo.PersonalVO">
        <include refid="Common_query_sql" />
        WHERE
            wf.business_type = "PERSONAL_BUSINESS"
            AND wf.state = 'PENDING'
            AND wf.deleted = 0
            AND wn.deleted = 0
            AND p.deleted = 0
        <if test="keyword!=null and keyword!='' ">
            and (
            p.location like concat ('%',#{keyword},'%')
            or p.order_id like concat('%',#{keyword},'%')
            or p.agent like concat('%',#{keyword},'%')
            or p.bailorA like concat('%',#{keyword},'%')
            or p.bailorB like concat('%',#{keyword},'%')
            or cc1.name like concat('%',#{keyword},'%')
            or cc2.name like concat('%',#{keyword},'%')
            or cl1.name like concat('%',#{keyword},'%')
            )
        </if>
        <if test="personal!=null and personal.startDate!=null and personal.startDate!=''">
            and p.created &gt;= #{personal.startDate}
        </if>
        <if test="personal!=null and personal.endDate!=null and personal.endDate!=''">
            and p.created &lt;= #{personal.endDate}
        </if>
        <if test="personal!=null and personal.nodeCode!=null and personal.nodeCode!=''">
            and wn.code = #{personal.nodeCode}
        </if>
        <if test="personal!=null and personal.clientName!=null and personal.clientName!=''">
            and cc1.NAME like concat('%',#{personal.clientName},'%')
        </if>
        <if test="personal!=null and personal.clientSubName!=null and personal.clientSubName!=''">
            and cc2.NAME like concat('%',#{personal.clientSubName},'%')
        </if>
        <if test="personal!=null and personal.clientManager!=null and personal.clientManager!='' ">
            and u.name like concat('%',#{personal.clientManager},'%')
        </if>
        <if test="personal!=null and personal.outwardStaffName!=null and personal.outwardStaffName!='' ">
            and u1.name like concat('%',#{personal.outwardStaffName},'%')
        </if>
        <if test="personal!=null and personal.inwardStaffName!=null and personal.inwardStaffName!='' ">
            and u2.name like concat('%',#{personal.inwardStaffName},'%')
        </if>
        <if test="personal!=null and personal.pricingStaffName!=null and personal.pricingStaffName!='' ">
            and u3.name like concat('%',#{personal.pricingStaffName},'%')
        </if>
        <if test="personal!=null and personal.handlerName!=null and personal.handlerName!='' ">
            and u4.name like concat('%',#{personal.handlerName},'%')
        </if>
    </select>

    <select id="getDetail" parameterType="java.lang.Long" resultType="com.dayou.vo.PersonalVO">
        SELECT
            p.*,
            cc1.NAME AS clientName,
            cc2.NAME AS clientSubName,
            cl1.NAME AS contactName,
            cl1.mobile AS contactTel,
            cl1.mobile AS contactTel,
            cl1.department as cDepartment,
            u.NAME AS clientManager,
            u1.NAME AS outwardStaffName,
            u2.NAME AS inwardStaffName,
            u3.NAME AS pricingStaffName,
            cc3.name as tTerminalClienteleName,
            cc3.phone as tCompanyMobile,
            cl2.name as tTerminalClienteleContactName,
            cl2.mobile as tMobile
        FROM
            personal p
                LEFT JOIN personal_target pt ON p.id = pt.personal_id
                LEFT JOIN customer_company cc1 ON cc1.id = p.clientele_id
                LEFT JOIN customer_company cc2 ON cc2.id = p.clientele_sub_id
                LEFT JOIN customer_linkman cl1 ON cl1.id = p.clientele_contact_id
                left join customer_company cc3 on cc3.id = p.terminal_clientele_id
                left join customer_linkman cl2 on cl2.id = p.terminal_clientele_contact_id
                LEFT JOIN user u ON u.id = p.client_manager_id
                LEFT JOIN user u1 ON u1.id = p.outward_staff
                LEFT JOIN user u2 ON u2.id = p.inward_staff
                LEFT JOIN user u3 ON u3.id = p.pricing_staff
        where p.deleted = 0 and p.id = #{id}
    </select>

    <select id="getByTargetId" parameterType="java.lang.Long" resultType="com.dayou.entity.Personal">
        select id,credentials from personal where id = (select personal_id from personal_target where id = #{targetId} and deleted = 0) and deleted = 0
    </select>

    <select id="waitingSaveFile" parameterType="com.dayou.vo.PersonalVO" resultType="com.dayou.vo.PersonalVO">
        select
            wf.id AS currentNodeId,
            wtr.id as taskId,
            wtr.created as taskCreated,
            p.id,
            p.order_id,
            p.location,
            pt.acreage,
            pt.price,
            pt.amount,
            cc1.NAME AS clientName,
            cc2.NAME AS clientSubName,
            u.NAME AS clientManager,
            u1.NAME AS outwardStaffName,
            u2.NAME AS inwardStaffName,
            u3.NAME AS pricingStaffName,
            p.created,
            o.real_amount
        from (select id,node_id,business_id from work_flow_node_instance where business_type = "PERSONAL_BUSINESS" and state = 'PENDING' and deleted = 0) wf
                 LEFT JOIN work_node wn ON wn.id = wf.node_id
                 LEFT JOIN work_task_record wtr ON wtr.instance_id = wf.id
                 LEFT JOIN personal p ON p.id = wf.business_id
                 LEFT JOIN personal_target pt ON pt.personal_id =  wf.business_id
                 LEFT JOIN customer_company cc1 ON cc1.id = p.clientele_id
                 LEFT JOIN customer_company cc2 ON cc2.id = p.clientele_sub_id
                 LEFT JOIN user u ON u.id = p.client_manager_id
                 LEFT JOIN user u1 ON u1.id = p.outward_staff
                 LEFT JOIN user u2 ON u2.id = p.inward_staff
                 LEFT JOIN user u3 ON u3.id = p.pricing_staff
                 left join (select business_id,real_amount from order_fund where business_type='PERSONAL_BUSINESS' and deleted = 0) o on o.business_id = p.id
        where  wn.code = 'BUSINESS_ARCHIVING'
          AND wn.deleted = 0
          AND p.deleted = 0
        <if test="keyword!=null and keyword!='' ">
            and (
            p.location like concat ('%',#{keyword},'%')
            or p.order_id like concat('%',#{keyword},'%')
            or cc1.name like concat('%',#{keyword},'%')
            or cc2.name like concat('%',#{keyword},'%')
            )
        </if>
    </select>

    <update id="updateSaveFileInfo" parameterType="java.util.List">
        update personal set if_save_file = 1 ,save_file_date= now() where id in (
        <foreach collection="list" index="index" item="item" separator=",">
            #{item}
        </foreach>
        )
    </update>

    <select id="saveFileDone" parameterType="com.dayou.vo.PersonalVO" resultType="com.dayou.vo.PersonalVO">
        select
            p.id,
            p.order_id,
            p.location,
            pt.acreage,
            pt.price,
            pt.amount,
            cc1.NAME AS clientName,
            cc2.NAME AS clientSubName,
            u.NAME AS clientManager,
            u1.NAME AS outwardStaffName,
            u2.NAME AS inwardStaffName,
            u3.NAME AS pricingStaffName,
            p.created,
            o.real_amount,
            p.save_file_date
        from
            personal p
                LEFT JOIN personal_target pt ON pt.personal_id =  p.id
                LEFT JOIN customer_company cc1 ON cc1.id = p.clientele_id
                LEFT JOIN customer_company cc2 ON cc2.id = p.clientele_sub_id
                LEFT JOIN user u ON u.id = p.client_manager_id
                LEFT JOIN user u1 ON u1.id = p.outward_staff
                LEFT JOIN user u2 ON u2.id = p.inward_staff
                LEFT JOIN user u3 ON u3.id = p.pricing_staff
                left join (select business_id,real_amount from order_fund where business_type='PERSONAL_BUSINESS' and deleted = 0) o on o.business_id = p.id
        where
            p.deleted = 0 and p.if_save_file = 1 and pt.deleted= 0
        <if test="keyword!=null and keyword!='' ">
            and (
            p.location like concat ('%',#{keyword},'%')
            or p.order_id like concat('%',#{keyword},'%')
            or p.bailorA like concat('%',#{keyword},'%')
            or p.bailorB like concat('%',#{keyword},'%')
            or cc1.name like concat('%',#{keyword},'%')
            or cc2.name like concat('%',#{keyword},'%')
            )
        </if>
        <if test="personal!=null and personal.startDate!=null and personal.startDate!=''">
            and p.created &gt;= #{personal.startDate}
        </if>
        <if test="personal!=null and personal.endDate!=null and personal.endDate!=''">
            and p.created &lt;= #{personal.endDate}
        </if>
        <if test="personal!=null and personal.clientName!=null and personal.clientName!=''">
            and cc1.NAME like concat('%',#{personal.clientName},'%')
        </if>
        <if test="personal!=null and personal.clientSubName!=null and personal.clientSubName!=''">
            and cc2.NAME like concat('%',#{personal.clientSubName},'%')
        </if>
        <if test="personal!=null and personal.clientManager!=null and personal.clientManager!='' ">
            and u.name like concat('%',#{personal.clientManager},'%')
        </if>
        <if test="personal!=null and personal.outwardStaffName!=null and personal.outwardStaffName!='' ">
            and u1.name like concat('%',#{personal.outwardStaffName},'%')
        </if>
        <if test="personal!=null and personal.inwardStaffName!=null and personal.inwardStaffName!='' ">
            and u2.name like concat('%',#{personal.inwardStaffName},'%')
        </if>
        <if test="personal!=null and personal.pricingStaffName!=null and personal.pricingStaffName!='' ">
            and u3.name like concat('%',#{personal.pricingStaffName},'%')
        </if>
    </select>

    <select id="myOrder" parameterType="com.dayou.vo.PersonalVO" resultType="com.dayou.vo.PersonalVO">
        select
            p.id,
            p.order_id,
            p.location as orderName,
            pt.acreage,
            pt.price,
            pt.amount,
            cc1.NAME AS clientName,
            cc2.NAME AS clientSubName,
            p.agent,
            cl.name as contactName,
            p.bailorA,
            p.bailorB,
            cl.mobile as contactTel,
            u1.NAME AS outwardStaffName,
            u2.NAME AS inwardStaffName,
            u3.NAME AS pricingStaffName,
            p.created,
            o.real_amount,
            o.should_amount,
            p.save_file_date
        from
            personal p
                LEFT JOIN personal_target pt ON pt.personal_id =  p.id
                LEFT JOIN customer_company cc1 ON cc1.id = p.clientele_id
                LEFT JOIN customer_company cc2 ON cc2.id = p.clientele_sub_id
                left join customer_linkman cl on cl.id = p.clientele_contact_id
                LEFT JOIN user u1 ON u1.id = p.outward_staff
                LEFT JOIN user u2 ON u2.id = p.inward_staff
                LEFT JOIN user u3 ON u3.id = p.pricing_staff
                left join (select business_id,real_amount,should_amount from order_fund where business_type='PERSONAL_BUSINESS' and deleted = 0) o on o.business_id = p.id
        where
            p.deleted = 0  and pt.deleted= 0 and p.client_manager_id = #{personal.clientManagerId}
        <if test="keyword!=null and keyword!='' ">
            and (
            p.location like concat ('%',#{keyword},'%')
            or p.order_id like concat('%',#{keyword},'%')
            or p.agent like concat('%',#{keyword},'%')
            or p.bailorA like concat('%',#{keyword},'%')
            or p.bailorB like concat('%',#{keyword},'%')
            or cc1.name like concat('%',#{keyword},'%')
            or cc2.name like concat('%',#{keyword},'%')
            or cl.name like concat('%',#{keyword},'%')
            )
        </if>
        <if test="personal!=null and personal.startDate!=null and personal.startDate!=''">
            and p.created &gt;= #{personal.startDate}
        </if>
        <if test="personal!=null and personal.endDate!=null and personal.endDate!=''">
            and p.created &lt;= #{personal.endDate}
        </if>
        <if test="personal!=null and personal.nodeCode!=null and personal.nodeCode!=''">
            and wn.code = #{personal.nodeCode}
        </if>
        <if test="personal!=null and personal.clientName!=null and personal.clientName!=''">
            and cc1.NAME like concat('%',#{personal.clientName},'%')
        </if>
        <if test="personal!=null and personal.clientSubName!=null and personal.clientSubName!=''">
            and cc2.NAME like concat('%',#{personal.clientSubName},'%')
        </if>
    </select>
</mapper>
