/* БалБанк до 16 года (14-15гг.) = БалБанк безнадега
*/

select distinct
    BB1415.id as 'ББ 14 15 год'
    ,bez.id as 'Безнадега'
    ,BB1415.FIO as 'ФИО'
    ,BB1415.contract as 'Договор 14 15'
    ,bez.contract as 'Договор без-га'
    ,BB1415.Pasport as 'Пасспорт'
    ,BB1415.name as 'Портфель ББ'
    ,BB1415.sign_date as 'Дата входа портфеля ББ'
    ,bez.name as 'Портфель Без-га'
    ,bez.sign_date as 'Дата входа портфеля Без-га'
    ,BB1415.uFIO as 'Оператор ББ'
    ,bez.uFIO as 'Оператор Без-га'
    ,BB1415.debt_sum as 'Остаток ББ'
    ,bez.debt_sum as 'Остаток Без-га'
/*  ,BB1415.exp_basic_sum as 'просроченный основной долг ПГ'
    ,bez.exp_basic_sum as 'просроченный основной долг ЭГ'
    ,BB1415.penalty_sum as 'штрафы ПГ'
    ,bez.penalty_sum as 'штрафы ЭГ'
    ,BB1415.percent_sum as 'проЭГенты ПГ'
    ,bez.percent_sum as 'проЭГенты ЭГ'
    ,BB1415.peni_sum as 'пени ПГ'
    ,bez.peni_sum as 'пени ЭГ'
    ,BB1415.commission_sum as 'коммиссия ПГ'
    ,bez.commission_sum as 'коммиссия ЭГ'
    ,BB1415.dt as 'дата крайнего платежа ПГ'
    ,bez.dt as 'дата крайнего платежа ЭГ'
    ,BB1415.sum as 'сумма крайнего платежа ПГ'
    ,bez.sum as 'сумма крайнего платежа ЭГ'
*/
from
    bank as b
    inner join (
        select
            p.parent_id
            ,per.f+' '+per.i+' '+per.o as FIO
            ,d.id
            ,pass.series+' '+pass.number as Pasport
            ,d.contract
            ,d.debt_sum
            ,d.exp_basic_sum
            ,d.percent_sum
            ,d.exp_percent_sum
            ,d.penalty_sum
            ,d.peni_sum
            ,d.commission_sum
            ,c.dt
            ,c.sum
            ,p.name
            ,p.sign_date
            ,u.uFIO
        from
            portfolio as p
            inner join debt as d on p.id = d.r_portfolio_id
            inner join person as per on d.parent_id = per.id
            inner join passport as pass on pass.id = per.r_passport_id

-- c -- платежи
            left join (
                    select dc.parent_id, dc.sum, dc.dt
                    from debt_calc dc
                    where dc.id in (
                            select max(id)
                            from debt_calc
                            group by parent_id
                    )
                    and dc.is_confirmed = 1
                    and dc.is_cancel = 0
            ) c on c.parent_id = d.id

-- u -- пользователи
            inner join(
                select wt.r_debt_id, u.f+' '+u.i+' '+u.o as uFIO
                from work_task wt
                    left join users u on u.id = wt.r_user_id
            ) as u on u.r_debt_id = d.id
-- условие
        where
            p.parent_id = 49
            and cast(p.sign_date as date) between '01-01-2014' and '31-12-2015'
        ) BB1415
            on b.id = BB1415.parent_id



    inner join(
        select
            p.parent_id
            ,d.id
            ,per.f+' '+per.i+' '+per.o as FIO
            ,pass.series+' '+pass.number as Pasport
            ,d.contract
            ,d.debt_sum
            ,d.exp_basic_sum
            ,d.percent_sum
            ,d.exp_percent_sum
            ,d.penalty_sum
            ,d.peni_sum
            ,d.commission_sum
            ,c.dt
            ,c.sum
            ,p.name
            ,p.sign_date
            ,u.uFIO
        from
            portfolio as p
            inner join debt as d on p.id = d.r_portfolio_id
            inner join person as per on d.parent_id = per.id
            inner join passport as pass on pass.id = per.r_passport_id

-- c -- платежи
            left join (
                select dc.parent_id, dc.sum, dc.dt
                from debt_calc dc
                where dc.id in (
                        select max(id)
                        from debt_calc
                        group by parent_id
                )
                and dc.is_confirmed = 1
                and dc.is_cancel = 0
            ) c on c.parent_id = d.id

-- u -- пользователи
            inner join(
                select wt.r_debt_id, u.f+' '+u.i+' '+u.o as uFIO
                from work_task wt
                    left join users u on u.id = wt.r_user_id
            ) as u on u.r_debt_id = d.id
-- условие
        where
            p.parent_id = 49
            and p.name like '%БАЛТБАНК_безнадёга%'
            --and cast(p.sign_date as date) >= '01-01-2016'

        ) as bez on
            bez.parent_id = b.id
where
    BB1415.FIO = bez.FIO
    and BB1415.contract = bez.contract






/* БалБанк до 16 года = БалБанк после 16 года
*/

select distinct
    bez.id as 'Прошлый год'
    ,cess.id as 'Этот год'
    ,bez.FIO as 'ФИО'
    ,bez.Pasport as 'Пасспорт'
    ,bez.name as 'Портфель ПГ'
    ,bez.sign_date as 'Дата входа портфеля ПГ'
    ,cess.name as 'Портфель ЭГ'
    ,cess.sign_date as 'Дата входа портфеля ЭГ'
    ,bez.contract as 'Договор ПГ'
    ,cess.contract as 'Договор ЭГ'
    ,bez.debt_sum as 'cумма неоПГходимая к погашению ПГ'
    ,cess.debt_sum as 'cумма неоПГходимая к погашению ЭГ'
    ,bez.exp_basic_sum as 'просроченный основной долг ПГ'
    ,cess.exp_basic_sum as 'просроченный основной долг ЭГ'
    ,bez.penalty_sum as 'штрафы ПГ'
    ,cess.penalty_sum as 'штрафы ЭГ'
    ,bez.percent_sum as 'проЭГенты ПГ'
    ,cess.percent_sum as 'проЭГенты ЭГ'
    ,bez.peni_sum as 'пени ПГ'
    ,cess.peni_sum as 'пени ЭГ'
    ,bez.commission_sum as 'коммиссия ПГ'
    ,cess.commission_sum as 'коммиссия ЭГ'
    ,bez.dt as 'дата крайнего платежа ПГ'
    ,cess.dt as 'дата крайнего платежа ЭГ'
    ,bez.sum as 'сумма крайнего платежа ПГ'
    ,cess.sum as 'сумма крайнего платежа ЭГ'
from
    bank as b
    inner join (
        select
            p.parent_id
            ,per.f+' '+per.i+' '+per.o as FIO
            ,d.id
            ,pass.series+' '+pass.number as Pasport
            ,d.contract
            ,d.debt_sum
            ,d.exp_basic_sum
            ,d.percent_sum
            ,d.exp_percent_sum
            ,d.penalty_sum
            ,d.peni_sum
            ,d.commission_sum
            ,c.dt
            ,c.sum
            ,p.name
            ,p.sign_date
        from
            portfolio as p
            inner join debt as d on p.id = d.r_portfolio_id
            inner join person as per on d.parent_id = per.id
            inner join passport as pass on pass.id = per.r_passport_id
            left join (
                    select dc.parent_id, dc.sum, dc.dt
                    from debt_calc dc
                    where dc.id in (
                            select max(id)
                            from debt_calc
                            group by parent_id
                    )
                    and dc.is_confirmed = 1
                    and dc.is_cancel = 0
            ) c on c.parent_id = d.id
        where
            p.parent_id = 49
            and cast(p.sign_date as date) <= '31-12-2015'
        ) bez
            on b.id = bez.parent_id

    inner join(
        select
            p.parent_id
            ,d.id
            ,per.f+' '+per.i+' '+per.o as FIO
            ,pass.series+' '+pass.number as Pasport
            ,d.contract
            ,d.debt_sum
            ,d.exp_basic_sum
            ,d.percent_sum
            ,d.exp_percent_sum
            ,d.penalty_sum
            ,d.peni_sum
            ,d.commission_sum
            ,c.dt
            ,c.sum
            ,p.name
            ,p.sign_date
        from
            portfolio as p
            inner join debt as d on p.id = d.r_portfolio_id
            inner join person as per on d.parent_id = per.id
            inner join passport as pass on pass.id = per.r_passport_id
            left join (
                select dc.parent_id, dc.sum, dc.dt
                from debt_calc dc
                where dc.id in (
                        select max(id)
                        from debt_calc
                        group by parent_id
                )
                and dc.is_confirmed = 1
                and dc.is_cancel = 0
            ) c on c.parent_id = d.id
        where
            p.parent_id = 49
            and cast(p.sign_date as date) >= '01-01-2016'

        ) as cess on
            cess.parent_id = b.id
where
    bez.FIO = cess.FIO
    and bez.Pasport = cess.Pasport