SELECT
    bb.yhpcbh,
    bb.yhxm,
    bb.jmsl,
    bb.jmcb,
    bb.jmje,
    bb.xlsl,
    ROUND(bb.xlcbdj, 2) AS xlcbdj,
    bb.xlsje,
    bb.zlsl,
    ROUND(bb.zlcbdj, 2) AS zlcbdj,
    bb.zlsje,
    bb.dlsl,
    ROUND(bb.dlcbdj, 2) AS dlcbdj,
    bb.dlsje,
    ROUND(
        (bb.xlsje + bb.zlsje + bb.dlsje),
        2
    ) AS lcbze,
    ROUND(bb.zyf, 2) AS zyf,
    ROUND(bb.dyf, 2) AS dyf,
    ROUND(bb.jmje / bb.sszl, 2) AS jjmcb,
    ROUND(
        (bb.xlsje + bb.zlsje + bb.dlsje) / bb.sszl,
        2
    ) AS jjlcb,
    ROUND(bb.zyf / bb.sszl, 2) AS jjycb,
    ROUND(bb.dyf / bb.sszl, 2) AS jjdyfcb,
    ROUND(
        (
            bb.jmje / bb.sszl + (bb.xlsje + bb.zlsje + bb.dlsje) / bb.sszl + bb.zyf / bb.sszl + bb.dyf / bb.sszl
        ),
        2
    ) AS jjzjcb,
    ROUND(bb.jmje / bb.sszs, 2) AS zjmcb,
    ROUND(
        (bb.xlsje + bb.zlsje + bb.dlsje) / bb.sszs,
        2
    ) AS zjlcb,
    ROUND(bb.zyf / bb.sszs, 2) AS zjycb,
    ROUND(bb.dyf / bb.sszs, 2) AS zjdyfcb,
    ROUND(
        (
            bb.jmje / bb.sszs + (bb.xlsje + bb.zlsje + bb.dlsje) / bb.sszs + bb.zyf / bb.sszs + bb.dyf / bb.sszs
        ),
        2
    ) AS zjzjcb,
    ROUND(
        (
            bb.jmje / bb.sszl + (bb.xlsje + bb.zlsje + bb.dlsje) / bb.sszl + bb.zyf / bb.sszl + bb.dyf / bb.sszl
        ) * bb.sszl,
        2
    ) AS zcb,
    ROUND(
        bb.zsr - (
            (
                bb.jmje / bb.sszl + (bb.xlsje + bb.zlsje + bb.dlsje) / bb.sszl + bb.zyf / bb.sszl + bb.dyf / bb.sszl
            ) * bb.sszl
        ),
        2
    ) AS ml,
    ROUND(
        (
            bb.zsr - (
                (
                    bb.jmje / bb.sszl + (bb.xlsje + bb.zlsje + bb.dlsje) / bb.sszl + bb.zyf / bb.sszl + bb.dyf / bb.sszl
                ) * bb.sszl
            )
        ) / bb.sszs,
        2
    ) AS zml,
    bb.sszs,
    ROUND(bb.sszl, 2) AS sszl,
    ROUND(bb.xsjj, 2) AS xsjj,
    ROUND(bb.zsr, 2) AS zsr,
    ROUND(bb.ssjz, 2) AS ssjz
FROM
    (
        SELECT
            tmps.yhpcbh AS yhpcbh,
            tmps.yhxm AS yhxm,
            (
                SELECT
                    SUM(`tmpl`.`receivenumber`)
                FROM
                    `leadregister` `tmpl`
                WHERE
                    `tmpl`.`code` = `tmps`.`yhpcbh`
                AND `tmpl`.`leadregistertype` = '苗'
            ) AS jmsl,
            ROUND(
                (
                    SELECT
                        SUM(
                            (
                                SELECT
                                    AVG(
                                        `tmpr`.purchasemoney / (
                                            COALESCE (`tmpr`.discount) + `tmpr`.purchasequantity
                                        )
                                    )
                                FROM
                                    `receiving` `tmpr`
                                WHERE
                                    `tmpr`.`mattercode` = `tmpl`.`materielcode`
                                AND `tmpr`.`receivingtime` = `tmpl`.`leadregistertime`
                            )
                        )
                    FROM
                        `leadregister` `tmpl`
                    WHERE
                        `tmpl`.`code` = `tmps`.`yhpcbh`
                    AND `tmpl`.leadregistertype = '苗'
                ),
                2
            ) AS jmcb,
            ROUND(
                (
                    SELECT
                        SUM(
                            (
                                `tmpl`.`receivenumber` * (
                                    SELECT
                                        AVG(
                                            `tmpr`.purchasemoney / (
                                                COALESCE (`tmpr`.discount, 0) + `tmpr`.purchasequantity
                                            )
                                        )
                                    FROM
                                        `receiving` `tmpr`
                                    WHERE
                                        `tmpr`.type = '苗'
                                    AND `tmpr`.`mattercode` = `tmpl`.`materielcode`
                                    AND `tmpr`.`receivingtime` = `tmpl`.`leadregistertime`
                                )
                            )
                        )
                    FROM
                        `leadregister` `tmpl`
                    WHERE
                        `tmpl`.`code` = `tmps`.`yhpcbh`
                    AND `tmpl`.leadregistertype = '苗'
                ),
                2
            ) AS jmje,
            (
                SELECT
                    SUM(`tmpl`.`receivenumber`)
                FROM
                    `leadregister` `tmpl`
                WHERE
                    `tmpl`.`code` = `tmps`.`yhpcbh`
                AND `tmpl`.`materielgoods` = '小鸡料'
            ) AS xlsl,
            (
                SELECT
                    AVG(
                        (
                            SELECT
                                AVG(
                                    `tmpr`.purchasemoney / (
                                        COALESCE (`tmpr`.discount, 0) + `tmpr`.purchasequantity
                                    )
                                )
                            FROM
                                `receiving` `tmpr`
                            WHERE
                                `tmpr`.`mattercode` = `tmpl`.`materielcode`
                            AND `tmpr`.`receivingtime` <= tmpll.maxtime
                            AND `tmpr`.`receivingtime` >= tmpll.mintime
                        )
                    )
                FROM
                    `leadregister` `tmpl`
                WHERE
                    `tmpl`.`code` = `tmps`.`yhpcbh`
                AND `tmpl`.`materielgoods` = '小鸡料'
            ) AS xlcbdj,
            (
                SELECT
                    SUM(
                        (
                            `tmpl`.`receivenumber` * (
                                SELECT
                                    AVG(
                                        `tmpr`.purchasemoney / (
                                            COALESCE (`tmpr`.discount, 0) + `tmpr`.purchasequantity
                                        )
                                    )
                                FROM
                                    `receiving` `tmpr`
                                WHERE
                                    `tmpr`.`mattercode` = `tmpl`.`materielcode`
                                AND `tmpr`.`receivingtime` <= tmpll.maxtime
                                AND `tmpr`.`receivingtime` >= tmpll.mintime
                            )
                        )
                    )
                FROM
                    `leadregister` `tmpl`
                WHERE
                    `tmpl`.`code` = `tmps`.`yhpcbh`
                AND `tmpl`.`materielgoods` = '小鸡料'
            ) AS xlsje,
            (
                SELECT
                    SUM(`tmpl`.`receivenumber`)
                FROM
                    `leadregister` `tmpl`
                WHERE
                    `tmpl`.`code` = `tmps`.`yhpcbh`
                AND `tmpl`.`materielgoods` = '中鸡料'
            ) AS zlsl,
            (
                SELECT
                    AVG(
                        (
                            SELECT
                                AVG(
                                    `tmpr`.purchasemoney / (
                                        COALESCE (`tmpr`.discount, 0) + `tmpr`.purchasequantity
                                    )
                                )
                            FROM
                                `receiving` `tmpr`
                            WHERE
                                `tmpr`.`mattercode` = `tmpl`.`materielcode`
                            AND `tmpr`.`receivingtime` <= tmpll.maxtime
                            AND `tmpr`.`receivingtime` >= tmpll.mintime
                        )
                    )
                FROM
                    `leadregister` `tmpl`
                WHERE
                    `tmpl`.`code` = `tmps`.`yhpcbh`
                AND `tmpl`.`materielgoods` = '中鸡料'
            ) AS zlcbdj,
            (
                SELECT
                    SUM(
                        (
                            `tmpl`.`receivenumber` * (
                                SELECT
                                    AVG(
                                        `tmpr`.purchasemoney / (
                                            COALESCE (`tmpr`.discount, 0) + `tmpr`.purchasequantity
                                        )
                                    )
                                FROM
                                    `receiving` `tmpr`
                                WHERE
                                    `tmpr`.`mattercode` = `tmpl`.`materielcode`
                                AND `tmpr`.`receivingtime` <= tmpll.maxtime
                                AND `tmpr`.`receivingtime` >= tmpll.mintime
                            )
                        )
                    )
                FROM
                    `leadregister` `tmpl`
                WHERE
                    `tmpl`.`code` = `tmps`.`yhpcbh`
                AND `tmpl`.`materielgoods` = '中鸡料'
            ) AS zlsje,
            (
                SELECT
                    SUM(`tmpl`.`receivenumber`)
                FROM
                    `leadregister` `tmpl`
                WHERE
                    `tmpl`.`code` = `tmps`.`yhpcbh`
                AND `tmpl`.`materielgoods` = '大鸡料'
            ) AS dlsl,
            (
                SELECT
                    AVG(
                        (
                            SELECT
                                AVG(
                                    `tmpr`.purchasemoney / (
                                        COALESCE (`tmpr`.discount, 0) + `tmpr`.purchasequantity
                                    )
                                )
                            FROM
                                `receiving` `tmpr`
                            WHERE
                                `tmpr`.`mattercode` = `tmpl`.`materielcode`
                            AND `tmpr`.`receivingtime` <= tmpll.maxtime
                            AND `tmpr`.`receivingtime` >= tmpll.mintime
                        )
                    )
                FROM
                    `leadregister` `tmpl`
                WHERE
                    `tmpl`.`code` = `tmps`.`yhpcbh`
                AND `tmpl`.`materielgoods` = '大鸡料'
            ) AS dlcbdj,
            (
                SELECT
                    SUM(
                        (
                            `tmpl`.`receivenumber` * (
                                SELECT
                                    AVG(
                                        `tmpr`.purchasemoney / (
                                            COALESCE (`tmpr`.discount, 0) + `tmpr`.purchasequantity
                                        )
                                    )
                                FROM
                                    `receiving` `tmpr`
                                WHERE
                                    `tmpr`.`mattercode` = `tmpl`.`materielcode`
                                AND `tmpr`.`receivingtime` <= tmpll.maxtime
                                AND `tmpr`.`receivingtime` >= tmpll.mintime
                            )
                        )
                    )
                FROM
                    `leadregister` `tmpl`
                WHERE
                    `tmpl`.`code` = `tmps`.`yhpcbh`
                AND `tmpl`.`materielgoods` = '大鸡料'
            ) AS dlsje,
            (
                SELECT
                    SUM(
                        (
                            `tmpl`.`receivenumber` * (
                                SELECT
                                    AVG(
                                        `tmpr`.purchasemoney / (
                                            COALESCE (`tmpr`.discount, 0) + `tmpr`.purchasequantity
                                        )
                                    )
                                FROM
                                    receiving tmpr
                                WHERE
                                    tmpr.mattercode = `tmpl`.materielcode
                            )
                        )
                    )
                FROM
                    `leadregister` `tmpl`
                WHERE
                    `tmpl`.`code` = `tmps`.`yhpcbh`
                AND `tmpl`.`leadregistertype` = '药'
            ) AS zyf,
            COALESCE (
                ROUND(
                    (
                        SELECT
                            SUM(`tmpl`.`receivenumber`)
                        FROM
                            `leadregister` `tmpl`
                        WHERE
                            `tmpl`.`code` = `tmps`.`yhpcbh`
                        AND `tmpl`.`leadregistertype` = '苗'
                    ) * 2.6,
                    0
                ),
                0
            ) AS dyf,
            tempwl.zs AS sszs,
            tempwl.zl AS sszl,
            tempwl.je / tempwl.zl AS xsjj,
            tempwl.je AS zsr,
            tempwl.jz AS ssjz
        FROM
            settlementparam tmps
        LEFT JOIN (
            SELECT
                stmpl.`code`,
                MIN(stmpl.leadregistertime) mintime,
                MAX(stmpl.leadregistertime) maxtime
            FROM
                leadregister stmpl
            GROUP BY
                stmpl.`code`
        ) tmpll ON tmpll.`code` = tmps.yhpcbh
        LEFT JOIN (
            SELECT
                wl.yhbh,
                SUM(wl.je) AS je,
                SUM(wl.zs) AS zs,
                SUM(wl.zl) AS zl,
                SUM(wl.zl) / SUM(wl.zs) AS jz
            FROM
                weightlist wl
            GROUP BY
                wl.yhbh
        ) tempwl ON tempwl.yhbh = tmps.yhpcbh
    ) bb