--------------PMS
----------------------Treatment wise length by all budget scenarios
select L.RoadCode, L.LinkCode,L.Direction as 'Direction'
,  w.WRK_Code as treatment
--,a.WORKS_NAME
,bs.[Name] 'Budget',round(convert(float,substring(a.[SECT_ID],charindex(':',a.[sect_id])+1,len(a.[sect_id])))/1000,3) as 'StartChainage'
        , round(convert(float,substring(a.[SECT_NAME],charindex(':',a.[SECT_NAME])+1,len(a.[SECT_NAME])))/1000,3) as 'EndChainage'
		, (round(convert(decimal(18,2),((isnull(Convert(float,a.[LENGTH]),0)))),2)) as [Length]
		,a.ExstStartCh,a.ExstEndCh
	, a.SurveyID,l.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision  
from WorkProgramme a 
inner join sections s on a.SECT_ID = s.SECT_ID  and a.HDMWorkSpaceName = s.HDMWorkSpaceName
inner join LKPTPmsWorks w on w.Wrk_Desc=a.WORKS_NAME
inner join linkmaster L on s.LINK_ID=l.linkcode
inner join BudgetScenarios bs on a.BUDGET_ID = bs.BUDGET_ID and a.HDMWorkSpaceName = bs.HDMWorkSpaceName
inner join VWAdminBoundaries ab on ab.SubDCode=l.GeoID
where  a.BUDGET_ID <> 255 and a.OPTIMISED = 1 --and a.YEAR_INDEX = 0
					--and a.BUDGET_ID = 0 
					and a.HDMWorkSpaceName = 'SikarJhunjhunu_02Mar24'
and a.Budget = 0


----------------------Treatment wise Cost by all scenarios
select L.RoadCode, L.LinkCode,L.Direction as 'Direction'
,  w.WRK_Code as treatment
--,a.WORKS_NAME
,bs.[Name] 'Budget'
		, (round(convert(decimal(18,2),((isnull(Convert(float,a.FIN_COST),0))))/10,2)) as [cost]
		,round(convert(float,substring(a.[SECT_ID],charindex(':',a.[sect_id])+1,len(a.[sect_id])))/1000,3) as 'StartChainage'
        , round(convert(float,substring(a.[SECT_NAME],charindex(':',a.[SECT_NAME])+1,len(a.[SECT_NAME])))/1000,3) as 'EndChainage'
		, (round(convert(decimal(18,2),((isnull(Convert(float,a.[LENGTH]),0)))),2)) as [Length]
	,a.ExstStartCh,a.ExstEndCh, a.SurveyID
	, a.SurveyID,l.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision 
from WorkProgramme a 
inner join sections s on a.SECT_ID = s.SECT_ID  and a.HDMWorkSpaceName = s.HDMWorkSpaceName
inner join LKPTPmsWorks w on w.Wrk_Desc=a.WORKS_NAME
inner join linkmaster L on s.LINK_ID=l.linkcode
inner join BudgetScenarios bs on a.BUDGET_ID = bs.BUDGET_ID and a.HDMWorkSpaceName = bs.HDMWorkSpaceName
inner join VWAdminBoundaries ab on ab.SubDCode=l.GeoID
where  a.BUDGET_ID <> 255 and a.OPTIMISED = 1 --and a.YEAR_INDEX = 0
					--and a.BUDGET_ID = 0 
					and a.HDMWorkSpaceName = 'SikarJhunjhunu_02Mar24'
and a.Budget = 0

----------ARSIS
--------------- Accident type
select al.RoadCode,al.LinkCode,al.Dir as 'Direction', t.[Name] as AccidentType, count(AccTypeId)   AS NoofAccidents,l.surveyid
,convert(varchar,l.SurveyDt,105)as 'SurveyDt',lm.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision 
FROM  dbo.AISAccDetl AS l 
INNER JOIN  AISAccLoc as al on l.AccLocCode=al.accloccode
inner join dbo.LKPTAISAccCls AS t ON t.Code = l.AccTypeId
inner join linkmaster LM on al.LinkCode=LM.linkcode
inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID
 group by al.LinkCode, al.RoadCode, t.[Name],l.surveyid,al.dir,lm.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision
 ,convert(varchar,l.SurveyDt,105)

-------------Vehicle type
SELECT al.RoadCode,  al.LinkCode,al.dir as 'Direction',  t.[Name] as VehicleType, count(VehTypeId)   AS NoofVehicles,l.surveyid
,convert(varchar,l.SurveyDt,105)as 'SurveyDt' ,lm.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision
FROM  dbo.AISAccDetl AS l 
INNER JOIN  AISAccLoc as al on l.AccLocCode=al.accloccode
inner join dbo.LKPTAISVehType AS t ON t.Code = l.VehTypeId
inner join linkmaster LM on al.LinkCode=LM.linkcode
inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID
 group by al.LinkCode, al.RoadCode, t.[Name],l.surveyid,al.dir,lm.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision
  ,convert(varchar,l.SurveyDt,105)

--------------Other objects
SELECT   al.RoadCode,al.LinkCode,al.dir as 'Direction',fc.[name] as OtherObjects,
count(Factsinvl)  AS NoofOtherObjects,l.surveyid,convert(varchar,l.SurveyDt,105)as 'SurveyDt' ,lm.Agency,ab.Zone,ab.District
,ab.Circle,ab.Division,ab.Subdivision, fc.Code
FROM  dbo.AISAccRptGen AS l  
INNER JOIN  AISAccLoc as al on l.AccLocCode=al.accloccode
inner join LKPTAISAccFctrs fc on l.FactsInvl=fc.Code
inner join linkmaster LM on al.LinkCode=LM.linkcode
inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID
group by l.surveyid, al.RoadCode, al.LinkCode,al.dir, FC.CODE, fc.[Name],convert(varchar,l.SurveyDt,105),lm.Agency,ab.Zone,ab.District
,ab.Circle,ab.Division,ab.Subdivision, fc.Code

--------------Accident effect on victims
select aal.RoadCode, aal.LinkCode,aal.dir as 'Direction','Death on Spot'as Victims,Count(arg.DeathonSpt) as Counts
,aal.SurveyID,convert(varchar,arg.SurveyDt,105) as 'SurveyDt',lm.Agency,ab.Zone,ab.District
,ab.Circle,ab.Division,ab.Subdivision from AISAccRptGen arg 
inner join AISAccLoc aal on arg.AccLocCode = aal.AccLocCode
inner join linkmaster LM on aal.LinkCode=LM.linkcode
inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID
group by aal.RoadCode,aal.LinkCode,aal.SurveyID,convert(varchar,arg.SurveyDt,105),lm.Agency,ab.Zone,ab.District
,ab.Circle,ab.Division,ab.Subdivision,aal.dir
union all
select aal.RoadCode,aal.LinkCode,aal.dir as 'Direction','Death In Hospitals' ,Count(arg.DeathInHsptls) 
,aal.SurveyID,convert(varchar,arg.SurveyDt,105)'SurveyDt',lm.Agency,ab.Zone,ab.District
,ab.Circle,ab.Division,ab.Subdivision from AISAccRptGen arg 
inner join AISAccLoc aal on arg.AccLocCode = aal.AccLocCode
inner join linkmaster LM on aal.LinkCode=LM.linkcode
inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID
group by aal.RoadCode,aal.LinkCode,aal.SurveyID,convert(varchar,arg.SurveyDt,105),lm.Agency,ab.Zone,ab.District
,ab.Circle,ab.Division,ab.Subdivision,aal.dir
union all
select aal.RoadCode,aal.LinkCode,aal.dir as 'Direction','Major Injury', Count(arg.MajInjury)
,aal.SurveyID,convert(varchar,arg.SurveyDt,105)'SurveyDt',lm.Agency,ab.Zone,ab.District
,ab.Circle,ab.Division,ab.Subdivision  from AISAccRptGen arg 
inner join AISAccLoc aal on arg.AccLocCode = aal.AccLocCode
inner join linkmaster LM on aal.LinkCode=LM.linkcode
inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID
group by aal.RoadCode,aal.LinkCode,aal.SurveyID,convert(varchar,arg.SurveyDt,105),lm.Agency,ab.Zone,ab.District
,ab.Circle,ab.Division,ab.Subdivision,aal.dir
union all
select aal.RoadCode,aal.LinkCode,aal.dir as 'Direction', 'Minor Injury',Count(arg.MinorInjury)
,aal.SurveyID,convert(varchar,arg.SurveyDt,105)'SurveyDt',lm.Agency,ab.Zone,ab.District
,ab.Circle,ab.Division,ab.Subdivision from AISAccRptGen arg 
inner join AISAccLoc aal on arg.AccLocCode = aal.AccLocCode
inner join linkmaster LM on aal.LinkCode=LM.linkcode
inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID
group by aal.RoadCode,aal.LinkCode,aal.SurveyID,convert(varchar,arg.SurveyDt,105),lm.Agency,ab.Zone,ab.District
,ab.Circle,ab.Division,ab.Subdivision,aal.dir

--------Defects count
select al.RoadCode, al.LinkCode,l.dir as 'Direction',l.Chainage,l.ExstCh as 'ExistingChainage',  t.[Desc] as DefectType, count(DefType)   AS NoofDefects,l.surveyid
,lm.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision
FROM dbo.AISDefects AS l 
INNER JOIN  AISAccLoc as al on l.RoadCode=al.RoadCode
inner join dbo.LKPTAISDefects AS t ON t.Code = l.DefType
inner join linkmaster LM on l.LinkCode=LM.linkcode
inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID
group by al.LinkCode, al.RoadCode,l.Chainage,l.ExstCh, t.[Desc],l.surveyid,l.dir
,lm.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision

--------Time of Accidents
SELECT  al.RoadCode,al.dir as 'Direction',al.linkcode,[time],
 CASE
WHEN [time]>='00:00' and [time]<='18:00' THEN 'Day'
WHEN [time]>'18:00' and [time]<='24:00' THEN 'Night'
Else [time]
END AS Other,Count([time]) as totalcount
,l.SurveyId,convert(varchar,l.SurveyDt,105)as 'SurveyDt'
,LM.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision 
FROM  dbo.AISAccRptGen AS l  
INNER JOIN  AISAccLoc as al on l.AccLocCode=al.accloccode
inner join linkmaster LM on al.LinkCode=LM.linkcode
inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID
group by al.linkcode , al.RoadCode,al.dir,[Time],l.SurveyId,convert(varchar,l.SurveyDt,105)
,LM.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision

------AMT
--------------------Reconstruction costwise
select a.RoadCode,a.rdnm as 'RoadName', l.linkcode,a.Dir as 'Direction', a.Startch,a.Endch,a.len as 'Length'
,a.ExstStartCh as 'ExistingStartChainage',a.ExstEndCh as 'ExistingEndChainage', w.WrkCode 'treatment'
, (round(convert(decimal(18,2),((isnull(a.cost,0)+isnull(a.widenCost,0))/10000000)),2)) as [cost]
, a.surveyid ,LM.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision  
from oSmplPmsRawData a inner join linkmaster L on a.roadcode=l.roadcode and a.linkcode=l.linkcode
inner join LKPTAMTWorks w on a.treatment = w.WrkName
inner join linkmaster LM on a.LinkCode=LM.linkcode
inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID
where a.trtcode like 'TRC%'

--------------Reconstruction lengthwise
select a.RoadCode,a.rdnm as 'RoadName', l.linkcode,a.Dir as 'Direction', a.Startch,a.Endch,a.len as 'Length'
,a.ExstStartCh as 'ExistingStartChainage',a.ExstEndCh as 'ExistingEndChainage', w.WrkCode 'treatment'
--, (round(convert(decimal(18,3),(a.EndCh-a.StartCh))/1000,3)) as [length]
, a.surveyid,LM.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision  
from oSmplPmsRawData a inner join linkmaster L on a.roadcode=l.roadcode and a.linkcode=l.linkcode
inner join LKPTAMTWorks w on a.treatment = w.WrkName
inner join linkmaster LM on a.LinkCode=LM.linkcode
inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID
where a.trtcode like 'TRC%'

--------TIS
--------------AADT traffic category
Select tm.RoadCode,tm.linkcode, t.StnCode As StationCode,tm.Chainage,tm.ExstCh,Sum(t.Tot)/6 as AADT,t.SurveyID,MIN(FORMAT(t.SurveyDt, 'MMM')) AS SurveyMonth
,LM.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision from TISTVC t
inner join tistrfmaster tm on t.stncode=tm.stncode
inner join linkmaster LM on tm.LinkCode=LM.linkcode
inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID
Group by t.StnCode,t.SurveyID,tm.RoadCode,tm.linkcode,tm.Chainage,tm.ExstCh,LM.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision

-----------AADT Traffic Category by Axle
Select b.RoadCode, b.linkcode,b.StationCode,b.Chainage,b.ExstCh as 'ExisingChainage',AxelAvg 
,c.[Desc] as [Traffic Category],b.SurveyID,SurveyMonth
,b.Agency,b.Zone,b.District,b.Circle,b.Division,b.Subdivision  
From 
(Select tm.RoadCode,tm.linkcode, t.StnCode As StationCode,tm.Chainage,tm.ExstCh,t.SurveyID,MIN(FORMAT(t.SurveyDt, 'MMM')) AS SurveyMonth
,LM.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision,Sum(t.Tot)/2 as AxelAvg from [dbo].[TISAxleLoadTVC] t
inner join tistrfmaster tm on t.stncode=tm.stncode
inner join TISAxleLoadDtls ax on t.StnCode = ax.StnCode
inner join linkmaster LM on tm.LinkCode=LM.linkcode
inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID
Group by t.StnCode,t.SurveyID,tm.RoadCode,tm.linkcode,LM.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision,tm.Chainage,tm.ExstCh) b
left Join LKPTTrfCatg c on b.AxelAvg >=c.[min] and b.AxelAvg  <c.[max]

----------AADT by Station-TVC
Select tm.RoadCode,tm.linkcode, t.StnCode As StationCode,tm.Chainage,tm.ExstCh,t.SurveyID,MIN(FORMAT(t.SurveyDt, 'MMM')) AS SurveyMonth
,LM.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision,Sum(t.Tot)/2 as TVCAvg from TISTVC t
inner join tistrfmaster tm on t.stncode=tm.stncode
inner join linkmaster LM on tm.LinkCode=LM.linkcode
inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID
Group by t.StnCode,t.SurveyID,tm.RoadCode,tm.linkcode
,LM.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision,tm.Chainage,tm.ExstCh

--------------AADT by Stations-Axle
Select tm.RoadCode,tm.linkcode, t.StnCode As StationCode,tm.Chainage,tm.ExstCh,t.SurveyID,MIN(FORMAT(t.SurveyDt, 'MMM')) AS SurveyMonth
,LM.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision,Sum(t.Tot)/2 as AxelAvg from [dbo].[TISAxleLoadTVC] t
inner join tistrfmaster tm on t.stncode=tm.stncode
inner join TISAxleLoadDtls td on td.StnCode=t.StnCode
inner join linkmaster LM on tm.LinkCode=LM.linkcode
inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID
Group by t.StnCode,t.SurveyID,tm.RoadCode,tm.linkcode
,LM.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision,tm.Chainage,tm.ExstCh

-------------Districtwise traffic locations
SELECT a.RoadCode,  a.linkcode,a.Dir as 'Direction',a.StnCode AS StationCode,a.Chainage,a.ExstCh as ExistingChainage
,a.SurveyID,MIN(FORMAT(a.SurveyDt, 'MMM')) AS SurveyMonth,L.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision
FROM          
TISTrfMaster  as a 
		INNER JOIN dbo.LinkMaster AS L ON a.LinkCode = L.LinkCode 
		inner join VWAdminBoundaries ab on ab.SubDCode=l.GeoID
		--inner join Sysgeolevel5 Sd on l.GeoID=sd.Code
		--inner join Sysgeolevel4 dv on sd.parentcode=dv.code
		--inner join Sysgeolevel3 Cr on dv.parentcode=cr.code
		--inner join Sysgeolevel2 dr on Cr.parentcode=dr.code
		--inner join Sysgeolevel1 z on dr.parentcode=z.code
Group by a.RoadCode,  a.linkcode,a.Dir,a.StnCode,a.Chainage,a.ExstCh
,a.SurveyID,L.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision

---------District Wise-TVC
SELECT a.RoadCode,  a.linkcode,a.Dir as 'Direction',a.StnCode AS StationCode,a.Chainage,a.ExstCh as ExistingChainage,count(t.StnCode) as TVC
,a.SurveyID,MIN(FORMAT(a.SurveyDt, 'MMM')) AS SurveyMonth,L.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision
FROM          
TISTrfMaster  as a 
Left join (Select Distinct StnCode From TISAxleLoadTVC)b on b.Stncode=a.StnCode
Left join (Select Distinct StnCode From  [dbo].[TISTVC]) t on t.Stncode=a.StnCode
INNER JOIN dbo.LinkMaster AS L ON a.LinkCode = L.LinkCode
		inner join VWAdminBoundaries ab on ab.SubDCode=l.GeoID
		--inner join Sysgeolevel5 Sd on l.GeoID=sd.Code
		--inner join Sysgeolevel4 dv on sd.parentcode=dv.code
		--inner join Sysgeolevel3 Cr on dv.parentcode=cr.code
		--inner join Sysgeolevel2 dr on Cr.parentcode=dr.code
		--inner join Sysgeolevel1 z on dr.parentcode=z.code
		Group by a.RoadCode,  a.linkcode,a.Dir,a.StnCode,a.Chainage,a.ExstCh
,a.SurveyID,L.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision

----------District Wise-Axle
SELECT a.RoadCode,  a.LinkCode,a.Dir as 'Direction',a.StnCode as StationCode,a.Chainage,a.ExstCh as ExistingChainage,count(ax.StnCode) as Axle
,a.SurveyID,MIN(FORMAT(a.SurveyDt, 'MMM')) AS SurveyMonth,L.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision
FROM          
TISTrfMaster  as a 
--Left join (Select Distinct StnCode From TISAxleLoadTVC)b on b.Stncode=a.StnCode
--Left join (Select Distinct StnCode From  [dbo].[TISTVC]) t on t.Stncode=a.StnCode
INNER JOIN dbo.LinkMaster AS L ON a.LinkCode = L.LinkCode 
inner join TISAxleLoadDtls ax on a.StnCode = ax.StnCode
inner join VWAdminBoundaries ab on ab.SubDCode=l.GeoID
		--inner join Sysgeolevel5 Sd on l.GeoID=sd.Code
		--inner join Sysgeolevel4 dv on sd.parentcode=dv.code
		--inner join Sysgeolevel3 Cr on dv.parentcode=cr.code
		--inner join Sysgeolevel2 dr on Cr.parentcode=dr.code
		--inner join Sysgeolevel1 z on dr.parentcode=z.code
		Group by a.RoadCode,  a.linkcode,a.Dir,a.StnCode,a.Chainage,a.ExstCh
,a.SurveyID,L.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision