Список говнокодов пользователя leo777777

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
Говнокод: по колено в коде.

Список говнокодов пользователя leo777777

SQL / Говнокод #2950
