--delete from facturas
--delete from fac_deta

-- delete from turnos_auditoria
-- delete from turnos
-- delete from turnos_bkp
-- delete from practicas_cie10_registracion
-- delete from practicas_autorizar_web
-- delete from practica_autorizar
 --select * from practica
 --delete from practica
 --select * from clientes

 --delete from practica

-- delete from cli_antecedentes
--delete from cli_conceptos
--delete from cli_derivaciones
--delete from cli_desarrollo
--delete from cli_determinaciones
--delete from cli_diagnostico_cie10
--delete from cli_diagnosticos
--delete from cli_ecocardiograma
--delete from cli_enfermeria_acciones
--delete from cli_estudiospedidos
--delete from cli_examenes
--delete from cli_ficha_ecg
--delete from cli_ficha_fim
--delete from cli_ficha_kinesiologia
--delete from cli_ficha_kinesiologia_evo
--delete from cli_fichaenfermeria
--delete from cli_fichaestudios
--delete from cli_fichaguss
--delete from cli_fichainternacion
--delete from cli_fichainternacion_cie10
--delete from cli_fichainternacion_evo_cie10
--delete from cli_fichainternacion_evolucion
--delete from cli_fichainternacion_evolucion_audit
--delete from cli_fichainternacion_ordenes
--delete from cli_fichapediatrica
--delete from cli_fichaperinatal
--delete from cli_fichaperinatal_evolucion
--delete from cli_flebologia
--delete from cli_imagenes
--delete from cli_informeenfermeria
--delete from cli_informeenfermeria_auditoria
--delete from cli_laboratorio_analisis
--delete from cli_laboratorio_analisis_detalle
--delete from cli_laboratorio_covid
--delete from cli_laboratorios
--delete from cli_lesionmedular_asa
--delete from cli_medicamentos
--delete from cli_paps_seguimiento
--delete from cli_preoperatorio
--delete from cli_preoperatorio_cuestionario
--delete from cli_recetas
--delete from cli_tamizajenutricional
--delete from cli_vacunas

--delete from oi_enlazadas
--delete from oi_medicamentos
--delete from oi_practicas
--delete from oi_practicas_auditoria
--delete from ointernacion_autorizar_web
--delete from ointernacion_cie10_registracion
--delete from ointernacion_practicas_registracion
--delete from orden_internacion_cie10
--delete from orden_internacion
-- delete from clientes where cli_codigo > 1
 
-- DELETE FROM dbo.ws_obsaut
-- SELECT * FROM obrasoc
-- DELETE FROM obrasoc WHERE obs_codigo <> 7000
-- UPDATE obrasoc SET obs_codigo = 7000 WHERE obs_codigo = 136

--delete from consultorios_usos

-- select * from medicos
-- delete from medicos

-- delete from cajas_saldos
-- SELECT * from cajas
--delete from accesos
--delete from accesos_detalle
--delete from accesos_procesos

--SELECT * from avisos
--delete from avisos


  SELECT T.name AS Table_Name ,
       C.name AS Column_Name ,
       P.name AS Data_Type ,
       P.max_length AS Size ,
       CAST(P.precision AS VARCHAR) + '/' + CAST(P.scale AS VARCHAR) AS Precision_Scale
FROM   sys.objects AS T
       JOIN sys.columns AS C ON T.object_id = C.object_id
       JOIN sys.types AS P ON C.system_type_id = P.system_type_id
WHERE  T.type_desc = 'USER_TABLE'
order by 1,2


 SELECT DISTINCT CONCAT('delete from ',T.name) AS Table_Name
FROM   sys.objects AS T
       JOIN sys.columns AS C ON T.object_id = C.object_id
       JOIN sys.types AS P ON C.system_type_id = P.system_type_id
WHERE  T.type_desc = 'USER_TABLE'
order by 1


-- ----------------------------------
-- MIGRACION
-- ----------------------------------
 INSERT INTO [Landamed].[dbo].[tbl_tipoid] ([tid_codigo]
      ,[tid_denom]
      ,[tid_abrev])
	  VALUES 	  
(2,'PAS','PAS')
,(3,'CI','CI')
,(4,'LC','LC')
,(5,'LE','LE')
,(6,'CF','CF')

-- ----------------------
-- DELETE FROM landamed.dbo.medicos
INSERT INTO Landamed.dbo.medicos
           ([med_codigo]
           ,[med_nombre]
           ,[med_dni]
		   ,[med_mp]
		   ,[esp_codigo]
           ,pro_codigo
		   ,loc_codigo
           )
     
select 		t1.ID
           ,t1.Nombre
		   ,t1.DNI000
		   ,t1.Matric
		   ,18
		   ,3
		   ,1
from ionc_backup.dbo.Medicos as t1
-- ,(SELECT t0.ID FROM ionc_backup.dbo.Consultorios as t0 WHERE t0.IDMedi = t1.ID )

-- -----------------------------------------

CREATE TABLE [dbo].[medico_consultorios](
	[medcon_numero] [int] IDENTITY(1,1) NOT NULL,
	[med_codigo] [smallint] NOT NULL,
	[con_numero] [int] NULL,
 CONSTRAINT [PK_medcon] PRIMARY KEY CLUSTERED 
(
	[medcon_numero] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO Landamed.dbo.medico_consultorios
           ([med_codigo]
           ,[con_numero]
           )
     
select 		t1.IDMedi
           ,t1.ID
from ionc_backup.dbo.Consultorios as t1

-- -----------------------------------------

INSERT INTO [Landamed].[dbo].[obrasoc] (
	   [obs_codigo]
      ,[obs_denom]
      ,[dgi_codigo]
      ,[pro_codigo]
      ,[loc_codigo]
	  ,[obs_dir]
	  ,[obs_observ]
	  ,[obs_lock]
	  ,[obs_fecing]
)

SELECT [ID]
      ,[Nombre]
	  ,1
	  ,3
	  ,1
	  ,[Domici]
	  ,'MIGRADO 07/11/2023'
	  ,(CASE WHEN [Baja00]='N' THEN 0 ELSE 1 END)
	  ,GETDATE()
  FROM [ionc_backup].[dbo].[Mutuales]


-- -----------------------------------------

  INSERT INTO [Landamed].[dbo].[obsplan] (obs_codigo,obsp_cod,obsp_denom)
  SELECT obs_codigo,1,'PLAN UNICO'
  FROM obrasoc
  WHERE obs_codigo  <> 7000
  
-- -----------------------------------------
-- UPDATE [ionc_backup].[dbo].[Pacientes] SET [NumDoc] = '33639400' WHERE  ID=7508
-- UPDATE [ionc_backup].[dbo].[Pacientes] SET [NumDoc] = null WHERE  ID=9311
-- UPDATE [ionc_backup].[dbo].[Pacientes] SET [NumDoc] = '16764851' WHERE  ID=10182
-- UPDATE [ionc_backup].[dbo].[Pacientes] SET [NumDoc] = '13035118' WHERE  ID=11840

-- UPDATE [ionc_backup].[dbo].[Pacientes] SET [NumDoc] = NULL WHERE  [NumDoc] = 'NO'
-- UPDATE [ionc_backup].[dbo].[Pacientes] SET [NumDoc] = NULL WHERE  [NumDoc] = 'no'
-- UPDATE [ionc_backup].[dbo].[Pacientes] SET [NumDoc] = NULL WHERE  [NumDoc] = 'No'
-- UPDATE [ionc_backup].[dbo].[Pacientes] SET [NumDoc] = NULL WHERE  [NumDoc] = 'nO'
-- DELETE FROM landamed.dbo.clientes
INSERT INTO Landamed.dbo.clientes
           ([cli_codigo]
           ,[cli_nombre]
		   ,[cli_dir]
		   ,[loc_codigo]
		   ,[cli_sexo]
		   ,[cli_tel]
		   ,[cli_observ]
		   ,[cli_carnet]
		   ,[cli_email]
		   ,[cli_dni]
		   ,[cli_bloqueado]
		   ,[tid_codigo]
           ,[cli_fecnto]
		   ,obs_codigo
		   ,obsp_cod
		   )
           
		   
SELECT  [ID]
      ,[Nombre]
      ,[Domici]
      ,1
      ,(CASE WHEN [Sexo00]='F' THEN 'F' WHEN [Sexo00]='M' THEN 'M' ELSE NULL END)
	  ,[TE0001]
	  ,CONCAT([Observ], ' - MIGRADO 07/11/2023')
	  ,[Afilia]
	  ,[Email1]
	  ,REPLACE(REPLACE([NumDoc],'.','')  ,'-','')
	  ,(CASE WHEN [Baja00]='N' THEN 0 ELSE 1 END)
	  ,(CASE WHEN IDTiDo = 'PAS' THEN 2
	  WHEN IDTiDo = 'CI' THEN 3
	  WHEN IDTiDo = 'LC' THEN 4
	  WHEN IDTiDo = 'LE' THEN 5
	  WHEN IDTiDo = 'CF' THEN 6
	  ELSE 1 END)
		,FecNac
		,[IDMutu]
		,[Plan00]
  FROM [ionc_backup].[dbo].[Pacientes]
WHERE id>1  and id<> 6218


-- update [ionc_backup].[dbo].[Pacientes] set [NumDoc] =10805016  WHERE  id = 6218

INSERT INTO Landamed.dbo.clientes
           ([cli_codigo]
           ,[cli_nombre]
		   ,[cli_dir]
		   ,[loc_codigo]
		   ,[cli_sexo]
		   ,[cli_tel]
		   ,[cli_observ]
		   ,[cli_carnet]
		   ,[cli_email]
		   ,[cli_dni]
		   ,[cli_bloqueado]
		   ,[tid_codigo]
           ,[cli_fecnto]
		   ,obs_codigo
		   ,obsp_cod
		   )
           
		   
SELECT  [ID]
      ,[Nombre]
      ,[Domici]
      ,1
      ,(CASE WHEN [Sexo00]='F' THEN 'F' WHEN [Sexo00]='M' THEN 'M' ELSE NULL END)
	  ,[TE0001]
	  ,CONCAT([Observ], ' - MIGRADO 07/11/2023')
	  ,[Afilia]
	  ,[Email1]
	  ,REPLACE(REPLACE([NumDoc],'.','')  ,'-','')
	  ,(CASE WHEN [Baja00]='N' THEN 0 ELSE 1 END)
	  ,(CASE WHEN IDTiDo = 'PAS' THEN 2
	  WHEN IDTiDo = 'CI' THEN 3
	  WHEN IDTiDo = 'LC' THEN 4
	  WHEN IDTiDo = 'LE' THEN 5
	  WHEN IDTiDo = 'CF' THEN 6
	  ELSE 1 END)
		,FecNac
		,[IDMutu]
		,[Plan00]
  FROM [ionc_backup].[dbo].[Pacientes]
WHERE  id = 6218


-- ---------------------------------------
INSERT INTO Landamed.dbo.empleado (
,emp_codigo
,emp_nombre
,emp_login
,emp_alta
,emp_activo
,emp_passw)
VALUES 
(100,'Melina Favaro','mFavaro',GETDATE(),1,'AC2C84139A3B22180DCC509A6F75849B'),
(101,'Florencia Iñiguez','fiñiguez',GETDATE(),1,'AC2C84139A3B22180DCC509A6F75849B'),
(102,'Vanina Magri','vmagri',GETDATE(),1,'AC2C84139A3B22180DCC509A6F75849B'),
(103,'Martin Richardet','mrichardet',GETDATE(),1,'AC2C84139A3B22180DCC509A6F75849B'),
(104,'Matias Molina','mmolina',GETDATE(),1,'AC2C84139A3B22180DCC509A6F75849B'),
(105,'Eduardo Richardet','erichardet',GETDATE(),1,'AC2C84139A3B22180DCC509A6F75849B'),
(106,'Ignacio Magi','imagi',GETDATE(),1,'AC2C84139A3B22180DCC509A6F75849B'),
(107,'Antonella Tello','atello',GETDATE(),1,'AC2C84139A3B22180DCC509A6F75849B'),
(108,'Pablo Perea','pperea',GETDATE(),1,'AC2C84139A3B22180DCC509A6F75849B'),
(109,'Santos Depetris','adepetris',GETDATE(),1,'AC2C84139A3B22180DCC509A6F75849B'),
(110,'Valeria Richardet','vrichardet',GETDATE(),1,'AC2C84139A3B22180DCC509A6F75849B'),
(111,'Mariana Palou','mpalou',GETDATE(),1,'AC2C84139A3B22180DCC509A6F75849B'),
(112,'Martin Vazquez','mvazquez',GETDATE(),1,'AC2C84139A3B22180DCC509A6F75849B'),
(113,'Maria Fredes','mfredes',GETDATE(),1,'AC2C84139A3B22180DCC509A6F75849B'),
(114,'Fatima Allende','fallende',GETDATE(),1,'AC2C84139A3B22180DCC509A6F75849B'),
(115,'Romina  Massaccesi','rmassaccesi',GETDATE(),1,'AC2C84139A3B22180DCC509A6F75849B')

INSERT INTO [Landamed].[dbo].[empcargo] ([emp_codigo]
      ,[cgo_codigo])
VALUES 
(100,1),
(101,1),
(102,1),
(103,1),
(104,1),
(105,1),
(106,1),
(107,1),
(108,1),
(109,1),
(110,1),
(111,1),
(112,1),
(113,1),
(114,1),
(115,1)

-- --------------------------
-- DELETE FROM landamed.dbo.medhora
insert into landamed.dbo.medhora
(
med_codigo
,mh_dia
,mh_desde1
,mh_hasta1
,mh_desde2
,mh_hasta2
,mh_int1
,mh_int2
)

SELECT (SELECT t1.IDMedi from [ionc_backup].[dbo].[Consultorios] as t1 WHERE t1.ID = t0.IDCons)
      ,(CASE WHEN t0.Dia000 = 'Jue' THEN 5
WHEN t0.Dia000 = 'Lun' THEN 2
WHEN t0.Dia000 = 'Mar' THEN 3
WHEN t0.Dia000 = 'Mié' THEN 4
ELSE  6 END)
      ,REPLACE(t0.HorDes,':','')
      ,REPLACE(t0.HorHas,':','')
	  ,0
	  ,0
	  ,15
	  ,0
  FROM [ionc_backup].[dbo].[Horarios] AS t0
  where t0.Dia000 in ('Jue','Lun','Mar','Mié','Vie')
  -- and IDCons <= 45
  
-- -----------------------------
-- DELETE FROM landamed.dbo.turnos
INSERT INTO landamed.dbo.turnos(
  	   [tur_numero]
      ,[tur_fecha]
      ,[tur_hora]
      ,[med_codigo]
      ,[cli_codigo]
      ,[obs_codigo]
      ,[obsp_cod]
	  ,[tur_comen]
	  ,[nom_codigo]
      ,[inos_cod]
	  ,[usu_codigo]
	  ,[tur_impobs]
      ,[tur_impart]
)
  
SELECT t0.[ID]
	  ,t0.[FecIni]
	  ,REPLACE(t0.HorIni,':','')
	  ,(SELECT t1.IDMedi from [ionc_backup].[dbo].[Consultorios] as t1 WHERE t1.ID = t0.IDCons)
 --	  ,t0.[IDCons]
	  ,t0.[IDPaci]     
      ,t0.[IDMutu]
	  ,1
	  ,CONCAT(t0.[NomPro],' - MIGRADO 07/11/2023')
	  ,1
	  ,'420101'
	  ,98
	  ,0
	  ,0
  FROM [ionc_backup].[dbo].[Turnos00] as t0
  where LEFT(CONVERT(VARCHAR,t0.FecIni, 120), 10) >= LEFT(CONVERT(VARCHAR,'2023-11-01', 120), 10)
  -- AND EXISTS (SELECT * FROM [landamed].[dbo].medicos as t1 where  t0.IDCons = t1.med_codigo)
  -- AND EXISTS (SELECT * FROM [landamed].[dbo].medicos as t1 where  t0.IDCons = t1.consulid)
  AND EXISTS (SELECT * FROM [landamed].[dbo].clientes as t2 where  t0.IDPaci = t2.cli_codigo)  
  
-- -------------------------------
-- DELETE FROM landamed.dbo.cli_diagnosticos
INSERT INTO [Landamed].[dbo].[cli_diagnosticos] 
(	cli_codigo,
	cd_item,
	cd_fecha,
	cd_observ,
	med_codigo,
	cd_motivo,
	cd_tstamp
)

SELECT [IDPaci]
	  ,1
      ,[Fecha0]
	  ,[Evoluc]
	  ,[IDMedi]
      ,'-'
      ,GETDATE()
  FROM [ionc_backup].[dbo].[Evolucion] AS t0
  WHERE  EXISTS (SELECT * FROM [landamed].[dbo].medicos as t1 where  t0.IDMedi = t1.med_codigo)
  AND EXISTS (SELECT * FROM [landamed].[dbo].clientes as t2 where  t0.IDPaci = t2.cli_codigo)  
  AND [Fecha0] IS NOT NULL  
  
  
  -- ------------------
  EXEC sp_fulltext_column
@tabname = 'clientes' ,
@colname = 'cli_nombre' ,
@action = 'drop'

EXEC sp_fulltext_column
@tabname = 'clientes' ,
@colname = 'cli_nombre' ,
@action = 'add'

-- ---------------------
  --delete from [Landamed].[dbo].cli_laboratorio_analisis
--delete from [Landamed].[dbo].cli_laboratorio_analisis_detalle
-- DELETE FROM [Landamed].[dbo].[lab_analisis]
-- DELETE FROM [Landamed].[dbo].[lab_analisis_detalle]


  INSERT INTO [Landamed].[dbo].[lab_analisis]
  (    [lab_codigo]
      ,[lab_denom]    
      ,[lab_titulo]
      ,[lab_pie]
	  ,lab_coment
  )
  SELECT  [ID]
      ,[Nombre]
	  ,[Nombre]
	  ,''
	  ,Observ
  FROM [ionc_backup].[dbo].[AnalisisGrupo]

INSERT INTO [Landamed].[dbo].[lab_analisis_detalle]
      ( [lab_codigo]
      ,[lad_codigo]
      ,[lad_denom]  
	  ,lad_undmed
      ,[lad_rango]
      ,[lad_orden]
      ,[lad_grupo]
      ,[lad_tipo])
  
SELECT [IDGrup]
      ,ID
      ,[Nombre]
      ,[UniMed]
      ,[ValRef]
      ,[Orden0]
	  ,''
	  ,'I'
  FROM [ionc_backup].[dbo].[AnalisisTipo]