--EXEC sp_addlinkedserver @server='Intelligence.karolinska.se', @srvproduct='', @provider=N'SQLNCLI', @datasrc='Intelligence.Karolinska.Se'
--EXEC sp_addlinkedsrvlogin @rmtsrvname='Intelligence.karolinska.se', @useself = 'FALSE', @locallogin = NULL, @rmtuser = '', @rmtpassword=''

--GO

--EXEC sp_configure 'remote query timeout', 0;
--GO
--RECONFIGURE;
--GO




DECLARE @oldUnitID varchar(20)
DECLARE @newUnitID varchar(20)
SET @oldUnitID = '123'
SET @newUnitID = '456'


Update Rave4.dbo.SystemSetting SET unit_id = @newUnitID WHERE unit_id = @oldUnitID
select 'UPDATE ' + TABLE_NAME + ' SET ' + COLUMN_NAME + ' = REPLACE(' + COLUMN_NAME + ', ''U' + @oldUnitID + ''', ''U' + @newUnitID + ''')' AS Query from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME IN ('patient_id', 'contact_id', 'care_provider_role_id', 'careprovider_social_security_no', 'careprovider_social_security_no_match', 'caregiver_social_security_no', 'caregiver_social_security_no_match')
or COLUMN_NAME like '%patient_id%'
UNION ALL
select 'UPDATE ' + TABLE_NAME + ' SET unit_id = ' + @newUnitId AS Query from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME = 'unit_id'
UNION ALL
select 'UPDATE ' + TABLE_NAME + ' SET data_from_previous_eprsystem = 1' AS Query from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME = 'data_from_previous_eprsystem'
UNION ALL
select 'UPDATE ' + TABLE_NAME + ' SET automatched = 0' AS Query from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME = 'automatched'


DECLARE @medDate datetime;
SET @medDate = (SELECT MAX(date_time) FROM Contact WHERE date_time <= GETDATE());
IF EXISTS(SELECT * FROM Rave4.dbo.Unit WHERE unit_id IN (99988, 4610222)) -- Läkemedel sätts ut ett år frammåt i VG och LTV
	SET @medDate = DATEADD(YEAR, 1, @medDate);


UPDATE StandingPrescriptionEvent SET medication_stop_date = @medDate WHERE YEAR(medication_stop_date) = 1900 OR medication_stop_date > @medDate;
UPDATE StandingPrescription SET standing_prescription_stop_date = @medDate
WHERE YEAR(standing_prescription_stop_date) = 1900 OR standing_prescription_stop_date > @medDate;
UPDATE Contact SET local_contact_type_name = '(OLD)' + local_contact_type_name
WHERE local_contact_type_name NOT LIKE '(OLD)%';
GO

DECLARE @minPackId bigint;
SET @minPackId = (SELECT ISNULL(ABS(MIN(ISNULL(MedicinePackage.medicine_package_id-1,0))), 0) FROM MedicinePackage WHERE medicine_package_id < 0);
IF EXISTS(select * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MedicinePackage' AND Column_name = 'from_npl')
BEGIN
EXEC('UPDATE MedicinePackage SET medicine_package_id = (medicine_package_id * -1) -' + @minPackId + ' WHERE medicine_package_id > 0 AND from_npl = 0')
EXEC('UPDATE Prescription SET medicine_package_id = (Prescription.medicine_package_id * -1) -' + @minPackId + ' FROM Prescription INNER JOIN MedicinePackage ON MedicinePackage.medicine_package_id = Prescription.medicine_package_id WHERE Prescription.medicine_package_id > 0 AND MedicinePackage.from_npl = 0')
END
ELSE
BEGIN
UPDATE MedicinePackage SET medicine_package_id = (medicine_package_id * -1) -@minPackId WHERE medicine_package_id > 0
UPDATE Prescription SET medicine_package_id = (medicine_package_id * -1) -@minPackId WHERE Prescription.medicine_package_id > 0
END
GO

DECLARE @minPackId bigint;
SET @minPackId = (SELECT ISNULL(ABS(MIN(ISNULL(ProductCodes.product_code_id-1,0))), 0) FROM ProductCodes WHERE product_code_id < 0);
UPDATE ProductCodes SET product_code_id = (product_code_id * -1) -@minPackId WHERE product_code_id > 0
UPDATE Products SET product_code_id = (product_code_id * -1) - @minPackId WHERE product_code_id > 0
GO
UPDATE Appointment SET local_appointment_name = '(OLD)' + local_appointment_name
WHERE local_appointment_name NOT LIKE '(OLD)%'
GO
UPDATE AppointmentCode SET appointment_name = '(OLD)' + appointment_name
WHERE appointment_name NOT LIKE '(OLD)%'
Go
UPDATE ContactTypeName SET contact_name = '(OLD)' + contact_name
WHERE contact_name NOT LIKE '(OLD)%'
GO
UPDATE AnalysisResult SET local_analysis_name = '(OLD)' + local_analysis_name
WHERE local_analysis_name NOT LIKE '(OLD)%'
GO
UPDATE PatientNotes SET local_keyword = '(OLD)' + local_keyword
WHERE local_keyword NOT LIKE '(OLD)%'
GO
UPDATE PatientNotesKeyword SET keyword = '(OLD)' + keyword
WHERE keyword  NOT LIKE '(OLD)%'
Go
UPDATE Analysis SET analysis_name = '(OLD)' + analysis_name
WHERE analysis_name  NOT LIKE '(OLD)%'