<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: wangwei  
    $Date: 2015-1-16 下午1:10:28  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <!-- <bm:fields>
        <bm:field name="bp_id" expression="${/parameter/@bp_id}"/> yyyy-mm-dd hh24:mi:ss
    </bm:fields> -->
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    *
                FROM
                    (SELECT
                        t1.bp_id,
                        t1.bp_name,
                        SUM(t1.agent_sub_num) agent_sub_num,
                        SUM(t1.zl_num)zl_num,
                        SUM(t1.zy_num)zy_num,
                        SUM(t1.zg_num)zg_num,
                        SUM(t1.zj_num)zj_num,
                        SUM(t1.zjl_num)zjl_num,
                        SUM((t1.cs_num + t1.hcs_num)) ch_num,
                        SUM((t1.fs_num + t1.hfs_num)) fh_num
                    FROM
                        (SELECT
                            tt.invoice_agent_id bp_id,
                            hb.bp_name, --经销商名称
                            (SELECT
                                COUNT(*)
                            FROM
                                prj_project pp,
                                sys_user su
                            WHERE
                                pp.submitter                                                  = su.user_id AND
                                su.bp_id                                                      = hb.bp_id AND
                                su.bp_category                                                = 'AGENT' AND
                                to_date(TO_CHAR(pp.submit_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') >= NVL(to_date(${@date_from}, 'yyyy-mm-dd'), to_date(TO_CHAR(pp.submit_date, 'yyyy-mm-dd'), 'yyyy-mm-dd')) AND
                                to_date(TO_CHAR(pp.submit_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= NVL(to_date(${@date_to}, 'yyyy-mm-dd'), sysdate) AND
                                pp.project_id                                                 = tt.project_id
                            ) agent_sub_num, --经销商申请件数
                            (SELECT
                                COUNT(zwarv.record_id)
                            FROM
                                zj_wfl_workflow zww,
                                zj_wfl_workflow_node zwwn,
                                zj_wfl_approve_record_v zwarv,
                                prj_project ppp1
                            WHERE
                                zww.workflow_code                                                     = 'WFL_PRJ' AND
                                zwwn.sequence_num                                                     = 5 AND
                                zwarv.record_type                                                    != 'AUTO' AND
                                zww.workflow_id                                                       = zwwn.workflow_id AND
                                zwwn.node_id                                                          = zwarv.node_id AND
                                zwarv.instance_id                                                     = ppp1.wfl_instance_id AND
                                to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') >= NVL(to_date(${@date_from}, 'yyyy-mm-dd'), to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd')) AND
                                to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= NVL(to_date(${@date_to}, 'yyyy-mm-dd'), sysdate) AND
                                ppp1.project_id                                                       = tt.project_id
                            ) zl_num, --信审助理在5节点的记录数量
                            (SELECT
                                COUNT(zwarv.record_id)
                            FROM
                                zj_wfl_workflow zww,
                                zj_wfl_workflow_node zwwn,
                                zj_wfl_approve_record_v zwarv,
                                prj_project ppp1
                            WHERE
                                zww.workflow_code                                                     = 'WFL_PRJ' AND
                                zwwn.sequence_num                                                     = 10 AND
                                zwarv.record_type                                                    != 'AUTO' AND
                                zww.workflow_id                                                       = zwwn.workflow_id AND
                                zwwn.node_id                                                          = zwarv.node_id AND
                                zwarv.instance_id                                                     = ppp1.wfl_instance_id AND
                                to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') >= NVL(to_date(${@date_from}, 'yyyy-mm-dd'), to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd')) AND
                                to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= NVL(to_date(${@date_to}, 'yyyy-mm-dd'), sysdate) AND
                                ppp1.project_id                                                       = tt.project_id
                            ) zy_num, --信审专员在10节点的记录数量
                            (SELECT
                                COUNT(zwarv.record_id)
                            FROM
                                zj_wfl_workflow zww,
                                zj_wfl_workflow_node zwwn,
                                zj_wfl_approve_record_v zwarv,
                                prj_project ppp1
                            WHERE
                                zww.workflow_code                                                     = 'WFL_PRJ' AND
                                zwwn.sequence_num                                                     = 20 AND
                                zwarv.record_type                                                    != 'AUTO' AND
                                zww.workflow_id                                                       = zwwn.workflow_id AND
                                zwwn.node_id                                                          = zwarv.node_id AND
                                zwarv.instance_id                                                     = ppp1.wfl_instance_id AND
                                to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') >= NVL(to_date(${@date_from}, 'yyyy-mm-dd'), to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd')) AND
                                to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= NVL(to_date(${@date_to}, 'yyyy-mm-dd'), sysdate) AND
                                ppp1.project_id                                                       = tt.project_id
                            ) zg_num, --信审主管在20节点的记录数量
                            (SELECT
                                COUNT(zwarv.record_id)
                            FROM
                                zj_wfl_workflow zww,
                                zj_wfl_workflow_node zwwn,
                                zj_wfl_approve_record_v zwarv,
                                prj_project ppp1
                            WHERE
                                zww.workflow_code                                                     = 'WFL_PRJ' AND
                                zwwn.sequence_num                                                     = 30 AND
                                zwarv.record_type                                                    != 'AUTO' AND
                                zww.workflow_id                                                       = zwwn.workflow_id AND
                                zwwn.node_id                                                          = zwarv.node_id AND
                                zwarv.instance_id                                                     = ppp1.wfl_instance_id AND
                                to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') >= NVL(to_date(${@date_from}, 'yyyy-mm-dd'), to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd')) AND
                                to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= NVL(to_date(${@date_to}, 'yyyy-mm-dd'), sysdate) AND
                                ppp1.project_id                                                       = tt.project_id
                            ) zj_num, --信审总监在30节点的记录数量
                            (SELECT
                                COUNT(zwarv.record_id)
                            FROM
                                zj_wfl_workflow zww,
                                zj_wfl_workflow_node zwwn,
                                zj_wfl_approve_record_v zwarv,
                                prj_project ppp1
                            WHERE
                                zww.workflow_code                                                     = 'WFL_PRJ' AND
                                zwwn.sequence_num                                                     = 40 AND
                                zwarv.record_type                                                    != 'AUTO' AND
                                zww.workflow_id                                                       = zwwn.workflow_id AND
                                zwwn.node_id                                                          = zwarv.node_id AND
                                zwarv.instance_id                                                     = ppp1.wfl_instance_id AND
                                to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') >= NVL(to_date(${@date_from}, 'yyyy-mm-dd'), to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd')) AND
                                to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= NVL(to_date(${@date_to}, 'yyyy-mm-dd'), sysdate) AND
                                ppp1.project_id                                                       = tt.project_id
                            ) zjl_num, --总经理在40节点的记录数量
                            (SELECT
                                COUNT(zwarv.record_id)
                            FROM
                                zj_wfl_workflow zww,
                                zj_wfl_workflow_node zwwn,
                                zj_wfl_approve_record_v zwarv,
                                prj_project ppp1
                            WHERE
                                zww.workflow_code                                                     = 'WFL_PRJ' AND
                                zwwn.sequence_num                                                     = 54 AND
                                zwarv.record_type                                                    != 'AUTO' AND
                                zww.workflow_id                                                       = zwwn.workflow_id AND
                                zwwn.node_id                                                          = zwarv.node_id AND
                                zwarv.instance_id                                                     = ppp1.wfl_instance_id AND
                                to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') >= NVL(to_date(${@date_from}, 'yyyy-mm-dd'), to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd')) AND
                                to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= NVL(to_date(${@date_to}, 'yyyy-mm-dd'), sysdate) AND
                                ppp1.project_id                                                       = tt.project_id
                            ) cs_num, --直通车初审在54节点的记录数量
                            (SELECT
                                COUNT(zwarv.record_id)
                            FROM
                                zj_wfl_workflow zww,
                                zj_wfl_workflow_node zwwn,
                                zj_wfl_approve_record_v zwarv,
                                prj_project ppp1
                            WHERE
                                zww.workflow_code                                                     = 'WFL_PRJ' AND
                                zwwn.sequence_num                                                     = 55 AND
                                zwarv.record_type                                                    != 'AUTO' AND
                                zww.workflow_id                                                       = zwwn.workflow_id AND
                                zwwn.node_id                                                          = zwarv.node_id AND
                                zwarv.instance_id                                                     = ppp1.wfl_instance_id AND
                                to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') >= NVL(to_date(${@date_from}, 'yyyy-mm-dd'), to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd')) AND
                                to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= NVL(to_date(${@date_to}, 'yyyy-mm-dd'), sysdate) AND
                                ppp1.project_id                                                       = tt.project_id
                            ) fs_num, --直通车复审在55节点的记录数量
                            (SELECT
                                COUNT(zwarv.record_id)
                            FROM
                                zj_wfl_workflow zww,
                                zj_wfl_workflow_node zwwn,
                                zj_wfl_approve_record_v zwarv,
                                prj_project ppp1
                            WHERE
                                zww.workflow_code                                                     = 'WFL_PRJ' AND
                                zwwn.sequence_num                                                     = 60 AND
                                zwarv.record_type                                                    != 'AUTO' AND
                                zww.workflow_id                                                       = zwwn.workflow_id AND
                                zwwn.node_id                                                          = zwarv.node_id AND
                                zwarv.instance_id                                                     = ppp1.wfl_instance_id AND
                                to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') >= NVL(to_date(${@date_from}, 'yyyy-mm-dd'), to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd')) AND
                                to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= NVL(to_date(${@date_to}, 'yyyy-mm-dd'), sysdate) AND
                                ppp1.project_id                                                       = tt.project_id
                            ) hcs_num, -- 合同初审60节点的记录数量
                            (SELECT
                                COUNT(zwarv.record_id)
                            FROM
                                zj_wfl_workflow zww,
                                zj_wfl_workflow_node zwwn,
                                zj_wfl_approve_record_v zwarv,
                                prj_project ppp1
                            WHERE
                                zww.workflow_code                                                     = 'WFL_PRJ' AND
                                zwwn.sequence_num                                                     = 70 AND
                                zwarv.record_type                                                    != 'AUTO' AND
                                zww.workflow_id                                                       = zwwn.workflow_id AND
                                zwwn.node_id                                                          = zwarv.node_id AND
                                zwarv.instance_id                                                     = ppp1.wfl_instance_id AND
                                to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') >= NVL(to_date(${@date_from}, 'yyyy-mm-dd'), to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd')) AND
                                to_date(TO_CHAR(zwarv.last_update_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') <= NVL(to_date(${@date_to}, 'yyyy-mm-dd'), sysdate) AND
                                ppp1.project_id                                                       = tt.project_id
                            ) hfs_num -- 合同复审70节点的记录数量
                        FROM
                            prj_project tt,
                            hls_bp_master hb
                        WHERE
                            hb.bp_category = 'AGENT' AND
                            hb.bp_id       = tt.invoice_agent_id
                        ) t1
                    GROUP BY
                        t1.bp_id,
                        t1.bp_name
                    ) tt #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="bp_id" queryExpression="tt.bp_id = ${@bp_id}"/>
    </bm:query-fields>
</bm:model>
