<?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.fs.provider.mapper.ServiceOrderMapper">

    <resultMap type="ServiceOrder" id="ServiceOrderResult">
        <result property="id"    column="id"    />
        <result property="serviceOrderNo"    column="service_order_no"    />
        <result property="companyName"    column="company_name"    />
        <result property="companyId"    column="company_id"    />
        <result property="doctorId"    column="doctor_id"    />
        <result property="doctorName"    column="doctor_name"    />
        <result property="deptId"    column="dept_id"    />
        <result property="deptName"    column="dept_name"    />
        <result property="settlementMethod"    column="settlement_method"    />
        <result property="taskAmount"    column="task_amount"    />
        <result property="afterTaxAmount"    column="after_tax_amount"    />
        <result property="serviceFeeRate"    column="service_fee_rate"    />
        <result property="serviceFeeAmount"    column="service_fee_amount"    />
        <result property="totalAmount"    column="total_amount"    />
        <result property="auditStatus"    column="audit_status"    />
        <result property="auditTime"    column="audit_time"    />
        <result property="doctorConfirm"    column="doctor_confirm"    />
        <result property="settlementStatus"    column="settlement_status"    />
        <result property="settlementTime"    column="settlement_time"    />
        <result property="initiatorUserId"    column="initiator_user_id"    />
        <result property="initiatorName"    column="initiator_name"    />
        <result property="initiatorPhone"    column="initiator_phone"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateTime"    column="update_time"    />
        <result property="createBy"    column="create_by"    />
        <result property="updateBy"    column="update_by"    />
        <result property="deleted"    column="deleted"    />
    </resultMap>

    <sql id="selectServiceOrderVo">
        select id, service_order_no, company_id,company_name, doctor_id,doctor_name,dept_id, dept_name, settlement_method, task_amount, after_tax_amount, service_fee_rate, service_fee_amount, total_amount, audit_status, audit_time, doctor_confirm, settlement_status, settlement_time, initiator_user_id, initiator_name, initiator_phone, create_time, update_time, create_by, update_by, deleted from service_order
    </sql>

    <select id="selectServiceOrderList" parameterType="ServiceOrder" resultMap="ServiceOrderResult">
        <include refid="selectServiceOrderVo"/>
        <where>
            <if test="serviceOrderNo != null  and serviceOrderNo != ''"> and service_order_no = #{serviceOrderNo}</if>
            <if test="companyId != null"> and company_id = #{companyId}</if>
            <if test="companyName != null  and companyName != ''"> and company_name like concat('%', #{companyName}, '%')</if>
            <if test="deptId != null "> and dept_id = #{deptId}</if>
            <if test="deptName != null  and deptName != ''"> and dept_name like concat('%', #{deptName}, '%')</if>
            <if test="settlementMethod != null  and settlementMethod != ''"> and settlement_method = #{settlementMethod}</if>
            <if test="taskAmount != null "> and task_amount = #{taskAmount}</if>
            <if test="afterTaxAmount != null "> and after_tax_amount = #{afterTaxAmount}</if>
            <if test="serviceFeeRate != null "> and service_fee_rate = #{serviceFeeRate}</if>
            <if test="serviceFeeAmount != null "> and service_fee_amount = #{serviceFeeAmount}</if>
            <if test="totalAmount != null "> and total_amount = #{totalAmount}</if>
            <if test="auditStatus != null "> and audit_status = #{auditStatus}</if>
            <if test="auditTime != null "> and audit_time = #{auditTime}</if>
            <if test="doctorConfirm != null "> and doctor_confirm = #{doctorConfirm}</if>
            <if test="settlementStatus != null "> and settlement_status = #{settlementStatus}</if>
            <if test="settlementTime != null "> and settlement_time = #{settlementTime}</if>
            <if test="initiatorUserId != null "> and initiator_user_id = #{initiatorUserId}</if>
            <if test="initiatorName != null  and initiatorName != ''"> and initiator_name like concat('%', #{initiatorName}, '%')</if>
            <if test="initiatorPhone != null  and initiatorPhone != ''"> and initiator_phone = #{initiatorPhone}</if>
            <if test="deleted != null "> and deleted = #{deleted}</if>
            <if test="doctorId != null "> and doctor_id = #{doctorId}</if>
        </where>
        order by create_time desc
    </select>

    <select id="selectServiceOrderById" parameterType="Long" resultMap="ServiceOrderResult">
        <include refid="selectServiceOrderVo"/>
        where id = #{id}
    </select>

    <insert id="insertServiceOrder" parameterType="ServiceOrder" useGeneratedKeys="true" keyProperty="id">
        insert into service_order
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="serviceOrderNo != null and serviceOrderNo != ''">service_order_no,</if>
            <if test="companyName != null">company_name,</if>
            <if test="companyId != null">company_id,</if>
            <if test="deptId != null">dept_id,</if>
            <if test="deptName != null">dept_name,</if>
            <if test="settlementMethod != null">settlement_method,</if>
            <if test="taskAmount != null">task_amount,</if>
            <if test="afterTaxAmount != null">after_tax_amount,</if>
            <if test="serviceFeeRate != null">service_fee_rate,</if>
            <if test="serviceFeeAmount != null">service_fee_amount,</if>
            <if test="totalAmount != null">total_amount,</if>
            <if test="auditStatus != null">audit_status,</if>
            <if test="auditTime != null">audit_time,</if>
            <if test="doctorConfirm != null">doctor_confirm,</if>
            <if test="settlementStatus != null">settlement_status,</if>
            <if test="settlementTime != null">settlement_time,</if>
            <if test="initiatorUserId != null">initiator_user_id,</if>
            <if test="initiatorName != null">initiator_name,</if>
            <if test="initiatorPhone != null">initiator_phone,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="createBy != null">create_by,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="deleted != null">deleted,</if>
            <if test="doctorId != null">doctor_id,</if>
            <if test="doctorName != null">doctor_name,</if>

         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="serviceOrderNo != null and serviceOrderNo != ''">#{serviceOrderNo},</if>
            <if test="companyName != null">#{companyName},</if>
            <if test="companyId != null">#{companyId},</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="deptName != null">#{deptName},</if>
            <if test="settlementMethod != null">#{settlementMethod},</if>
            <if test="taskAmount != null">#{taskAmount},</if>
            <if test="afterTaxAmount != null">#{afterTaxAmount},</if>
            <if test="serviceFeeRate != null">#{serviceFeeRate},</if>
            <if test="serviceFeeAmount != null">#{serviceFeeAmount},</if>
            <if test="totalAmount != null">#{totalAmount},</if>
            <if test="auditStatus != null">#{auditStatus},</if>
            <if test="auditTime != null">#{auditTime},</if>
            <if test="doctorConfirm != null">#{doctorConfirm},</if>
            <if test="settlementStatus != null">#{settlementStatus},</if>
            <if test="settlementTime != null">#{settlementTime},</if>
            <if test="initiatorUserId != null">#{initiatorUserId},</if>
            <if test="initiatorName != null">#{initiatorName},</if>
            <if test="initiatorPhone != null">#{initiatorPhone},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="deleted != null">#{deleted},</if>
            <if test="doctorId != null">#{doctorId},</if>
            <if test="doctorName != null">#{doctorName},</if>
         </trim>
    </insert>

    <update id="updateServiceOrder" parameterType="ServiceOrder">
        update service_order
        <trim prefix="SET" suffixOverrides=",">
            <if test="serviceOrderNo != null and serviceOrderNo != ''">service_order_no = #{serviceOrderNo},</if>
            <if test="companyName != null">company_name = #{companyName},</if>
            <if test="companyId != null">company_Id = #{companyId},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="deptName != null">dept_name = #{deptName},</if>
            <if test="settlementMethod != null">settlement_method = #{settlementMethod},</if>
            <if test="taskAmount != null">task_amount = #{taskAmount},</if>
            <if test="afterTaxAmount != null">after_tax_amount = #{afterTaxAmount},</if>
            <if test="serviceFeeRate != null">service_fee_rate = #{serviceFeeRate},</if>
            <if test="serviceFeeAmount != null">service_fee_amount = #{serviceFeeAmount},</if>
            <if test="totalAmount != null">total_amount = #{totalAmount},</if>
            <if test="auditStatus != null">audit_status = #{auditStatus},</if>
            <if test="auditTime != null">audit_time = #{auditTime},</if>
            <if test="doctorConfirm != null">doctor_confirm = #{doctorConfirm},</if>
            <if test="settlementStatus != null">settlement_status = #{settlementStatus},</if>
            <if test="settlementTime != null">settlement_time = #{settlementTime},</if>
            <if test="initiatorUserId != null">initiator_user_id = #{initiatorUserId},</if>
            <if test="initiatorName != null">initiator_name = #{initiatorName},</if>
            <if test="initiatorPhone != null">initiator_phone = #{initiatorPhone},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="deleted != null">deleted = #{deleted},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteServiceOrderById" parameterType="Long">
        delete from service_order where id = #{id}
    </delete>

    <delete id="deleteServiceOrderByIds" parameterType="String">
        delete from service_order where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <!-- 幂等抢占：只有仍满足条件的订单才能更新成功 -->
    <update id="markAutoConfirmed">
        update service_order
        set doctor_confirm = 1,
        settlement_status = 1,
        settlement_time = #{settlementTime}
        where id = #{orderId}
        and deleted = 0
        and doctor_confirm = 0
        and settlement_status = 0
        and audit_status in (1, 3)
        and (
        (audit_status = 3 and create_time &lt;= date_sub(now(), interval 30 minute))
        or (audit_status = 1 and audit_time &lt;= date_sub(now(), interval 30 minute))
        )
    </update>




    <select id="selectNeedAutoConfirmIds" resultType="java.lang.Long">
        select id
        from service_order
        where deleted = 0
        and doctor_confirm = 0
        and settlement_status = 0
        and audit_status in (1, 3)
        and (
        (audit_status = 3 and create_time &lt;= date_sub(now(), interval 30 minute))
        or (audit_status = 1 and audit_time &lt;= date_sub(now(), interval 30 minute))
        )
        order by create_time asc
        limit #{limit}
    </select>
    <select id="queryStatis" resultType="com.fs.provider.domain.ServiceOrder">
        -- 服务单统计（含任务类型）
        SELECT
        so.dept_id AS dept_id,
        so.dept_name AS dept_name,
        ti.task_type AS task_type,
        so.company_name AS company_name,
        so.initiator_user_id AS company_user_id,
        so.initiator_name AS initiator_name,
        COUNT(DISTINCT so.service_order_no) AS service_order_count,
        COUNT(DISTINCT ti.task_no) AS task_count,
        SUM(so.total_amount) AS total_amount
        FROM service_order so
        LEFT JOIN service_order_task_rel sot ON so.id = sot.service_order_id AND sot.deleted = 0
        LEFT JOIN task_info ti ON sot.task_id = ti.id AND ti.deleted = 0
        <where>
            so.deleted = 0
            <if test="companyId != null">
                AND so.company_id = #{companyId}
            </if>
            <if test="companyUserId != null">
                AND so.initiator_user_id = #{companyUserId}
            </if>
            <if test="startDate != null and endDate != null">
                AND so.create_time BETWEEN #{startDate} AND #{endDate}
            </if>
            <if test="taskType != null and taskType != ''">
                AND ti.task_type = #{taskType}
            </if>
            <if test="taskStatus != null and taskStatus != ''">
                AND ti.task_status = #{taskStatus}
            </if>
        </where>
        GROUP BY so.dept_id, so.dept_name, ti.task_type, so.company_name, so.initiator_user_id, so.initiator_name
        ORDER BY so.dept_id, ti.task_type
    </select>

    <!-- 服务单统计汇总（按部门分组） -->
    <resultMap id="ServiceOrderStatisticsResult" type="com.fs.provider.vo.ServiceOrderStatisticsVO">
        <result property="deptId" column="dept_id"/>
        <result property="deptName" column="dept_name"/>
        <result property="serviceOrderCount" column="service_order_count"/>
        <result property="taskCount" column="task_count"/>
        <result property="companyUserCount" column="company_user_count"/>
        <result property="doctorCount" column="doctor_count"/>
        <result property="totalAmount" column="total_amount"/>
        <result property="taskAmount" column="task_amount"/>
        <result property="serviceFeeAmount" column="service_fee_amount"/>
    </resultMap>

    <select id="selectServiceOrderStatistics" parameterType="com.fs.provider.vo.ServiceOrderStatisticsReqVO"
            resultMap="ServiceOrderStatisticsResult">
        SELECT
            so.dept_id,
            so.dept_name,
            COUNT(DISTINCT so.id) AS service_order_count,
            COUNT(DISTINCT rel.task_id) AS task_count,
            COUNT(DISTINCT so.initiator_user_id) AS company_user_count,
            COUNT(DISTINCT ti.doctor_id) AS doctor_count,
            COALESCE(SUM(so.total_amount), 0) AS total_amount,
            COALESCE(SUM(so.task_amount), 0) AS task_amount
        FROM service_order so
        LEFT JOIN service_order_task_rel rel ON so.id = rel.service_order_id AND rel.deleted = 0
        LEFT JOIN task_info ti ON rel.task_id = ti.id AND ti.deleted = 0
        <where>
            so.deleted = 0
            <if test="companyId != null">
                AND so.company_id = #{companyId}
            </if>
            <if test="deptId != null">
                AND so.dept_id = #{deptId}
            </if>
            <if test="startTime != null">
                AND so.create_time &gt;= #{startTime}
            </if>
            <if test="endTime != null">
                AND so.create_time &lt;= #{endTime}
            </if>
            <if test="auditStatus != null">
                AND so.audit_status = #{auditStatus}
            </if>
            <if test="settlementStatus != null">
                AND so.settlement_status = #{settlementStatus}
            </if>
        </where>
        GROUP BY so.dept_id, so.dept_name
        ORDER BY so.dept_id
    </select>
    <select id="selectServiceOrderStatCount" resultType="java.lang.Long">
        SELECT
        so.dept_id,
        so.dept_name,
        COUNT(DISTINCT so.id) AS service_order_count,
        COUNT(DISTINCT rel.task_id) AS task_count,
        COUNT(DISTINCT so.initiator_user_id) AS company_user_count,
        COUNT(DISTINCT ti.doctor_id) AS doctor_count,
        COALESCE(SUM(so.total_amount), 0) AS total_amount,
        COALESCE(SUM(so.task_amount), 0) AS task_amount
        FROM service_order so
        LEFT JOIN service_order_task_rel rel ON so.id = rel.service_order_id AND rel.deleted = 0
        LEFT JOIN task_info ti ON rel.task_id = ti.id AND ti.deleted = 0
        <where>
            so.deleted = 0
            <if test="companyId != null">
                AND so.company_id = #{companyId}
            </if>
            <if test="deptId != null">
                AND so.dept_id = #{deptId}
            </if>
            <if test="startTime != null">
                AND so.create_time &gt;= #{startTime}
            </if>
            <if test="endTime != null">
                AND so.create_time &lt;= #{endTime}
            </if>
            <if test="auditStatus != null">
                AND so.audit_status = #{auditStatus}
            </if>
            <if test="settlementStatus != null">
                AND so.settlement_status = #{settlementStatus}
            </if>
        </where>
        GROUP BY so.dept_id, so.dept_name
        ORDER BY so.dept_id
    </select>

</mapper>
