<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            SELECT fsd.result_detail_id,
                   decode('PD',
                          'LGD',
                          fsd.result_detail_id,
                          'PD',
                          fsd.score_template_line_id)          tree_id_feild,
                   fsd.result_id,
                   fsd.score_template_line_id,
                   decode('PD',
                          'LGD',
                          fsd.parent_result_detail_id,
                          'PD',
                          fst.parent_line_id)                  tree_parent_field,
                   fsd.target_value,
                   CASE
                       when fst.score_target_code in ('HL_CM_10','HL_CM_11','HL_CM_14') then
                           to_char(fsd.target_value,'FM999,999,999,990.00')
                       when fst.score_target_code = 'HL_CM_24' then
                           (SELECT DECODE(sign(t.EXCAVATOR_QUANTITY), 1, t.EXCAVATOR_QUANTITY || '台挖掘机;', null) ||
                                   DECODE(sign(t.LOADER_QUANTITY), 1, t.LOADER_QUANTITY || '台装载机;', null) ||
                                   DECODE(sign(t.OTHER_OTHER), 1, t.OTHER_OTHER || '台其他机器;', null) ||
                                   decode(t.EXCAVATOR_QUANTITY + t.LOADER_QUANTITY + t.OTHER_OTHER, 0 , '无工程机械设备') target_value
                            FROM (SELECT nvl(SUM(DECODE(item_type, 'EXCAVATOR', 1, 0) * nvl(quantity, 1)),0) EXCAVATOR_QUANTITY,
                                         nvl(SUM(DECODE(item_type, 'LOADER', 1, 0) * nvl(quantity, 1)) ,0)   LOADER_QUANTITY,
                                             nvl(SUM(DECODE(item_type, 'OTHER', 1, 0) * nvl(quantity, 1)),0)     OTHER_OTHER
                                  FROM bp_tenant_rate_item_device_lv t
                                  WHERE project_id = (select project_id
                                                      from fnd_sc_score fs,
                                                           fnd_sc_score_result sr
                                                      where fs.SC_SCORE_ID = sr.SC_SCORE_ID
                                                        and sr.RESULT_ID = ${@result_id})) t)
                       when fst.score_target_code = 'HL_CM_25' then
                           (SELECT CASE
                                       WHEN count_3 > 0 THEN
                                           '存在机器债务情况:偿还中，有延期'
                                       ELSE
                                           CASE
                                               WHEN count_2 > 0 THEN
                                                   '存在机器债务情况:偿还中，无延期'
                                               ELSE
                                                   CASE
                                                       WHEN count_1 > 0 THEN
                                                           '机器债务情况均已偿还'
                                                       ELSE
                                                           '无工程机械设备'
                                                       END
                                               END
                                       END target_value
                            FROM (SELECT SUM(DECODE(loans_status, 'REPAID', 1, 0)) * 3        count_1,
                                         SUM(DECODE(loans_status, 'REPAYING_NO_DELAY', 1, 0)) count_2,
                                         SUM(DECODE(loans_status, 'REPAYING_DELAY', 1, 0))    count_3
                                  FROM bp_tenant_rate_item_device_lv t
                                  WHERE project_id =
                                        (SELECT project_id
                                         FROM fnd_sc_score fs,
                                              fnd_sc_score_result sr
                                         WHERE fs.SC_SCORE_ID = sr.SC_SCORE_ID
                                           AND sr.RESULT_ID = ${@result_id})) t
                           )
                       WHEN fst.target_value_type = 'CHAR' THEN
                           (SELECT t.description
                            FROM fnd_score_target_values t
                            WHERE t.score_target_id = fst.score_target_id
                              AND t.fixed_target_value = fsd.target_value)
                       ELSE
                           fsd.target_value
                       END                                     target_value_desc,
                   CASE
                       WHEN fst.score_target_code IN
                            ('HL_CM_04', 'HL_CM_12', 'HL_CM_18', 'HL_CM_27') THEN --日常信用  健康状况 借款情况 工程合同真实性
                           CASE
                               WHEN fsd.target_value IN ('BAD', 'N') THEN
                                   'DQ'
                               ELSE
                                   to_char(fsd.target_score, 'FM9999')
                               END
                       WHEN fst.score_target_code IN ('HL_CM_21', 'HL_CM_08') THEN --是否为他人担保
                           DECODE(fsd.target_value, 'Y', 'DQ', 'CRIMINAL', 'DQ', to_char(fsd.target_score, 'FM9999'))
                       ELSE
                           to_char(fsd.target_score, 'FM9999')
                       END                                     target_score,
                   fsd.target_score_original,
                   fsd.note,
                   fst.score_target_code,
                   fst.score_target_name ||
                   (SELECT '（' || decode(fsd.object_type,
                                         'RRG',
                                         (SELECT prj.bp_name
                                          FROM prj_project_bp prj
                                          WHERE prj.prj_bp_id = fsd.object_id
                                            AND prj.bp_category = 'GUARANTOR'),
                                         'RRB',
                                         (SELECT prj.bp_name
                                          FROM prj_project_bp prj
                                          WHERE prj.prj_bp_id = fsd.object_id
                                            AND prj.bp_category = 'TENANT'),
                                         'RRL',
                                         (SELECT prj.full_name
                                          FROM prj_project_lease_item prj
                                          WHERE prj.project_lease_item_id = fsd.object_id),
                                         'RRC',
                                         (SELECT prj.mortgage_name
                                          FROM prj_project_mortgage prj
                                          WHERE prj.prj_mortgage_id = fsd.object_id),
                                         '') || '）'
                    FROM dual
                    WHERE 'PD' = 'LGD'
                      AND fst.multiple_flag = 'Y'
                      AND fsd.parent_result_detail_id IS NULL) score_target_name,
                   fst.score_value,
                   fst.data_value_from,
                   fst.display_flag,
                   fst.summary_flag,
                   'Y'                                         expand_flag,
                   fst.target_value_type
            FROM fnd_sc_score_result_dtl fsd,
                 fnd_score_template_all_v fst #WHERE_CLAUSE#
            order by fst.score_target_code asc
                ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="target_value_type"/>
        <bm:field name="result_detail_id" databaseType="NUMBER" datatype="java.lang.Long"
                  physicalName="RESULT_DETAIL_ID" required="true"/>
        <bm:field name="result_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="RESULT_ID"
                  required="true"/>
        <bm:field name="score_template_line_id" databaseType="NUMBER" datatype="java.lang.Long"
                  physicalName="SCORE_TEMPLATE_LINE_ID" required="true"/>
        <bm:field name="target_value" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="TARGET_VALUE"/>
        <bm:field name="target_value_desc" databaseType="VARCHAR2" datatype="java.lang.String"
                  physicalName="TARGET_VALUE_DESC"/>
        <bm:field name="target_score" physicalName="TARGET_SCORE"/>
        <bm:field name="target_score_original" databaseType="NUMBER" datatype="java.lang.Double"
                  physicalName="TARGET_SCORE_ORIGINAL"/>
        <bm:field name="note" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="NOTE"/>
        <bm:field name="tree_parent_field" databaseType="NUMBER" datatype="java.lang.Long"
                  physicalName="PARENT_LINE_ID"/>
        <bm:field name="tree_id_feild" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="score_target_code" databaseType="VARCHAR2" datatype="java.lang.String"
                  physicalName="SCORE_TARGET_CODE"/>
        <bm:field name="score_target_name" databaseType="VARCHAR2" datatype="java.lang.String"
                  physicalName="SCORE_TARGET_NAME"/>
        <bm:field name="score_value" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="SCORE_VALUE"/>
        <bm:field name="data_value_from" databaseType="VARCHAR2" datatype="java.lang.String"
                  physicalName="DATA_VALUE_FROM"/>
        <bm:field name="display_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="DISPLAY_FLAG"/>
        <bm:field name="summary_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="SUMMARY_FLAG"/>
        <bm:field name="expand_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="EXPAND_FLAG"/>
    </bm:fields>
    <bm:data-filters>
        <bm:data-filter enforceOperations="query"
                        expression="fsd.score_template_line_id = fst.score_template_line_id and fsd.result_id=${@result_id}"/>
    </bm:data-filters>
</bm:model>
