SELECT
        surname_nominative as surname,
        name_nominative as name,
        patr_nominative as patr,
        card_birthdate as birthday,
        study_recordbook as recordbook,
        studgroups_number as studgroups,
        study_kurs as course,
        DATE_FORMAT(payment_agreement_begin, '%Y-%m-%d') as payment_begin,
        DATE_FORMAT(DATE_ADD(payment_agreement_begin, interval payment_period * 6 MONTH), '%Y-%m-%d') as payment_end,
        t_uPlan.plan_begin_date,
        t_uPlan.plan_end_date

        from study


        join student.card on card_id = study_card_id
        join name on card_name_id = name_id
        join surname on card_surname_id = surname_id
        join studgroups on studgroups_id = study_studgroups_id
        join payment on payment_study_id = study_id
        join t_uWorkPlanStudy as workPlanStudy on Study = study_id
        join t_uWorkPlan on uWorkPlan_id = WorkPlan
        join
            (
                SELECT
                t_uPlan1.uPlan_id,
                DATE_FORMAT(CONCAT(t_uPlan1.beginYear, '-09-01'), '%Y-%m-%d') as plan_begin_date,
                DATE_FORMAT(
                    DATE_ADD(
                    DATE_ADD(DATE_FORMAT(CONCAT(t_uPlan1.beginYear, '-09-01'), '%Y-%m-%d'),
                    interval t_uPlan1.duration YEAR),
                    interval t_uPlan1.durationMonth MONTH), '%Y-%m-%d') as plan_end_date
                FROM t_uPlan as t_uPlan1
                JOIN t_uPlan as t_uPlan2 on workPlanStudy.Study = student.study.study_id
                AND ( (

                    t_uPlan1.beginYear < t_uPlan2.beginYear

                    ) or (

                    t_uPlan1.beginYear = t_uPlan2.beginYear

                    ) )
             )
            t_uPlan on uPlan_id = Plan_id
        left join patr on card_patr_id = patr_id


WHERE
        payment_close = 0 and
        payment_action = 1 and
        payment_period <> 0


        group by study_id
        order by surname, payment_agreement_begin