MobiVoc pricing plans

SELECT x.plan_name, x.plan_date_from, x.plan_date_to,

       x.max_vocabulary, x.setup_date_from, x.setup_date_to, x.max_vocabulary, x.max_count_of_wordpairs_in_vocabulary, x.vocabulary_incrementum, x.wordpair_incrementum,

        ROUND(x.vocabulary_incrementum * x.price_of_one_wordpair_in_cents * x.max_count_of_wordpairs_in_vocabulary, 2) full_price_in_cents,

       x.discount_percent,

       ROUND(x.vocabulary_incrementum * x.price_of_one_wordpair_in_cents * x.max_count_of_wordpairs_in_vocabulary * (100 - x.discount_percent) / 100, 0) end_price_in_cents

 

FROM (

         SELECT pp.plan_id, pp.plan_name, pp.plan_date_from, pp.plan_date_to, pp.id, pp.setup_date_from, pp.setup_date_to, pp.max_vocabulary, pp.max_count_of_wordpairs_in_vocabulary, pp.price_of_one_wordpair_in_cents, pp.discount_percent, pp.based_on,

                bp.base_plan_id, bp.base_plan_name, bp.base_plan_date_from, bp.base_plan_date_to, bp.base_id, bp.setup_base_date_from, bp.setup_base_date_to,  bp.base_max_vocabulary, bp.base_max_count_of_wordpairs_in_vocabulary, bp.base_price_of_one_wordpair_in_cents,

                bp.base_discount_percent, bp.base_based_on,

                COALESCE(pp.max_vocabulary - bp.base_max_vocabulary, 0) vocabulary_incrementum,

                COALESCE(pp.max_count_of_wordpairs_in_vocabulary * (pp.max_vocabulary - bp.base_max_vocabulary), 0) wordpair_incrementum

         FROM

             (SELECT p.id plan_id, p.name plan_name, p.date_from plan_date_from, p.date_to plan_date_to, s.id, s.date_from setup_date_from, s.date_to setup_date_to, s.max_vocabulary, s.max_count_of_wordpairs_in_vocabulary, s.price_of_one_wordpair_in_cents,

                    s.discount_percent, s.based_on

              FROM trs_subscription_db.pricing_plan P

                       LEFT JOIN trs_subscription_db.pricing_plan_setup S ON p.id = s.plan_id) PP

 

                 LEFT JOIN      (SELECT p.id base_plan_id, p.name base_plan_name, p.date_from base_plan_date_from, p.date_to base_plan_date_to, s.id base_id, s.date_from setup_base_date_from, s.date_to setup_base_date_to,

                                      s.max_vocabulary base_max_vocabulary, s.max_count_of_wordpairs_in_vocabulary base_max_count_of_wordpairs_in_vocabulary, s.price_of_one_wordpair_in_cents base_price_of_one_wordpair_in_cents,

                                      s.discount_percent base_discount_percent, s.based_on base_based_on

                               FROM trs_subscription_db.pricing_plan P

                                        LEFT JOIN trs_subscription_db.pricing_plan_setup S ON p.id = s.plan_id) BP ON pp.based_on = bp.base_plan_id

         ORDER BY plan_id, plan_date_from, plan_date_to) X

WHERE UNIX_TIMESTAMP() BETWEEN plan_date_from AND plan_date_to

AND UNIX_TIMESTAMP() BETWEEN setup_date_from AND setup_date_to

ORDER BY end_price_in_cents