USE [msdb]

GO

/****** Object:  StoredProcedure [dbo].[MeterreadCheck]    Script Date: 02/07/2022 22:27:24 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

--  exec MeterReadCheck '2355252522@qq.com' 

-- =============================================

ALTER  PROCEDURE [dbo].[MeterreadCheck]

@mailAddress varchar(50) 

AS

BEGIN

declare @StratDate datetime

declare @EndDate datetime 

select ROW_NUMBER() over(partition by MeterId order by Id) row 

, MeterId,Id as 'MeterReadId',PreRead ,NowRead ,PreMeterReadDate ,NowMeterReadDate,ProcessType,BuildEmployeeNo,ActualReceiveQty     

into #tMeterRead from 佳木斯供水收费系统..tMeterRead where NowMeterReadDate >='2021-01-01' and isnull(ProcessType,'') not in ('CZ','CK') 

--and  meterid='10288'

--and isnull(ProcessType,'')not in ('CZ','CK')  

delete from #tMeterRead where MeterId in (select MeterId from   #tMeterRead where isnull(ProcessType,'')  in ('CZ','CK')   )

--delete from #tMeterRead where MeterId in (select MeterId from   #tMeterRead where  PreRead =0 and nowread=0 and BuildEmployeeNo ='systemmanager' and ActualReceiveQty <>0   )

select * INTO #明细表上下行读数不对应 from ( 

select '表中为上下行读数不对应' as  'meterno',*, 

(select a.NowRead-b.PreRead as cc from #tMeterRead a where a.MeterId=b.MeterId 

and b.row=a.row+1

) as cc  

from #tMeterRead b ) a WHERE ISNULL(cc,0)<>0 

    --         select *

--from #明细表上下行读数不对应 order by MeterId 

select meterno,MeterId,PreRead ,NowRead ,PreMeterReadDate ,NowMeterReadDate  into #temp 

from #明细表上下行读数不对应 order by MeterId   

--drop table #temp   

--drop table #明细表上下行读数不对应  drop table  #tMeterRead 

--查询用户档案和手工抄表的记录对不上的

insert into #temp 

select c.meterno,a.MeterId,a.PreRead ,a.NowRead ,a.PreMeterReadDate ,a.NowMeterReadDate-- ,

--c.PreRead ,c.NowRead ,c.PreMeterReadDate ,c.NowMeterReadDate 

from 佳木斯供水收费系统..tMeterRead a,

(

select MeterId,MAX(ID) maxid from 佳木斯供水收费系统..tMeterRead where FeeTypeId =1   and  NowMeterReadDate >='2019-01-01' 

--and isnull(ProcessType,'')=''  

group by MeterId 

)b ,佳木斯供水收费系统..tmeter c where a.Id=b.maxid and a.MeterId =c.Id 

and a.NowRead <>c.NowRead and isnull(a.ProcessType,'')=''   

and c.NowRead <>0 and c.NowRead >c.PreRead 

order by c.NowMeterReadDate 

--select c.meterno,a.MeterId,a.PreRead ,a.NowRead ,a.PreMeterReadDate ,a.NowMeterReadDate  ,

-- c.PreRead ,c.NowRead ,c.PreMeterReadDate ,c.NowMeterReadDate 

-- from 佳木斯供水收费系统..tMeterRead a,

-- (

-- select MeterId,MAX(ID) maxid from 佳木斯供水收费系统..tMeterRead where FeeTypeId =1   and  NowMeterReadDate >='2019-01-01' 

-- --and isnull(ProcessType,'')=''  

-- group by MeterId 

-- )b ,佳木斯供水收费系统..tmeter c where a.Id=b.maxid and a.MeterId =c.Id 

-- and a.NowRead <>c.NowRead and isnull(a.ProcessType,'')=''   

-- and c.NowRead <>0 and c.NowRead >c.PreRead and c.NowMeterReadDate <'2019-12-01'

-- order by c.NowMeterReadDate 

--update  c set  c.PreRead =a.PreRead ,c.NowRead=a.NowRead  ,c.PreMeterReadDate=a.PreMeterReadDate ,c.NowMeterReadDate =a.NowMeterReadDate  

-- from 佳木斯供水收费系统..tMeterRead a,

--(

-- select MeterId,MAX(ID) maxid from 佳木斯供水收费系统..tMeterRead where FeeTypeId =1   and  NowMeterReadDate >='2019-01-01' 

-- --and isnull(ProcessType,'')=''  

-- group by MeterId 

--)b ,佳木斯供水收费系统..tmeter c where a.Id=b.maxid and a.MeterId =c.Id 

--and a.NowRead <>c.NowRead and isnull(a.ProcessType,'')=''   

--and c.NowRead <>0 and c.NowRead >c.PreRead and c.NowMeterReadDate <'2019-12-01'  

declare @i int  

declare @tableHtml nvarchar(Max) 

select @i=COUNT(*) from #temp 

if (@i>0) 

begin

set @tableHtml=

+N'<H4>'+convert(varchar(19),DATEADD(day,-1,getdate()),120)+'至'+convert(varchar(19),GETDATE(),120)

+'佳木斯上下期读数不衔接通知</H4>'--标题

+N'<Table border="1">'--表宽

+N'<tr><th>水表编码</th>

<th>水表id</th> 

<th>上期读数</th>

<th>本期读数</th>

<th>上期抄表日</th>

<th>本期抄表日</th>

  '--表头

+CAST((select td=meterno,'',

td=MeterId,'',

td=PreRead,'',

td=NowRead,'',

td=PreMeterReadDate,'',

td=NowMeterReadDate,''  

from #temp   

for XML PATH('tr'),TYPE)AS nvarchar(MAX))

+N'</TABLE>'

exec msdb.dbo.sp_send_dbmail 

@Profile_name='作业失败邮件通知',

@recipients=@mailAddress,

@subject='佳木斯上下期读数不衔接通知', 

@body=@tableHtml,  

@body_format='HTML' 

end

--select * into temp202 from #temp 

    select top 100 * from sysmail_event_log order by log_id desc 

END