USE [BELDMS]
GO
/****** Object:  StoredProcedure [dbo].[pp_DMS_L2_SendInforAfterBaseline]    Script Date: 10/22/2019 2:25:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--exec pp_DMS_L2_SendInforAfterBaseline  3 ,'20180912','20180901','20180912'
ALTER PROCEDURE [dbo].[pp_DMS_L2_SendInforAfterBaseline]
	@CompanyID int,
	@CheckDate datetime,
	@FromDate datetime,
	@ToDate datetime
AS
     SET NOCOUNT ON;
     declare @getdate date,@Period nvarchar(6) , @SOMonth date , @PeriodTD nvarchar(6),@EOMonthLastMonth date , @SOMonthLastMonth date
     set @getdate = CONVERT(date,dateadd(dd,-1,GETDATE()));
     set @SOMonth = DATEADD(month, DATEDIFF(month, 0, @ToDate), 0);
     set @SOMonthLastMonth = DATEADD(MONTH,-1,@SOMonth)
     set @EOMonthLastMonth = DATEADD(DD,-1,@SOMonth);
     set @Period = (case when MONTH(@SOMonth) > 9 then  CONVERT(nvarchar(2),MONTH(@SOMonth)) + CONVERT(nvarchar(4),Year(@SOMonth)) else '0' + CONVERT(nvarchar(2),MONTH(@SOMonth)) + CONVERT(nvarchar(4),Year(@SOMonth)) end)
     set @PeriodTD = (case when MONTH(@EOMonthLastMonth) > 9 then  CONVERT(nvarchar(2),MONTH(@EOMonthLastMonth)) + CONVERT(nvarchar(4),Year(@EOMonthLastMonth)) else '0' + CONVERT(nvarchar(2),MONTH(@EOMonthLastMonth)) + CONVERT(nvarchar(4),Year(@EOMonthLastMonth)) end)
	 DECLARE @_FromDate DATETIME = @FromDate
	 DECLARE @_ToDate DATETIME = DATEADD(dd, 1, @ToDate)
	 Declare @NumRecordBL bigint , @NumRecordBLMtd bigint;
	 Declare @TotalValueBL decimal(19,3) , @TotalValueBLMtd decimal(19,3);

	 select @NumRecordBL = SUM(temp.numberRecord) ,@TotalValueBL = SUM(temp.TotalValue)
	 from
	 (	select count(1) as numberRecord ,
			   SUM(case when OrderType = 'CM' then -1*TranAmt else TranAmt end) as TotalValue
		from BELBaseline..DMSBLRawSales
		where CompanyID = @CompanyID and LastModifiedDateTime >= @_FromDate and LastModifiedDateTime < @_ToDate
		union all
		select count(1) as numberRecord ,
			   SUM(case when OrderType = 'CM' then -1*TranAmt else TranAmt end) as TotalValue
		from BELBaseline..DMSBLRawSalesHistory
		where CompanyID = @CompanyID and LastModifiedDateTime >= @_FromDate and LastModifiedDateTime < @_ToDate) as temp

	 select @NumRecordBLMtd = SUM(temp.numberRecord) , @TotalValueBLMtd = SUM(temp.TotalValue)
	 from
	 (	select count(1) as numberRecord ,
			   SUM(case when OrderType = 'CM' then -1*TranAmt else TranAmt end) as TotalValue
		from BELBaseline..DMSBLRawSales
		where CompanyID = @CompanyID and MonthID = @Period
		union all
		select count(1) as numberRecord ,
			   SUM(case when OrderType = 'CM' then -1*TranAmt else TranAmt end) as TotalValue
		from BELBaseline..DMSBLRawSalesHistory
		where CompanyID = @CompanyID and MonthID = @Period) as temp


	 create table #temp
	 (
		Region nvarchar(50),
		Area nvarchar(50),
		SystemID int,
		AccnetID nvarchar(30),
		OrderDate datetime,
		NumberOrderNonRelease int,
		NumberOrderPending int,
		NumberWrongStatus int
	 )

	insert into #temp
	(
		Region ,
		Area ,
		SystemID ,
		AccnetID ,
		so.OrderDate,
		NumberOrderNonRelease ,
		NumberOrderPending ,
		NumberWrongStatus
	)
	 select
	 dis.Region as 'Region',
	 dis.Area as 'Area',
	 dis.DistributorID,
	 dis.DistributorCD,
	 convert(nvarchar(20),so.OrderDate),
	 SUM(case when OrderShip.InvtRefNbr is not null and reg.Released = 0 then 1 else 0 end) as 'NumberOrderNonRelease',
	 SUM(case when OrderShip.InvtRefNbr is null then 1 else 0 end) as 'NumberOrderPending',
	 SUM(case when ( OrderShip.InvoiceNbr is not null or OrderShip.InvtRefNbr is not null ) and so.Status not in ('C','I') then 1 else 0 end ) as 'NumberWrongStatus'
	 from [dbo].[DMSViewL2InforDistribution]  dis
	 left join SOOrder so
	 on dis.CompanyID = so.CompanyID
	 --and convert(date,so.OrderDate) > convert(date,dateadd(dd,-31,@_ToDate)) and convert(date,so.OrderDate) < @_ToDate
	 and dis.DistributorID = so.BranchID
	 and so.Cancelled <> 1
	 left join SOOrderShipment OrderShip
	 on so.CompanyID = OrderShip.CompanyID
	 and so.OrderNbr = OrderShip.OrderNbr
	 and so.OrderType = OrderShip.OrderType
	 left join INRegister reg
	 on OrderShip.CompanyID = reg.CompanyID
	 and OrderShip.InvtRefNbr = reg.RefNbr
	 and OrderShip.InvtDocType = reg.DocType
	 where dis.CompanyID = @CompanyID and convert(date,so.OrderDate) > convert(date,dateadd(dd,-31,@_ToDate)) and convert(date,so.OrderDate) < @_ToDate --and reg.LastModifiedDateTime >=@_FromDate and reg.LastModifiedDateTime < @_ToDate
	 group by
	 dis.Region ,
	 dis.Area,
	 dis.DistributorID,
	 dis.DistributorCD,
	 so.OrderDate



	-- dua xuong thong tin email
	 select
		convert(nvarchar(20),GETDATE()) as 'BLDate',
		count(1) as 'RecordIntran',
		sum(intran.TranAmt*intran.InvtMult) as 'TotalValue'
	 into #temp2
	 from INTran intran
	 inner join INRegister reg
	 on intran.CompanyID = reg.CompanyID
	 and intran.RefNbr = reg.RefNbr
	 and intran.DocType = reg.DocType
	 where intran.BaseQty > 0 and intran.SOOrderNbr is not null and reg.Released = 1 and reg.CompanyID = @CompanyID and reg.LastModifiedDateTime > @_FromDate and reg.LastModifiedDateTime < @_ToDate


     BEGIN
         DECLARE @query AS NVARCHAR(MAX);
         SET @query = -----------------Style
         N'<h1>BASELINE DATA STATUS</h1>'+N'<style type=''text/css''>'+N'table {border-collapse:collapse; border:1px solid #3399FF; font:11pt Calibri; color:#343434; }'+N'table td,table th,table caption {border:1px solid #3399FF; }'+N'table caption {background-color:white; font-weight = bold; }'+N'table th {background-color:#3399FF; font-weight = bold; }'+N'table tr td {padding-left:3px; padding-right:3px;}'+N'</style>'+

         N'<Table>'+N'<Caption></caption>'+N'<tr><th>DATE BASELINE</th><th>STORE NAME</th><th>Start Time</th><th>End Time</th><th>Duration</th><th>Total Record</th><th>Parameters</th></tr>'+CAST(
																				(
																					SELECT
																							'left' AS [td/@align],
																							td = BaselineDate,
																							'',
																							'left' AS [td/@align],
																							td = Store,
																							'',
																							'left' AS [td/@align],
																							td = StartTime ,
																							'',
																							'left' AS [td/@align],
																							td = EndTime,
																							'',
																							'right' AS [td/@align],
																							td = Duration,
																							'',
																							'right' AS [td/@align],
																							td =CountRecord,
																							'',
																							'left' AS [td/@align],
																							td = [Parameters],
																							''
																					FROM
																					(
																						--SUM theo Region
																						select
																							convert(nvarchar(20),convert(date,BaselineDate)) as 'BaselineDate',
																							Store,
																							[Parameters],
																							convert(nvarchar(20),Min(StartTime)) as 'StartTime' ,
																							convert(nvarchar(20),Max(EndTime)) as 'EndTime',
																							convert(nvarchar(MAX), Max(EndTime)-Min(StartTime), 108) as duration,
																							LEFT(CONVERT( VARCHAR, CAST(SUM(CountRecord) AS MONEY), 1), LEN(CONVERT(VARCHAR, CAST(SUM(CountRecord) AS MONEY), 1)) - 3) as CountRecord
																						from BELBaseline..DMSBLTrackingHistory
																						where BaselineDate >= @CheckDate and IsSuccess = 1
																						group by
																						convert(nvarchar(20),convert(date,BaselineDate)),
																						Store,
																						[Parameters]
																					) AS data2
																					ORDER BY BaselineDate
																					FOR XML PATH('tr'), TYPE
																				) AS NVARCHAR(MAX))+N'</table>'+N'<h3>Baseline Fail </h3>' +

		   ISnull(N'<Table>'+N'<Caption></caption>'+N'<tr><th>DATE BASELINE</th><th>STORE NAME</th><th>Message</th><th>Parameters</th></tr>'+CAST(
		(
			SELECT
					'left' AS [td/@align],
					td = BaselineDate,
					'',
					'left' AS [td/@align],
					td = Store,
					'',

					'left' AS [td/@align],
					td = [Message] ,
					'',
					'left' AS [td/@align],
					td = [Parameters],
					''
			FROM
			(
				--SUM theo Region
				select
					convert(nvarchar(20),convert(date,BaselineDate)) as 'BaselineDate',
					Store,
					[Parameters],
					[Message]
				from BELBaseline..DMSBLTrackingHistory
				where BaselineDate >= @CheckDate and IsSuccess = 0
			) AS data2
			ORDER BY BaselineDate
			FOR XML PATH('tr'), TYPE
		) AS NVARCHAR(MAX)),'')+N'</table>'+N'<h3>TÌNH HÌNH XỬ LÝ PENDING SAU KHI BASELINE - HEADER </h3>' +

			         Isnull(N'<Table>'+N'<Caption></caption>'+N'<tr><th>PENDING DATE</th><th>PENDING ORDER AFTER BL</th><th>LÔ TREO AFTER BL</th><th>ORDER WRONG STATUS AFTER BL</th></tr>'+CAST(
															(
																SELECT
																		'left' AS [td/@align],
																		td = OrderDate,
																		'',
																		'Right' AS [td/@align],
																		td =NumberOrderPending,
																		'',
																		'Right' AS [td/@align],
																		td =NumberOrderNonRelease,
																		'',
																		'right' AS [td/@align],
																		td =NumberWrongStatus,
																		''
																FROM
																(
																	--SUM theo Region
																	select
																		convert(nvarchar(20),convert(date,OrderDate)) as 'OrderDate',
																		LEFT(CONVERT( VARCHAR, CAST(SUM(NumberOrderPending) AS MONEY), 1), LEN(CONVERT(VARCHAR, CAST(SUM(NumberOrderPending) AS MONEY), 1)) - 3)  as 'NumberOrderPending',
																		LEFT(CONVERT( VARCHAR, CAST(SUM(NumberOrderNonRelease) AS MONEY), 1), LEN(CONVERT(VARCHAR, CAST(SUM(NumberOrderNonRelease) AS MONEY), 1)) - 3) as 'NumberOrderNonRelease',
																		LEFT(CONVERT( VARCHAR, CAST(SUM(NumberWrongStatus) AS MONEY), 1), LEN(CONVERT(VARCHAR, CAST(SUM(NumberWrongStatus) AS MONEY), 1)) - 3) as 'NumberWrongStatus'
																	from #Temp
																	group by convert(nvarchar(20),convert(date,OrderDate))
																) AS data2
																ORDER BY OrderDate
																FOR XML PATH('tr'), TYPE
															) AS NVARCHAR(MAX)),'')+N'</table>'+N'<h3>TÌNH HÌNH XỬ LÝ PENDING SAU KHI BASELINE - DETAIL </h3>' +
         ------------------Table Summary
          Isnull(N'<Table>'+N'<Caption></caption>'+N'<tr><th>PENDING DATE</th><th>DistributorCD</th><th>PENDING ORDER AFTER BL</th><th>LÔ TREO AFTER BL</th><th>ORDER WRONG STATUS AFTER BL</th></tr>'+CAST(
											(
												SELECT
														'left' AS [td/@align],
														td = OrderDate,
														'',
														'left' AS [td/@align],
														td = AccnetID,
														'',
														'Right' AS [td/@align],
														td =NumberOrderPending,
														'',
														'Right' AS [td/@align],
														td =NumberOrderNonRelease,
														'',
														'right' AS [td/@align],
														td =NumberWrongStatus,
														''
												FROM
												(
													--SUM theo Region
													select
														convert(nvarchar(20),convert(date,OrderDate)) as 'OrderDate',
														AccnetID,
														LEFT(CONVERT( VARCHAR, CAST(SUM(NumberOrderPending) AS MONEY), 1), LEN(CONVERT(VARCHAR, CAST(SUM(NumberOrderPending) AS MONEY), 1)) - 3)  as 'NumberOrderPending',
														LEFT(CONVERT( VARCHAR, CAST(SUM(NumberOrderNonRelease) AS MONEY), 1), LEN(CONVERT(VARCHAR, CAST(SUM(NumberOrderNonRelease) AS MONEY), 1)) - 3) as 'NumberOrderNonRelease',
														LEFT(CONVERT( VARCHAR, CAST(SUM(NumberWrongStatus) AS MONEY), 1), LEN(CONVERT(VARCHAR, CAST(SUM(NumberWrongStatus) AS MONEY), 1)) - 3) as 'NumberWrongStatus'
													from #Temp
													where NumberOrderPending <> 0 or NumberOrderNonRelease <> 0 and NumberWrongStatus <> 0
													group by convert(nvarchar(20),convert(date,OrderDate)) , AccnetID
												) AS data2
												ORDER BY OrderDate
												FOR XML PATH('tr'), TYPE
											) AS NVARCHAR(MAX)),'')+N'</table>'+N'<h3>CROSS CHECK DATA SAU BASELINE </h3>' +
	ISnull(N'<Table>'+N'<Caption></caption>'+N'<tr><th>BASELINE DATE</th><th>ACU.SUM OP RECORD ACU</th><th>ACU.SUM OP VALUE</th><th>SUM OP BASELINE RECORD</th><th>SUM OP BASELINE VALUE</th><th>MTD OP BASELINE RECORD</th><th>MTD OP BASELINE VALUE</th></tr>'+CAST(
																						(
																							SELECT
																									'left' AS [td/@align],
																									td = BLDate,
																									'',
																									'right' AS [td/@align],
																									td = NumRecordIntran,
																									'',
																									'Right' AS [td/@align],
																									td =TotalIntran ,
																									'',
																									'Right' AS [td/@align],
																									td = NumRecordBL,
																									'',
																									'right' AS [td/@align],
																									td = TotalValueBL,
																									'',
																									'Right' AS [td/@align],
																									td =NumRecordBLMtd ,
																									'',
																									'right' AS [td/@align],
																									td =TotalValueBLMtd ,
																									''
																							FROM
																							(
																								--SUM theo Region
																								select
																									BLDate,
																									LEFT(CONVERT( VARCHAR, CAST(RecordIntran AS MONEY), 1), LEN(CONVERT(VARCHAR, CAST(RecordIntran AS MONEY), 1)) - 3) as 'NumRecordIntran',
																									LEFT(CONVERT( VARCHAR, CAST(TotalValue AS MONEY), 1), LEN(CONVERT(VARCHAR, CAST(TotalValue AS MONEY), 1)) - 3) as 'TotalIntran',
																									LEFT(CONVERT( VARCHAR, CAST(@NumRecordBL AS MONEY), 1), LEN(CONVERT(VARCHAR, CAST(@NumRecordBL AS MONEY), 1)) - 3) as 'NumRecordBL',
																									LEFT(CONVERT( VARCHAR, CAST(@NumRecordBLMtd AS MONEY), 1), LEN(CONVERT(VARCHAR, CAST(@NumRecordBLMtd AS MONEY), 1)) - 3) as 'NumRecordBLMtd',
																									LEFT(CONVERT( VARCHAR, CAST(@TotalValueBL AS MONEY), 1), LEN(CONVERT(VARCHAR, CAST(@TotalValueBL AS MONEY), 1)) - 3) as 'TotalValueBL',
																									LEFT(CONVERT( VARCHAR, CAST(@TotalValueBLMtd AS MONEY), 1), LEN(CONVERT(VARCHAR, CAST(@TotalValueBLMtd AS MONEY), 1)) - 3) as 'TotalValueBLMtd'
																									from #temp2
																							) AS data2
																							ORDER BY BLDate
																							FOR XML PATH('tr'), TYPE
																						) AS NVARCHAR(MAX)),'')
     END;
     BEGIN
         DECLARE @subject NVARCHAR(MAX);
         SET @subject = N'[INFO][BASELINE][BEL]THÔNG TIN TÌNH TRẠNG BASELINE '+CONVERT(NVARCHAR, GETDATE(), 112) + N' BASELINE TO DATE ' + convert(nvarchar(20),convert(date,@ToDate));
         EXEC msdb.dbo.sp_send_dbmail @profile_Name ='DMSpro Supports',

             @recipients = 'nghia.nguyen@dmspro.vn;quang.nguyen@dmspro.vn;linh.tran@dmspro.vn;nghia.nguyen@dmspro.vn;sieu.vo@dmspro.vn;lhphi@groupe-bel.vn; nlnan@groupe-bel.vn',
			   @copy_recipients = 'L2.Supports@dmspro.vn',
              @subject = @subject,
              @body = @query,
              @body_format = 'HTML',
              @query_no_truncate = 1;
     END;