---------------------------------------------------------------------
-- BASE_VERSION 580

-- You could execute this script from MSSQL Query Analyzer
-- or use OSQL utility 
-- Don't forget that user should have appropriate rights
--
--#########################################################################################################

if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 10

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_mediaplan_scheds' AND TYPE='U')
BEGIN
	CREATE TABLE mp_mediaplan_scheds(
	id				int identity NOT NULL,
	mediaplan_id	int NULL,
	schedule_id		int NULL,
	constraint PK_MP_MEDIAPLAN_SCHEDS primary key nonclustered (id) 
)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(SELECT sysobjects.id FROM sysobjects WHERE xtype = 'F' AND name = 'FK_MP_MEDIAPLAN_SCHEDS_14_MP_MEDIAPLANS')
BEGIN
	ALTER TABLE mp_mediaplan_scheds
	   add constraint FK_MP_MEDIAPLAN_SCHEDS_14_MP_MEDIAPLANS  foreign key (mediaplan_id)
		  references mp_mediaplans (id)
			on delete cascade
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END


IF NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_phonograms' AND syscolumns.name='placement_method')
BEGIN 
	ALTER TABLE mp_phonograms
	ADD placement_method int default 0 not null
END

IF NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_payment_types' AND syscolumns.name='color')
BEGIN 
	ALTER TABLE mp_payment_types
	ADD color int default null
END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END



COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 20

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_settings_tmp' AND TYPE='U')
BEGIN
	create table mp_settings_tmp (
	   id                   int                  identity,
	   name                 varchar(1000)        not null,
	   value                image				 not null,
	   enabled              bit                  not null,
	   constraint PK_MP_SETTINGS_EXT primary key nonclustered (id)
	)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_settings_tmp' AND TYPE='U')
BEGIN
	insert into mp_settings_tmp (name, value, enabled) select name, value, enabled from mp_settings
	DROP TABLE mp_settings
	EXEC sp_rename 'mp_settings_tmp', 'mp_settings'
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 30

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='UpdateMpPositions' AND TYPE='P')
	DROP PROCEDURE UpdateMpPositions
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

exec sp_executesql N'CREATE PROCEDURE UpdateMpPositions @Type varchar(32), @sXML xml
AS
BEGIN

DECLARE @tmpTable TABLE
(
id int,
date datetime,
block_number smallint,
plan_number smallint,
version int,
type int, 
position int
);

BEGIN TRANSACTION Transact;

IF @Type = ''INSERT''
BEGIN

;WITH XmlData AS 
(
    SELECT
	   List.Id.query(''mediaplans_id'').value(''.'', ''int'') as mediaplans_id,
	   List.Id.query(''users_id'').value(''.'', ''int'') as users_id,
	   List.Id.query(''date'').value(''.'', ''datetime'') as date,
	   List.Id.query(''block_number'').value(''.'', ''smallint'') as block_number,
	   List.Id.query(''plan_number'').value(''.'', ''smallint'') as plan_number,
	   List.Id.query(''first'').value(''.'', ''bit'') as first,
	   List.Id.query(''version'').value(''.'', ''int'') as version,
	   List.Id.query(''type'').value(''.'', ''int'') as type,
	   List.Id.query(''position'').value(''.'', ''int'') as position,
	   List.Id.query(''placementMethod'').value(''.'', ''int'') as placementMethod,
	   List.Id.query(''is_bonus'').value(''.'', ''bit'') as is_bonus
    FROM 
        @sXML.nodes(''/positions/position'') AS List(Id)
)
INSERT INTO mp_positions(mediaplans_id, users_id, date, block_number, plan_number, first, version, type, position, placementMethod, is_bonus)
OUTPUT INSERTED.id, INSERTED.date, INSERTED.block_number, INSERTED.plan_number, INSERTED.version, INSERTED.type, INSERTED.position INTO @tmpTable
SELECT DISTINCT mediaplans_id, users_id, date, block_number, plan_number, first, version, type, position, placementMethod, is_bonus FROM XmlData;

END

IF @Type = ''UPDATE''
BEGIN

;WITH XmlData AS 
(
    SELECT
	   List.Id.query(''id'').value(''.'', ''int'') as id,
	   List.Id.query(''mediaplans_id'').value(''.'', ''int'') as mediaplans_id,
	   List.Id.query(''users_id'').value(''.'', ''int'') as users_id,
	   List.Id.query(''date'').value(''.'', ''datetime'') as date,
	   List.Id.query(''block_number'').value(''.'', ''smallint'') as block_number,
	   List.Id.query(''plan_number'').value(''.'', ''smallint'') as plan_number,
	    List.Id.query(''version'').value(''.'', ''int'') as version,
	   List.Id.query(''type'').value(''.'', ''int'') as type,
	   List.Id.query(''position'').value(''.'', ''int'') as position
    FROM 
        @sXML.nodes(''/positions/position'') AS List(Id)
)
UPDATE mp_positions SET 
	   mp_positions.mediaplans_id = XmlData.mediaplans_id,
	   mp_positions.users_id = XmlData.users_id,
	   mp_positions.date = XmlData.date,
	   mp_positions.block_number = XmlData.block_number,
	   mp_positions.plan_number = XmlData.plan_number,
	   mp_positions.version = XmlData.version,
	   mp_positions.type = XmlData.type,
	   mp_positions.position = XmlData.position
FROM   mp_positions INNER JOIN XmlData ON mp_positions.id = XmlData.id;

END

IF @Type = ''DELETE'' 
BEGIN

;WITH XmlData AS 
(
    SELECT
	   List.Id.query(''id'').value(''.'', ''int'') as id
    FROM 
        @sXML.nodes(''/positions/position'') AS List(Id)
)
DELETE mp_positions FROM mp_positions JOIN XmlData ON mp_positions.id = XmlData.id;

END

COMMIT TRANSACTION Transact;

SELECT *FROM @tmpTable;

END'
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END


IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='UpdateMpPositionReplacement' AND TYPE='P')
	DROP PROCEDURE UpdateMpPositionReplacement
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

exec sp_executesql N'CREATE PROCEDURE UpdateMpPositionReplacement @Type varchar(32), @sXML xml
AS
BEGIN

DECLARE @tmpTable TABLE
(
id int,
position_id int,
schedules_id int
);

BEGIN TRANSACTION Transact;

IF @Type = ''UPDATE'' 
BEGIN

;WITH XmlData AS 
(
    SELECT
	   List.Id.query(''id'').value(''.'', ''int'') as id,
	   List.Id.query(''position_id'').value(''.'', ''int'') as position_id,
	   List.Id.query(''schedules_id'').value(''.'', ''int'') as schedules_id,
	   List.Id.query(''release_date'').value(''.'', ''datetime'') as release_date,
	   List.Id.query(''placementMethod'').value(''.'', ''int'') as placementMethod,
	   List.Id.query(''released'').value(''.'', ''bit'') as released,
	   List.Id.query(''is_bonus'').value(''.'', ''bit'') as is_bonus
    FROM 
        @sXML.nodes(''/positions/position'') AS List(Id)
)
UPDATE	mp_position_replacement  SET 
		mp_position_replacement.position_id = XmlData.position_id,
		mp_position_replacement.schedules_id = XmlData.schedules_id,
		mp_position_replacement.release_date = CASE WHEN XmlData.release_date = '''' THEN NULL ELSE XmlData.release_date END,
		mp_position_replacement.placementMethod = XmlData.placementMethod,
		mp_position_replacement.released = CASE WHEN XmlData.released = '''' THEN NULL ELSE XmlData.released END,
		mp_position_replacement.is_bonus = XmlData.is_bonus 
FROM	mp_position_replacement INNER JOIN XmlData ON mp_position_replacement.id = XmlData.id;

END

IF @Type = ''INSERT''
BEGIN

;WITH XmlData AS 
(
    SELECT
	   List.Id.query(''id'').value(''.'', ''int'') as id,
	   List.Id.query(''position_id'').value(''.'', ''int'') as position_id,
	   List.Id.query(''schedules_id'').value(''.'', ''int'') as schedules_id,
	   List.Id.query(''release_date'').value(''.'', ''datetime'') as release_date,
	   List.Id.query(''placementMethod'').value(''.'', ''int'') as placementMethod,
	   List.Id.query(''released'').value(''.'', ''bit'') as released,
	   List.Id.query(''is_bonus'').value(''.'', ''bit'') as is_bonus
    FROM 
        @sXML.nodes(''/positions/position'') AS List(Id)
)
INSERT mp_position_replacement(position_id, schedules_id, release_date, placementMethod, released, is_bonus)
OUTPUT INSERTED.id, INSERTED.position_id, INSERTED.schedules_id INTO @tmpTable
SELECT DISTINCT position_id, schedules_id, release_date, placementMethod, released, is_bonus FROM XmlData;

END

IF @Type = ''DELETE''
BEGIN

;WITH XmlData AS 
(
    SELECT
	   List.Id.query(''position_id'').value(''.'', ''int'') as position_id,
	   List.Id.query(''schedules_id'').value(''.'', ''int'') as schedules_id
    FROM 
        @sXML.nodes(''/positions/position'') AS List(Id)
)
delete mp_position_replacement FROM mp_position_replacement JOIN XmlData ON 
mp_position_replacement.position_id = XmlData.position_id AND 
mp_position_replacement.schedules_id not in (select x.schedules_id from XmlData x where mp_position_replacement.position_id = x.position_id);

END

COMMIT TRANSACTION Transact;

SELECT tmp.*, pos.version as version FROM @tmpTable tmp inner join mp_positions pos on pos.id = tmp.position_id;
END'
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO


--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 40

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_running_app' AND TYPE='U')
BEGIN
	create table mp_running_app (
	   mp_id                uniqueidentifier    not null,
	   owner_name           varchar(255)        not null,
	   last_time_active     datetime			not null,
	   constraint PK_MP_RUNNING_APP primary key nonclustered (mp_id) 
	)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_sync_events' AND TYPE='U')
BEGIN
	create table mp_sync_events (
	   id					int					identity,
	   mp_id				uniqueidentifier	not null,
	   event_type           int					not null,
	   gen_evt_time			datetime			not null,
	   constraint PK_MP_SYNC_EVENTS primary key nonclustered (id)
	)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(SELECT sysobjects.id FROM sysobjects WHERE xtype = 'F' AND name = 'FK_MP_SYNC_EVENTS_VS_MP_RUNNING_APP')
BEGIN
	ALTER TABLE mp_sync_events
	   add constraint FK_MP_SYNC_EVENTS_VS_MP_RUNNING_APP  foreign key (mp_id)
		  references mp_running_app (mp_id)
			on delete cascade
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO


--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 50

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_mediaplans' AND syscolumns.name='organization_id')
BEGIN
ALTER TABLE mp_mediaplans ADD organization_id INT NULL;
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO


--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 60

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_discount_values' AND syscolumns.name='apply_type')
BEGIN
ALTER TABLE mp_discount_values ADD apply_type smallint NULL;
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO


--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 70

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_tariff_scales' AND syscolumns.name='name' AND syscolumns.name='end_date' AND syscolumns.name='default_scale')
BEGIN
ALTER TABLE mp_tariff_scales ADD name varchar(128) NULL;
ALTER TABLE mp_tariff_scales ADD end_date datetime NULL;
ALTER TABLE mp_tariff_scales ADD default_scale bit NOT NULL DEFAULT(0);
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO


--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 80

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_spot_types' AND TYPE='U')
BEGIN
	CREATE TABLE mp_spot_types(
	id				int identity NOT NULL,
	show_type		int NULL,
	name			varchar(255) NULL,
	system_type		bit default(0),
	constraint PK_MP_SPOT_TYPES primary key nonclustered (id) 
)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 90

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE veri>=@ver )
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_tariff_scales' AND syscolumns.name='max_bonus_spots')
BEGIN
ALTER TABLE mp_tariff_scales ADD max_bonus_spots varchar(16) NULL;
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO


--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 100

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_accounts' AND syscolumns.name='barter')
BEGIN
ALTER TABLE mp_accounts ADD barter BIT DEFAULT(0);
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END


if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_payments' AND syscolumns.name='barter')
BEGIN
ALTER TABLE mp_payments ADD barter BIT DEFAULT(0);
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO


--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 110

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_account_services' AND TYPE='U')
BEGIN
	CREATE TABLE mp_account_services(
	id				int identity NOT NULL,
	account_id		int NULL,
	service_name	nvarchar(1024) NULL,
	service_cost	money NULL,
	constraint PK_MP_ACCOUNT_SERVICES primary key nonclustered (id) 
)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(SELECT sysobjects.id FROM sysobjects WHERE xtype = 'F' AND name = 'PK_MP_ACCOUNT_SERVICES_MP_ACCOUNTS')
BEGIN
	ALTER TABLE mp_account_services
	   add constraint PK_MP_ACCOUNT_SERVICES_MP_ACCOUNTS foreign key (account_id)
		  references mp_accounts (id)
			on delete cascade
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO


--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 120

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_account_mediaplans' AND TYPE='U')
BEGIN
	CREATE TABLE mp_account_mediaplans(
	id				int identity NOT NULL,
	account_id		int NULL,
	mediaplan_id	int NULL,
	constraint PK_MP_ACCOUNT_MEDIAPLANS primary key nonclustered (id) 
)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(SELECT sysobjects.id FROM sysobjects WHERE xtype = 'F' AND name = 'PK_MP_ACCOUNT_MEDIAPLANS_MP_ACCOUNTS')
BEGIN
	ALTER TABLE mp_account_mediaplans
	   add constraint PK_MP_ACCOUNT_MEDIAPLANS_MP_ACCOUNTS foreign key (account_id)
		  references mp_accounts (id)
			on delete cascade
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(SELECT sysobjects.id FROM sysobjects WHERE xtype = 'F' AND name = 'PK_MP_ACCOUNT_MEDIAPLANS_MP_MEDIAPLANS')
BEGIN
	ALTER TABLE mp_account_mediaplans
	   add constraint PK_MP_ACCOUNT_MEDIAPLANS_MP_MEDIAPLANS foreign key (mediaplan_id)
		  references mp_mediaplans (id)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END


IF EXISTS(SELECT sysobjects.id FROM sysobjects WHERE xtype = 'F' AND name = 'FK_MP_MEDIA_MP_ACCOUNT')
BEGIN
	ALTER TABLE mp_accounts DROP constraint FK_MP_MEDIA_MP_ACCOUNT 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

if EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_accounts' AND syscolumns.name='mediaplan_id')
BEGIN

insert into mp_account_mediaplans (account_id, mediaplan_id) select id as account_id, mediaplan_id from mp_accounts

--ALTER TABLE mp_accounts DROP COLUMN mediaplan_id;
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO


--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 130

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_phonograms' AND syscolumns.name='use_planned_duration')
BEGIN
ALTER TABLE mp_phonograms ADD use_planned_duration BIT DEFAULT(0);
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 140

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_discount_values' AND syscolumns.name='combination_type')
BEGIN
ALTER TABLE mp_discount_values ADD combination_type smallint DEFAULT(0);
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 150

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_base_costs' AND syscolumns.name='spot_duration')
BEGIN
ALTER TABLE mp_base_costs ADD spot_duration int NOT NULL DEFAULT(0);
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 160

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

CREATE TABLE [dbo].[mp_owners_mediaplans](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[id_mediaplan] [int] NULL,
	[id_user] [int] NULL,
 CONSTRAINT [PK_mp_owners_mediaplans] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 170

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

ALTER TABLE [dbo].[mp_owners_mediaplans]  WITH CHECK ADD  CONSTRAINT [FK_mp_owners_mediaplans_mp_mediaplans] FOREIGN KEY([id_mediaplan])
REFERENCES [dbo].[mp_mediaplans] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE

ALTER TABLE [dbo].[mp_owners_mediaplans] CHECK CONSTRAINT [FK_mp_owners_mediaplans_mp_mediaplans]

ALTER TABLE [dbo].[mp_owners_mediaplans]  WITH CHECK ADD  CONSTRAINT [FK_mp_owners_mediaplans_USERS] FOREIGN KEY([id_user])
REFERENCES [dbo].[USERS] ([id])
ON UPDATE CASCADE
ON DELETE SET NULL

ALTER TABLE [dbo].[mp_owners_mediaplans] CHECK CONSTRAINT [FK_mp_owners_mediaplans_USERS]

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 180

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

insert into mp_owners_mediaplans (id_mediaplan, id_user)
select id as id_mediaplan, users_id as id_user  from mp_mediaplans

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 190

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_positions' AND syscolumns.name='grid_block_id')
BEGIN
ALTER TABLE mp_positions ADD grid_block_id int NULL

ALTER TABLE [dbo].mp_positions  WITH CHECK ADD  CONSTRAINT [FK_mp_positions_GRID_BLOCKS] FOREIGN KEY([grid_block_id])
REFERENCES [dbo].[GRID_BLOCKS] ([id])
ON UPDATE CASCADE
ON DELETE SET NULL
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 200

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

IF NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_discount_values' AND syscolumns.name='default_value_f' AND syscolumns.name='min_value_f' AND syscolumns.name='max_value_f')
BEGIN 
	ALTER TABLE mp_discount_values ADD default_value_f float default 0 not null;
	ALTER TABLE mp_discount_values ADD min_value_f float default 0 not null;
	ALTER TABLE mp_discount_values ADD max_value_f float default 0 not null;
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_used_discounts' AND syscolumns.name='manager_value_f')
BEGIN 
	ALTER TABLE mp_used_discounts ADD manager_value_f float default 0 not null;
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 210

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

UPDATE mp_discount_values SET default_value_f=default_value, min_value_f=min_value, max_value_f=max_value WHERE default_value_f=0 AND min_value_f=0 AND max_value_f=0
UPDATE mp_used_discounts SET  manager_value_f=manager_value WHERE manager_value_f=0

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO


--#########################################################################################################
if(@@ERROR<>0) BEGIN RAISERROR('Script terminated...',16,1) return END
DECLARE @comp_ver int
DECLARE @comp_name varchar(128)
SET @comp_ver = 220
SET @comp_name = 'MediaPlannerComponent'

IF EXISTS( SELECT * FROM components WHERE name = @comp_name and veri >= @comp_ver) 
    BEGIN print 'Component: '+ @comp_name + ' already updated to version '  +   CAST(@comp_ver as varchar(128))+ ' or more' return END
    
SET XACT_ABORT ON
BEGIN TRAN


alter table mp_accounts add edit_type int not null default(1)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END


-- ------  end payload  --------------------------------------------------------------------------------------
INSERT INTO components (name, veri) VALUES(@comp_name,@comp_ver) if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print '**** Component:   ' + @comp_name + '   v: ' + CAST(@comp_ver as varchar(128)) + '    OK'
GO
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< end step <<



--#########################################################################################################
if(@@ERROR<>0) BEGIN RAISERROR('Script terminated...',16,1) return END
DECLARE @comp_ver int
DECLARE @comp_name varchar(128)
SET @comp_ver = 230
SET @comp_name = 'MediaPlannerComponent'

IF EXISTS( SELECT * FROM components WHERE name = @comp_name and veri >= @comp_ver) 
    BEGIN print 'Component: '+ @comp_name + ' already updated to version '  +   CAST(@comp_ver as varchar(128))+ ' or more' return END
    
SET XACT_ABORT ON
BEGIN TRAN

IF NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_mediaplan_scheds' AND syscolumns.name='tariff_scales_id')
BEGIN 
	ALTER TABLE mp_mediaplan_scheds ADD tariff_scales_id int null;
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END


-- ------  end payload  --------------------------------------------------------------------------------------
INSERT INTO components (name, veri) VALUES(@comp_name,@comp_ver) if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print '**** Component:   ' + @comp_name + '   v: ' + CAST(@comp_ver as varchar(128)) + '    OK'
GO
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< end step <<

--#########################################################################################################
if(@@ERROR<>0) BEGIN RAISERROR('Script terminated...',16,1) return END
DECLARE @comp_ver int
DECLARE @comp_name varchar(128)
SET @comp_ver = 240
SET @comp_name = 'MediaPlannerComponent'

IF EXISTS( SELECT * FROM components WHERE name = @comp_name and veri >= @comp_ver) 
    BEGIN print 'Component: '+ @comp_name + ' already updated to version '  +   CAST(@comp_ver as varchar(128))+ ' or more' return END
    
SET XACT_ABORT ON
BEGIN TRAN


ALTER TABLE mp_mediaplan_scheds  WITH CHECK ADD  CONSTRAINT FK_mp_mediaplan_scheds_mp_tariff_scales FOREIGN KEY(tariff_scales_id)
REFERENCES mp_tariff_scales (id)
ON UPDATE CASCADE
ON DELETE SET NULL

ALTER TABLE mp_mediaplan_scheds CHECK CONSTRAINT FK_mp_mediaplan_scheds_mp_tariff_scales

-- ------  end payload  --------------------------------------------------------------------------------------
INSERT INTO components (name, veri) VALUES(@comp_name,@comp_ver) if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print '**** Component:   ' + @comp_name + '   v: ' + CAST(@comp_ver as varchar(128)) + '    OK'
GO
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< end step <<


--#########################################################################################################
if(@@ERROR<>0) BEGIN RAISERROR('Script terminated...',16,1) return END
DECLARE @comp_ver int
DECLARE @comp_name varchar(128)
SET @comp_ver = 250
SET @comp_name = 'MediaPlannerComponent'

IF EXISTS( SELECT * FROM components WHERE name = @comp_name and veri >= @comp_ver) 
    BEGIN print 'Component: '+ @comp_name + ' already updated to version '  +   CAST(@comp_ver as varchar(128))+ ' or more' return END
    
SET XACT_ABORT ON
BEGIN TRAN

update mp_mediaplan_scheds set mp_mediaplan_scheds.tariff_scales_id = (select tariff_scales_id from mp_mediaplans where id=mp_mediaplan_scheds.mediaplan_id)


-- ------  end payload  --------------------------------------------------------------------------------------
INSERT INTO components (name, veri) VALUES(@comp_name,@comp_ver) if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print '**** Component:   ' + @comp_name + '   v: ' + CAST(@comp_ver as varchar(128)) + '    OK'
GO
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< end step <<


--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 260

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

ALTER TABLE mp_discount_types
ADD base_discount_id int NULL

ALTER TABLE mp_discount_values
ADD use_in_max_discount_calculation bit

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 270

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

UPDATE mp_discount_types
SET mp_discount_types.base_discount_id = mp_discount_types.id
WHERE id >= 1 AND id <= 8

DECLARE @id int
SET @id = (SELECT MAX(id) FROM mp_discount_types) + 1

INSERT INTO mp_discount_types
VALUES (@id, 'Скидка для бонусного проката', 0, 9)

UPDATE mp_discount_values
SET use_in_max_discount_calculation=1
WHERE type_id<>@id

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
--обновление mp_used_discounts 1
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 290

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

IF NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_used_discounts' AND syscolumns.name='schedule_id')
BEGIN 
	ALTER TABLE mp_used_discounts ADD schedule_id int NULL
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
--обновление mp_used_discounts 2
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 295

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

IF EXISTS( SELECT schedule_id FROM mp_used_discounts WHERE schedule_id IS NULL )
BEGIN
--1
update mp_used_discounts set schedule_id = (select mp_mediaplans.schedules_id from mp_mediaplans where mp_mediaplans.id = mediaplans_id)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

--2
insert mp_used_discounts(mediaplans_id, discount_values_id, manager_value, manager_value_f, schedule_id)
  select mp_used_discounts.mediaplans_id as mediaplans_id,
		 mp_used_discounts.discount_values_id as discount_values_id,
		 mp_used_discounts.manager_value as manager_value,
		 mp_used_discounts.manager_value_f as manager_value_f,
		 mp_mediaplan_scheds.schedule_id as schedule_id
  from mp_mediaplan_scheds inner join mp_used_discounts 
  on mp_used_discounts.mediaplans_id = mp_mediaplan_scheds.mediaplan_id 
  where mp_used_discounts.schedule_id = -1 and mp_mediaplan_scheds.schedule_id <> -1 
  if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

--3
delete from mp_used_discounts where schedule_id=-1

--4
update mp_used_discounts set schedule_id = (select schedules_id from mp_mediaplans where id = mp_used_discounts.mediaplans_id) where schedule_id is null

if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
END


------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO


--#########################################################################################################
--обновление mp_used_discounts 3
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 300

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

ALTER TABLE mp_used_discounts  WITH CHECK ADD  CONSTRAINT FK_mp_used_discounts_schedules FOREIGN KEY(schedule_id)
REFERENCES SCHEDULES (id)
ON UPDATE CASCADE
ON DELETE SET NULL

ALTER TABLE mp_used_discounts CHECK CONSTRAINT FK_mp_used_discounts_schedules
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO


--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 310

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

ALTER TABLE mp_used_discounts
ADD apply_type smallint NULL

ALTER TABLE mp_covenantees
ADD discount_type smallint NOT NULL DEFAULT 0, 
    discount_value float NOT NULL DEFAULT 0

DECLARE @id int
SET @id = (SELECT MAX(id) FROM mp_discount_types) + 1

INSERT INTO mp_discount_types
VALUES (@id, 'Скидка контрагента', 0, 10)

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO


--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 320

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

ALTER TABLE mp_covenantees
ADD use_discount bit NOT NULL DEFAULT 0

ALTER TABLE mp_used_discounts
ADD discount_enabled bit NOT NULL DEFAULT 1

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO


--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 330

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

ALTER TABLE mp_spot_types
ADD template int NOT NULL DEFAULT 0

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 340

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

ALTER TABLE mp_mediaplans ALTER COLUMN name nvarchar(1024)

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 350

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE veri>=@ver )
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

ALTER TABLE locked_objects ALTER COLUMN obj_name nvarchar(1024)

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 360

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_schedule_tariff_scale' AND TYPE='U')
BEGIN
	create table mp_schedule_tariff_scale(
		id int identity NOT NULL,
		schedule_id int unique NOT NULL,
		tariff_scale_id int unique NOT NULL,
		constraint PK_MP_SCHEDULE_TARIFF_SCALE
			primary key clustered (id),
		constraint FK_MP_SCHEDULE_TARIFF_SCALE_SCHEDULE
			foreign key (schedule_id) references SCHEDULES(id)
			on delete cascade,
		constraint FK_MP_SCHEDULE_TARIFF_SCALE_MP_TARIFF_SCALES 
			foreign key (tariff_scale_id) references mp_tariff_scales(id)
			on delete cascade
	);
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 370

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

IF EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_schedule_tariff_scale' AND TYPE='U')
BEGIN

	DECLARE @Results TABLE
(
   Name nvarchar(100)
);
INSERT INTO @Results 
SELECT sys.objects.name
FROM sys.objects 
cross join sys.columns
WHERE type = 'UQ' AND OBJECT_NAME(parent_object_id) = N'mp_schedule_tariff_scale' AND sys.columns.name = 'tariff_scale_id'

DECLARE @Name NVARCHAR(MAX)
WHILE EXISTS (SELECT 1 FROM @Results)
  BEGIN
   -- Get the top record
   SELECT TOP 1
    @Name = r.Name
   FROM
    @Results r

   -- This is where the work will be done
   --Insert/Update
   DECLARE @Command NVARCHAR(MAX)
   SELECT @Command ='Alter Table dbo.mp_schedule_tariff_scale Drop Constraint [' + @Name + ']'
   exec sp_executesql @Command
   -- Remove the record
   DELETE FROM @Results WHERE Name = @Name
  END
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 380

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

UPDATE mp_discount_values
   SET use_in_max_discount_calculation = 1
  FROM mp_discount_values
  INNER JOIN mp_discount_types ON mp_discount_values.type_id = mp_discount_types.id
  WHERE mp_discount_types.base_discount_id <> 9
  
UPDATE mp_discount_values
   SET use_in_max_discount_calculation = 0
  FROM mp_discount_values
  INNER JOIN mp_discount_types ON mp_discount_values.type_id = mp_discount_types.id
  WHERE mp_discount_types.base_discount_id = 9

if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 390

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

ALTER TABLE mp_positions ADD block_time time(7) NULL
ALTER TABLE mp_positions ADD clock_id int NULL

if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 400

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

ALTER TABLE mp_mediaplans ADD creation_date datetime NULL

if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 410

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

--добавляем скидки для бонусного проката во все тарифные сетки (если таких скидок нет)
insert into mp_discount_values (tariff_scales_id, default_value, min_value, max_value, apply_method, name, type_id, apply_type, combination_type, default_value_f, min_value_f, max_value_f, use_in_max_discount_calculation)
  select tariff_scales_id, 100, 100, 100, 0, 'Скидка для бонусного проката', 9, 0, 0, 100, 100, 100, 0 from (
  select tariff_scales_id from mp_discount_values as dv where not exists (select id from mp_discount_values where type_id=9 and dv.tariff_scales_id=tariff_scales_id) group by tariff_scales_id
  ) 
  as tbl

if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 420

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

--новая таблица для дэйпартов
create table mp_dayparts (
	id                       int    			identity,
	name                     varchar(1000)  	not null,
	tariff_scales_id         int    			not null,
	time_start               time   			null,
	time_end                 time   			null,
	block_types              int    			not null,
	week_days                int    			not null,
	hide_empty_rows			 bit    	 		not null,
	hide_empty_columns	     bit    	 		not null,
	constraint PK_MP_DAYPARTS primary key nonclustered (id) 
)

if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 430

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

--новая колонка для медиплана - расширенное имя (описание)
ALTER TABLE mp_mediaplans ADD name_detailed varchar(1024) NULL

if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 440

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

ALTER TABLE mp_position_replacement ADD sort_position int not null default 0

if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 450

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

ALTER TABLE mp_tariff_scales ADD default_payment_type_id int NULL

if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 460

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

ALTER TABLE mp_covenantees ADD ID_Number nvarchar(128) NULL

if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 470

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

CREATE UNIQUE INDEX covenantee_id_number_uniq ON mp_covenantees(ID_Number)
WHERE mp_covenantees.ID_Number IS NOT NULL

if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 480

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

ALTER TABLE mp_phonograms ADD broadcast_id int NULL

ALTER TABLE mp_phonograms
add constraint PK_MP_PHONOGRAMS_BROADCASTS foreign key (broadcast_id)
references BROADCASTS (id)
on update cascade

if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 490

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

ALTER TABLE mp_base_costs ADD broadcast_id int NULL

ALTER TABLE mp_base_costs
add constraint PK_MP_BASE_COST_BROADCASTS foreign key (broadcast_id)
references BROADCASTS (id)
on update cascade

if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO


--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 500

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_phonograms' AND syscolumns.name='template_type')
BEGIN
ALTER TABLE mp_phonograms ADD template_type int DEFAULT(0);

UPDATE mp_spot_types SET template=show_type WHERE system_type=1
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 510

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

if EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_phonograms' AND syscolumns.name='template_type')
BEGIN

UPDATE mp_phonograms SET mp_phonograms.template_type = (SELECT template FROM mp_spot_types WHERE mp_spot_types.show_type = mp_phonograms.type)

END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 520

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

ALTER TABLE mp_organizations ADD last_sync datetime NULL -- хранит последний дату последней синхронизации (с bpm)

if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO




--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 530

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

ALTER TABLE mp_position_replacement ADD discounts_enum nvarchar(128) NULL

if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 540

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_base_costs' AND syscolumns.name='holiday')
BEGIN
ALTER TABLE mp_base_costs ADD holiday bit NULL DEFAULT(0);
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 550

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_tariff_scales' AND syscolumns.name='hide')
BEGIN
ALTER TABLE mp_tariff_scales ADD hide bit NULL DEFAULT(0);
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO


--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 560

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------


IF NOT EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='FK_MP_POSITION_REPLACEMENT_VS_MP_POSITION' AND sysobjects.name='mp_position_replacement'
)
BEGIN
	create index FK_MP_POSITION_REPLACEMENT_VS_MP_POSITION on mp_position_replacement (position_id ASC)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='FK_MP_USED_DISCOUNTS_VS_MP_DISCOUNT_VALUES' AND sysobjects.name='mp_used_discounts'
)
BEGIN
	create index FK_MP_USED_DISCOUNTS_VS_MP_DISCOUNT_VALUES on mp_used_discounts (discount_values_id ASC)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END


IF NOT EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='FK_MP_DISCOUNT_VALUES_VS_MP_TYPE_ID' AND sysobjects.name='mp_discount_values'
)
BEGIN
	create index FK_MP_DISCOUNT_VALUES_VS_MP_TYPE_ID on mp_discount_values (type_id ASC)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END


IF NOT EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='FK_MP_PAYMENTS_VS_MP_ACCOUNTS' AND sysobjects.name='mp_payments'
)
BEGIN
	create index FK_MP_PAYMENTS_VS_MP_ACCOUNTS on mp_payments (account_id ASC)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='FK_MP_MEDIAPLAN_SCHEDS_VS_MP_MEDIAPLANS' AND sysobjects.name='mp_mediaplan_scheds'
)
BEGIN
	create index FK_MP_PAYMENTS_VS_MP_ACCOUNTS on mp_mediaplan_scheds (mediaplan_id ASC)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO


--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 570

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

ALTER TABLE mp_reports ADD processor_type int DEFAULT(0) NOT NULL;
ALTER TABLE mp_reports ADD external_processor_name varchar(128) NULL;

if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 580

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

IF EXISTS( SELECT veri FROM components WHERE name = 'MediaPlannerComponent' and veri>=@ver)
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

UPDATE mp_tariff_scales set name=CONVERT(nvarchar, begin_date) where name is null

if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO components (name, veri) VALUES('MediaPlannerComponent', @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO