USE [RUSCAN]
GO
/****** Object:  StoredProcedure [dbo].[Mailstats]    Script Date: 20.11.2019 21:15:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Mailstats]

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
declare @d datetime set @d=convert(datetime,getdate(),121)
declare @t datetime set @t=convert(datetime,convert(varchar(10),@d,121)+' 21:00',121)

declare @d1 datetime-- set @d1=convert(datetime,'2019-01-01 15:00',121)
declare @d2 datetime-- set @d2=convert(datetime,'2019-01-01 15:00',121)

if @D>@t
    begin
        set @d1=convert(datetime,convert(varchar(10),@d,121)+' 21:00',121)
        set @d2=convert(datetime,convert(varchar(10),@d+1,121)+' 08:59:59',121)
    end
else
    begin
        set @d1=convert(datetime,convert(varchar(10),@d,121)+' 09:00',121)
        set @d2=convert(datetime,convert(varchar(10),@d,121)+' 20:59:59',121)
    end

declare @dd1 datetime-- set @d1=convert(datetime,'2019-01-01 15:00',121)
declare @dd2 datetime-- set @d2=convert(datetime,'2019-01-01 15:00',121)
declare @td datetime set @td=convert(datetime,convert(varchar(10),@d,121)+' 20:50',121)
if @D>@td
    begin
        set @dd1=convert(datetime,convert(varchar(10),@d,121)+' 20:50',121)
        set @dd2=convert(datetime,convert(varchar(10),@d+1,121)+' 08:49:59',121)
    end
else
    begin
        set @dd1=convert(datetime,convert(varchar(10),@d,121)+' 08:50',121)
        set @dd2=convert(datetime,convert(varchar(10),@d,121)+' 20:49:59',121)
    end


declare @out table
         ( ID int  identity (1, 1)     not null,
           pr int default 0,
           CREATEDATE datetime,
           l_id int default 0,
           PLAN_START_DATE datetime,
           PLAN_end_date datetime,
           START_DATE datetime,
           end_date datetime,
           job_type int default 0,
           status_job int default 0,
           [Подразделение-исполнитель] varchar(255) default '',
           [Запланировано работ] decimal(19,9) default 0,
           [Выполнено работ] decimal(19,9) default 0,
           [Процент выполнения] decimal(19,9) default 0 ,
           [Участок] varchar(255) default '',
           [Зарегистрировал] varchar(255) default '',
           [Наименование тех. места] varchar(255) default '',
           [Описание дефекта] varchar(255) default '',

           s_id int default 0,
           [Наименование номенклатуры] varchar(255) default '',
           [Производитель] varchar(255) default '' not null,
           [Ед. изм.] varchar(55) default '',
           [Расход] decimal(19,9) default 0 ,
           [Остатки] decimal(19,9) default 0 ,
            primary key (id))
--insert into @out (pr,l_id,PLAN_START_DATE,PLAN_end_date,START_DATE,end_date,job_type,status_job,[Подразделение-исполнитель])
--select distinct  10,
--l.id as l_id,l.plan_start_date,l.plan_end_date,l.start_date,l.end_date,l.JOB_TYPE,l.status_job
----,r.name
--,r0.name
--from mnt_log l
--join MNT_LOG_RESOURCE lr on lr.log_id=l.id and lr.deleted<100 and lr.res_type=2 and l.JOB_TYPE<>4
--join mnt_resource r on r.id=lr.RESOURCE_ID
--left join mnt_resource r0 on r0.id=r.parent_ID and r0.deleted<100
--where l.plan_start_date between @d1 and @d2 and l.deleted<100
--order by l.id

insert into @out (pr,[Подразделение-исполнитель],[Запланировано работ])
select 0,mr.name, COUNT(l.id)
from mnt_log l
--join MNT_LOG_RESOURCE lr on lr.log_id=l.id and lr.deleted<100 and lr.res_type=2 --and l.JOB_TYPE<>4
--join mnt_resource r on r.id=lr.RESOURCE_ID
--left join mnt_resource r0 on r0.id=r.parent_ID and r0.deleted<100
join mnt_resource mr on mr.ID = l.resp_group_id
where l.plan_start_date between @d1 and @d2 and l.deleted<100
group by mr.name

--select r0.name, --COUNT(l.id) as ss
--l.status_job,l.*
--from mnt_log l
--join MNT_LOG_RESOURCE lr on lr.log_id=l.id and lr.deleted<100 and lr.res_type=2 and l.JOB_TYPE<>4 and l.status_job=8 and l.plan_start_date between @d1 and @d2 and l.deleted<100
--join mnt_resource r on r.id=lr.RESOURCE_ID
--left join mnt_resource r0 on r0.id=r.parent_ID and r0.deleted<100
--where
--group by r0.name
update @out set [Выполнено работ]=sss.ss
from @out o
join (select r0.name, COUNT(l.id) as ss
from mnt_log l
join MNT_LOG_RESOURCE lr on lr.log_id=l.id and lr.deleted<100 and lr.res_type=2 and l.status_job in (6,8) --and l.JOB_TYPE<>4 --l.status_job in (6,8)
join mnt_resource r on r.id=lr.RESOURCE_ID
left join mnt_resource r0 on r0.id=r.parent_ID and r0.deleted<100
where l.plan_start_date between @d1 and @d2 and l.deleted<100
group by r0.name) sss on sss.NAME=o.[Подразделение-исполнитель]

update @out set [Процент выполнения]=[Выполнено работ]/[Запланировано работ]*100 where  [Запланировано работ]>0


insert into @out (pr,l_id,CREATEDATE,PLAN_START_DATE,PLAN_end_date,START_DATE,end_date,job_type,status_job,[Участок],[Наименование тех. места],[Зарегистрировал],[Описание дефекта])
select 1,
l.id as l_id,l.CREATE_DATE,l.plan_start_date,l.plan_end_date,l.start_date,l.end_date,l.status_job,l.JOB_TYPE,
sub.NAME_MENU,c.name,l.CREATE_USER AS ACT_USER,l.[UNDERTAKE]--,l.[UNDERTAKE_ADD],l.COMMENT_
--,l.*
from mnt_log l
join mnt_log_defect df on df.log_id=l.id and l.deleted<100
join MNT_LOG_SUBTYPE sub on sub.ID=l.SUBTYPE_ID
left join MNT_COMPONENT c on c.id=l.techspace_id
where l.CREATE_DATE between @dd1 and @dd2 and l.deleted<100
--l.plan_start_date between @dd1 and @dd2 and l.deleted<100

insert into @out (pr,[Производитель],s_id,[Наименование номенклатуры],[Ед. изм.],[Расход])
select 2,
--l.id as l_id,l.plan_start_date,l.plan_end_date,l.start_date,l.end_date,l.status_job,l.JOB_TYPE,
--coalesce(f.name, 'QEWTASWFAS') as f_name,
case when f.name is null
then
'Не указано'
else
f.NAME end as F_NAME,
s.id,
s.name,
s.[UNIT]
--,sum(ls.PLAN_SPARES_VALUE)
,sum(SPARES_VALUE)
--,ls.[PLAN_UNIT_ID],ls.[FACT_UNIT_ID]
from mnt_log l
join mnt_log_spares ls on ls.log_id=l.id and ls.deleted<100 and ls.[FLAG_STOCK]<11
join spares s on s.id=ls.spares_id
left join firms f on f.id=s.[MAKERS_ID]
where l.plan_start_date between @d1 and @d2 and l.deleted<100
group by f.name,s.id,s.name,ls.[PLAN_UNIT_ID],ls.[FACT_UNIT_ID],s.[UNIT]

update @out set [Остатки]=ost.COUNT_
from @out o
join (select spares_id,sum(COUNT_) COUNT_ from [dbo].[SKLCARDVIEW]
group by spares_id) ost on ost.SPARES_ID=o.s_id
where o.pr=2



--select  [Подразделение-исполнитель],count(l_id) from @out
--where pr=0
--group by [Подразделение-исполнитель]
--select  [Подразделение-исполнитель],count(l_id) from @out
--where pr=0 and status_job=8
--group by [Подразделение-исполнитель]

--select [Подразделение-исполнитель], [Запланировано работ],[Выполнено работ],[Процент выполнения]--@d1,@d2,@dd1,@dd2,
-- from @out
-- where [Подразделение-исполнитель]<>''

--  order by [Подразделение-исполнитель] asc

select * from @out


declare @HTMLbody nvarchar(max),
    @isp varchar(100),
    @plancount varchar(100),
    @donecount varchar(100),
    @percentcount varchar(100),
    @uch varchar(100),
    @reg varchar(100),
    @tmname varchar(max),
    @defdesc varchar(max),
    @statusj varchar(100),
    @sparname varchar(max),
    @maker varchar(max),
    @unit varchar(100),
    @consump varchar(100),
    @balance varchar(100),
    @SQL nvarchar(max),
    @SQL2 nvarchar(max),
    @SQL3 nvarchar(max)

    SET @SQL = ''
    SET @SQL2 = ''
    SET @SQL3 = ''

set @HTMLbody = '<html><head><title>Статистика за смену</title></head><body>'
set @HTMLbody = @HTMLbody + '<center><h1 style="color:#008954;">PREVENTIVE MAINTENANCE COMPLETION RATIO</h1></center>'


set @HTMLbody = @HTMLbody + '<center><table><th style="background-color:#EDEDED;color:#000;">Подразделение-исполнитель</th><th style="background-color:#EDEDED;color:#000;">Запланировано работ</th><th style="background-color:#EDEDED;color:#000;">Выполнено работ</th><th style="background-color:#EDEDED;color:#000;">Процент выполнения</th>'
--select @HTMLbody
--Таблица 1. Работы

declare stat cursor
read_only
for
select
[Подразделение-исполнитель] as isp,
cast(round([Запланировано работ], 0) as int) as plancount,
cast(round([Выполнено работ], 0) as int) as donecount,
cast(ROUND([Процент выполнения], 1) as decimal(6,1)) as percentcount --[Процент выполнения] as percentcount --@d1,@d2,@dd1,@dd2,
from @out
where [Подразделение-исполнитель]<>''
order by [Подразделение-исполнитель] asc

open stat
Fetch next from stat into @isp, @plancount, @donecount, @percentcount

While (@@FETCH_STATUS <> -1)
Begin
DECLARE @percent_done_color char(6)
SET @percent_done_color=(CASE
WHEN CAST(@percentcount as float) >= 95 THEN '80ff80'
WHEN CAST(@percentcount as float) >= 80 THEN 'ffff80'
ELSE 'ff8080' END)
Set @SQL = @SQL + '<TR><TD style="border:1; border-style:solid">'
+ @isp + '</TD><TD style="border:1; border-style:solid; text-align:center">'
+ @plancount + '</TD><TD style="border:1; border-style:solid; text-align:center">'
+ @donecount + '</TD><TD style="border:1; border-style:solid; text-align:center;background-color:#' + @percent_done_color + '">'
+ @percentcount + ' %' + '</TD></TR>'
Fetch next from stat into @isp, @plancount, @donecount, @percentcount
End

Close stat
Deallocate stat
--select @HTMLbody
--Таблица 2. Дефекты

declare def cursor
read_only
for
select [Участок] as uch,
[Зарегистрировал] as reg,
[Наименование тех. места] as tmname,
[Описание дефекта] as defdesc,
--[status_job] as statusj--@d1,@d2,@dd1,@dd2,
(CASE WHEN  [status_job] = 0 THEN 'Создание работы'
WHEN  [status_job] = 1 THEN 'Не запланировано'
WHEN  [status_job] = 2 THEN 'Одобрено'
WHEN  [status_job] = 3 THEN 'Запланировано'
WHEN  [status_job] = 4 THEN 'Начато'
WHEN  [status_job] = 5 THEN 'Не принято'
WHEN  [status_job] = 6 THEN 'Выполнено'
WHEN  [status_job] = 7 THEN 'Принято'
WHEN  [status_job] = 8 THEN 'Архив'
WHEN  [status_job] = 9 THEN 'Отменено'
WHEN  [status_job] = 10 THEN 'Приостановлено'
ELSE 'Неизвестный статус' END) as statusj     --Статус работы 0;'Создание работы'; 1;'Не запланировано'; 2;'Одобрено'; 3;'Запланировано'; 4;'Начато'; 5;'Не принято'; 6;'Выполнено'; 7;'Принято'; 8;'Архив'; 9;'Отменено'; 10;'Приостановлено';
from @out
where [Участок]<>''
order by CREATEDATE asc

set @HTMLbody = @HTMLbody + @SQL --+  --'</center>'--</body></html>'

open def
Fetch next from def into @uch, @reg, @tmname, @defdesc, @statusj

While (@@FETCH_STATUS <> -1)
Begin
DECLARE @defect_status_color char(6)
SET @defect_status_color=(CASE
WHEN @statusj in ('Архив','Отменено') THEN '80ff80'
ELSE 'ff8080' END)
Set @SQL2 = @SQL2 + '<TR><TD style="border:1; border-style:solid">'
+ @uch + '</TD><TD style="border:1; border-style:solid">'
+ @reg + '</TD><TD style="border:1; border-style:solid">'
+ @tmname + '</TD><TD style="border:1; border-style:solid">'
+ @defdesc + '</TD><TD style="border:1; border-style:solid;background-color:#' + @defect_status_color + '">'
+ @statusj + '</TD></TR>'
Fetch next from def into @uch, @reg, @tmname, @defdesc, @statusj
End

Close def
Deallocate def


Set @SQL2 = '<center>' +  @SQL2

--select @HTMLbody
---Таблица 3. МТР


declare spar cursor
read_only
for
select
[Наименование номенклатуры] as sparname,
[Производитель] as maker,
[Ед. изм.] as unit,
cast(round([Расход], 3, 0) as numeric(10,4)) as consump,
cast(round([Остатки], 3, 0) as numeric(10,4)) as balance --@d1,@d2,@dd1,@dd2, cast(round([Остатки], 0) as int) as balance
from @out
where [Ед. изм.]<>''
order by CREATEDATE asc

--set @HTMLbody = @HTMLbody + @SQL --+  --'</center>'--</body></html>'

open spar
Fetch next from spar into @sparname, @maker, @unit, @consump, @balance

While (@@FETCH_STATUS <> -1)
Begin
Set @SQL3 = @SQL3 + '<TR><TD style="border:1; border-style:solid">'
+ @sparname + '</TD><TD style="border:1; border-style:solid">'
+ @maker + '</TD><TD style="border:1; border-style:solid">'
+ @unit + '</TD><TD style="border:1; border-style:solid; text-align:center">'
+ @consump + '</TD><TD style="border:1; border-style:solid; text-align:center">'
+ @balance + '</TD></TR>'

Fetch next from spar into @sparname, @maker, @unit, @consump, @balance
End

Close spar
Deallocate spar
select @SQL3
--select @HTMLbody

set @HTMLbody = @HTMLbody + '</table><h1 style="color:#008954;">Дефектов зарегистрировано</h1><br><center><table><th style="background-color:#EDEDED;color:#000;">Участок</th><th style="background-color:#EDEDED;color:#000;">Зарегистрировал</th><th style="background-color:#EDEDED;color:#000;">Наименование тех.места</th><th style="background-color:#EDEDED;color:#000;">Описание дефекта</th><th style="background-color:#EDEDED;color:#000;">Статус</th>'
--select @HTMLbody

set @HTMLbody = @HTMLbody + @SQL2 + '</table></center>'--</body></html>'
--select @HTMLbody
set @HTMLbody = @HTMLbody + '<h1 style="color:#008954;">МТР израсходовано</h1><br><center><table><th style="background-color:#EDEDED;color:#000;">Наименование номенклатуры</th><th style="background-color:#EDEDED;color:#000;">Производитель</th><th style="background-color:#EDEDED;color:#000;">Ед. изм.</th><th style="background-color:#EDEDED;color:#000;">Расход</th><th style="background-color:#EDEDED;color:#000;">Остатки</th>'
--select @HTMLbody
--set @HTMLbody = @HTMLbody + @SQL3 + '</table></center></body></html>'
set @HTMLbody = @HTMLbody + @SQL3
--select @HTMLbody
set @HTMLbody = @HTMLbody + '</table></center></body></html>'
--select @HTMLbody

  /* Send a test email */
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'RUSCAN_MAIL_PROFILE',
@recipients = 'matveycep@spectec.ru;sergey.pinchuk@royalcanin.com;yulia.shagimurdanova@royalcanin.com;romani@spectec.ru',
--@recipients = 'matveycep@spectec.ru',
@subject = 'Статистика за смену',
@body = @HTMLbody,
@body_format='HTML'
END

--exec Mailstats