IF EXISTS (SELECT name  FROM sys.key_constraints  
         WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'TMDLOZELDURUM')

	ALTER TABLE TMDLOZELDURUM
	    DROP CONSTRAINT PK_TMDLOZELDURUM;

GO  

IF EXISTS (SELECT * FROM SYSCOLUMNS INNER JOIN SYSOBJECTS ON SYSOBJECTS.id = SYSCOLUMNS.id 
              WHERE SYSCOLUMNS.NAME = 'ID' AND SYSOBJECTS.name = 'TMDLOZELDURUM')
BEGIN
	ALTER TABLE TMDLOZELDURUM DROP COLUMN ID
    ALTER TABLE TMDLOZELDURUM ADD  ID INT NULL
END
GO

IF NOT EXISTS (SELECT * FROM SYSCOLUMNS INNER JOIN SYSOBJECTS ON SYSOBJECTS.id = SYSCOLUMNS.id 
              WHERE SYSCOLUMNS.NAME = 'ID' AND SYSOBJECTS.name = 'TMDLOZELDURUM')
BEGIN
 ALTER TABLE TMDLOZELDURUM ADD  ID INT NULL
END
GO


if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='@' )   UPDATE TMDLOZELDURUM SET ID=1 WHERE MDLOZELDURUMKOD='@'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='|' )   UPDATE TMDLOZELDURUM SET ID=2 WHERE MDLOZELDURUMKOD='|'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='0' )   UPDATE TMDLOZELDURUM SET ID=3 WHERE MDLOZELDURUMKOD='0'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='1' )   UPDATE TMDLOZELDURUM SET ID=4 WHERE MDLOZELDURUMKOD='1'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='2' )   UPDATE TMDLOZELDURUM SET ID=5 WHERE MDLOZELDURUMKOD='2'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='3' )   UPDATE TMDLOZELDURUM SET ID=6 WHERE MDLOZELDURUMKOD='3'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='4' )   UPDATE TMDLOZELDURUM SET ID=7 WHERE MDLOZELDURUMKOD='4'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='5' )   UPDATE TMDLOZELDURUM SET ID=8 WHERE MDLOZELDURUMKOD='5'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='6' )   UPDATE TMDLOZELDURUM SET ID=9 WHERE MDLOZELDURUMKOD='6'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='7' )   UPDATE TMDLOZELDURUM SET ID=10 WHERE MDLOZELDURUMKOD='7'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='8' )   UPDATE TMDLOZELDURUM SET ID=11 WHERE MDLOZELDURUMKOD='8'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='9' )   UPDATE TMDLOZELDURUM SET ID=12 WHERE MDLOZELDURUMKOD='9'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='A' )   UPDATE TMDLOZELDURUM SET ID=13 WHERE MDLOZELDURUMKOD='A'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='B' )   UPDATE TMDLOZELDURUM SET ID=14 WHERE MDLOZELDURUMKOD='B'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='C' )   UPDATE TMDLOZELDURUM SET ID=15 WHERE MDLOZELDURUMKOD='C'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='D' )   UPDATE TMDLOZELDURUM SET ID=16 WHERE MDLOZELDURUMKOD='D'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='E' )   UPDATE TMDLOZELDURUM SET ID=17 WHERE MDLOZELDURUMKOD='E'
GO
if  exists (select *  from TMDLOZELDURUM where ASCII(MDLOZELDURUMKOD)=ASCII('F'))   UPDATE TMDLOZELDURUM SET ID=18 WHERE ASCII(MDLOZELDURUMKOD)=ASCII('F')
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='G' )   UPDATE TMDLOZELDURUM SET ID=19 WHERE MDLOZELDURUMKOD='G'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='gg' )   UPDATE TMDLOZELDURUM SET ID=20 WHERE MDLOZELDURUMKOD='gg'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='H' )   UPDATE TMDLOZELDURUM SET ID=21 WHERE MDLOZELDURUMKOD='H'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='hh' )   UPDATE TMDLOZELDURUM SET ID=22 WHERE MDLOZELDURUMKOD='hh'
GO
if  exists (select *  from TMDLOZELDURUM where ASCII(MDLOZELDURUMKOD)=ASCII('I')  )   UPDATE TMDLOZELDURUM SET ID=23 WHERE  ASCII(MDLOZELDURUMKOD)=ASCII('I') 
GO
if  exists (select *  from TMDLOZELDURUM where ASCII(MDLOZELDURUMKOD)=ASCII('i') )   UPDATE TMDLOZELDURUM SET ID=24 WHERE  ASCII(MDLOZELDURUMKOD)=ASCII('i')
GO
if  exists (select *  from TMDLOZELDURUM where ASCII(MDLOZELDURUMKOD)=ASCII('J') )   UPDATE TMDLOZELDURUM SET ID=25 WHERE ASCII(MDLOZELDURUMKOD)=ASCII('J') 
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='K' )   UPDATE TMDLOZELDURUM SET ID=26 WHERE MDLOZELDURUMKOD='K'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='L' )   UPDATE TMDLOZELDURUM SET ID=27 WHERE MDLOZELDURUMKOD='L'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='M' )   UPDATE TMDLOZELDURUM SET ID=28 WHERE MDLOZELDURUMKOD='M'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='N' )   UPDATE TMDLOZELDURUM SET ID=29 WHERE MDLOZELDURUMKOD='N'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='O' )   UPDATE TMDLOZELDURUM SET ID=30 WHERE MDLOZELDURUMKOD='O'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='P' )   UPDATE TMDLOZELDURUM SET ID=31 WHERE MDLOZELDURUMKOD='P'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='R' )   UPDATE TMDLOZELDURUM SET ID=32 WHERE MDLOZELDURUMKOD='R'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='S' )   UPDATE TMDLOZELDURUM SET ID=33 WHERE MDLOZELDURUMKOD='S'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='T' )   UPDATE TMDLOZELDURUM SET ID=34 WHERE MDLOZELDURUMKOD='T'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='U' )   UPDATE TMDLOZELDURUM SET ID=35, AKTIF=0,PASIFTARIHI='2021-03-01 00:00:00' WHERE MDLOZELDURUMKOD='U'
GO
if  exists (select *  from TMDLOZELDURUM where MDLOZELDURUMKOD='Z' )   UPDATE TMDLOZELDURUM SET ID=36 WHERE MDLOZELDURUMKOD='Z'
GO
if  exists (select *  from TMDLOZELDURUM where ASCII(MDLOZELDURUMKOD)= ASCII('j') )   UPDATE TMDLOZELDURUM SET ID=37 WHERE ASCII(MDLOZELDURUMKOD)= ASCII('j')
GO
if  exists (select *  from TMDLOZELDURUM where ASCII(MDLOZELDURUMKOD)= ASCII('c') )   UPDATE TMDLOZELDURUM SET ID=38 WHERE ASCII(MDLOZELDURUMKOD)= ASCII('c')
GO
if  exists (select *  from TMDLOZELDURUM where ASCII(MDLOZELDURUMKOD)= ASCII('d') )   UPDATE TMDLOZELDURUM SET ID=39 WHERE ASCII(MDLOZELDURUMKOD)= ASCII('d')
GO
if  exists (select *  from TMDLOZELDURUM where ASCII(MDLOZELDURUMKOD)=ASCII('f'))   UPDATE TMDLOZELDURUM SET ID=40 WHERE ASCII(MDLOZELDURUMKOD)=ASCII('f')
GO

IF not EXISTS(select * from dbo.sysobjects where id = object_id(N'PK_TMDLOZELDURUM') and xtype in (N'PK'))
BEGIN
 ALTER TABLE TMDLOZELDURUM ALTER COLUMN ID INT NOT NULL;
END

GO

IF not EXISTS(select * from dbo.sysobjects where id = object_id(N'PK_TMDLOZELDURUM') and xtype in (N'PK'))
BEGIN
	ALTER TABLE dbo.TMDLOZELDURUM ADD CONSTRAINT
		PK_TMDLOZELDURUM PRIMARY KEY CLUSTERED 
		(
		ID
		) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

END
GO


IF NOT EXISTS (SELECT * FROM SYSCOLUMNS INNER JOIN SYSOBJECTS ON SYSOBJECTS.id = SYSCOLUMNS.id 
              WHERE SYSCOLUMNS.NAME = 'MDLOZELDURUMKODID' AND SYSOBJECTS.name = 'HESAPISL')

    ALTER TABLE HESAPISL ADD  MDLOZELDURUMKODID INT NULL


GO

UPDATE HESAPISL SET  MDLOZELDURUMKODID=TMDLOZELDURUM.ID  FROM HESAPISL 
	INNER JOIN TMDLOZELDURUM ON TMDLOZELDURUM.MDLOZELDURUMKOD=HESAPISL.MDLOZELDURUMKOD 
		WHERE YEAR(ITARIH)='2021' AND ISNULL(HESAPISL.MDLOZELDURUMKOD,'')<>''

GO

