<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
     select (select nvl(sum(nvl(ci.set_number, 1)), 0)
           from con_contract cc, con_contract_lease_item ci
          where cc.lease_start_date = TRUNC(sysdate )
            and ci.contract_id = cc.contract_id
            and cc.contract_status = 'INCEPT'
            and cc.data_class = 'NORMAL') day_num, --日台数
        nvl((select to_char(round(sum(nvl(cc.total_rental, 0)) +
                   sum(nvl(cc.residual_value, 0)),2),'fm99999999999990.00')
              from con_contract cc
             where cc.lease_start_date = TRUNC(sysdate )
               and cc.contract_status = 'INCEPT'
               and cc.data_class = 'NORMAL'),
            0) day_money, --日金额
        (select nvl(sum(nvl(ci.set_number, 1)),0)
           from con_contract cc, con_contract_lease_item ci
          where cc.lease_start_date between trunc(sysdate, 'mm') and
                trunc(sysdate)
            and ci.contract_id = cc.contract_id
            and cc.contract_status = 'INCEPT'
            and cc.data_class = 'NORMAL') month_num, --月数量
        nvl((select to_char(round(sum(nvl(cc.total_rental, 0)) +
                   sum(nvl(cc.residual_value, 0)),2),'fm99999999999990.00')
              from con_contract cc
             where cc.lease_start_date between trunc(sysdate, 'mm') and
                   trunc(sysdate)
               and cc.contract_status = 'INCEPT'
               and cc.data_class = 'NORMAL'),
            0) month_money, --月金额
        case
          when trunc(sysdate) >
               to_date(to_char(sysdate, 'yyyy') || '0331', 'yyyymmdd') then
           (select sum(nvl(ci.set_number, 1))
              from con_contract cc, con_contract_lease_item ci
             where cc.lease_start_date between
                   to_date(to_char(sysdate, 'yyyy') || '0401', 'yyyymmdd') and
                   to_date(to_char(add_months(sysdate, 12), 'yyyy') || '0331',
                           'yyyymmdd')
               and ci.contract_id = cc.contract_id
               and cc.contract_status = 'INCEPT'
               and cc.data_class = 'NORMAL')
          else
           (select sum(nvl(ci.set_number, 1))
              from con_contract cc, con_contract_lease_item ci
             where cc.lease_start_date between
                   to_date(to_char(add_months(sysdate, -12), 'yyyy') ||
                           '0401',
                           'yyyymmdd') and
                   to_date(to_char(sysdate, 'yyyy') || '0331', 'yyyymmdd')
               and cc.contract_status = 'INCEPT'
               and ci.contract_id = cc.contract_id
               and cc.data_class = 'NORMAL')
        end as year_num, --年台数
        (case
          when trunc(sysdate) >
               to_date(to_char(sysdate, 'yyyy') || '0331', 'yyyymmdd') then
           (select to_char(round(sum(nvl(cc.total_rental, 0)) +
                   sum(nvl(cc.residual_value, 0)),2),'fm99999999999990.00')
              from con_contract cc
             where cc.lease_start_date between
                   to_date(to_char(sysdate, 'yyyy') || '0401', 'yyyymmdd') and
                   to_date(to_char(add_months(sysdate, 12), 'yyyy') || '0331',
                           'yyyymmdd')
               and cc.contract_status = 'INCEPT'
               and cc.data_class = 'NORMAL')
          else
           (select to_char(round(sum(nvl(cc.total_rental, 0)) +
                   sum(nvl(cc.residual_value, 0)),2),'fm99999999999990.00')
              from con_contract cc
             where cc.lease_start_date between
                   to_date(to_char(add_months(sysdate, -12), 'yyyy') ||
                           '0401',
                           'yyyymmdd') and
                   to_date(to_char(sysdate, 'yyyy') || '0331', 'yyyymmdd')
               and cc.contract_status = 'INCEPT'
               and cc.data_class = 'NORMAL')
        end) year_money, --年金额
        (select nvl(sum(nvl(ci.set_number, 1)), 0)
           from con_contract cc, con_contract_lease_item ci
          where (cc.contract_status in ('INCEPT', 'REPURING', 'ETING') or
                (cc.contract_status = 'PENDING' and cc.project_id is not null and
                exists
                 (select 1
                     from zj_wfl_workflow_instance_v     zw,
                          ZJ_WFL_WORKFLOW_INSTANCE_PARA  zi,
                          zj_wfl_workflow_type_parameter zp
                    where zw.workflow_code = 'CONTRACT_INCEPT_WFL'
                      and zw.instance_status = '10'
                      and zi.workflow_type_para_id = zp.workflow_type_para_id
                      and zp.parameter_code = 'CONTRACT_ID'
                      and zw.instance_id = zi.instance_id
                      and to_char(cc.contract_id) = zi.parameter_value))or
                             (cc.contract_status = 'PENDING' and
                             cc.project_id is null))
            and ci.contract_id = cc.contract_id
            and cc.lease_start_date <= TRUNC(sysdate )
            and cc.data_class = 'NORMAL') all_num, --昨日资产台数
        nvl((select to_char(round(sum(ccc.due_amount) - sum(nvl(ccc.received_amount, 0)),2),'fm99999999999990.00')
              from con_contract_cashflow ccc
             where ccc.contract_id in
                   (select cc.contract_id
                      from con_contract cc
                     where (cc.contract_status in
                           ('INCEPT', 'REPURING', 'ETING') or
                           (cc.contract_status = 'PENDING' and
                           cc.project_id is not null and exists
                            (select 1
                                from zj_wfl_workflow_instance_v     zw,
                                     ZJ_WFL_WORKFLOW_INSTANCE_PARA  zi,
                                     zj_wfl_workflow_type_parameter zp
                               where zw.workflow_code = 'CONTRACT_INCEPT_WFL'
                                 and zw.instance_status = '10'
                                 and zi.workflow_type_para_id =
                                     zp.workflow_type_para_id
                                 and zp.parameter_code = 'CONTRACT_ID'
                                 and zw.instance_id = zi.instance_id
                                 and to_char(cc.contract_id) =
                                     zi.parameter_value))or
                             (cc.contract_status = 'PENDING' and
                             cc.project_id is null))
                       and cc.lease_start_date <= TRUNC(sysdate)
                       and cc.data_class = 'NORMAL')
          and ccc.cf_status = 'RELEASE'
               and ccc.cf_item in  (1, 8, 9,250,200,11)),
            0) all_money --昨日资产金额

   from dual

                ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>