STABLE @DL Select month(getdate()), year(getdate()),0;
alias _dlgresource={
(0,0,390,190,0);
e,(20,10,80,20, 0x1401),'месяц',(20,30,80,20,0),1;
e,(120,10,80,20, 0x1401),'Год',(120,30,80,20,0),2;
e,(20,60,350,20, 0x10),'Подразделение:',(20,80,350,20,0x4),3,{pr_look_tree},0,2,1;
};
DIALOG @DL,0x0801, '~Ведомость по зарплате';

Let pm_ := @DL:1[''];
LET ye_ := @DL:2[''];
LET pd_ := @DL:3[''];

IF pm_ < 1 OR pm_ > 12 then (FREE *; DESTROY @DL; ERROR[\3Неверно указан месяц];);
IF @DL:2 < 2008 OR @DL:2 > 2099 then (FREE *; DESTROY @DL; ERROR[\3Неверно указан год];);

select dateadd(day,-1,dateadd(month,1,convert(datetime,'ye_-pm_-01'))) INTO :end_month; --последнее число месяца отчёта
select convert(datetime,'ye_-pm_-01') INTO :beg_month;
select struct_name INTO :str_ from structs where struct_code = pd_;
select top 1 pid into zacr_ from lic where cmonth = ye_ * 12 + pm_ and p = '+'; -- признак закрытия месяца

DESTROY @DL;

LOCAL {_where_zacr := '';};
if zacr_ = 0 then alias _where_zacr := tabel else { alias _where_zacr := ytabel};

----------ограничение по подразделению
LOCAL {_where_tree := '';};
if pd_ = 0 then alias _where_tree := 
else { alias _where_tree := JOIN tree ON tree.Struct_code = s.Struct_code AND tree.Struct_Parent = pd_};

-- готовлю перечень видов оплат в различных видах для подстановки
ALIAS pp_ :=;
ALIAS st_ :=;
ALIAS np_ :=;
ALIAS ns_ :=;
ALIAS ma_ :=;
SCAN @STR
select code_pay from lic where cmonth = ye_*12+pm_ and shop in
(select struct_code from tree where Struct_Parent = pd_)
group by code_pay
having sum(summa) > 0
Execute {
let cp_ := @str:1;
let cp__ := @str:1;
ALIAS pp_ := pp_ , [cp_];
ALIAS st_ := st_ , [cp__] [float];
ALIAS np_ := np_ , cp__;
ALIAS ns_ := ns_ , sum([cp__]);
ALIAS ma_ := ma_, [cp_] [];
};
-- создаю строку с названиями ВО
ALIAS so_ :=;
ALIAS ma1_ :=;
SCAN @VO
select name_pay, code_pay from typ_pay where code_pay in (0 np_)
Execute {
let vo_ = @VO:1;
let von_ = @VO:2;
ALIAS so_ := (so_ ; 'vo_',0,15);
ALIAS ma1_ := ma1_,[von_][]'vo_';
};
-- создаю и заполняю динамическую табличку с суммами по видам  оплат
if (object_id('tempdb..#lc') is not null) Then DROP TABLE #lc;
create table #lc (pid[int],cmonth[int], SN [float], SO [float], RR [float] st_);
insert into #lc
select pid, cmonth, 0 as SN, 0 as SO, 0 as RR pp_
from (select pid, cmonth, code_pay, summa from dbo.lic where cmonth = ye_*12+pm_ and shop in
(select struct_code from tree where Struct_Parent = pd_)
) l
PIVOT
(
sum(summa)
for code_pay in ([0] pp_)
) as pvt
order by pid;

-- заполняю промежуточную таблицу для вычисления подитогов
if (object_id('tempdb..#lc_') is not null) Then DROP TABLE #lc_;

$select Q.num_tab, Q.full_name, Q.ss, Q.s, Q.a, Q.wage, Q.pl, Q.d, Q.h, #lc.*
INTO #lc_
FROM
(
select p.pid, p.num_tab, c.full_name, ss.struct_name ss, s.struct_name s, a.name_appoint a, r.wage,
sum(isnull(lr.k2,0)) as pl, -- плановые дни из прямых начислений
(case when sum(isnull(l.days,0))>128 then sum(isnull(l.days,0))-128 ELSE 0 END)  as d,
sum(isnull(l.hours,0)) as h
FROM card c join people p ON (p.auto_card = c.auto_card)
join lic l ON (l.pid = p.pid and l.cmonth=ye_*12+pm_ and code_pay in (1,3) and l.cmonth = ye_*12+pm_)
left join _where_zacr lr ON (lr.pid = p.pid and lr.cmonth = ye_*12+pm_ and lr.code_pay in (1,3))
left join pr_current r on (r.pid=p.pid and r.date_trans < end_month[''] and r.date_depart >= end_month[''])
left join structs s on (s.struct_code=r.code_struct_name)
left join structs ss on (ss.struct_code=s.struct_root) left join appointments a on (a.code_appoint=r.code_appoint)
_where_tree
group by p.pid, p.num_tab, c.full_name, ss.struct_name, s.struct_name, a.name_appoint, r.wage
) as Q
LEFT JOIN #lc ON #lc.pid = Q.pid;
-- нахожу сумму по начислениям
if (object_id('tempdb..#lc___') is not null) Then DROP TABLE #lc___;
create table #lc___ (pid [int], sm [float]);
insert into #lc___
select lic.pid, sum(lic.summa) as sm
from lic where lic.code_pay < 300 and lic.cmonth = ye_*12+pm_
group by lic.pid;
-- нахожу сумму по удержаниям
if (object_id('tempdb..#lc____') is not null) Then DROP TABLE #lc____;
create table #lc____ (pid [int], sm [float]);
insert into #lc____
select lic.pid, sum(lic.summa) as sm
from lic where lic.code_pay > 300 and lic.cmonth = ye_*12+pm_ and lic.code_pay not in (411,410)
group by lic.pid;
-- проставляю подитоги в основную таблицу
update #lc_ set SN = #lc___.sm
from #lc___ left join #lc_ ON (#lc___.pid = #lc_.pid);
update #lc_ set SO = #lc____.sm
from #lc____ left join #lc_ ON (#lc____.pid = #lc_.pid);
update #lc_ set RR = SN - SO
from #lc____ left join #lc_ ON (#lc____.pid = #lc_.pid);
-- удаляю лишние столбцы
ALTER TABLE #lc_ DROP COLUMN pid ;
ALTER TABLE #lc_ DROP COLUMN cmonth ;
-- добавляю итоги
insert into #lc_ ( full_name, SN, SO, RR pp_)
select 'яя___ИТОГО:     ', sum(SN), sum(SO), sum(RR) ns_ from #lc_;

alias _brwresource=(
(0,0,0,160,0x80),0x0,0x7,Dlist,,,0,{};
C,{
"Таб.№",1,10;
"ФИО",0,30;
"Подр.верхн.ур.",0,30;
"Подразделение",0,30;
"Должность",0,30;
"Оклад",0,12;
"План",0,12;
"Дней",0,12;
"Часов",0,12;
"Начислено",0,15;
"Удержано",0,15;
'На руки',0,15
so_
;
};
);
Browser{SELECT num_tab,full_name,ss,s,a,wage[%15.2f],pl[%15.2f],d[%15.2f],h[%15.2f],SN[%15.2f],SO[%15.2f],RR[%15.2f] ma1_ FROM #lc_
order by full_name
;},0x0, "Ведомость по зарплате str_";

--browser {select * from #lc_ order by full_name},,;
free *;