--********************** NRS ICD Diagnosis and Intervention
if exists (select * from information_schema.tables where table_name = 'zzICD10_diagNRS')
    drop table zzICD10_diagNRS
GO
Create table zzICD10_diagNRS(
    CIHIValue varchar(10),
    shortDescription varchar(100),
    longDescription varchar(1000)
)
GO
--bulk insert zzICD10_diagNRS  from '<path>NRS_ICD10CA_Picklist_FY2018-2019_v1.0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
bulk insert zzICD10_diagNRS  from '<path>NRS_ICD10CA_Picklist_FY2018-2019_v1.0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
update zzICD10_diagNRS
set CIHIValue = ltrim(rtrim(CIHIValue)),
    shortDescription = ltrim(rtrim(shortDescription)),
    longDescription = ltrim(rtrim(longDescription))
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10_CCI_NRS')
    drop table zzICD10_CCI_NRS
GO
Create table zzICD10_CCI_NRS(
    CIHIValue varchar(10),
    shortDescription varchar(100),
    longDescription varchar(1000)
)
GO
bulk insert zzICD10_CCI_NRS  from '<path>NRS_CCI_Picklist_FY2018-2019_v1.0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
update zzICD10_CCI_NRS
set CIHIValue = ltrim(rtrim(CIHIValue)),
    shortDescription = ltrim(rtrim(shortDescription)),
    longDescription = ltrim(rtrim(longDescription))
GO
Update ICD_DiagFileNRS
set UserDescription = left(z.longDescription, 255),
    DefaultDescription = left(z.longDescription, 255),
    ShortDescription = z.shortDescription
-- select z.*, icd.*
from zzICD10_diagNRS z
	inner join ICD_DiagFileNRS Icd on z.CIHIValue = ICD.CIHIValue
where (icd.DefaultDescription <>  left(z.longDescription, 255) or icd.ShortDescription <> z.shortDescription)
    and icd.code < 500000	--this is for NUNAVUT custom code, do not overwrite description
GO
Update dbo.ICD_ProcFileNRS
set UserDescription = left(z.longDescription, 255),
    DefaultDescription = left(z.longDescription, 255),
    ShortDescription = z.shortDescription
-- select z.*, icd.*
from zzICD10_CCI_NRS z
	inner join ICD_ProcFileNRS Icd on z.CIHIValue = ICD.CIHIValue
where (icd.DefaultDescription <>  left(z.longDescription, 255) or icd.ShortDescription <> z.shortDescription)
    and icd.code < 500000	--this is for NUNAVUT custom code, do not overwrite description
GO
update ICD_DiagFileNRS
set ValidTo = '2099/03/31'
-- select icd.*
from zzICD10_diagNRS z
	inner join ICD_DiagFileNRS Icd on z.CIHIValue = ICD.CIHIValue
where icd.validto < '2018/04/01'
GO
update ICD_ProcFileNRS
set ValidTo = '2099/03/31'
-- select icd.*
from zzICD10_CCI_NRS z
	inner join ICD_ProcFileNRS Icd on z.CIHIValue = ICD.CIHIValue
where icd.validto < '2018/04/01'
GO
update ICD_DiagFileNRS
set ValidTo = '2018/03/31'
-- select icd.*
from zzICD10_diagNRS z
	right join ICD_DiagFileNRS Icd on z.CIHIValue = ICD.CIHIValue
where z.cihivalue is null and icd.validto > '2018/03/31' and icd.code < 500000
GO
update ICD_ProcFileNRS
set ValidTo = '2018/03/31'
-- select icd.*
from zzICD10_CCI_NRS z
	right join ICD_ProcFileNRS Icd on z.CIHIValue = ICD.CIHIValue
where icd.validto > '2018/03/31' and  z.cihivalue is null and icd.code < 500000
GO
--Add New codes
if exists (select * from information_schema.tables where table_name = 'zz2018_CCI_Rehab')
    drop table zz2018_CCI_Rehab
GO
select z.*, code = identity(int, 1, 1) into zz2018_CCI_Rehab
from zzICD10_CCI_NRS z
	left join ICD_ProcFileNRS Icd on z.CIHIValue = ICD.CIHIValue
where icd.cihivalue is null
order by z.CIHIValue
GO
declare @MaxID int
set @MaxID = isnull((select max(code) from [dbo].[ICD_ProcFileNRS]), 0)
Insert into [dbo].[ICD_ProcFileNRS](Code, DisplayCode, UserDescription, DefaultDescription, ShortDescription, CIHIValue, ValidFrom, ValidTo, DiagnosisGender, AgeLow, AgeHigh, InterfaceIn, HospitalID, LUText)
select @maxID + code, CIHIValue, longDescription, longDescription, ShortDescription, CIHIValue, '2018/04/01', '2099/03/31', 'A', 0, 130, CIHIValue, 0, ''
from zz2018_CCI_Rehab
GO
if exists (select * from information_schema.tables where table_name = 'zz2018_CA_Rehab')
    drop table zz2018_CA_Rehab
GO
select z.*, code = identity(int, 1, 1) into zz2018_CA_Rehab
from zzICD10_diagNRS z
	left join ICD_DiagFileNRS Icd on z.CIHIValue = ICD.CIHIValue
where icd.cihivalue is null
order by z.CIHIValue
GO
declare @MaxID int
set @MaxID = isnull((select max(code) from [dbo].ICD_DiagFileNRS), 0)
Insert into [dbo].ICD_DiagFileNRS(Code, DisplayCode, UserDescription, DefaultDescription, ShortDescription, CIHIValue, ValidFrom, ValidTo, DiagnosisGender, AgeLow, AgeHigh, InterfaceIn, HospitalID, LUText)
select @maxID + code, CIHIValue, longDescription, longDescription, ShortDescription, CIHIValue, '2018/04/01', '2099/03/31', 'A', 0, 130, CIHIValue, 0, ''
from zz2018_CA_Rehab
GO


-------------------------NRS Diagnosis Validation
if exists (select * from information_schema.tables where table_name = 'zzICD10CA_NRS_Validation')
    drop table zzICD10CA_NRS_Validation
GO
Create table zzICD10CA_NRS_Validation(
    CIHIValue varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    Sector   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    CIHIFlag   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    MRDxFlag   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    NewBorn   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    DiagType   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    Gender   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    AgeLow   varchar(3) COLLATE Latin1_General_CI_AS NOT NULL,
    AgeHigh   varchar(10) COLLATE Latin1_General_CI_AS NOT NULL
)
GO
bulk insert zzICD10CA_NRS_Validation  from '<path>NRS_ICD10CA_Validation_FY2018-2019_v1.0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
GO
Update ICD_DiagFileNRS set DiagnosisGender = v.Gender
--select v.*, d.DiagnosisGender
from zzICD10CA_NRS_Validation v
	inner join ICD_DiagFileNRS d on v.cihivalue = d.cihivalue   
where isnull(v.Gender, '@') <> isnull(d.DiagnosisGender, '#') and d.code < 500000
GO
Update ICD_DiagFileNRS set AgeLow = (case when isnull(v.AgeLow, '') = '' then 0 else v.AgeLow end)
--select v.*, d.AgeLow
from zzICD10CA_NRS_Validation v
	inner join ICD_DiagFileNRS d on v.cihivalue = d.cihivalue   
where (case when isnull(v.AgeLow, '') = '' then 0 else v.AgeLow end) <> isnull(d.AgeLow, -1) and d.code < 500000
GO
Update ICD_DiagFileNRS set AgeHigh = (case when isnull(v.AgeHigh, '') = '' then 0 else v.AgeHigh end)
--select v.*, d.AgeHigh
from zzICD10CA_NRS_Validation v
	inner join ICD_DiagFileNRS d on v.cihivalue = d.cihivalue   
where (case when isnull(v.AgeHigh, '') = '' then 0 else v.AgeHigh end) <> isnull(d.AgeHigh, -1) and d.code < 500000
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10_CC1_NRS_Validation')
    drop table zzICD10_CC1_NRS_Validation
GO
Create table zzICD10_CC1_NRS_Validation(
    CIHIValue varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    Sector   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    CIHIFlag   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    Gender   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    AgeLow   varchar(6) COLLATE Latin1_General_CI_AS NOT NULL,
    AgeHigh   varchar(6) COLLATE Latin1_General_CI_AS NOT NULL
)
GO
bulk insert zzICD10_CC1_NRS_Validation  from '<path>NRS_CCI_Validation_FY2018-2019_v1.0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
GO
Update ICD_ProcFileNRS set DiagnosisGender = v.Gender
--select v.*, d.DiagnosisGender
from zzICD10_CC1_NRS_Validation v
	inner join ICD_ProcFileNRS d on v.cihivalue = d.cihivalue   
where isnull(v.Gender, '@') <> isnull(d.DiagnosisGender, '#') and d.Code < 500000
GO
Update ICD_ProcFileNRS set AgeLow = (case when isnull(v.AgeLow, '') = '' then 0 else v.AgeLow end)
--select v.*, d.AgeLow
from zzICD10_CC1_NRS_Validation v
	inner join ICD_ProcFileNRS d on v.cihivalue = d.cihivalue   
where (case when isnull(v.AgeLow, '') = '' then 0 else v.AgeLow end) <> isnull(d.AgeLow, -1) and d.Code < 500000
GO
Update ICD_ProcFileNRS set AgeHigh = (case when isnull(v.AgeHigh, '') = '' then 0 else v.AgeHigh end)
--select v.*, d.AgeLow
from zzICD10_CC1_NRS_Validation v
	inner join ICD_ProcFileNRS d on v.cihivalue = d.cihivalue   
where (case when isnull(v.AgeHigh, '') = '' then 0 else v.AgeHigh end) <> isnull(d.AgeHigh, -1) and d.Code < 500000
GO

if exists (select * from information_schema.tables where table_name = 'zzICD10_diagNRS')
    drop table zzICD10_diagNRS
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10_CCI_NRS')
    drop table zzICD10_CCI_NRS
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10CA_NRS_Validation')
    drop table zzICD10CA_NRS_Validation
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10_CC1_NRS_Validation')
    drop table zzICD10_CC1_NRS_Validation
GO
if exists (select * from information_schema.tables where table_name = 'zz2018_CCI_Rehab')
    drop table zz2018_CCI_Rehab
GO
if exists (select * from information_schema.tables where table_name = 'zz2018_CA_Rehab')
    drop table zz2018_CA_Rehab
GO

--********************** Cancer Care
if not exists (select * from information_schema.columns where table_name = 'LU_AJCC7_SITE_GRP' and column_name = 'Staging_Version')
    alter table LU_AJCC7_SITE_GRP add Staging_Version varchar(10)
GO
if not exists (select * from information_schema.columns where table_name = 'LLU_CC_STAGE_VALUE' and column_name = 'Staging_Version')
    alter table LLU_CC_STAGE_VALUE add Staging_Version varchar(10)
GO


if exists (select * from information_schema.tables where table_name = 'zzAJCC8_Stage')
    drop table zzAJCC8_Stage
GO
Create table zzAJCC8_Stage(
    AJCC8_CHAPTER_KEY varchar(10),
    AJCC8_CHAPTER_NUM varchar(100),
    AJCC8_CHAPTER_DESCR varchar(200),
    STAGE varchar(10),
    STAGING_METHOD varchar(10)
)
GO
bulk insert zzAJCC8_Stage  from '<path>2018AJCC8_Stage.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
update zzAJCC8_Stage
set AJCC8_CHAPTER_KEY = ltrim(rtrim(AJCC8_CHAPTER_KEY)),
    AJCC8_CHAPTER_DESCR = ltrim(rtrim(AJCC8_CHAPTER_DESCR)),
    STAGE = ltrim(rtrim(STAGE)),
    STAGING_METHOD = ltrim(rtrim(STAGING_METHOD))
GO
alter table zzAJCC8_Stage add code int identity(1, 1)
GO
declare @MaxID int
set @MaxID = isnull((select max(code) from [dbo].[LLU_CC_STAGE_VALUE]), 0)

insert into [dbo].[LLU_CC_STAGE_VALUE](Code, AJCC_SITE_GRP, STAGE, STAGING_METHOD, ValidFrom, ValidTo, Staging_Version)
select @MaxID + z.code, z.AJCC8_CHAPTER_KEY, z.STAGE, z.STAGING_METHOD, '2018/04/01', '2099/03/31', '08'
from zzAJCC8_Stage z
    left join [dbo].[LLU_CC_STAGE_VALUE] v on z.AJCC8_CHAPTER_KEY = v.[AJCC_SITE_GRP]
        and z.[STAGE] = v.[STAGE] and v.[Staging_Version] = '08'
where v.code is null
GO
if exists (select * from information_schema.tables where table_name = 'zzAJCC8_Stage')
    drop table zzAJCC8_Stage
GO
if exists (select * from information_schema.tables where table_name = 'zzICDCA_ICDO3_Mapping')
    drop table zzICDCA_ICDO3_Mapping
GO
if exists (select * from information_schema.tables where table_name = 'zzICDCA_ICDO3_Mapping_new')
    drop table zzICDCA_ICDO3_Mapping_new
GO
Create table zzICDCA_ICDO3_Mapping(
    ICD10CA_CD varchar(10),
    ICDO3_TOPOG_CD varchar(10),
    ICDO3_MORPH_CD varchar(10),
    HISTOLOGY_CD varchar(10),
    BEHAVIOUR_CD varchar(10)
)
GO
bulk insert zzICDCA_ICDO3_Mapping  from '<path>2018_CA_ICDO3_Mapping.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
update zzICDCA_ICDO3_Mapping
set ICD10CA_CD = ltrim(rtrim(ICD10CA_CD)),
    ICDO3_TOPOG_CD = ltrim(rtrim(ICDO3_TOPOG_CD)),
    ICDO3_MORPH_CD = ltrim(rtrim(ICDO3_MORPH_CD)),
    HISTOLOGY_CD = ltrim(rtrim(HISTOLOGY_CD)),
    BEHAVIOUR_CD = ltrim(rtrim(BEHAVIOUR_CD))
GO
select z.*, code = identity(int, 1, 1) into zzICDCA_ICDO3_Mapping_new
from zzICDCA_ICDO3_Mapping z
    left join [dbo].[LLU_CC_Topo03_ICD10CA] m on z.ICD10CA_CD = m.[ICD10CA] and z.ICDO3_TOPOG_CD = m.[TopographyICD_03]
where m.code is null or m.validto < '2018/01/01'
GO
declare @MaxID int
set @MaxID = isnull((select max(code) from [dbo].[LLU_CC_Topo03_ICD10CA]), 0)

insert into [dbo].[LLU_CC_Topo03_ICD10CA](Code, TopographyICD_03, ICD10CA, ValidFrom, ValidTo)
select @MaxID + z.code, z.ICDO3_TOPOG_CD, z.ICD10CA_CD, '2018/01/01', '2099/03/31'
from zzICDCA_ICDO3_Mapping_new z
GO
if exists (select * from information_schema.tables where table_name = 'zzICDCA_ICDO3_Mapping_new')
    drop table zzICDCA_ICDO3_Mapping_new
GO
if exists (select * from information_schema.tables where table_name = 'zzICDCA_ICDO3_Mapping_new')
    drop table zzICDCA_ICDO3_Mapping_new
GO

if exists (select * from information_schema.tables where table_name = 'zzICDCA_Morph_Mapping_new')
    drop table zzICDCA_Morph_Mapping_new
GO
select z.*, code = identity(int, 1, 1) into zzICDCA_Morph_Mapping_new
from zzICDCA_ICDO3_Mapping z
    left join [dbo].[LLU_CC_Morpho03_ICD10CA] m on z.ICD10CA_CD = m.[ICD10CA] and z.ICDO3_MORPH_CD = m.[MorphologyICD_03]
where m.code is null or m.validto < '2018/01/01'
GO
declare @MaxID int
set @MaxID = isnull((select max(code) from [dbo].[LLU_CC_Morpho03_ICD10CA]), 0)

insert into [dbo].[LLU_CC_Morpho03_ICD10CA](Code, MorphologyICD_03, ICD10CA, ValidFrom, ValidTo)
select @MaxID + z.code, z.ICDO3_MORPH_CD, z.ICD10CA_CD, '2018/01/01', '2099/03/31'
from zzICDCA_Morph_Mapping_new z
GO
if exists (select * from information_schema.tables where table_name = 'zzICDCA_Morph_Mapping_new')
    drop table zzICDCA_Morph_Mapping_new
GO

if exists (select * from information_schema.tables where table_name = 'zzAJCC8_ICDO3')
    drop table zzAJCC8_ICDO3
GO
Create table zzAJCC8_ICDO3(
    AJCC8_CHAPTER_NUM varchar(10),
    ICDO3_TOPOGRAPHY_CODE varchar(10),
    ICDO3_TOPOGRAPHY_DESC varchar(200),
    ICDO3_HISTOLOGY_CODE varchar(10),
    ICDO3_HISTOLOGY_DESCR varchar(200),
    AJCC8_CHAPTER_KEY varchar(10)
)
GO
bulk insert zzAJCC8_ICDO3  from '<path>2018AJCC8_ICDO3.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
update zzAJCC8_ICDO3
set AJCC8_CHAPTER_KEY = ltrim(rtrim(AJCC8_CHAPTER_KEY)),
    ICDO3_TOPOGRAPHY_CODE = ltrim(rtrim(ICDO3_TOPOGRAPHY_CODE)),
    ICDO3_TOPOGRAPHY_DESC = ltrim(rtrim(ICDO3_TOPOGRAPHY_DESC)),
    ICDO3_HISTOLOGY_CODE = ltrim(rtrim(ICDO3_HISTOLOGY_CODE)),
    ICDO3_HISTOLOGY_DESCR = ltrim(rtrim(ICDO3_HISTOLOGY_DESCR))
GO
alter table zzAJCC8_ICDO3 add code int identity(1, 1)
GO
declare @MaxID int
set @MaxID = isnull((select max(code) from [dbo].LLU_CC_ICD03_Topo_Morpho), 0)

insert into [dbo].LLU_CC_ICD03_Topo_Morpho(CODE, ICDO3_TOPO, ICDO3_Morpho, AJCC_SITEGRP_CD, Staging_Version, Stageable_Flag, ValidFrom, ValidTo)
select @MaxID + z.code, z.ICDO3_TOPOGRAPHY_CODE, z.ICDO3_HISTOLOGY_CODE, z.AJCC8_CHAPTER_KEY, '08',
    [Stageable_Flag] = (case when t.[AJCC_SITE_GRP] is null then 'N' else 'Y' end), 
    '2018/04/01', '2099/03/31'
from zzAJCC8_ICDO3 z
    left join [dbo].LLU_CC_ICD03_Topo_Morpho v on z.AJCC8_CHAPTER_KEY = v.[AJCC_SITEGRP_CD]
        and z.ICDO3_TOPOGRAPHY_CODE = v.[ICDO3_TOPO]
        and z.ICDO3_HISTOLOGY_CODE = v.[ICDO3_Morpho]
        and v.[Staging_Version] = '08'
    left join (select distinct [AJCC_SITE_GRP], [STAGING_METHOD] from [dbo].[LLU_CC_STAGE_VALUE] where [Staging_Version] = '08' and [STAGING_METHOD] = 'TNM')  t on z.AJCC8_CHAPTER_KEY = t.[AJCC_SITE_GRP]
where v.code is null
GO
if exists (select * from information_schema.tables where table_name = 'zzAJCC8_ICDO3_Group')
    drop table zzAJCC8_ICDO3_Group
GO
select m.ICD10CA_CD, a.AJCC8_CHAPTER_KEY, code = identity(int, 1, 1) into zzAJCC8_ICDO3_Group
from
(select distinct ICDO3_TOPOGRAPHY_CODE, AJCC8_CHAPTER_KEY
from zzAJCC8_ICDO3) a
    inner join zzICDCA_ICDO3_Mapping m on a.ICDO3_TOPOGRAPHY_CODE = m.ICDO3_TOPOG_CD
order by m.ICD10CA_CD
GO
declare @MaxID int
set @MaxID = isnull((select max(code) from [dbo].LLU_CC_SITE_GROUP), 0)

insert into [dbo].LLU_CC_SITE_GROUP(Code, ICD10CA, AJCC_SITE_GRP, STAGEABLE, ValidFrom, ValidTo, Staging_Version)
select @MaxID + z.code, z.ICD10CA_CD, z.AJCC8_CHAPTER_KEY,
    [Stageable_Flag] = (case when t.[AJCC_SITE_GRP] is null then 'N' else 'Y' end), 
    '2018/04/01', '2099/03/31', '08'
from zzAJCC8_ICDO3_Group z
    left join [dbo].LLU_CC_SITE_GROUP v on z.AJCC8_CHAPTER_KEY = v.AJCC_SITE_GRP
        and z.ICD10CA_CD = v.ICD10CA
        and v.[Staging_Version] = '08'
    left join (select distinct [AJCC_SITE_GRP] from [dbo].[LLU_CC_STAGE_VALUE] where [Staging_Version] = '08')  t on z.AJCC8_CHAPTER_KEY = t.[AJCC_SITE_GRP]
where v.code is null
GO
if exists (select * from information_schema.tables where table_name = 'zzAJCC8_ICDO3_Group')
    drop table zzAJCC8_ICDO3_Group
GO
if exists (select * from information_schema.tables where table_name = 'zzAJCC8_ICDO3')
    drop table zzAJCC8_ICDO3
GO
