Demo version requirements

/*
** Project: Creatio
** DBMS   : MSSQL 2008
** Type   : Script
** Name   : Actualize Date For Demo
*/
IF EXISTS (
    SELECT NULL
    FROM sys.objects
    WHERE [type] = 'TR' AND [name] = 'TR_BulkEmail_ProcessStatisticAfterUpdate'
)
DISABLE TRIGGER [dbo].[TR_BulkEmail_ProcessStatisticAfterUpdate] ON [dbo].[BulkEmail]
GO
IF EXISTS (
    SELECT NULL
    FROM sys.objects
    WHERE [type] = 'TR' AND [name] = 'TRCallAI'
)
DISABLE TRIGGER [dbo].[TRCallAI] ON [dbo].[Call]
GO


set nocount on
declare @NL char(1)
set @NL = char(13)

declare @ActualizedDemoDate datetime2
set @ActualizedDemoDate = (
    select top 1 sv.DateTimeValue 
    from SysSettings ss, SysSettingsValue sv
    where ss.Id = sv.SysSettingsId 
    and ss.Code like 'ActualizedDemoDate%'
    order by sv.ModifiedOn desc
)

if (@ActualizedDemoDate is null) 
begin
    print 'DemoDate is null.'
    return
end

print 'DemoDate is ' + cast(@ActualizedDemoDate as varchar)
declare @DaysDiff int
set @DaysDiff = datediff(DAY, @ActualizedDemoDate, getdate())
declare @DaysDiffStr varchar(6)
set @DaysDiffStr = cast(@DaysDiff as varchar(6))

declare @Table sysname
declare @Column sysname    

declare [c] cursor for
select table_name, column_name
from INFORMATION_SCHEMA.COLUMNS
where 
    (upper(data_type) = 'DATETIME' or upper(data_type) = 'DATETIME2' or upper(data_type) = 'DATE')
    and (column_name not in ('ModifiedOn') or 
            table_name in ('Opportunity', 'OpportunityInStage', 'SocialMessage', 'Lead', 'Contact', 'Account', 'KnowledgeBase', 'Product',
            'ApplicationApproval', 'Campaign', 'BulkEmail', 'CaseMessageHistory', 'Case', 'Activity', 'Invoice'))
    and (column_name not in ('CreatedOn') or 
            table_name in ('Opportunity', 'OpportunityInStage', 'SocialMessage', 'Lead', 'Contact', 'Account', 'KnowledgeBase', 'Product',
            'ApplicationApproval', 'Campaign', 'BulkEmail', 'CaseMessageHistory', 'Case', 'Activity', 'Invoice'))
    and not table_name in ('SysLic', 'RemindInterval', 'SysRecentEntity', 'PlanYear', 'PlanMonth', 'PlanQuarter', 'Period', 'FinIndicator', 'CampaignPlanner', 'MktgActivity', 'CurrencyRate')
    and not (table_name in ('ContactAnniversary', 'AccountAnniversary') and column_name = 'Date')
    and not (table_name in ('Contact') and column_name = 'BirthDate')
    and not (table_name in ('SysAdminUnit') and column_name = 'PasswordExpireDate')
    and not (table_name in ('SysImage') and column_name = 'UploadedOn')
    and objectproperty(object_id(table_name), 'IsTable') = 1
    order by table_name, column_name

open [c]

while (1 = 1)
begin
    fetch next from [c] into @Table, @Column

    if @@fetch_status = -1 break
    if @@fetch_status = -2 continue

    declare @tempExecStr nvarchar(max)
    set @tempExecStr = (
        ' update [' + @Table + '] ' + @NL +
        ' set [' + @Column + '] = dateadd(DAY, ' + @DaysDiffStr + ', [' + @Column + '])' + @NL + 
        ' where not [' + @Column + '] is null ' + @NL + 
        ' and datediff(DAY, [' + @Column + '], ''99991231'') > ' + @DaysDiffStr)
        
    exec (@tempExecStr)
    
    set @tempExecStr = (
    'DECLARE @currentDate DATETIME2 = getutcdate();' +
    ' update [' + @Table + '] ' + @NL +
    ' set [' + @Column + '] = @currentDate' + @NL + 
    ' where [' + @Column + '] > @currentDate' + @NL +
    ' and ''' + @Column + ''' in (''CreatedOn'', ''ModifiedOn'')')

    exec (@tempExecStr)
    
    print @Table + ' Column ->' +@Column + ' Diff->' + @DaysDiffStr + '(days) is updated.'
end
close [c]
deallocate [c]
GO
/* Actualize Date For Demo Forecasts */

IF (OBJECT_ID(N'ForecastSheet') IS NOT NULL)
BEGIN
    /*Actualize opportunity DueDate for demo Forecast*/
    --DECLARE @minOpportunityDueDate DATETIME2 = (SELECT MIN(o.DueDate) FROM Opportunity o);
    --DECLARE @newMinOpportunityDueDate DATETIME2 = DATEADD(MONTH, ((DATEPART(YEAR, GETUTCDATE()) - 2000) * 12) + DATEPART(MONTH, GETUTCDATE()) - 2, CONVERT(DATETIME, '2000.01.13', 102));
    --DECLARE @opportunityDueDateDiff INT = DATEDIFF(DAY,@minOpportunityDueDate, @newMinOpportunityDueDate);
    --IF @opportunityDueDateDiff <> 0 BEGIN
    -- PRINT 'Will add ' + CAST(@opportunityDueDateDiff AS VARCHAR(10)) + ' days to Opportunity DueDate';
    -- UPDATE Opportunity
    -- SET DueDate = DATEADD(DAY, @opportunityDueDateDiff, DueDate)                                    
    --END
    

    DECLARE @entityForecastMap TABLE(
        ForecastEntityName NVARCHAR(50)
    );  

    INSERT INTO @entityForecastMap 
    VALUES 
        ('AccountForecast'),
        ('ContactForecast'),
        ('LeadTypeForecast'),
        ('OppDepartmentForecast')

    DECLARE @sql NVARCHAR(max);
    DECLARE @tableName NVARCHAR(100);

    DECLARE c CURSOR FOR
        SELECT ForecastEntityName FROM @entityForecastMap
    OPEN c

    WHILE 1 = 1
    BEGIN
        FETCH NEXT FROM c INTO @tableName
        IF @@FETCH_STATUS = -1 BREAK
        IF @@FETCH_STATUS = -2 CONTINUE

        IF (OBJECT_ID(@tableName) IS NOT NULL)
        BEGIN
            SET @sql = '
                UPDATE f
                SET PeriodId = (
                    SELECT 
                        inp.Id 
                    FROM Period inp
                    WHERE inp.PeriodTypeId = p.PeriodTypeId
                    AND inp.StartDate = DATEADD(year, DATEDIFF(year, p.StartDate, GETDATE()), p.StartDate)
                    AND inp.DueDate = DATEADD(year, DATEDIFF(year, p.DueDate, GETDATE()), p.DueDate)
                    )
                FROM '+ @tableName +' f
                JOIN Period p ON p.Id = f.PeriodId
            ';
            EXEC sp_executesql @sql;
        END;
    END;
    CLOSE c
    DEALLOCATE c
END;

GO

IF EXISTS (
    SELECT NULL
    FROM sys.objects
    WHERE [type] = 'TR' AND [name] = 'TR_BulkEmail_ProcessStatisticAfterUpdate'
)
ENABLE TRIGGER [dbo].[TR_BulkEmail_ProcessStatisticAfterUpdate] ON [dbo].[BulkEmail]
GO
IF EXISTS (
    SELECT NULL
    FROM sys.objects
    WHERE [type] = 'TR' AND [name] = 'TRCallAI'
)
ENABLE TRIGGER [dbo].[TRCallAI] ON [dbo].[Call]
GO