create view vi_dealer_collection as (
SELECT T.PROJECT_NO           AS PROJECT_NO,
       T.CONTRACT_NO          AS CONTRACT_NO,
       T.FULLNAME             AS FULLNAME,
       T.MOBILE               AS MOBILE,
       T.NAME                 AS NAME,
       T.TEL                  AS TEL,
       T.OVER_PRINCIPAL       AS OVER_PRINCIPAL,
       T.OVER_INTEREST        AS OVER_INTEREST,
       T.OVER_PENALTY         AS OVER_PENALTY,
       T.OVER_MONEY           AS OVER_MONEY,
       T.CORPUS_OVERS         AS CORPUS_OVERS,
       T.INTEREST_OVERS       AS INTEREST_OVERS,
       T.RENT_OVERS           AS RENT_OVERS,
       T.DISTRIBUTOR_NAME     AS DISTRIBUTOR_NAME,
       T.DISTRIBUTOR_NO       AS DISTRIBUTOR_NO,
       T.LICENSE_PLATE_NUMBER AS LICENSE_PLATE_NUMBER,
       T.OVER_DAYS            AS OVER_DAYS,
       T.RETURN_PERIOD        AS RETURN_PERIOD,
       T.OVER_LIST            AS OVER_LIST,
       T.OVER_NUM             AS OVER_NUM,
       T.CERTID               AS CERTID,
       T.CERTID1              AS CERTID1
FROM (
    SELECT O.PROJECT_MANAGE                   AS PROJECT_MANAGE,
           LPI.PROJECT_NO                     AS PROJECT_NO,
           O.CONTRACT_NO                      AS CONTRACT_NO,
           CP.FULLNAME                        AS FULLNAME,
           CP.MOBILE                          AS MOBILE,
           CF.NAME                            AS NAME,
           CF.TEL                             AS TEL,
           (VO.OVER_MONEY - VO.OVER_INTEREST) AS OVER_PRINCIPAL,
           VO.OVER_INTEREST                   AS OVER_INTEREST,
           VO.OVER_PENALTY                    AS OVER_PENALTY,
           VO.OVER_MONEY                      AS OVER_MONEY,
           Z.CORPUS_OVERS                     AS CORPUS_OVERS,
           Z.INTEREST_OVERS                   AS INTEREST_OVERS,
           Z.RENT_OVERS                       AS RENT_OVERS,
           DI.DISTRIBUTOR_NAME                AS DISTRIBUTOR_NAME,
           DI.DISTRIBUTOR_NO                  AS DISTRIBUTOR_NO,
           LEC.LICENSE_PLATE_NUMBER           AS LICENSE_PLATE_NUMBER,
           VO.OVER_DAYS                       AS OVER_DAYS,
           ifnull(Q.OVER_NUM, '0')            AS RETURN_PERIOD,
           ifnull(ZJ.TOTAL, '0')              AS OVER_LIST,
           Q.OVER_NUM                         AS OVER_NUM,
           CP.CERTID                          AS CERTID,
           CF.CERTID                          AS CERTID1,
           CF.PARTNER_                        AS PARTNER_
    FROM (((((((((((APZL.LB_CONTRACT_INFO O LEFT JOIN APZL.LB_PROJECT_INFO LPI ON ((convert(O.PROJECT_ID USING UTF8) = LPI.ID))) LEFT JOIN APZL.LB_UNION_LESSEE LUL ON ((O.PROJECT_ID = LUL.PROJECT_ID))) LEFT JOIN APZL.CUSTOMER_PERSON CP ON ((CP.CUSTOMERID = LUL.CUSTOMER_ID))) LEFT JOIN APZL.CUSTOMER_FAMILY CF ON (((O.PROJECT_ID = CF.PROJECT_ID) AND (CF.PARTNER_ = 'Y')))) LEFT JOIN APZL.DISTRIBUTOR_INFO DI ON ((DI.DISTRIBUTOR_NO = O.DISTRIBUTOR_ID))) LEFT JOIN APZL.LB_EQUIPMENT_CAR LEC ON ((convert(O.FLOWUNID USING UTF8) = LEC.FLOWUNID))) LEFT JOIN APZL.VI_OVERDUE_RENT_PLAN_SUM VO ON ((VO.CONTRACT_ID = O.ID))) LEFT JOIN APZL.LC_CALC_CONDITION LCC ON ((O.ID = LCC.CONTRACT_ID))) LEFT JOIN (
        SELECT APZL.VI_LC_RENT_PLAN.CONTRACT_ID        AS CONTRACT_ID,
               sum(APZL.VI_LC_RENT_PLAN.CORPUS_OVER)   AS CORPUS_OVERS,
               sum(APZL.VI_LC_RENT_PLAN.INTEREST_OVER) AS INTEREST_OVERS,
               sum(APZL.VI_LC_RENT_PLAN.RENT_OVER)     AS RENT_OVERS
        FROM APZL.VI_LC_RENT_PLAN
        GROUP BY APZL.VI_LC_RENT_PLAN.CONTRACT_ID
    ) Z ON ((Z.CONTRACT_ID = O.ID))) LEFT JOIN (
        SELECT APZL.VI_OVERDUE_RENT_PLAN.CONTRACT_ID AS CONTRACT_ID, count(APZL.VI_OVERDUE_RENT_PLAN.PLAN_LIST) AS OVER_NUM
        FROM APZL.VI_OVERDUE_RENT_PLAN
        WHERE (APZL.VI_OVERDUE_RENT_PLAN.PLAN_STATUS = '已结清')
        GROUP BY APZL.VI_OVERDUE_RENT_PLAN.CONTRACT_ID
    ) Q ON ((Q.CONTRACT_ID = O.ID)))
    LEFT JOIN (
        SELECT APZL.VI_OVERDUE_RENT_PLAN.CONTRACT_ID AS CONTRACT_ID, count(APZL.VI_OVERDUE_RENT_PLAN.PLAN_LIST) AS TOTAL
        FROM APZL.VI_OVERDUE_RENT_PLAN
        WHERE (APZL.VI_OVERDUE_RENT_PLAN.PLAN_STATUS = '未结清')
        GROUP BY APZL.VI_OVERDUE_RENT_PLAN.CONTRACT_ID
    ) ZJ ON ((ZJ.CONTRACT_ID = O.ID)))
    WHERE ((VO.OVER_MONEY > 0) AND (O.CONTRACT_STATUS = '31'))
) T);

-- comment on column vi_dealer_collection.project_no not supported: 项目编号

-- comment on column vi_dealer_collection.CONTRACT_NO not supported: 合同号

-- comment on column vi_dealer_collection.FULLNAME not supported: 姓名

-- comment on column vi_dealer_collection.mobile not supported: 手机号

-- comment on column vi_dealer_collection.NAME not supported: 姓名

-- comment on column vi_dealer_collection.tel not supported: 联系电话

-- comment on column vi_dealer_collection.distributor_name not supported: 经销商名称

-- comment on column vi_dealer_collection.distributor_no not supported: 经销商编码

-- comment on column vi_dealer_collection.LICENSE_PLATE_NUMBER not supported: 车牌号码

-- comment on column vi_dealer_collection.CERTID not supported: 证件号码

-- comment on column vi_dealer_collection.certid1 not supported: 证件号码