SET XACT_ABORT ON;
GO

PRINT 'Beginning transaction...';
BEGIN TRANSACTION;
GO

IF adeofw.GetSchemaVersion('adeoerp') IS NULL BEGIN
	EXECUTE sys.sp_addextendedproperty 
	@name = N'Adeo.Version',
	@value = '1.2.4',
	@level0type = N'SCHEMA', 
	@level0name = adeoerp
END
GO

IF adeofw.CompareVersions('1.2.5', adeofw.GetSchemaVersion('adeoerp')) > 0 BEGIN
	PRINT 'Migrating AdeoERP to 1.2.5'

	PRINT 'Altering structure of table adeoerp.WorkItem';

	ALTER TABLE adeoerp.WorkItem
		ADD solved DATETIME,
			closed DATETIME
END
GO

IF adeofw.CompareVersions('1.2.5', adeofw.GetSchemaVersion('adeoerp')) > 0 BEGIN
	PRINT 'Altering data of table adeoerp.WorkItem';

	-- trigger works only for modifying one row, so it has to
	-- be disalbed before batch updates
	DISABLE TRIGGER adeoerp.adeoerp_WorkItem_AfterUpdate ON adeoerp.WorkItem

	-- migrate entries manually closed or solved
	UPDATE adeoerp.WorkItem
	SET solved = (
		SELECT MAX(db2.LastModificationDate)
		FROM adeoerp.adeodms_WorkItem wi1
			INNER JOIN adeodms.adeodms_DocumentBase db1 ON wi1.guid = db1.ObjectId
			LEFT JOIN (adeoerp.adeodms_WorkItem wi2
				INNER JOIN adeodms.adeodms_DocumentBase db2 ON wi2.guid = db2.ObjectId) ON db1.VersionSeriesId = db2.VersionSeriesId
		WHERE db1.VersionLabel + 1 = db2.VersionLabel
		  AND wi1.wfstatename = 'Opened'
		  AND wi2.wfstatename = 'Solved'
		  AND db1.VersionSeriesId = adeoerp.WorkItem.guid
	),
	closed = (
		SELECT MAX(db2.LastModificationDate)
		FROM adeoerp.adeodms_WorkItem wi1
			INNER JOIN adeodms.adeodms_DocumentBase db1 ON wi1.guid = db1.ObjectId
			LEFT JOIN (adeoerp.adeodms_WorkItem wi2
				INNER JOIN adeodms.adeodms_DocumentBase db2 ON wi2.guid = db2.ObjectId) ON db1.VersionSeriesId = db2.VersionSeriesId
		WHERE db1.VersionLabel + 1 = db2.VersionLabel
			  AND wi1.wfstatename = 'Solved'
		  AND wi2.wfstatename = 'ItemClosed'
		  AND db1.VersionSeriesId = adeoerp.WorkItem.guid
	);

	-- migrate entries solved by migration tool
	UPDATE adeoerp.WorkItem
	SET solved = (
		SELECT MAX(db1.LastModificationDate)
		FROM adeoerp.adeodms_WorkItem wi1
			INNER JOIN adeodms.adeodms_DocumentBase db1 ON wi1.guid = db1.ObjectId
		WHERE db1.VersionSeriesId = adeoerp.WorkItem.guid
	)
	WHERE wfstatename = 'Solved'
	  AND solved IS NULL;

	-- migrate entries closed by migration tool
	UPDATE adeoerp.WorkItem
	SET solved = (
		SELECT MAX(db1.LastModificationDate)
		FROM adeoerp.adeodms_WorkItem wi1
			INNER JOIN adeodms.adeodms_DocumentBase db1 ON wi1.guid = db1.ObjectId
		WHERE db1.VersionSeriesId = adeoerp.WorkItem.guid
	), closed = (
		SELECT MAX(db1.LastModificationDate)
		FROM adeoerp.adeodms_WorkItem wi1
			INNER JOIN adeodms.adeodms_DocumentBase db1 ON wi1.guid = db1.ObjectId
		WHERE db1.VersionSeriesId = adeoerp.WorkItem.guid
	)
	WHERE wfstatename = 'ItemClosed'
	  AND closed IS NULL;

	ENABLE TRIGGER adeoerp.adeoerp_WorkItem_AfterUpdate ON adeoerp.WorkItem
END
GO


IF adeofw.CompareVersions('1.2.6', adeofw.GetSchemaVersion('adeoerp')) > 0 BEGIN
	PRINT 'Migrating AdeoERP to 1.2.6'
	
	PRINT 'Updating adeoerp.DocumentType Table'

   ALTER TABLE [adeoerp].[DocumentType]
      ADD [advance_payment_account_guid] [uniqueidentifier] NULL

   ALTER TABLE [adeoerp].[DocumentType]
      ADD [advance_payment_cd_code] [char] (8)  NULL

   ALTER TABLE [adeoerp].[DocumentType]
      ADD [expenses_account_cd_code] [char] (8)  NULL

   ALTER TABLE [adeoerp].[DocumentType]
      ADD [expenses_account_guid] [uniqueidentifier] NULL

	ALTER TABLE [adeoerp].[DocumentType] ADD CONSTRAINT [FK_adeoerp_DocumentType_adeoerp_Account3] FOREIGN KEY ([expenses_account_guid]) REFERENCES [adeoerp].[Account] ([guid])


    ALTER TABLE [adeoerp].[DocumentType] ADD CONSTRAINT [FK_adeoerp_DocumentType_adeoerp_Account4] FOREIGN KEY ([advance_payment_account_guid]) REFERENCES [adeoerp].[Account] ([guid])

    ALTER TABLE [adeoerp].[DocumentType] ADD CONSTRAINT [FK_adeoerp_DocumentType_adeoerp_Code3] FOREIGN KEY ([expenses_account_cd_code]) REFERENCES [adeoerp].[Code] ([code])

    ALTER TABLE [adeoerp].[DocumentType] ADD CONSTRAINT [FK_adeoerp_DocumentType_adeoerp_Code4] FOREIGN KEY ([advance_payment_cd_code]) REFERENCES [adeoerp].[Code] ([code])

	PRINT 'Updating adeoerp.FolderType Table'
	   ALTER TABLE [adeoerp].[FolderType]
		  ADD [folder_code_template] [nvarchar] (255) NULL

	PRINT 'Creating adeoerp.VDocumentItemTravelOrders View'

	exec('CREATE VIEW [adeoerp].[VDocumentItemTravelOrders]
	AS 
	SELECT Dito.doc_item_guid,Dito.foreign_currency_guid,Dito.unit_price,Dito.unit_price_in_foreign_currency,
	Dito.qty,Dito.exchange_rate,Dito.amount_in_foreign_currency,Dito.amount,Dito.allowance_amount_guid,
		   Di.guid, Di.wfstatename, Di.doc_guid, Di.doc_item_class_code, Di.doc_item_type_guid, Di.doc_item_desc,
		   Dic.[label] AS doc_item_class, Dit.[doc_item_type_name] AS doc_item_type
	FROM [adeoerp].[DocumentItemTravelOrders] AS Dito
		LEFT OUTER JOIN [adeoerp].[DocumentItem] AS Di ON Di.guid = Dito.doc_item_guid
		LEFT OUTER JOIN [adeoerp].[DocumentItemClass] AS Dic ON Dic.code = Di.doc_item_class_code
		LEFT OUTER JOIN [adeoerp].[DocumentItemType] AS Dit ON Dit.guid = Di.doc_item_type_guid')

	

	exec('CREATE VIEW [adeoerp].[VDocumentTravelOrders]
	AS
	SELECT      DTO.date, DTO.assignment, DTO.expenses, DTO.advance_payments, DTO.departure_date, DTO.arrival_date,
						  D.guid, D.wfstatename, D.parent_guid, 
						  D.company_guid, D.doc_class_code, D.doc_type_guid, D.doc_no, D.doc_ord, 
						  D.doc_date, D.city_guid,''Putni nalog za zadatak: '' + DTO.assignment as doc_desc, D.folder_guid, D.registry_doc_no, 
						  D.registry_doc_ord, D.assigned_to_guid, D.assigned_on, D.comment, Dc.label AS doc_class, 
						  P.partner_guid AS partner_company_guid, 
						  P.person_guid AS partner_person_guid, Dt.doc_type_code, Dt.doc_type_name,P2.guid as partner_guid
       
	FROM         adeoerp.DocumentTravelOrders AS DTO INNER JOIN
						  adeoerp.[Document] AS D ON D.guid = DTO.doc_guid INNER JOIN
						  adeoerp.DocumentClass AS Dc ON Dc.code = D.doc_class_code LEFT OUTER JOIN
						  adeoerp.Partner AS P ON D.partner_guid = P.guid LEFT OUTER JOIN
						  adeoerp.DocumentType AS Dt ON Dt.guid = D.doc_type_guid LEFT OUTER JOIN
						  adeoerp.Partner AS P2 ON DTO.person_guid = P2.person_guid')

	PRINT 'Updating adeoerp.Employee Table'

    ALTER TABLE [adeoerp].[Employee]
      ADD [employee_code] [varchar] (255) NULL

PRINT 'Creating procedure adeoerp.UpdateSubfoldersOrd...';

EXEC('
	CREATE PROCEDURE adeoerp.UpdateSubfoldersOrd
		@parent_guid uniqueidentifier,
		@max_ord int,
		@year int = null
	AS
	BEGIN
	
		DECLARE @diff int;
		SET @diff = @max_ord - (select count(*) from adeoerp.Folder where parent_guid = @parent_guid AND (@year IS NULL OR YEAR(folder_date) = @year));

		WITH CTE
		AS (
			SELECT folder_ord, ROW_NUMBER() OVER(ORDER BY folder_code ASC) + @diff AS ord_new 
			FROM adeoerp.Folder where parent_guid = @parent_guid AND (@year IS NULL OR YEAR(folder_date) = @year))
		UPDATE CTE SET folder_ord = ord_new;

		UPDATE adeoerp.adeodms_Folder set folder_ord = F.folder_ord 
		FROM adeoerp.adeodms_Folder FolderH JOIN adeodms.DocumentBase Db ON FolderH.guid = Db.ObjectId
		JOIN adeoerp.Folder F ON Db.VersionSeriesId = F.guid
		where FolderH.folder_ord <> F.folder_ord
	END');

END
GO

IF adeofw.CompareVersions('1.2.7', adeofw.GetSchemaVersion('adeoerp')) > 0 BEGIN
	
	--Implement changes (1.2.6 --> 1.2.7) here
	PRINT 'Migrating AdeoERP to 1.2.7'
	PRINT 'Altering table adeoerp.Project'
	
	ALTER TABLE adeoerp.AddressBank
	DROP COLUMN is_hq

	ALTER TABLE adeoerp.AddressCompany
	DROP COLUMN is_hq

	ALTER TABLE adeoerp.Project
	ADD is_active BIT DEFAULT(0) 

	DROP VIEW [adeoerp].[VEvent]
	EXEC('CREATE VIEW [adeoerp].[VEvent]
			AS 
			SELECT TP.guid, TP.time_period_type_guid, TP.time_period_desc, TP.time_from, TP.time_to, TP.total_hours, TP.net_hours, TP.date_from, TP.date_to, TP.total_days, 
				TP.net_days, TP.weekday_from, TP.weekday_to, EV.time_period_guid, EV.employee_guid,
				adeofw.GetPersonFullName(PersonFW.person_name, PersonFW.person_surname) AS person_full_name,
				TP.date_from + COALESCE (TP.time_from, 0) AS datetime_from,
				TP.date_to + COALESCE (TP.time_to, 0) AS datetime_to,
				Tpt.time_period_class_code
			FROM adeoerp.TimePeriod AS TP
				INNER JOIN adeoerp.Event AS EV ON EV.time_period_guid = TP.guid
				LEFT JOIN adeoerp.TimePeriodType AS Tpt ON Tpt.guid = Tp.time_period_type_guid 
				LEFT JOIN adeofw.Person AS PersonFW ON PersonFW.guid = EV.employee_guid')

	PRINT 'Altering table adeoerp.FolderType'
	ALTER TABLE adeoerp.FolderType
	ADD is_folder_ord_zero_based BIT not null DEFAULT(0)

    ALTER TABLE adeoerp.FolderType
    ADD is_folder_code_auto_generated BIT not null DEFAULT(0)

	ALTER TABLE adeoerp.FolderType
	ADD is_folder_ord_unique_within_month BIT not null DEFAULT(0)

	PRINT 'Altering table adeoerp.DocumentType'
	ALTER TABLE adeoerp.DocumentType
	ADD is_doc_ord_unique_within_month BIT not null DEFAULT(0)

	PRINT 'Altering foreign key:[FK_adeoerp_DocumentItemAmountInterest_adeoerp_DocumentItemAmount] '
	ALTER TABLE [adeoerp].[DocumentItemAmountInterest]
	DROP CONSTRAINT [FK_adeoerp_DocumentItemAmountInterest_adeoerp_DocumentItemAmount] 
	
    ALTER TABLE [adeoerp].[DocumentItemAmountInterest]	
	ADD CONSTRAINT [FK_adeoerp_DocumentItemAmountInterest_adeoerp_DocumentItemAmount] 
	FOREIGN KEY (doc_item_amount_guid)
	REFERENCES adeoerp.DocumentItemAmount (doc_item_guid) ON DELETE CASCADE ON UPDATE NO ACTION;

	PRINT 'Altering trigger:[adeoerp].[adeoerp_DocumentItemAmount_AfterInsert]'
	DROP TRIGGER [adeoerp].adeoerp_DocumentItemAmount_AfterInsert
	EXEC('CREATE TRIGGER [adeoerp].[adeoerp_DocumentItemAmount_AfterInsert]
	ON [adeoerp].[DocumentItemAmount]
	AFTER INSERT
	AS 
	BEGIN
		IF ((SELECT adeoerp.Document.doc_class_code
				FROM inserted
					INNER JOIN adeoerp.DocumentItem ON inserted.doc_item_guid = adeoerp.DocumentItem.guid
					INNER JOIN adeoerp.Document ON adeoerp.DocumentItem.doc_guid = adeoerp.Document.guid) = ''DOCCGDSI'') 
			RETURN; 

  		DECLARE @doc_guid uniqueidentifier
		
		SET @doc_guid = (SELECT doc_guid FROM adeoerp.DocumentItem WHERE guid = (SELECT doc_item_guid FROM inserted))
		
   		EXEC adeoerp.UpdateDocumentAmount @doc_guid
		
	END')

	PRINT 'Altering trigger:[adeoerp].[adeoerp_DocumentItemAmount_AfterUpdate]'
	DROP TRIGGER [adeoerp].[adeoerp_DocumentItemAmount_AfterUpdate]
	EXEC('CREATE TRIGGER [adeoerp].[adeoerp_DocumentItemAmount_AfterUpdate]
	ON [adeoerp].[DocumentItemAmount]
	AFTER UPDATE 
	AS 
	BEGIN
		IF ((SELECT adeoerp.Document.doc_class_code
				FROM inserted
					INNER JOIN adeoerp.DocumentItem ON inserted.doc_item_guid = adeoerp.DocumentItem.guid
					INNER JOIN adeoerp.Document ON adeoerp.DocumentItem.doc_guid = adeoerp.Document.guid) = ''DOCCGDSI'') 
			RETURN; 

  		DECLARE @doc_guid uniqueidentifier
		
		SET @doc_guid = (SELECT doc_guid FROM adeoerp.DocumentItem WHERE guid = (SELECT doc_item_guid FROM inserted))
		
   		EXEC adeoerp.UpdateDocumentAmount @doc_guid
	
	END')
END
GO
EXECUTE sys.sp_updateextendedproperty 
@name = N'Adeo.Version',
@value = '1.2.7',
@level0type = N'SCHEMA', 
@level0name = adeoerp
GO

PRINT 'Committing transaction...';
COMMIT TRANSACTION;
GO