- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
CREATE procedure dbo.BASE_W_ORDER_NEYAVKAM
@date_in as nvarchar(10),
@id_users as int
as
declare @date as datetime
set @date = convert(datetime, @date_in, 104)
--set @date = convert(datetime, cast(@date_in as datetime), 104)
select* from(
select
p.[ID] as [ID],
p.TABEL_NUM as TABEL_NUM,
p.FAMILY as FAMILY,
p.[NAME] as NAME,
p.SECNAME as SECNAME,
p.ID_DEPT as ID_DEPT,
d.[NAME] as DEPT,
p.POSITION as POSITION,
--dbo.GetCmena(p.ID_GRAPH, convert(nvarchar(10), getdate(), 104)) as SID,
s.[id] as SID,
s.[NAME] as SMENA,
s.START_SM as START_SM,
s.END_SM as END_SM,
s.START_DIN as START_DIN,
s.END_DIN as END_DIN,
p.PHOTO as PHOTO,
-- формируем отчет для тех
-- если приказ не назначен
case when not exists(select*from base_codes_s cc where cc.[id] = p.id_codes and cc.status like '0_1_____') then
-- у кого график назначен
case when (isnull(p.id_graph,0) = 0) then 1
-- у кого код пропуска присвоен
when (isnull(p.id_Codes,0) = 0) then 1
-- пропуск не имеет статус администратора
when exists(select*from base_codes_s cc where cc.[id] = p.id_codes and cc.status like '0_1_____') then 1
-- пропуск не просрочен
when (@date < isnull(c.date_in, @date)
or @date > isnull(c.date_out, @date)) then 1
-- Сотрудник в отпуске
when exists(select*from TABL_OTPUSK o where o.ID_PERSONAL = p.id_codes and convert(nvarchar(10), o.[DATE], 104) = convert(nvarchar(10), @date, 104)) then 1
-- празднечный день
when exists(select*from TABL_HOLIDAY_S hol where convert(nvarchar(10), hol.[DATE], 104) = convert(nvarchar(10), @date, 104)) then 1
else 0 end
else
-- делаем расчеты в соответствии с приказом
-- у кого график назначен
case when (isnull(p.id_graph,0) = 0) then 1
-- у кого код пропуска присвоен
when (isnull(p.id_Codes,0) = 0) then 1
-- пропуск не имеет статус администратора
when exists(select*from base_codes_s cc where cc.[id] = p.id_codes and cc.status like '0_1_____') then 1
-- пропуск не просрочен
when (@date < isnull(c.date_in, @date)
or @date > isnull(c.date_out, @date)) then 1
-- Сотрудник в отпуске
when exists(select*from TABL_OTPUSK o where o.ID_PERSONAL = p.id_codes and convert(nvarchar(10), o.[DATE], 104) = convert(nvarchar(10), @date, 104)) then 1
-- празднечный день
when exists(select*from TABL_HOLIDAY_S hol where convert(nvarchar(10), hol.[DATE], 104) = convert(nvarchar(10), @date, 104)) then 1
else 0 end end as XREN
from base_pesonal_S p
-- присоединяем пропуска
left join base_codes_s c on
p.id_codes = c.[id]
-- присоединяем отделы
left join base_dept d on
p.id_dept = d.[id]
-- Узнаем смену назначенную на день отчета для графика который назначен сотруднику
LEFT OUTER join BASE_SMENA_S s ON
s.[id] = (select top 1 ID_SM
from
BASE_GRAPH as g
inner join BASE_GRAPH_NAME_S as gn on gn.[id] = g.ID_GRAPH_NAME
inner join BASE_SMENA as s on s.[id] = g.id_SM
inner join (select BASE_GRAPH.ID_GRAPH_NAME, count(*) cntsmen from BASE_GRAPH group by BASE_GRAPH.ID_GRAPH_NAME) sm
on (datediff(dd, gn.date_in, convert(datetime, @date, 104)) % sm.cntsmen) + 1 = g.[num] and g.ID_GRAPH_NAME = sm.ID_GRAPH_NAME
where g.id_graph_name = p.ID_GRAPH)
) as t1
-- смена не должна быть выходной
where XREN <> 1
and SID <> 1
and not exists(select top 1 * from base_events e where id_p = [id]
and convert(nvarchar(10), e.[TIME], 104) = convert(nvarchar(10), @date, 104)
and (ascii(e.CODE) = ascii('I')
or ascii(e.CODE) = ascii('O')
)
)
and id_dept in(select id_dept from BASE_ACCEESS where id_users = @id_users) -- отчет только для интерисующих на отделов
GO
Во. У меня все.
Прочел таки... Ну да... здоровый селект... и шо?