/*    ==Scripting Parameters==

    Source Server Version : SQL Server 2016 (13.0.4001)
    Source Database Engine Edition : Microsoft SQL Server Standard Edition
    Source Database Engine Type : Standalone SQL Server

    Target Server Version : SQL Server 2016
    Target Database Engine Edition : Microsoft SQL Server Standard Edition
    Target Database Engine Type : Standalone SQL Server
*/

USE [RouteManager]
GO

/****** Object:  StoredProcedure [dbo].[spProcessRoutesFromUploadFile]    Script Date: 21/07/2018 3:05:12 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER Procedure [dbo].[spProcessRoutesFromUploadFile]
as

DECLARE @errmessage VarChar(Max)

DECLARE @FormattedRouteData Table (
FileDate Varchar(100),
PickupTime DateTime,
RouteNo Int,
LoadNo Int,
SupplierNo Varchar(100),
Volume Int,
PickupOrder Int,
SiteID int
)

Declare @RecordCount Int
SET @RecordCount = 0
select @RecordCount = count(*) from DataIntegration_Import where Processed = 0

IF @RecordCount = 0
	BEGIN
		SET @errmessage = 'No files to process'
		RAISERROR (@errmessage,16, 1)
		RETURN 
	END

declare @Site Varchar;
declare @RecordToProcessID int;
select top 1 @Site = case substring(FileName,1,4)
when '6000' then 22
when '6500' then 23
else '' end,
@RecordToProcessID = rowid
from DataIntegration_Import
where processed = 0

IF @Site = '' 
	BEGIN
		SET @errmessage = 'Invalid filename in dataintegration rowid ' + Cast(@RecordToProcessID as varchar(100))
		RAISERROR (@errmessage,16, 1)
		RETURN
	END

BEGIN TRY  
    --The below Stored Proc will return the data from the top 1 record in
	--DataIntegration_Import table where Processed = false
	--If all is well with the file import will return a table set
	--Otherwise will return an error  
	INSERT INTO @FormattedRouteData
	EXEC spFormatRouteData

	--Update the DataIntegration_Import table to set the top record to be processed
	update DataIntegration_Import set Processed = 1
	where rowid = (Select top 1 rowid from DataIntegration_Import where Processed = 0)
END TRY  
BEGIN CATCH  
	--Update the DataIntegration_Import table to set the top record to be processed
	update DataIntegration_Import set Processed = 1
	where rowid = (Select top 1 rowid from DataIntegration_Import where Processed = 0)

	SET @errmessage = ERROR_MESSAGE()
	RAISERROR (@errmessage,16, 1)
	RETURN 
END CATCH; 

--The logic is that the file will have data collected from the previous day. I.e. If file date is 
--23rd of May will be for data collected on 22nd of May and should be imported into 24th Routes.

--Check if there is already data for the date
Declare @ExistingRouteDataCount Int
Declare @FileDate Date
Declare @SiteID Int

Select top 1 @FileDate = cast(FileDate as Date),
@SiteID = SiteID
from @FormattedRouteData

Set @ExistingRouteDataCount = 0

Select @ExistingRouteDataCount = count(*)
from ROUTES
where DATE = dateadd(dd,1,@FileDate)
and SITE_ID = (@SiteID)

--If data already exists return an error as we don't want to overwrite any data.
--IF @ExistingRouteDataCount > 0
--BEGIN
--	SET @errmessage = 'File from ' + cast(@FileDate as Varchar(100)) + ', Site ID ' + cast(@SiteID as Varchar(100)) + ' already has route data and cannot be overwritten'
--	RAISERROR (@errmessage,16, 1)
--	RETURN
--END

--Remove Date Litres Data
DELETE from DATE_LITRES
where SITE_ID = @SiteID
and Date = dateadd(dd,1,@FileDate) 

--Insert Date Litres Data
Insert into DATE_LITRES
select
DATE,
SUPPLIER_ID,
isnull(sum(DAY_LITRES),0) as DAY_LITRES,
isnull(sum(NIGHT_LITRES),0) as NIGHT_LITRES,
SITE_ID
from
(Select 
dateadd(dd,1,cast(a.FileDate as Date)) as DATE,
b.Supplier_ID as SUPPLIER_ID,
case when cast(PickupTime as Time) between '05:00' and '16:59' then Volume else 0 end as DAY_LITRES,
case when cast(PickupTime as Time) between '05:00' and '16:59' then 0 else Volume end as NIGHT_LITRES,
a.SiteID as SITE_ID
from @FormattedRouteData a
left join SUPPLIERS b  on (a.SupplierNo = b.SUPPLIER_NO and a.SiteID = b.SITE_ID)
where b.SITE_ID = @SiteID
) qry
group by DATE, SUPPLIER_ID, SITE_ID

--Ensure all suppliers have a record for date litres...
insert into DATE_LITRES
select dateadd(dd,1,@FileDate),
a.SUPPLIER_ID,
0,0,
@SiteID
from Suppliers a
left outer join DATE_LITRES b on (a.SUPPLIER_ID = b.SUPPLIER_ID and b.Date = dateadd(dd,1,@FileDate))
where a.SITE_ID = @SiteID
and b.DATE_LITRES_ID is null
and a.FREQUENCY <> 'Ceased Supply'


----Insert Route Data
--Insert into [ROUTES]
--select DateAdd(dd,1,@FileDate) as DATE,
--case when cast(PickupTime as Time) between '06:00' and '17:59' then 'Day' else 'Night' end as TYPE,
--RouteNo as RUN_NO,
--LoadNo as LOAD_NO,
--0 as TOTAL_DISTANCE,
--0 as TOTAL_DURATION,
--sum(Volume) as TOTAL_LITRES,
--SiteID as SITE_ID,
--'' as COMMENTS,
--null as DRIVER_ID

--from @FormattedRouteData 
--group by 
--case when cast(PickupTime as Time) between '06:00' and '17:59' then 'Day' else 'Night' end,
--RouteNo,
--LoadNo,
--SiteID
--order by RouteNo, LoadNo


----Insert Route Pickups
--insert into ROUTE_PICKUPS
--select (select ROUTE_ID from ROUTES where
--DATE = dateadd(dd,1,cast(a.FileDate as Date))
--and Type = case when cast(a.PickupTime as Time) between '06:00' and '17:59' then 'Day' else 'Night' end
--and RUN_NO = a.RouteNo
--and LOAD_NO = a.LoadNo
--and SITE_ID = a.SiteID) as ROUTE_ID,
--b.SUPPLIER_ID,
--a.Volume,
--a.PickupOrder,
--'' as ACM_NO,
--'' as COMMENTS
-- from @FormattedRouteData a
--left outer join SUPPLIERS b on (a.SupplierNo = b.SUPPLIER_NO and b.SITE_ID = a.SiteID)





--Update route pickups with new litres data
update ROUTE_PICKUPS
set LITRES = qry.NEW_LITRES
from
(select a.ROUTE_PICKUP_ID, 
a.LITRES as CurrentLitres,
b.DATE,
b.TYPE,
(select case when b.TYPE = 'DAY' then DAY_LITRES else NIGHT_LITRES end from DATE_LITRES where DATE = b.DATE and a.SUPPLIER_ID = SUPPLIER_ID) as NEW_LITRES
from 
ROUTE_PICKUPS a
left outer join ROUTES b on (a.ROUTE_ID = b.ROUTE_ID)
where a.ROUTE_ID in (
select distinct ROUTE_ID from ROUTES
where SITE_ID = @SiteID
and DATE = dateadd(dd,1,@FileDate)
)) qry
where ROUTE_PICKUPS.ROUTE_PICKUP_ID = qry.ROUTE_PICKUP_ID

select 1 as Result;

GO