<?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="org.jeecg.modules.projectManagement.mapper.FdWorkingHoursItemsMapper">
    <select id="queryByContractId" resultType="org.jeecg.modules.projectManagement.entity.FdWorkingHoursItems">
        SELECT
            a.id AS pkContract,
            a.`code` AS contractCode,
            a.project_name AS projectDescription,
            b.project_name AS milestones ,
            b.id AS pkMilestones,
            b.milestones_type AS milestonesType
        FROM
            fd_contract a
            LEFT JOIN fd_clinical_project_milestones b ON a.id = b.pk_contract
        where
            a.del_flag='0'
            and a.id=#{0}
            and b.special_type='1'
        </select>

    <select id="queryBySpecialType" resultType="org.jeecg.modules.projectManagement.entity.FdWorkingHoursItems">
        SELECT
            id AS pkMilestones,
            project_name AS milestones,
            milestones_type AS milestonesType
        FROM
            fd_clinical_project_milestones
        where
            special_type = 2
        </select>

    <select id="findByPage" resultType="org.jeecg.modules.projectManagement.entity.FdWorkingHoursItems">
            SELECT
                whi.id,
                whi.special_type,
                whi.special_type_name,
                whi.estimate_end_time,
                whi.estimate_start_time,
                whi.estimate_end_time1,
                whi.estimate_start_time1,
                whi.project_description,
                con.code as contractCode,
                whi.pk_contract,
                whi.milestones_items_description as milestonesItemsDescription,
                whi.milestones as milestones,
                whi.pk_milestones_items,
                whi.working_hours_number,
                whi.memo
            FROM
                fd_working_hours_items whi
                left join fd_contract con on con.id=whi.pk_contract
                ${ew.customSqlSegment}
        </select>

    <!--left join fd_clinical_project_milestones_items cpmi on cpmi.id=whi.pk_milestones_items-->
    <!--left join fd_clinical_project_milestones cpm on cpm.id=whi.pk_milestones-->

    <!--按部门查询报表-->
    <select id="getWorkingReport" resultType="org.jeecg.modules.projectManagement.entity.FdWorkingHoursItems">
        SELECT
        whi.id,
        whi.pk_contract,
        whi.special_type_name,
        d.depart_name AS departName,
        whi.contract_code AS contractCode,
        fpq.customer_name AS customerName,
        whi.working_hours_number
        FROM
        fd_working_hours_items whi
        LEFT JOIN fd_working_hours wh ON wh.id = whi.pk_woking_hour
        left join fd_project_quotation fpq on fpq.id=whi.quotation_id
        left join sys_user su on wh.create_by=su.username
        LEFT JOIN fd_personnel p ON p.id=su.personnel_id
        LEFT JOIN sys_depart d ON p.pk_depart = d.id
        WHERE
        0 = 0
        AND wh.del_flag = '0'
        <if test="startDate!=null and startDate!='' and startDate!='null'">
            <![CDATA[ and DATE_FORMAT(wh.actual_hours,'%Y-%m-%d') >=DATE_FORMAT(#{startDate},'%Y-%m-%d') ]]>
        </if>
        <if test="endDate!=null and endDate!='' and endDate!='null'">
            <![CDATA[ and DATE_FORMAT(wh.actual_hours,'%Y-%m-%d') <=DATE_FORMAT(#{endDate},'%Y-%m-%d') ]]>
        </if>
        <if test="departs!=null and departs.size>0">
            and d.id in
            <foreach collection="departs" index="index" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        ORDER BY
        d.depart_name,whi.contract_code
    </select>

    <!--按人员查询报表-->
    <select id="getWorkingReportPersonnel" resultType="org.jeecg.modules.projectManagement.entity.FdWorkingHoursItems">
        SELECT
        wh.hour_filler as personnelName,
        whi.pk_contract,
        whi.special_type_name,
        whi.contract_code as contractCode,
        fpq.customer_name as customerName,
        whi.working_hours_number,
        wh.actual_hours as actualHours
        FROM
        fd_working_hours_items whi
        LEFT JOIN fd_working_hours wh on wh.id=whi.pk_woking_hour
        left join fd_project_quotation fpq on fpq.id=whi.quotation_id
        where
        0=0 AND wh.del_flag='0'
        <if test="startDate!=null and startDate!='' and startDate!='null'">
            <![CDATA[ and DATE_FORMAT(wh.actual_hours,'%Y-%m-%d') >=DATE_FORMAT(#{startDate},'%Y-%m-%d') ]]>
        </if>
        <if test="endDate!=null and endDate!='' and endDate!='null'">
            <![CDATA[ and DATE_FORMAT(wh.actual_hours,'%Y-%m-%d') <=DATE_FORMAT(#{endDate},'%Y-%m-%d') ]]>
        </if>
        <if test="personnelCodes!=null and personnelCodes.size>0">
            and wh.create_by in
            <foreach collection="personnelCodes" index="index" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        ORDER BY
        wh.hour_filler,whi.contract_code
    </select>

    <!--根据人员时间分组查询求和工时-->
    <select id="getWorkingItemsSumHours" resultType="org.jeecg.modules.projectManagement.entity.FdWorkingHoursItems">
        SELECT
            wh.hour_filler AS personnelName,
            sum( whi.working_hours_number ) AS workingHoursNumber,
            wh.actual_hours AS actualHours
        FROM
            fd_working_hours_items whi
            LEFT JOIN fd_working_hours wh ON wh.id = whi.pk_woking_hour
        WHERE
            0 = 0 AND wh.del_flag='0'
        <if test="startDate!=null and startDate!='' and startDate!='null'">
            <![CDATA[ and DATE_FORMAT(whi.create_time,'%Y-%m-%d') >=DATE_FORMAT(#{startDate},'%Y-%m-%d') ]]>
        </if>
        <if test="endDate!=null and endDate!='' and endDate!='null'">
            <![CDATA[ and DATE_FORMAT(whi.create_time,'%Y-%m-%d') <=DATE_FORMAT(#{endDate},'%Y-%m-%d') ]]>
        </if>
        GROUP BY
            personnelName,
            actualHours
        ORDER BY
            actualHours
    </select>

    <select id="getContractList" resultType="org.jeecg.modules.projectManagement.entity.FdProjectFile" parameterType="org.jeecg.modules.projectManagement.entity.FdProjectFile">
        SELECT
            pf.id,
            c.id as contractId,
            c.CODE as contractCode
        FROM
            fd_project_file pf
            LEFT JOIN fd_contract c ON c.id = pf.contract_id
        WHERE pf.status='1' AND pf.del_flag='0'
        <if test="fdProjectFile.customerId!=null and fdProjectFile.customerId!=''">
            and pf.customer_id = #{fdProjectFile.customerId}
        </if>
        ORDER BY
        pf.create_time DESC
    </select>

    <select id="getWorkingItemsExport" resultType="org.jeecg.modules.projectManagement.entity.FdWorkingHoursItems">
        SELECT
            fwh.`code`,
            fwh.actual_hours,
            fwh.role_name AS mainRoleName,
            fwhi.*
        FROM
            fd_working_hours_items fwhi
            LEFT JOIN fd_working_hours fwh ON fwhi.pk_woking_hour = fwh.id
        WHERE fwh.del_flag='0'
            and fwh.create_by=#{fdWorkingHoursItems.mainCreateBy}
            AND fwh.actual_hours BETWEEN #{fdWorkingHoursItems.startDate} and #{fdWorkingHoursItems.endDate}
            ORDER BY actual_hours desc
    </select>
<!--根据合同号人员统计工时-->
    <select id="getDateExport" resultType="org.jeecg.modules.projectManagement.entity.FdWorkingHoursItems">
        SELECT
            fwhi.contract_code,
            sum(fwhi.working_hours_number) as workingHoursNumberTotal,
            su.personnel_id as personnelId,
            fpq.customer_name
        FROM
            fd_working_hours_items fwhi
            LEFT JOIN fd_working_hours fwh ON fwhi.pk_woking_hour = fwh.id
            left join sys_user su on fwh.create_by=su.username
            left join fd_project_quotation fpq on fwhi.quotation_id=fpq.id
        WHERE
            fwh.del_flag = '0'
            AND fwh.status='0'
            AND fwhi.del_flag = '0'
            and fwhi.contract_code is not null
            AND fwh.actual_hours BETWEEN #{fdWorkingHoursItems.startDate} and #{fdWorkingHoursItems.endDate}
            AND su.personnel_id in
            <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
            <if test="fdWorkingHoursItems.contractCode!=null and fdWorkingHoursItems.contractCode!=''">
                and fwhi.contract_code=#{fdWorkingHoursItems.contractCode}
            </if>
            GROUP BY fwhi.contract_code,fpq.customer_name,su.personnel_id

    </select>
<!--根据任务项统计非项目工时，因无合同号所以把任务当作合同号-->
    <select id="getDateExport2" resultType="org.jeecg.modules.projectManagement.entity.FdWorkingHoursItems">
        SELECT
            fwhi.milestones_items_description as contractCode,
            sum( fwhi.working_hours_number ) AS workingHoursNumberTotal,
            su.personnel_id AS personnelId
        FROM
            fd_working_hours_items fwhi
            LEFT JOIN fd_working_hours fwh ON fwhi.pk_woking_hour = fwh.id
            LEFT JOIN sys_user su ON fwh.create_by = su.username
        WHERE
            fwh.del_flag = '0'
            AND fwh.status='0'
            AND fwhi.del_flag = '0'
            AND fwhi.contract_code IS NULL
            AND fwhi.milestones_items_description IS not NULL
            AND fwh.actual_hours BETWEEN #{fdWorkingHoursItems.startDate} and #{fdWorkingHoursItems.endDate}
            AND su.personnel_id in
            <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        GROUP BY
            fwhi.milestones_items_description,
            su.personnel_id
    </select>


    <select id="getProjectDayExport" resultType="org.jeecg.modules.projectManagement.entity.FdWorkingHoursItems">
        SELECT
            fwhi.contract_code,
            sum(fwhi.working_hours_number) as workingHoursNumber,
            fwh.actual_hours,
            su.personnel_id AS personnelId,
            fpq.customer_name
        FROM
            fd_working_hours_items fwhi
            LEFT JOIN fd_working_hours fwh ON fwhi.pk_woking_hour = fwh.id
            LEFT JOIN sys_user su ON fwh.create_by = su.username
            left join fd_project_quotation fpq on fwhi.quotation_id=fpq.id
        WHERE
            fwh.del_flag = '0'
            AND fwh.status <![CDATA[ <> ]]> '2'
            AND fwhi.del_flag = '0'
            and fwhi.contract_code is not null
            AND fwh.actual_hours BETWEEN #{fdWorkingHoursItems.startDate} and #{fdWorkingHoursItems.endDate}
            AND su.personnel_id in
            <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
            <if test="fdWorkingHoursItems.contractCode!=null and fdWorkingHoursItems.contractCode!=''">
                and fwhi.contract_code=#{fdWorkingHoursItems.contractCode}
            </if>
        GROUP BY su.personnel_id,fwh.actual_hours,fwhi.contract_code,fpq.customer_name
        ORDER BY fwhi.contract_code
    </select>


    <select id="getNotProjectDayExport" resultType="org.jeecg.modules.projectManagement.entity.FdWorkingHoursItems">
        SELECT
            fwhi.milestones_items_description as contractCode,
            sum(fwhi.working_hours_number) as workingHoursNumber,
            fwh.actual_hours,
            su.personnel_id AS personnelId
        FROM
            fd_working_hours_items fwhi
            LEFT JOIN fd_working_hours fwh ON fwhi.pk_woking_hour = fwh.id
            LEFT JOIN sys_user su ON fwh.create_by = su.username
        WHERE
            fwh.del_flag = '0'
            AND fwh.status  <![CDATA[ <> ]]> '2'
            AND fwhi.del_flag = '0'
            AND fwhi.contract_code IS NULL
            AND fwhi.milestones_items_description IS not NULL
            AND fwh.actual_hours BETWEEN #{fdWorkingHoursItems.startDate} and #{fdWorkingHoursItems.endDate}
            AND su.personnel_id in
            <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        GROUP BY su.personnel_id,fwh.actual_hours,fwhi.milestones_items_description
        ORDER BY fwhi.milestones_items_description
    </select>

    <select id="getProjectTotalByProjectId" resultType="java.lang.String">
        SELECT
            sum( fwhi.working_hours_number ) as projectTotal
        FROM
            fd_working_hours_items fwhi
            LEFT JOIN fd_contract fc ON fwhi.quotation_id = fc.quotation_id
        WHERE
            fwhi.del_flag = '0'
            AND fc.id = #{contractId}
    </select>

    <select id="getViewPage" resultType="org.jeecg.modules.projectManagement.entity.FdWorkingHoursItems">
        SELECT
            fwhi.*,
            fwh.actual_hours,
            fwh.hour_filler
        FROM
            fd_working_hours_items fwhi
            LEFT JOIN fd_working_hours fwh ON fwhi.pk_woking_hour = fwh.id
            ${ew.customSqlSegment}
    </select>

</mapper>