<?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.njzscloud.dispose.cst.org.mapper.OrgMapper">
    <resultMap id="BaseResultMap" autoMapping="true" type="com.njzscloud.dispose.cst.org.pojo.result.OrgResult">
    </resultMap>
    <resultMap id="CustomerResultMap" autoMapping="true" type="com.njzscloud.dispose.cst.customer.pojo.result.CustomerResult">
    </resultMap>
    <resultMap id="TransOrgStatisticsResultMap" autoMapping="true" type="com.njzscloud.dispose.cst.org.pojo.result.TransOrgStatistics">
    </resultMap>
    <update id="admitCustomer">
        UPDATE cst_customer
        SET org_id = #{orgId}
        WHERE id = #{customerId}
          AND org_id IS NULL
    </update>

    <update id="quitOrg">
        UPDATE cst_customer
        SET org_id = NULL
        WHERE id = #{customerId}
          AND org_id IS NOT NULL
    </update>

    <select id="paging" resultMap="BaseResultMap">
        SELECT
        o.id,
        o.org_category,
        o.uscc,
        o.org_name,
        o.business_license,
        o.license_start_time,
        o.license_end_time,
        o.legal_representative,
        o.idcard,
        o.idcard_start_time,
        o.idcard_end_time,
        o.idcard_front,
        o.idcard_back,
        o.province,
        o.city,
        o.area,
        o.town,
        o.province_name,
        o.city_name,
        o.area_name,
        o.town_name,
        o.address,
        o.lng,
        o.lat,
        o.creator_id,
        o.modifier_id,
        o.create_time,
        o.modify_time,
        o.deleted
        FROM cst_org o
        LEFT JOIN cst_customer c ON o.id = c.org_id AND c.deleted = 0 AND c.manager = 1
        <if test="ew.customSqlSegment != null and ew.customSqlSegment != ''">
            ${ew.customSqlSegment}
        </if>
    </select>

    <select id="queryList" resultMap="CustomerResultMap">
        SELECT
            cc.id,
            cc.user_id,
            su.avatar,
            cc.identity_category,
            cc.org_id,
            cc.customer_name,
            cc.phone,
            cc.settlement_way,
            cc.manager,
            cc.creator_id,
            cc.modifier_id,
            cc.create_time,
            cc.modify_time,
            cc.deleted
        from cst_customer cc
        LEFT JOIN sys_user su ON su.id = cc.user_id
        <if test="ew.customSqlSegment != null and ew.customSqlSegment != ''">
            ${ew.customSqlSegment}
        </if>
    </select>

    <select id="getCustomer" resultType="com.njzscloud.dispose.cst.customer.pojo.result.SearchCustomerResult">
        SELECT *
        FROM cst_customer
        WHERE id = #{customerId}
    </select>

    <!-- 查询运输方统计信息：订单数量、清运数量、综合评分 -->
    <select id="queryTransOrgStatistics" resultMap="TransOrgStatisticsResultMap">
        SELECT
            t1.org_id AS orgId,
            t1.orderCount,
            t2.clearanceCount,
            t3.overallScore
        FROM (
            -- 订单数量统计
            SELECT
                trans_org_id AS org_id,
                COUNT(*) AS orderCount
            FROM cst_order
            WHERE deleted = 0 AND order_status = 'YiWanCheng' AND trans_org_id IS NOT NULL
            GROUP BY trans_org_id
        ) t1
        LEFT JOIN (
            -- 清运数量统计（通过orderId关联）
            SELECT
                o.trans_org_id AS org_id,
                COUNT(*) AS clearanceCount
            FROM cst_order_trans t
            INNER JOIN cst_order o ON t.order_id = o.id
            WHERE t.trans_status = 'YiWanCheng'
            GROUP BY o.trans_org_id
        ) t2 ON t1.org_id = t2.org_id
        LEFT JOIN (
            -- 综合评分（向上取整到0.5）
            SELECT
                trans_org_id AS org_id,
                CEIL(AVG(overall_score) * 2) / 2 AS overallScore
            FROM cst_order_evaluate
            WHERE deleted = 0
            GROUP BY trans_org_id
        ) t3 ON t1.org_id = t3.org_id
        WHERE t1.org_id IN
        <foreach collection="orgIds" item="orgId" open="(" separator="," close=")">
            #{orgId}
        </foreach>
    </select>
</mapper>
