SELECT
    CP.customer_plan_id AS id,
    P.code AS name,
    IFNULL(P.rate, 0) AS plan_rate,
    VP.code AS visit_plan_name,
    IFNULL(VP.rate, 0) AS visit_plan_rate,
    PD.max_license_count AS max_license_count,
    :license_rate AS license_plan_rate,
    CP.start_at AS created_at,
    CP.expire_at,
    NOW() AS billed_at,
    PD.payment_detail_from AS payment_detail_from,
    PD.payment_detail_to AS payment_detail_to,
    PD.paid_until AS last_payment_detail_to,
    ROUND((PD.monthsused + (PD.daysused - PD.blockeddays) / 30)* IFNULL(P.rate, 0), 2) AS plan_amount,
    ROUND((PD.monthsused + (PD.daysused - PD.blockeddays) / 30)* IFNULL(VP.rate, 0), 2) AS visit_plan_amount,
    ROUND((PD.monthsused + (PD.daysused - PD.blockeddays) / 30)* :license_rate * PD.max_license_count, 2) AS license_plan_amount,
    IF(PD.payment_detail_from < PD.payment_detail_to, PD.discount, -PD.discount) AS discount,
    ROUND((PD.monthsused + (PD.daysused - PD.blockeddays) / 30)* IFNULL(P.rate, 0), 2) + ROUND((PD.monthsused + (PD.daysused - PD.blockeddays) / 30)* IFNULL(VP.rate, 0), 2)+ ROUND((PD.monthsused + (PD.daysused - PD.blockeddays) / 30)* :license_rate * PD.max_license_count, 2) AS amount,
    IF( PD.payment_detail_from < PD.payment_detail_to,
        'pay',
        'refund' ) AS payorrefund,
    PD.monthsused AS months,
    (PD.daysused - PD.blockeddays) AS days
FROM
    (
        SELECT
            X.customer_plan_id,
            X.planid,
            X.visit_planid,
            X.max_license_count,
            MAX(X.payment_detail_from) AS payment_detail_from,
            MAX(X.payment_detail_to) AS payment_detail_to,
            MAX(X.paid_until) AS paid_until,
            MAX(lp_discount) AS discount,
            AVG(PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM X.payment_detail_to), EXTRACT(YEAR_MONTH FROM X.start_at)) - PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM X.payment_detail_from), EXTRACT(YEAR_MONTH FROM X.start_at )) ) AS monthsused ,
            AVG(DATEDIFF(X.payment_detail_to, DATE_ADD(X.start_at, INTERVAL (PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM X.payment_detail_to), EXTRACT(YEAR_MONTH FROM X.start_at))) MONTH )) - DATEDIFF( X.payment_detail_from, DATE_ADD(X.start_at, INTERVAL (PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM X.payment_detail_from), EXTRACT(YEAR_MONTH FROM X.start_at))) MONTH )) ) AS daysused,
            IFNULL(SUM( IF (X.payment_detail_from < X.payment_detail_to, DATEDIFF(LEAST(X.payment_detail_to, COALESCE(b.unblock_date, NOW()) ), GREATEST(X.payment_detail_from, b.block_date)) , -1 * DATEDIFF(LEAST(X.payment_detail_from, COALESCE(b.unblock_date, X.payment_detail_from) ), GREATEST(X.payment_detail_to, b.block_date)) )), 0) AS blockeddays
        FROM
            (
                SELECT
                    cp.*,
                    COALESCE(lp.payment_detail_to, cp.start_at) payment_detail_from,
                    COALESCE(cp.expire_at, :to_param, IF(cp.expire_at < :to_param, :to_param, cp.expire_at)) payment_detail_to,
                    lp.payment_detail_to AS paid_until,
                    lp.discount AS lp_discount
                FROM
                    customerplan cp
                        LEFT JOIN (
                        SELECT
                            pd.customer_plan_id,
                            MAX(p.billed_at) lastbilldate,
                            MAX(pd.payment_detail_to) payment_detail_to,
                            SUM(pd.discount) AS discount
                        FROM
                            customer c
                                INNER JOIN payment p ON
                                    c.customer_id = p.customer_id
                                INNER JOIN paymentdetail pd ON
                                        p.payment_id = pd.payment_id
                                    AND p.customer_id = :customer_param
                                    AND p.completed = TRUE
                        GROUP BY
                            pd.customer_plan_id) lp ON
                            cp.customer_plan_id = lp.customer_plan_id
                WHERE
                        cp.customer_id = :customer_param
                  AND cp.start_at <= :to_param
                  AND (cp.expire_at > lp.lastbilldate
                    OR cp.expire_at IS NULL
                    OR lp.lastbilldate IS NULL)) X
                LEFT JOIN blockhistory b ON
                        X.customer_plan_id = b.customer_plan_id
                    AND NOT ((b.unblock_date IS NOT NULL
                    AND b.unblock_date < LEAST(X.payment_detail_from , X.payment_detail_to ) )
                    OR (b.block_date > GREATEST(X.payment_detail_from,
                                                X.payment_detail_to)))
        GROUP BY
            X.customer_plan_id,
            X.planid,
            X.visit_planid,
            X.max_license_count) AS PD
        LEFT JOIN plan P ON
            PD.planid = P.plan_id
        LEFT JOIN visit_plans VP ON
            PD.visit_planid = VP.plan_id
        LEFT JOIN customerplan CP ON
            PD.customer_plan_id = CP.customer_plan_id