VisiPsLib

0.9.35

VisiPsLib.psm1

function Find-SysadminMembers { 
   <#
    .SYNOPSIS
    Looks for sysadmin members in ALL S1 and Caisses
    Saves results in ONECM.VisiSystem.Control.SysadminMembers
    Sends an email with results (if any).
    Caisses down saved in Control.CaisseDown
     
    .Description
    Looks for sysadmin members in ALL S1 and Caisses
    Saves results in ONECM.VisiSystem.Control.SysadminMembers
    Sends an email with results (if any).
     
    .PARAMETER MailRecipients
    Recipients for report email
    Default "listeDba@visilab.ch"
 
    .PARAMETER ExcludeList
    Accepted sysadmin members
    Default "sa|ssi-dba|ssi-sqladm|visiSQLAdmin
 
    .EXAMPLE
    #
    Find-SysadminMembers
 
    .EXAMPLE
    #
    Find-SysadminMembers -MailRecipients 'mircea.nicolescu@visilab.ch'
 
    .EXAMPLE
    #
    Find-SysadminMembers -ExcludeList 'sa|ssi-dba'
 
    #> 
    [CmdletBinding(SupportsShouldProcess)] 

    Param (
        [parameter(Mandatory=$false,position=0)]
        [string] $mailRecipients = 'listeDBA@visilab.ch' 
        ,
        [parameter(Mandatory=$false,position=1)]
        [string] $excludeList = 'sa|ssi-dba|ssi-sqladm|visiSQLAdmin' 
    ) 
    Begin {
    } 
    Process{
        $srv = 'SRV-ONECM' 
        $dbn = 'VisiSystem' 
        $table = 'Control.SysadminMembers'
        $tableCaisseDown = 'Control.CaisseDown'
        $title = 'Non-dba Sysadmin SQL Accounts'
        
        # process servers
            Get-DbaServerRoleMember -SqlInstance $(gvsp) -ServerRole sysadmin | ? Name -notmatch $excludeList | Write-DbaDataTable -SqlInstance $srv -Database $dbn -Table $table -AutoCreateTable -Truncate 
        
        # process caisses
            Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query "IF OBJECT_Id('$tableCaisseDown','U') IS NOT NULL TRUNCATE TABLE $tableCaisseDown" 
            Get-VisiCaisses -scope Prod | % {
                $_.crpos_name
                if (Test-Connection -ComputerName $($_.crpos_name) -Quiet -Count 1 ) { 
                    Get-DbaServerRoleMember -SqlInstance $($_.crpos_name) -ServerRole sysadmin | ? Name -notmatch $excludeList | Write-DbaDataTable -SqlInstance $srv -Database $dbn -Table $table -AutoCreateTable 
                }
                else { 
                    $_ | Write-DbaDataTable -SqlInstance $srv -Database $dbn -Table $tableCaisseDown -AutoCreateTable
                } 
            } 
        
        # send email
        $qry = "
        IF EXISTS ( SELECT 1 FROM $dbn.$table )
        BEGIN
            DECLARE @tableHTML NVARCHAR(MAX) ;
            SET @tableHTML =
                N'<H1>$title</H1>' +
                N'<table border=`"1`">' +
                N'<tr><th>Computer Name</th>' +
                N'<th>InstanceName</th>' +
                N'<th>SQL Instance</th>' +
                N'<th>Role</th>' +
                N'<th>Name</th>'+
                N'</tr>' +
                CAST ( ( SELECT td = ComputerName, '',
                                td = InstanceName, '',
                                td = SQlinstance, '',
                                td = Role, '',
                                td = Name
                            FROM $dbn.$table
                            ORDER BY ComputerName ASC, [name] ASC
                            FOR XML PATH('tr'), TYPE
                ) AS NVARCHAR(MAX) ) +
                N'</table>' ;
         
            IF EXISTS ( SELECT 1 from VisiSystem.Control.CaisseDown )
            BEGIN
                DECLARE @tableHTML2 NVARCHAR(MAX) ;
                SET @tableHTML2 =
                    N'<H1>Caisses down</H1>' +
                    N'<table border=`"1`">' +
                    N'<tr><th>CRPOS Name</th>' +
                    N'<th>Sub short name</th>'+
                    N'</tr>' +
                    CAST ( ( SELECT td = CRPOS_name, '',
                                    td = Sub_short_name
                                FROM VisiSystem.Control.CaisseDown
                                ORDER BY CRPOS_Name ASC
                                FOR XML PATH('tr'), TYPE
                    ) AS NVARCHAR(MAX) ) +
                    N'</table>' ;
                 
                SELECT @tableHTML = @tableHTML + @tableHTML2
            END
         
            EXEC msdb.dbo.sp_send_dbmail @recipients='$MailRecipients',
                @subject = '$title',
                @body = @tableHTML,
                @body_format = 'HTML'
        END
        "
        Write-Verbose $qry  
        Invoke-Sqlcmd -ServerInstance $srv -Query $qry 
    }
}




function Compare-Procedures { 
    <#
    .SYNOPSIS
    Compare procedures code and VisiVersion between 2 servers
 
    .Description
    Compare results are saved on Server1.VisiSystem.Control.Objects
 
    .PARAMETER SQLInstance1
    Server1
    Will persist the comparison results in VisiSystem.Control.Objects
 
    .PARAMETER SQLInstance2
    Server2
 
    .PARAMETER Database1
    Database1
     
    .PARAMETER Database2
    Database12
 
    .PARAMETER MailRecipients
    Recipients for report email
    Default "listeDbaDevSql@visilab.ch;eric.colombara@visilab.ch"
 
    .PARAMETER ProcedurePattern
    TSQL LIKE pattern for procedure name filter
 
    .EXAMPLE
    #
    Compare-Procedures -SQLInstance1 SRV-ONECM SQLInstance2 SRV-ONEWEB -Database1 Arizona -Database2 Arizona -ProcedurePattern '%sel%' -MailRecipients mircea.nicolescu@visilab.ch
 
    .EXAMPLE
    #
    $params = @{
        SQLInstance1 = 'ONECM'
        SQLInstance2 = 'BVT1'
        Database1 = 'Arizona'
        Database2 = 'Arizona_ref'
        MailRecipients = 'mircea.nicolescu@visilab.ch'
        ProcedurePattern = '%'
        Verbose = $true
    }
    Compare-Procedures @params
 
 
    #> 
    [CmdletBinding(SupportsShouldProcess)] 

    Param (
        [parameter(Mandatory=$true,position=0)]
        [ValidateNotNullOrEmpty()]
        [string] $SQLInstance1
        , 
        [parameter(Mandatory=$true,position=1)]
        [ValidateNotNullOrEmpty()]
        [string] $SQLInstance2
        , 
        [parameter(Mandatory=$true,position=2)]
        [ValidateNotNullOrEmpty()]
        [string] $Database1
        ,
        [parameter(Mandatory=$true,position=3)]
        [ValidateNotNullOrEmpty()]
        [string] $Database2
        ,
        [parameter(Mandatory=$false,position=4)]
        [string] $MailRecipients = 'listeDbaDevSql@visilab.ch;eric.colombara@visilab.ch'
        ,
        [parameter(Mandatory=$false,position=5)]
        [string] $ProcedurePattern = '%' 
    ) 
    Begin {
        If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
            $DebugPreference = "Continue" 
        } 
        else { 
            $DebugPreference = "SilentlyContinue" 
        } 

        try { 
            Invoke-Sqlcmd -ServerInstance $SQLInstance1 -Query "select 1" -ErrorAction Stop -ConnectionTimeout 2 | Out-Null 
        }
        catch { 
            Throw "Connection to $SQLInstance1 failed"
        }

        try { 
            Invoke-Sqlcmd -ServerInstance $SQLInstance2 -Query "select 1" -ErrorAction Stop -ConnectionTimeout 2 | Out-Null
        }
        catch { 
            Throw "Connection to $SQLInstance2 failed"
        }

    } 

    Process { 
        $ErrorActionPreference = "stop"

        $server1 = $SQLInstance1 
        $server2 = $SQLInstance2


        # create results table if not exists
        $resultsTableQry = "
        IF OBJECT_ID('Control.Objects','U') IS NULL
            CREATE TABLE Control.[Objects] (
                    Id INT IDENTITY PRIMARY KEY
                    , Server_1 SYSNAME
                    , Server_2 SYSNAME
                    , [Database_1] SYSNAME
                    , [Database_2] SYSNAME
                    , [Name] SYSNAME
                    , [Type] CHAR(2)
                    , Diff_type VARCHAR(50)
                    , Modify_date_1 DATETIME2(0)
                    , Modify_date_2 DATETIME2(0)
                    , Visi_version_1 CHAR(10)
                    , Visi_version_2 CHAR(10)
                    , Hb_1 VARBINARY(8000)
                    , Hb_2 VARBINARY(8000)
                    , Control_time DATETIME2(0) DEFAULT CURRENT_TIMESTAMP
                    , Control_user SYSNAME DEFAULT CURRENT_USER
            )
        EXEC sp_save_visiVersion 'Control.Objects', '101.2'; "
        Invoke-Sqlcmd -ServerInstance $Server1 -Database Visisystem -QueryTimeout  5 

        # build collect procs query
        $qryProcs = "
            DROP TABLE IF EXISTS #procs ;
            CREATE TABLE #procs (proc_name SYSNAME PRIMARY KEY, create_Date DATETIME2, modify_date DATETIME2, visi_version CHAR(10), [code] VARCHAR(MAX), hb VARBINARY(8000)) ;
            INSERT #procs
            SELECT CONCAT(s.name, '.', p.name) AS [proc_name]
                , p.create_date
                , p.modify_date
                , CONVERT(CHAR(10), v.value) AS visi_version
                , sm.definition AS [code]
                , NULL AS hb
            FROM sys.procedures AS p
            JOIN sys.schemas AS s ON s.[schema_id] = p.[schema_id]
            JOIN sys.sql_modules AS sm ON sm.[object_id] = p.[object_id]
            CROSS APPLY ( SELECT * FROM sys.fn_listextendedproperty('VisiVersion' , 'schema', s.name, 'procedure', p.name, NULL, NULL ) ) v
            WHERE p.name NOT LIKE 'msmerge%'
            AND p.is_ms_shipped = 0
            --and s.name <>'Control'
            AND p.name LIKE '$ProcedurePattern' ;
            UPDATE #procs SET code = REPLACE([code],';','') ; -- eliminate false positives caused by ending semicolon
            UPDATE #procs SET hb = hashbytes('SHA2_256', [code]) ;
            ALTER TABLE #procs DROP COLUMN [code] ;
            SELECT * FROM #procs ;
        " 
        
        # collect procs info
            # create tempdb tables - this must be done manaually as AutoCreate messes up the datatypes
            $qry = "
            DROP TABLE IF EXISTS dbo.local_procs
            CREATE TABLE local_procs (proc_name SYSNAME PRIMARY KEY, create_Date DATETIME2, modify_date DATETIME2, visi_version CHAR(10), hb VARBINARY(8000)) ;
            DROP TABLE IF EXISTS dbo.remote_procs
            CREATE TABLE remote_procs (proc_name SYSNAME PRIMARY KEY, create_Date DATETIME2, modify_date DATETIME2, visi_version CHAR(10), hb VARBINARY(8000)) ; " 
            Invoke-Sqlcmd -ServerInstance $server1 -Database Tempdb -Query $qry 
        
            # populate local_proce and remote_procs
            Write-Verbose "Collect procs info $server1" 
            Invoke-Sqlcmd -ServerInstance $server1 -Database $database1 -Query $qryProcs -QueryTimeout 10000 | Write-DbaDbTableData -SqlInstance $server1 -Database tempdb -Table dbo.local_procs -Truncate 
            Write-Verbose 'ok' 
            Write-Verbose "Collect procs info $server2" 
            Invoke-Sqlcmd -ServerInstance $server2 -Database $database2 -Query $qryProcs -QueryTimeout 10000 | Write-DbaDbTableData -SqlInstance $server1 -Database tempdb -Table dbo.remote_procs -Truncate
            Write-Verbose 'ok' 
        # process results
            Write-Verbose "Process results" 
            $objType = 'P' 
            $qryDiff = "
                ---------------------------
                --delete from results table
                ---------------------------
                    DELETE Visisystem.control.Objects
                    WHERE [Type] = '$objType'
                    AND server_1 = '$server1'
                    AND server_2 = '$server2'
                    AND database_1 = '$database1'
                    AND database_2 = '$database2' ;
                 
                -----------------------
                --miss local (server 1)
                -----------------------
                    ;WITH x AS (
                        SELECT proc_name
                        FROM tempdb.dbo.remote_procs AS rp
                        EXCEPT
                        SELECT proc_name
                        FROM tempdb.dbo.local_procs AS lp
                    )
                    INSERT [Visisystem].[Control].[Objects](
                          Server_1
                        , Server_2
                        , [Database_1]
                        , [Database_2]
                        , [Name]
                        , [Type]
                        , Diff_type
                        , Modify_date_1
                        , Modify_date_2
                        , Visi_version_1
                        , Visi_version_2
                        , Hb_1
                        , Hb_2
                    )
                    SELECT
                        '$server1'
                        , '$server2'
                        , '$database1'
                        , '$database2'
                        , rp.proc_name
                        , '$objType'
                        , 'MISS_$Server1'
                        , NULL
                        , rp.modify_date
                        , NULL
                        , rp.visi_version
                        , NULL
                        , NULL
                    FROM tempdb.dbo.remote_procs AS rp
                    JOIN x AS x ON x.proc_name = rp.proc_name
             
                ------------------------
                --miss remote (server 2)
                ------------------------
                    ;WITH y as (
                        SELECT proc_name
                        FROM tempdb.dbo.local_procs AS lp
                        EXCEPT
                        SELECT proc_name
                        FROM tempdb.dbo.remote_procs AS rp
                    )
                    INSERT [Visisystem].[Control].[Objects](
                          Server_1
                        , Server_2
                        , [Database_1]
                        , [Database_2]
                        , [Name]
                        , [Type]
                        , Diff_type
                        , Modify_date_1
                        , Modify_date_2
                        , Visi_version_1
                        , Visi_version_2
                        , Hb_1
                        , Hb_2
                    )
                    SELECT
                        '$server1'
                        , '$server2'
                        , '$database1'
                        , '$database2'
                        , lp.proc_name
                        , '$objType'
                        , 'MISS_$Server2'
                        , lp.modify_date
                        , NULL
                        , lp.visi_version
                        , NULL
                        , NULL
                        , NULL
                    FROM tempdb.dbo.local_procs AS lp
                    JOIN y AS y ON y.proc_name = lp.proc_name ;
         
                ------------------------
                -- different visiVersion
                ------------------------
                       INSERT [Visisystem].[Control].[Objects](
                          Server_1
                        , Server_2
                        , [Database_1]
                        , [Database_2]
                        , [Name]
                        , [Type]
                        , Diff_type
                        , Modify_date_1
                        , Modify_date_2
                        , Visi_version_1
                        , Visi_version_2
                        , Hb_1
                        , Hb_2
                    )
                    SELECT
                        '$server1'
                        , '$server2'
                        , '$database1'
                        , '$database2'
                        , rp.proc_name
                        , '$objType'
                        , 'DIFF_VISIVERSION'
                        , lp.modify_date
                        , rp.modify_date
                        , lp.visi_version
                        , rp.visi_version
                        , NULL--lp.hb
                        , NULL--rp.hb
                    FROM tempdb.dbo.local_procs AS lp
                    JOIN tempdb.dbo.remote_procs AS rp ON rp.proc_name = lp.proc_name
                    WHERE rp.visi_version <> lp.visi_version
         
                ---------------------------------
                --same VisiVersion different code
                ---------------------------------
                      INSERT [Visisystem].[Control].[Objects](
                          Server_1
                        , Server_2
                        , [Database_1]
                        , [Database_2]
                        , [Name]
                        , [Type]
                        , Diff_type
                        , Modify_date_1
                        , Modify_date_2
                        , Visi_version_1
                        , Visi_version_2
                        , Hb_1
                        , Hb_2
                    )
                    SELECT
                        '$server1'
                        , '$server2'
                        , '$database1'
                        , '$database2'
                        , rp.proc_name
                        , '$objType'
                        , 'DIFF_CODE'
                        , lp.modify_date
                        , rp.modify_date
                        , lp.visi_version
                        , rp.visi_version
                        , lp.hb
                        , rp.hb
                    FROM tempdb.dbo.local_procs AS lp
                    JOIN tempdb.dbo.remote_procs AS rp ON rp.proc_name = lp.proc_name
                    WHERE ISNULL(rp.hb,-1) <> ISNULL(lp.hb,-1)
                    AND rp.visi_version = lp.visi_version
                    " 
        # copy to clipboard
        #[Windows.forms.Clipboard]::SetText($qryDiff)
        
        Invoke-Sqlcmd -ServerInstance $server1 -Database Tempdb -Query $qryDiff 
        Write-Verbose 'ok' 

        #send email report
        $qryMail = "
        DECLARE @tableHTML NVARCHAR(MAX) ;
        SET @tableHTML =
            N'<H1>Procedures Control Report</H1>' +
            N'<table border=`"1`">' +
            N'<tr><th>Server 1</th>' +
            N'<th>Server 2</th>' +
            N'<th>Database 2</th>' +
            N'<th>Database 2</th>' +
            N'<th>Object name</th>' +
            N'<th>Object type</th>' +
            N'<th>Diff type</th>' +
            N'<th>Modify date 1 </th>'+
            N'<th>Modify date 2 </th>'+
            N'<th>VisiVersion 1 </th>' +
            N'<th>VisiVersion 2 </th>' +
            N'<th>Control time </th>' +
            N'<th>Control user</th></tr>' +
            CAST ( ( SELECT td = Server_1, '',
                            td = Server_2, '',
                            td = Database_1, '',
                            td = Database_2, '',
                            td = [Name], '',
                            td = [Type], '',
                            td = Diff_type, '',
                            td = ISNULL(CONVERT(CHAR(20),Modify_date_1),'NULL'), '',
                            td = ISNULL(CONVERT(CHAR(20),Modify_date_2),'NULL'), '',
                            td = ISNULL(Visi_version_1,'NULL'), '',
                            td = ISNULL(Visi_version_2,'NULL'), '',
                            td = Control_time , '',
                            td = Control_user
                      FROM VisiSystem.Control.Objects
                      WHERE server_1 = '$server1'
                      AND server_2 = '$server2'
                      AND database_1 = '$database1'
                      AND database_2 = '$database2'
                      AND [Type] = '$objType'
                      ORDER BY Id ASC
                      FOR XML PATH('tr'), TYPE
            ) AS NVARCHAR(MAX) ) +
            N'</table>' ;
             
        --SELECT @tableHTML
        EXEC msdb.dbo.sp_send_dbmail @recipients='$mailRecipients',
            @subject = 'Procedures Version control report $server1 - $server2 ',
            @body = @tableHTML,
            @body_format = 'HTML'
            "
        Write-Verbose "Send email report" 
        Invoke-Sqlcmd -ServerInstance $Server1 -Query $qryMail 
        Write-Verbose 'ok' 
    } 
} 

function Compare-ProcedureCode { 
    <#
    .SYNOPSIS
    Compare Stored Procedeure code between 2 servers
    Requires BeyondCompare installed in "C:\Program Files\Beyond Compare 4"
 
    .Description
     
    .PARAMETER SQlInstance1
    Server #1
    .PARAMETER SQLInstance2
    Server #2
    .PARAMETER Database1
    Database on server 1 - default Arizona
    .PARAMETER Database2
    Database on server 2 - default Arizona
    .PARAMETER ProcedureName
    Procedure to compare
    .EXAMPLE
    #
    Compare-ProcedureCode -SQLInstance1 srv-onecm -SQLInstance2 srv-oneweb -ProcedureName p_GetNextID
 
    .EXAMPLE
    #
    $params = @{
        SQLInstance1 = 'SRV-ONECM'
        SQLInstance2 = 'SRV-ONEWEB'
        Database1 = 'VisiSystem'
        Database2 = 'VisiSystem'
        ProcedureName = 'p_visi_purge_agent_history'
    }
    Compare-ProcedureCode @params
    #> 
 
    [CmdletBinding(SupportsShouldProcess)] 
    Param (     
        [string]$SQLInstance1 ,
        [string]$SQLInstance2 , 
        [string]$Database1 = 'Arizona' , 
        [string]$Database2 = 'Arizona' ,
        [string]$ProcedureName 
    ) 
    Begin{
        If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
            $DebugPreference = "Continue" 
        } 
        else { 
            $DebugPreference = "SilentlyContinue" 
        } 
    }
    Process { 
        # split proce name - extract schema
        if ($ProcedureName -match '\.' ) { 
            $schema = ($ProcedureName -split '\.')[0]
            $object = ($ProcedureName -split '\.')[1]
        }
        else { 
            $schema = 'dbo' 
            $object = $ProcedureName 
        } 
        Write-Debug "Schema = $schema Object = $object"

        #$guid1 = [guid]::NewGuid().ToString()
        #($file1 = Join-Path -Path $env:TEMP -ChildPath $($guid1 + '.sql'))
        $file1 = Join-Path -Path $env:TEMP -ChildPath $($ProcedureName + '-'+$SQLInstance1 + '.sql')
        Write-Debug $file1 

        #$guid2 = [guid]::NewGuid().ToString()
        #($file2 = Join-Path -Path $env:TEMP -ChildPath $($guid2 + '.sql'))
        $file2 = Join-Path -Path $env:TEMP -ChildPath $($ProcedureName + '-'+$SQLInstance2 + '.sql')
        Write-Debug $file2 
        #New-Item $file2 -ItemType File

        $srv1 = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $SQLInstance1 -ea Stop
        $srv1.Databases[$database1].StoredProcedures[$Object,$schema] | Export-DbaScript -FilePath $file1 -NoPrefix | Out-Null 

        Write-Debug 'srv1 ok' 

        $srv2 = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $SQLInstance2 -ea Stop
        $srv2.Databases[$database2].StoredProcedures[$object,$schema] | Export-DbaScript -FilePath $file2 -NoPrefix |Out-Null
        Write-Debug 'srv2 ok ' 

        if ($PSCmdlet.ShouldProcess($ProcedureName,"Comparing on $sqlinstance1, $sqlInstance2")) {
            set-location "C:\Program Files\Beyond Compare 4"
            start-process bcompare -ArgumentList "$file1 $file2" 
        } 
    }
}
function Get-Arc8 {
    $archived = @('SRV-ACHATS','VI015-S1','VI016-S1','VI017-S1','VI019-S1','VI020-S1','VI021-S1','VI022-S1','VI024-S1','VI025-S1','VI026-S1','VI027-S1','VI028-S1','VI031-S1','VI032-S1','VI035-S1','VI038-S1','VI039-S1','VI040-S1','VI040-S1','VI042-S1','VI042-S1','VI046-S1','VI047-S1','VI047-S1','VI049-S1','VI049-S1','VI050-S1','VI050-S1','VI060-S1','VI512-S1','VI519-S1','VI581-S1','VI583-S1')
    (Get-VisiServers prod | ? server_name -notin $archived | ? server_name -notmatch 'SRV|PV').server_name 
} 

function Export-UDDT {
    [CmdletBinding(SupportsShouldProcess)] 
    Param ( 
        [parameter(Mandatory=$true,position=0,ValueFromPipeline=$true)]
        [ValidateNotNullOrEmpty()]
        [string]$Destination 
        ,
        [parameter(Mandatory=$false,position=1)]
        [ValidateNotNullOrEmpty()]
        [string]$Source = 'SRV-ONECM' 
        ,
        [parameter(Mandatory=$false,position=2)]
        [switch]$ExcludeCash 
    ) 
    Begin {
        If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
            $DebugPreference = "Continue" 
        } 
        else { 
            $DebugPreference = "SilentlyContinue" 
        } 

        $srvx = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $Source  
        $so = New-DbaScriptingOption
            $so.ScriptSchema = $true
            $so.IncludeIfNotExists  = $true
    }
    Process{ 
        Write-Verbose "Destination: $destination" 
        # process Arizona
        $srvx.Databases['Arizona'].UserDefinedDataTypes  | Export-DbaScript -ScriptingOptionsObject $so -Passthru | % { 
            Write-Debug $_ 
            foreach ($tgt_dbn in @('model','tempdb')) { 
                Invoke-Sqlcmd -ServerInstance $Destination -Database $tgt_dbn -Query $_  
            } 
        } 
        # process Cash
        if(-not($ExcludeCash.isPresent)){
            $srvx.Databases['ArizonaCASH'].UserDefinedDataTypes  | Export-DbaScript -ScriptingOptionsObject $so -Passthru | % { 
                Write-Debug $_ 
                foreach ($tgt_dbn in @('model','tempdb')) { 
                    Invoke-Sqlcmd -ServerInstance $Destination -Database $tgt_dbn -Query $_  
                } 
            } 
        }
    }
}


function New-CloneIndex{ 
    <#
    .SYNOPSIS
    Clone indexes to another server
    .Description
     
    .PARAMETER
     
     
    .EXAMPLE
     
    #> 
    [CmdletBinding(SupportsShouldProcess)] 
        Param ( 
            [parameter(Mandatory=$true,position=0)]
            [ValidateNotNullOrEmpty()]
            [string]$Source
            ,
            [parameter(Mandatory=$true,position=1,ValueFromPipeline=$true)]
            [ValidateNotNullOrEmpty()]
            [string]$Destination 
            ,
            [parameter(Mandatory=$true,position=2)]
            [string]$SourceDatabase
            ,
            [parameter(Mandatory=$true,position=3)]
            [string]$DestinationDatabase
            ,
            [parameter(Mandatory=$true,position=4)]
            [string]$Table
            ,
            [parameter(Mandatory=$false,position=5)]
            [switch]$SaveOldIndex 

        ) 
    Begin { 
            if ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
                $DebugPreference = "Continue" 
            }
            else { 
                $DebugPreference = "SilentlyContinue" 
            } 

            # split table name - extract schema
            if ($table -match '\.' ) { 
                $schema = ($table -split '\.')[0]
                $object = ($table -split '\.')[1]
            }
            else { 
                $schema = 'dbo' 
                $object = $table 
            } 
            Write-Debug "Schema = $schema Object = $object"

            # script indexes to apply
            # NB having this code here willa void the indexes being scripted again for every destination in the pipe
            $src_srvx = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $Source
            $guid = [guid]::NewGuid().ToString()
            $gPath = Join-Path -Path $env:TEMP -ChildPath $guid 
            New-Item $gPath -ItemType Directory -Force | Out-Null 
            $optScript = New-DbaScriptingOption
            $optScript.IncludeIfNotExists  = $true
            $src_srvx.databases[$Sourcedatabase].tables[$object,$schema].Indexes | ? IndexKeyType -notin (1,2)  | Export-DbaScript -ScriptingOptionsObject $optScript -FilePath $(Join-DbaPath $gPath $('new index - '+$table +'.sql')) | Out-Null 
            if ($debugPreference -eq "Continue" ) { 
                invoke-item $gPath
            } 
        } 

    Process { 
        $tgt_srvx = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $Destination
                
        # save target indexes before ovwerwrite
        if ($SaveOldIndex.IsPresent) { 
            $save_file = Join-DbaPath -Path $env:LOCALAPPDATA 'log' $($($Destination -replace '\\', '-' )+'-'+$DestinationDatabase+'-'+$schema+'.'+$object +'-indexesBeforeOverwrite.log' )
            Write-Verbose "Old indexes save file = $save_file" 
            $orig_indexes = $tgt_srvx.Databases[$DestinationDatabase].Tables[$object,$schema].Indexes | ? IndexKeyType -notin (1,2) 
            $orig_indexes | Export-DbaScript -FilePath $save_file -debug:$false | Out-Null
            if ($debugPreference -eq "Continue") {
                Invoke-Item $save_file     
            }
        }
         

        # drop target indexes async
            #$orig_indexes | ForEach-Object { $_.Drop() }
        $optDrop = New-DbaScriptingOption
        $optDrop.IncludeIfNotExists  = $true
        $optDrop.ScriptDrops = $true 
        $gPathDrop = Join-Path -Path $env:TEMP -ChildPath $([guid]::NewGuid().ToString())
        New-Item $gPathDrop -ItemType Directory -Force | Out-Null 
        Write-Verbose "Idx drop path = $gpathdrop"
        $tgt_srvx.databases[$DestinationDatabase].tables[$object,$schema].Indexes | ? IndexKeyType -notin (1,2)  | Export-DbaScript -ScriptingOptionsObject $optDrop -FilePath $(Join-DbaPath $gPathDrop $('drop old index - ' + $table +'.sql')) | out-null
        if ($debugPreference -eq "Continue" ) { 
            invoke-item $gPathDrop
        } 
        New-AsyncIndexDeploy -SQLInstance $Destination -Database $DestinationDatabase -path $gPathDrop 
        
        # apply indexes to destination asynchroneously
        Write-Verbose "---`n`nDestination = $destination" 
        New-AsyncIndexDeploy -SQLInstance $Destination -Database $DestinationDatabase -path $gPath
    } 
} 



function New-RefactoryIndex { 
    <#
    .SYNOPSIS
    Renames indexes on table according to Visi DB index naming
    .Description
     
    .PARAMETER
     
     
    .EXAMPLE
     
    #> 
    [CmdletBinding(SupportsShouldProcess)] 
        Param ( 
            [parameter(Mandatory=$true,position=0)]
            [ValidateNotNullOrEmpty()]
            [string]$SQLInstance
            ,
            [parameter(Mandatory=$true,position=1)]
            [ValidateNotNullOrEmpty()]
            [string]$Database  
            ,
            [parameter(Mandatory=$false,position=3,ValueFromPipeline=$true)]
            [string]$table
        ) 
        Begin {
            if ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
                $DebugPreference = "Continue" 
            }
            else { 
                $DebugPreference = "SilentlyContinue" 
            } 
        } 
    
        Process { 
            
            Write-Box $table 
            # extract schema from table name
            if ($table -match '\.' ) { 
                $schema = ($table -split '\.')[0]
                $tableName = ($table -split '\.')[1]
            }
            else { 
                $schema = 'dbo' 
                $tableName = $table
            } 
            write-debug "Table = $table" 
            write-debug "Schema = $schema" 
            write-debug "TableName = $tableName" 
    
            # loop thru indexes and build string
            $str = '' 
            $SMOsrv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $SQLInstance 
            $SMOsrv.Databases[$Database].Tables[$tableName,$schema].Indexes | ? { $_.IndexKeyType -notin (1,2)  } | % { 
                $idx_cols = $($_.IndexedColumns | ? { -not $_.IsIncluded } ) -join ',' 
                $include_cols = $($_.IndexedColumns | ? { $_.IsIncluded } ) -join ',' 
                $str  += $("EXEC dbo.sp_visi_index `n`t@table = '$table', `n`t@index_cols = '$idx_cols', `n`t@unique = $([int]$_.IsUnique) " ) 
                if ($include_cols.Length -gt 0 ) { 
                    $str += $(", `n`t@include_cols = '" + $include_cols + "'") 
                } 
                $str += "`n" 
            }
            
            # proceed to drop/create indexes
            if ($PSCmdlet.ShouldProcess($table)) {
                Measure-Command { 
                    "`n"
                    $str | Out-Default 
                    invoke-sqlcmd -ServerInstance $SQLInstance -Database $Database -Query $str -QueryTimeout 1000 | Out-Default
                }  | select @{n="Total time elapsed";e={$_.Hours,"h" , $_.Minutes,"m",$_.Seconds,"s"-join " "}}
            } 
            else { 
                #whatIf true , print script and exit
                Write-Output $str
            } 
        }
    }
    

Function Update-StatsParallel { 
    [CmdLetbinding()]
    Param (
            [parameter(Mandatory=$true,position=0)]
            [ValidateNotNullOrEmpty()] 
            [string] $SQLInstance 
            ,
            [parameter(Mandatory=$true,position=1)]
            [string] $Database 
            ,
            [parameter(Mandatory=$true,position=2,ValueFromPipeline=$true)]
            [string] $Table 
            , 
            [parameter(Mandatory=$true,position=3)]
            [switch]$WaitForCompletion
    
        ) # end param
        
        Process { 
            $ErrorActionPreference = 'Stop'
            # check trace Flag 7471 stop if not set
            $qryCheckTraceFlag = "
            IF OBJECT_ID('tempdb.dbo.#t7471') IS NOT NULL
                DROP TABLE #t7471;
            CREATE TABLE #t7471([traceFlag] SYSNAME, [status] INT, [global] INT, [session] INT ) ;
            INSERT #t7471
            EXEC sys.sp_executeSQL N'DBCC TRACESTATUS (7471) WITH NO_INFOMSGS '
     
            IF NOT EXISTS (
                SELECT *
                FROM #t7471
                WHERE traceFlag = 7471 AND STATUS = 1
            )
            BEGIN
                RAISERROR('TraceFlag 7471 not set. Execution stopped', 16, -1) ;
                RETURN ;
                END
            " 
            Write-Verbose $qryCheckTraceFlag 
            Invoke-Sqlcmd -ServerInstance $SQLInstance -Query $qryCheckTraceFlag 
    
            # log start
            $qryLog = "
            DECLARE @logId UNIQUEIDENTIFIER;
            EXEC dbo.sp_Save_VisiSys_Log @Id = @logId OUTPUT ,@Domain = 'UPDATE STATISTICS',@Module = '$table', @note = '$Database', @message = 'parallel XXL';
            SELECT @logId AS [logid]; "
            $logId = (Invoke-Sqlcmd -ServerInstance $SQLInstance -Query $qryLog ).logId
    
            # get stats and create jobs
            $qryStats  = "
            DECLARE @id BIGINT = OBJECT_ID('$table','U')
            SELECT s.name AS statName
            FROM sys.[stats] AS s
            WHERE s.[object_id] = @id "
            $stats = (Invoke-Sqlcmd -ServerInstance $SQLInstance -Database $Database -Query $qryStats -Verbose -ErrorAction Stop ).statName 
    
            foreach ($stat in $stats) { 
                $command  = "
                UPDATE STATISTICS $table [$stat] WITH FULLSCAN,MAXDOP=0;
                EXEC dbo.sp_Save_VisiSys_Log @Id = ''$logId'';
                "
                $desc = "Update statistics for table $table ; Generated by PS Script ; Contact: MNI" 
                $job_name = "## Update STATS $table $stat " 
                $outputFile = '$(ESCAPE_SQUOTE(SQLLOGDIR))\$(ESCAPE_SQUOTE(JOBNAME))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_SQUOTE(TIME)).log'
    
                $qry  = "
                DECLARE @job_name SYSNAME = '$job_name'
     
                IF EXISTS ( SELECT 1 FROM msdb.dbo.sysjobs AS j WHERE j.name = @job_name)
                    EXEC msdb.dbo.sp_delete_job @job_name=@job_name, @delete_unused_schedule=1;
                EXEC msdb.dbo.sp_add_job
                    @job_name=@job_name,
                    @enabled=1,
                    @notify_level_eventlog=0,
                    @notify_level_email=2, --2=on fail 3=always
                    @notify_level_netsend=0,
                    @notify_level_page=0,
                    @delete_level=1, -- 0=never, 1=after run
                    @description='$desc',
                    @category_name=N'[Uncategorized (Local)]',
                    @owner_login_name=N'sa',
                    @notify_email_operator_name=N'DBA';
     
                EXEC msdb.dbo.sp_add_jobstep
                    @job_name = @job_name,
                    @step_name=N'step_1',
                    @step_id=1,
                    @cmdexec_success_code=0,
                    @on_success_action=1,
                    @on_success_step_id=0,
                    @on_fail_action=2,
                    @on_fail_step_id=0,
                    @retry_attempts=0,
                    @retry_interval=0,
                    @os_run_priority=0, @subsystem=N'TSQL',
                    @command=N'$command',
                    @database_name=N'$Database',
                    --@output_file_name=N'$outputFile' ,
                    @flags=0;
                      
     
                EXEC msdb.dbo.sp_update_job @job_name = @job_name, @start_step_id = 1;
                EXEC msdb.dbo.sp_add_jobserver @job_name = @job_name, @server_name = N'(local)';
                " 
                ## create job
                $job_name 
                Invoke-Sqlcmd -ServerInstance $SQLInstance  -Query $qry -DisableVariables 
        
                ## start job
                $start_job = "exec msdb.dbo.sp_start_job @job_name = '$job_name'" ; 
                Invoke-Sqlcmd -ServerInstance $SQLInstance -Query $start_job 
            } 
    
            # wait for completion
            if ( $WaitForCompletion.IsPresent) { 
                while ($(Get-UpdateStatsRunning -SQLInstance $SQLInstance -table $table -database $database ) -gt 0 ) { 
                    Start-Sleep -Seconds 5 
                } 
            }
        } #end process
    } #end function
    
Function Get-UpdateStatsRunning  { 
    [CmdLetbinding()]
    Param (
        [string]$SQLInstance ,
        [string]$database , 
        [string]$table
    ) 
    Process{ 
        $qry = "
        SELECT COUNT(*) as stats_running
        FROM sys.dm_exec_requests r
        OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
        WHERE session_id != @@SPID -- don't show this query
        AND session_id > 50 -- don't show system queries
        AND r.command LIKE 'UPDATE STATISTICS'
        AND t.[text] LIKE '%$table%'
    "
        (Invoke-Sqlcmd -ServerInstance $SQLInstance -Database $database -Query $qry ).stats_running
    } 
} 

function Get-AgentJobHistoryTimeline { 

<#
 
.SYNOPSIS
Get Agent job history using dbaTools
 
.Description
 
 
.PARAMETER
SQLInstance
 
.PARAMETER
StartDate
 
.PARAMETER
EndDate
 
.EXAMPLE
 
#> 
[CmdLetbinding()]
    Param (
        [string]$SQLInstance 
        ,
        [string]$StartDate = (Get-Date).Adddays(-1).toString('yyyy-MM-dd') + ' 20:00' 
        ,
        [string]$EndDate = (Get-Date).toString('yyyy-MM-dd') + ' 09:00'
    ) 
    Process{ 
        ($file = 'd:\temp\sqlAgentHistory.html' )
        get-DbaAgentJobHistory -SqlInstance $SQLInstance -StartDate $startDate -EndDate $endDate -ExcludeJobSteps | ConvertTo-DbaTimeline | Out-File $file -Encoding ASCII
        Invoke-Item $file
    } 
} 

function New-AsyncIndexDeploy  { 
<#
.SYNOPSIS
Builds all indexes in UpgradeBD asycnhroneously
.Description
Builds Async index from folder
Creates one self delete Agent job for each file in parameter folder
Path MUST contain a deploy.json file with dbn and job notification info
.PARAMETER
Path
Where to find files
.PARAMETER
SQLInstance
.EXAMPLE
New-AsyncIndex -Path 'D:\sources\mni\Projet One\One\Release 10.1\SQL\OneCash\800.UpgradeBD\300.Indexes' -srv ONECMR
#> 
[CmdLetbinding()]
    Param (
        [parameter(Mandatory=$false,position=0,ValueFromPipeline=$true)]
        [ValidateNotNullOrEmpty()]
        [string]$SQLInstance 
        ,
        [parameter(Mandatory=$false,position=1,ValueFromPipeline=$true)]
        [ValidateNotNullOrEmpty()]
        [string]$Database
        ,
        [parameter(Mandatory=$true,position=2)]
        [ValidateScript({test-path $_ })]
        [string]$path 
        , 
        [parameter(Mandatory=$false,position=1)]
        [switch]$KeepJob 

    ) 
    Begin{
        if ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
            $DebugPreference = "Continue" 
        }
        else { 
            $DebugPreference = "SilentlyContinue" 
        } 
    }
    Process{ 
        # get files and create job
        $operator = 'DBA' 
        Get-ChildItem $path -Recurse -file -filter *.sql | ForEach-Object { 
            Write-debug  "Table Name = $($_.BaseName) " 
            $jobName = "## - $SQLInstance - Async Index - ## - $($_.BaseName)" 
            Write-Verbose "Job Name = $jobName" 
            $command = $(Get-Content $_.FullName) -replace "'" , "''" 
            $outputFile = '$(ESCAPE_SQUOTE(SQLLOGDIR))\$(ESCAPE_SQUOTE(JOBNAME))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_SQUOTE(TIME)).log'
            if ($KeepJob.IsPresent){
                $deleteLevel = 0 
            }
            else {
                $deleteLevel = 1 
            }
            $qry = " DECLARE @job_name SYSNAME = '$jobName' ;
            IF EXISTS ( SELECT 1 FROM sysjobs WHERE [name] = '$jobName')
                EXEC dbo.sp_delete_job @job_name = '$jobName' ;
            EXEC dbo.sp_add_job
                @job_name = @job_name,
                @enabled = 1,
                @description = null,
                @start_step_id = 1,
                @category_name = null,
                @category_id = null,
                @owner_login_name = 'sa',
                @notify_level_eventlog = 0,
                @notify_level_email = 2,
                @notify_level_netsend = 0,
                @notify_level_page = 0,
                @notify_email_operator_name = '$($operator)',
                @delete_level = $deleteLevel;
            EXEC dbo.sp_add_jobserver
                @job_name = @job_name;
            EXEC sp_add_jobstep
                @job_name = @job_name,
                @step_name = N'step_1',
                @subsystem = N'TSQL',
                @retry_attempts = 3,
                @retry_interval = 3,
                @database_name = '$($database)' ,
                @command = N'$command' ,
                @output_file_name=N'$outputFile' ;
            --start job
            EXEC msdb.dbo.sp_start_job @job_name = '$jobName'"
            Write-Debug $qry 
            Invoke-Sqlcmd -ServerInstance $SQLInstance -Database msdb -Query $qry  -DisableVariables 
        } 
    }
} 

function Set-NewGUID_ApsVersionManager{

<#
.Synopsis
 
  Generate a guid and update the file "APSVersionManagerServer.xml" for applications
  OneOptic or OneCash
 
.Description
  Generate a guid and update the file "APSVersionManagerServer.xml" for applications
  OneOptic or OneCash.
 
.Parameter server
  targeted server
 
.Parameter Appli
  Application name (OneOptic or OneCash only)
  Select the GUID to modify
 
.Example
  Set-NewGUID_ApsVersionManager -server Vi099-S1 -Appli OneOptic
   
  .output file's name
  \\$server\c$\inetpub\APSVersionManager\APSVersionManagerServer.xml
 
#>
[CmdLetbinding()]

    param ([String]$server, [Validateset("OneOptic", "ArizonaCash")]$Appli)
    $XML_path = "\\$server\c$\inetpub\APSVersionManager\APSVersionManagerServer.xml"
    [XML]$XML = Get-Content -Path $XML_path 
     $1= ($xml.APSVersionManagerServerSettings.RegisteredApplication | Where-Object {$_.ApplicationName -eq $Appli} | Select-Object version)
    $guid = [guid]::NewGuid()
    $Newguid = "{"+$guid+"}"
    ### modification du APSVersionManagerServer.xml avec le nouveau GUID ###
    $XML_File = get-content -Path $XML_path 
     $XML_File = $XML_File -replace $1.Version , $Newguid 
     $XML_File |Out-File -FilePath $XML_path -Encoding utf8
    } 
    


function Get-VisiServersProd {(Get-VisiServers -scope PROD -IncludeCentrale).server_name } 
Set-Alias -Name gvsp -Value Get-VisiServersProd 
function Get-VisiMagsProd {(Get-VisiServers -scope PROD -ExcludeAchats).server_name}  
Set-Alias -Name gvmp -Value Get-VisiMagsProd
    
function Get-VisiServersRec {(Get-VisiServers -scope REC -IncludeCentrale).server_name} 
Set-Alias gvsr Get-VisiServersRec 
function Get-VisiMagsRec {(Get-VisiServers -scope REC -ExcludeAchats).server_name}  
Set-Alias -Name gvmr -Value Get-VisiMagsRec 
function Get-VisiServersDev1 {(Get-VisiServers -scope DEV1 -IncludeCentrale).server_name} 
Set-Alias gvsd1 Get-VisiServersDev1 
function Get-VisiMagsDev1 {(Get-VisiServers -scope Dev1 -ExcludeAchats).server_name}  
Set-Alias -Name gvmd1 -Value Get-VisiMagsDev1 
function Get-VisiServersDev2 {(Get-VisiServers -scope DEV2 -IncludeCentrale).server_name} 
Set-Alias gvsd2 Get-VisiServersDev2 
function Get-VisiMagsDev2 {(Get-VisiServers -scope Dev2 -ExcludeAchats).server_name}  
Set-Alias -Name gvmd2 -Value Get-VisiMagsDev2 
function Get-VisiCaissesProd {(Get-VisiCaisses -scope PROD).crpos_name}
Set-Alias -Name gvcp -Value Get-VisiCaissesProd
function Get-VisiCaissesRec {(Get-VisiCaisses -scope REC).crpos_name}
Set-Alias -Name gvcr -Value Get-VisiCaissesRec
function Get-VisiCaissesD1 {(Get-VisiCaisses -scope DEV1).crpos_name}
Set-Alias -Name gvcd1 -Value Get-VisiCaissesD1
function Get-VisiCaissesD2 {(Get-VisiCaisses -scope DEV2).crpos_name}
Set-Alias -Name gvcd2 -Value Get-VisiCaissesD2

Function Get-VisiServers { 
<#
.SYNOPSIS
Returns Visilab Servers Dev, Rec or Prod
 
.Description
Returns Visilab Servers
 
.PARAMETER Scope
 
.PARAMETER IncludeCentrale
 
.PARAMETER ExcludeAchats
 
.EXAMPLE
Get-VisiServers -Scope REC
#> 

[CmdLetbinding()]
    Param (
        [parameter(Mandatory=$false,position=0)]
        [ValidateSet('Dev1','Dev2', 'Rec', 'Prod', ignorecase=$True)]
        [string] $scope = 'DEV1', 

        [parameter(Mandatory=$false,position=1)]
            [switch] $IncludeCentrale , 

        [parameter(Mandatory=$false,position=2)]
            [switch] $ExcludeAchats  

        #[parameter(Mandatory=$false,position=3)]
        # [switch] $IncludeCaisses ,

        #[parameter(Mandatory=$false,position=3)]
        # [switch] $Simple = $false ,

        #[parameter(Mandatory=$false,position=4)]
        # [switch] $ExcludePreviousRelease = $false
    ) #end param
    Begin {
            if ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
                $DebugPreference = "Continue" 
            } 
            else { 
                $DebugPreference = "SilentlyContinue" 
            } 
    } 
    Process{ 
        switch ($scope) {
            'DEV1' { $srv = 'DEV1OCM'}
            'DEV2' { $srv = 'DEV2OCM'}
            'REC'  { $srv = 'ONECMR'}
            'PROD' { $srv = 'ONECM'}
        }    

        $qry  = "select * from Arizona.dbo.v_servers where 1=1 " 
        if (-not($IncludeCentrale.IsPresent)) { 
            $qry += "
                AND [server_name] NOT LIKE '%ONECM%'
                AND [server_name] NOT LIKE 'DEV[1-3]OCM'"
        } 

        if ($ExcludeAchats.IsPresent){ 
            if ($scope -match 'DEV'){ 
                $xaPattern = '%DEV[1-9]OA%'
            }
            else {
                $xaPattern = '%ACHATS%'
            }
            $qry += "
                AND [server_name] NOT LIKE '$xaPattern'" 
        } 
        #run query
        Write-Debug $qry 
        Invoke-Sqlcmd -ServerInstance $srv -database Arizona -Query $qry | Sort-Object server_name 
    } # end process
} 

Function Add-CMAGroup { 
#############################################################
#
# Function Add_to_CMA_Group
# --------------------------------------------------------------------
#
# ---------------------------------------------------------------------
# History:
# 2018-02-19 MNI Creation
#
#############################################################
[cmdletbinding()] 
Param( 
    [parameter(Mandatory=$true,position=0)]
    [ValidateScript({invoke-sqlcmd -serverInstance $_ -query "select 1" -ConnectionTimeout 3 })] 
    [string] $CMAsrv 
    ,
    [parameter(Mandatory=$true,position=1)]
    #[ValidateNotNull]
    [string] $group 
    ,
    [parameter(Mandatory=$true,position=2)]
    #[ValidateNotNull]
    [string] $parentGroup 
    ,
    [parameter(Mandatory=$true,position=3)]
    #[ValidateNotNull]
    $hostList  
) # end param
    Process {
        If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
            $DebugPreference = "Continue" 
        } 
        else { 
            $DebugPreference = "SilentlyContinue" 
        } 
        Write-Debug $("Group = {0} | ParentGroup = {1} " -f $group, $parentGroup) 
        $dbn = 'MSDB' 

        ## resolve groupId
        $qry = "
        DECLARE @group SYSNAME = '$group' ;
        DECLARE @parentGroup SYSNAME = '$parentGroup';
        DECLARE @groupId INT = -1;
 
        SELECT @groupId = g.server_group_id
        FROM dbo.sysmanagement_shared_server_groups AS g
        JOIN dbo.sysmanagement_shared_server_groups AS gp ON g.parent_id = gp.server_group_id
        WHERE g.name = @group
        AND gp.name = @parentGroup;
       
        IF @groupId = -1
        BEGIN
            RAISERROR('Group [%s] not found ' , 16, -1, @group) ;
            RETURN ;
        END
        SELECT @groupId AS groupId;
        " 
        $groupId = (invoke-sqlcmd -ServerInstance $CMAsrv -Database $dbn -Query $qry -ea Stop ).groupId 
        Write-Debug $("GroupId = {0} " -f $groupId ) 

        ##proceed to inserting rows
        foreach ($host in $($hostList | sort-object) ) { 
            $qry = "INSERT INTO dbo.sysmanagement_shared_registered_servers_internal
            (server_group_id,name,server_name,[description],server_type)
            VALUES($groupId,'$host','$host','$host',0)" 
            Write-Debug $qry 
            invoke-sqlcmd -ServerInstance:$CMAsrv -Database:$dbn -Query:$qry -Verbose -ea Stop 
        } 
    } #end Process
} #end function

function Disable-LogShipping {
#############################################################
#
# Function Disable-LogShipping
# --------------------------------------------------------------------
#
# ---------------------------------------------------------------------
# History:
# 2017-12-22 MNI Creation
# 2019-04-18 MNI Nouvelle version avec bds Arizona et Archive
#
#############################################################
[CmdletBinding()] 
    Param( 
        [parameter(Mandatory=$false,position=0)]
        [ValidateSet('Az','Arc', 'Both', ignorecase=$True)]
        [string] $scope = 'Both'
    ) 
    Begin {
        #If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) {
        # $DebugPreference = "Continue" }
        #else {
        # $DebugPreference = "SilentlyContinue" }
    } 
    Process { 
        $srv = 'SRV-ONEMIRROR' 
        $SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $srv -ErrorAction Stop 

        switch ($scope) { 
            'Az'  { $jobs = $SMOserver.JobServer.Jobs | Where-Object {$_.Name -match '^LSRestore.*Arizona$'} } 
            'Arc'  { $jobs = $SMOserver.JobServer.Jobs | Where-Object {$_.Name -match '^LSRestore.*Archive$'} }
            'Both'  { $jobs = $SMOserver.JobServer.Jobs | Where-Object {$_.Name -match '^LSRestore'} }
        } 
        #Write-Debug $jobs | select Name

        $jobs | ForEach-Object { 
            Box $_.Name 
            "JobName = [{0}] | Status before = [{1}] " -f $_.Name , $_.IsEnabled 
            "Altering job status, please wait..." 
            $_.IsEnabled = $false 
            $_.Alter() 
            "JobName = [{0}] | Status after = [{1}] " -f $_.Name , $_.IsEnabled 
        }
    } # end process
} # end function

Function dbBuild_basic_v2 { 
<#
.SYNOPSIS
Visilab SQL Db Build
 
.Description
Builds a given releasePath ( folder or object) against a SQL database
Version 0.9.23
    - parameter SkipGetLatest deprecated
    - default behaviour will NOT get latest from TFS, -GetLAtest parameter added to force this operation
 
.PARAMETER ReleasePath
The path or object to build
 
.PARAMETER srv
Target SQL Server
 
.PARAMETER dbn
Target Database
 
.PARAMETER BuildAction
Choose among New, UpgradeDB, UpgradeBoth or PartialBuild
Default Partial Build
 
.PARAMETER SkipGetLatest
Will NOT perform a tf get if specified
Deprecated - replaced by -GetLatest
 
.PARAMETER NoTranscript
Will NOT save the build details in D:\MSSQL\SQLJobsLog if present
 
.PARAMETER DisableVisiSysLog
Will NOT write to Visisys.Log if present
 
.PARAMETER BuildInfo
Will return a hash containing the build result ( used for nightly build logging )
 
.PARAMETER GetLatest
Will perform a TFS GET -recursive on the provided releasePath
 
.PARAMETER IncludeIndex
Will include any file having INDEX in the name
Index files are excluded by default
 
.EXAMPLE
Build-VisiDB -srv SRV-ONECMR bla bla bla
#>

    [cmdletbinding()] 
    Param( 
        [parameter(Mandatory=$true,position=0)]
        [ValidateScript({test-path $_ })]
        [string] $ReleasePath  
        ,
        [parameter(Mandatory=$true,position=1,valueFromPipeline=$true )]
        [ValidateNotNullOrEmpty()]
        [string] $srv 
        ,
        [parameter(Mandatory=$false,position=2)]
        [string] $dbn = 'Arizona' 
        , 
        [parameter(Mandatory=$false,position=3)]
        [ValidateSet('New','UpgradeBD', 'UpgradeSOFT', 'UpgradeBOTH', 'PartialBuild', ignorecase=$True)]
        [string] $buildAction = 'PartialBuild' 
        , 
        [parameter(Mandatory=$false,position=4)]
        [switch] $GetLatest = $false 
        ,
        [parameter(Mandatory=$false,position=5)]
        [switch] $noTranscript = $false   
        ,
        [parameter(Mandatory=$false,position=6)]
        [switch] $disableVisiSysLog = $false   
        ,
        [parameter(Mandatory=$false,position=7)]
        [hashtable]$buildInfo = @{} 
        ,
        [parameter(Mandatory=$false,position=6)]
        [switch] $IncludeIndex = $false   

    ) # end param

    Begin   {
        If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } 
        else { $DebugPreference = "SilentlyContinue" } 
    } 
    Process { 
        ## attempt to stop transcription
        if (-not($noTranscript.IsPresent)) { 
            try{stop-transcript|out-null}
            catch [System.InvalidOperationException]{}
        } 

        ## clear errors
        $Global:Error.Clear() 
        
        ## start transcription
        if (-not($noTranscript.IsPresent)) { 
            $logFile  = Join-Path -Path D:\MSSQL\SQLJobsLog -ChildPath "Build_$($dbn)_$(Split-Path $ReleasePath -Leaf)_$($buildAction)_$($srv).$($(Get-Date -Format s ) -ireplace ':', '-' ).log" 
            Write-Debug $("Log file = {0}" -f $logFile ) 
            if ($VerbosePreference -eq "Continue" ) { 
                Start-Transcript $logFile } 
            else { 
                Start-Transcript $logFile | Out-Null } 
        } 
        try { 
            ### get latest from TFS
            if ($GetLatest.IsPresent) { 
                if ($VerbosePreference -eq "Continue" ) { tf get $ReleasePath /recursive } 
                else { tf get $ReleasePath /recursive  | Out-Null } 
                Write-Debug "TF Get Ok " 
            } # end if NoGet

            ##get last changeSet
            try { $changeSet = @(tf history $ReleasePath /stopafter:1 /noprompt /recursive)[2].Split(' ')[0] } 
            catch { $changeSet = -1 } 
            Write-Debug "Get Changeset ok, changeset = $changeSet" 

            ### get relPath files to build

            Write-Debug "$buildAction build starting" 
            if ($IncludeIndex.IsPresent) {
                $exclude =  [char](0) # use something that won't show up in a file name
            }  
            else { 
                $exclude = 'NORUN|INDEX'
            }   
            switch ($buildAction.ToUpper()) { 
                'NEW'         { $files = Get-ChildItem -Path $ReleasePath -Recurse -File -Filter *.sql | Where-Object {$_.FullName -imatch '100\.New' -and $_.FullName -inotmatch $exclude } } 
                'PARTIALBUILD'{ $files = Get-ChildItem -Path $ReleasePath -Recurse -File -Filter *.sql | Where-Object {$_.FullName -inotmatch $exclude } } 
                'UPGRADEBD'   { $files = Get-ChildItem -Path $ReleasePath -Recurse -File -Filter *.sql | Where-Object {$_.FullName -imatch '800\.UpgradeBD' -and $_.FullName -inotmatch $exclude } }
                'UPGRADESOFT' { $files = Get-ChildItem -Path $ReleasePath -Recurse -File -Filter *.sql | Where-Object {$_.FullName -imatch '801\.Upgrade' -and $_.FullName -inotmatch $exclude } }
                'UPGRADEBOTH' { $files = Get-ChildItem -Path $ReleasePath -Recurse -File -Filter *.sql | Where-Object {($_.FullName -imatch '801\.Upgrade' -or $_.FullName -imatch '800\.UpgradeBD' )  -and $_.FullName -inotmatch $exclude } } 
            } 
            Write-Debug $("Release path = {0} " -f $ReleasePath )
            Write-Debug $("Files OK Count = {0} " -f $($files.Count)) 

            ## check if there is something to build
            if ($files.Count -eq 0) { 
                Write-Warning "No files to build were found"
            }
    
            ### log build start on target server
            Write-Debug 'Log build start ' 
            try { $qry = "DECLARE @log_id UNIQUEIDENTIFIER; EXEC dbo.sp_Save_VisiSys_Log @Id=@log_id OUTPUT,@Domain='DB_Build',@Module='$dbn',@Message='$ReleasePath',@note='$buildAction',@RowCount='$changeSet';SELECT @log_id as log_id;" 
                  write-debug $qry 
                  $log_id = (Invoke-Sqlcmd -ServerInstance:$srv -Query:$qry ).log_id  } # end try
            catch { Write-Debug "VisiSys.Log failed "; $_.Exception.Message } 
    
            ## start building
            try { 
                foreach ($file in $files | Sort-Object { $_.FullName -replace "\W", 'z' }) { 
                    $($srv + '.' + $dbn + " " +  [char]0x21e8 + " " + $(split-path  $(Split-Path $file.FullName ) -leaf) + '/' + $file.BaseName)
                    Write-Debug $($file.DirectoryName -replace '^d\:\\sources\\\w+\\', '' ) 
                    Invoke-Sqlcmd -ServerInstance:$srv -Database:$dbn -InputFile:$($file.FullName) -QueryTimeout:10000 
                    Write-Debug "--- Build object OK `n" 
                } 
                
                ## gather build info
                $buildInfo.FileCount = $($files.Count) 
                
                ### log build OK
                $qry = "EXEC dbo.sp_Save_VisiSys_Log @Id = '$log_id', @note = '$buildAction OK', @status='0'" 
                write-debug $qry 
                try { Invoke-Sqlcmd -ServerInstance:$srv -Query $qry } 
                catch { write-debug "VisiSys log failed" ; $_.Exception.Message } 
            } #end try
            catch { 
               ## gather custom error info
                   $buildInfo.Message=$_.Exception.Message 
                   $buildinfo.File = $file.Name
                   $buildInfo.Dir = $file.DirectoryName
                   $buildInfo.ts = get-date 
               
               #### log db build error
               write-debug " --- Log build NOK" 
               $qry ="EXEC dbo.sp_Save_VisiSys_Log @Id='$log_id',@note='Build $buildAction FAILED',@status= '1',@remark='$($buildInfo.Message -replace "'", "''")',@fileName='$($file.Name)',@parent='$($file.DirectoryName)'" 
               Write-Debug $qry 
               try {Invoke-Sqlcmd -ServerInstance:$srv -Query $qry } 
               catch { write-debug "Visisys log failed"; $_.Exception.Message } 
               
               ##throw error
               throw "Build failed | $($buildInfo.file) | $($buildinfo.Message) | $($buildInfo.Dir)" 

            } #end catch
        } # end outer try
     
        finally { 
            if (-not($noTranscript.IsPresent)) { 
                if ($VerbosePreference -eq "Continue" ) { Stop-Transcript } 
                else { Stop-Transcript | Out-Null } 
            } #end if
        } #end outer finally
    } #end process
} #end function
Set-Alias -Name Build-VisiDb -Value dbBuild_basic_v2



Function Build_VisiSQLCommonTools { 
<#
.SYNOPSIS
Builds Visilab SQL common tools
$/Projets BDD/70 - Common Tools and Helpers
 
.Description
Builds Visilab SQL common tools
$/Projets BDD/70 - Common Tools and Helpers
 
.PARAMETER Scope
 
.PARAMETER IncludeCentrale
 
.PARAMETER ExcludeAchats
 
.PARAMETER IncludeCaisses
 
.PARAMETER ExcludePreviousRelease
 
.EXAMPLE
Get-VisiServers -Scope REC
#> 
[cmdletbinding()] 
    Param (
        [parameter(Mandatory=$false,position=0,ValueFromPipeline=$true)]
        #[ValidateScript({invoke-sqlcmd -serverInstance $_ -query "select 1" -ConnectionTimeout 1 })]
        [string] $srv = $env:COMPUTERNAME
        ,
        [parameter(Mandatory=$false,position=1)]
        [ValidateScript({test-path $_})] 
        [string] $relPath = "D:\Sources\MNI\Projets BDD\70 - Common Tools and Helpers"
        ) 
    Process {
        If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" } 
        else { $DebugPreference = "SilentlyContinue" }        
        
        write-debug $relPath 
        Build-VisiDb -srv $srv -dbn Master -ReleasePath $relPath -IncludeIndex 
    } # end process
} # end function
Set-Alias -Name Build-SQLCommonTools -value Build_VisiSQLCommonTools





Function New-RefreshEnv { 
[CmdLetbinding()]
    Param (
        [parameter(Mandatory=$false,position=0,ValueFromPipeline=$true)]
        [ValidateNotNullOrEmpty() ]
        [string] $destination  
    ) #end param

    Begin {
        if ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { $DebugPreference = "Continue" 
        } 
        else { $DebugPreference = "SilentlyContinue" 
        } 
    } 
    Process{ 
        # log and transcript ops
        New-Item "$env:LOCALAPPDATA\Log" -ItemType Directory -Force 
        $log = Join-Path -Path $env:LOCALAPPDATA  -ChildPath "Log\Refresh.$($destination).$(get-date -f yyyy-MM-dd).log"
        try{
          stop-transcript|out-null
        }
        catch [System.InvalidOperationException]{}
        Start-Transcript -Path $log -Append 

        #load hash table
        $h = @{} 
        $listDBMag = @('VisiTemp','Arizona','ArizonaCASH','Archive') 
        $listDbCentrale = @('Arizona','ArizonaCASH','Archive','Elvira','ElviraPV','VisiTemp','OneImport','OneShare','OneShareArchive','OneCustomer') 
        
        $h.Add('DEV1OA'     , @('SRV-ACHATS'   , $listDBMag)) 
        $h.Add('DEV1OCM' , @('SRV-ONECM'    , $listDbCentrale))
        $h.Add('DEV1OM'     , @('VI022-S1'        , $listDBMag))
        $h.Add('DEV1OM2' , @('PV421-S1'        , $listDBMag))
        $h.Add('DEV1OM3' , @('VI015-S1'     , $listDBMag))
        $h.Add('DEV1OW'     , @('SRV-ONEWEB'   , $listDBMag))
                                                 
        $h.Add('DEV2OA'     , @('SRV-ACHATS'   , $listDBMag)) 
        $h.Add('DEV2OCM' , @('SRV-ONECM'    , $listDbCentrale))
        $h.Add('DEV2OM'     , @('VI022-S1'        , $listDBMag))
        $h.Add('DEV2OM2' , @('PV421-S1'        , $listDBMag))
        $h.Add('DEV2OM3' , @('VI015-S1'     , $listDBMag))
        $h.Add('DEV2OW'     , @('SRV-ONEWEB'   , $listDBMag))

        $h.Add('SRV-ACHATSR'    ,@('SRV-ACHATS'                    , $listDBMag)) 
        $h.Add('SRV-ATHOMER'    ,@('SRV-ATHOME'                    , $listDBMag)) 
        $h.Add('SRV-ATWORKR'    ,@('SRV-ATWORK'                    , $listDBMag))
        $h.Add('SRV-MAG1'        ,@('VI022-S1'                    , $listDBMag))
        $h.Add('SRV-MAG2'        ,@('VI031-S1'                    , $listDBMag))
        $h.Add('SRV-MAG3'        ,@('VI015-S1'                     , $listDBMag))
        $h.Add('SRV-ONECMR'        ,@('SRV-ONECM'                    , $listDbCentrale))
        $h.Add('SRV-ONEWEBR'    ,@('SRV-ONEWEB'                    , $listDBMag))
        $h.Add('SRV-PV1'        ,@('PV420-S1'                    , $listDBMag))
        $h.Add('SRV-PV2'        ,@('PV421-S1'                    , $listDBMag))
        $h.Add('STRATEGIC'        ,@('SRV-STRATEGIC\STRATEGICSQL'    , @('Arizona','ArizonaLD')))


        write-debug "destination = $destination" 
        if ( $destination -inotin $h.keys) { 
            throw "Destination $destination is not valid for a refresh recette procedure" 
            return 
        } 

        "Destination = {0} " -f $destination 
        "Source = {0}" -f $h.$destination[0]
        
        ## copy database
        'Copy database(s)...'
        $sharedPath = "\\SRV-NAS\BACKUP\RefreshEnv\$destination"
        New-Item $sharedPath -ItemType Directory -Force 
        "dbList = {0} "  -f $($h.$destination[1] -join ',' ) 

        $params = @{
            Source = $h.$destination[0]
            Destination = $destination
            SharedPath = $sharedPath
            BackupRestore = $true
            Force = $true
            Database = $h.$destination[1]
            WithReplace = $true
            NoBackupCleanUp = $true 
            NumberFiles = 1 
        }
        Measure-Command { 
            Copy-DbaDatabase @Params | Out-Default 
        }  | Select-Object @{n="Total time elapsed";e={$_.Hours,"h" , $_.Minutes,"m",$_.Seconds,"s"-join " "}}
        'ok'  

        ## repair orphan users
        'Repair orphan users...'
        Repair-DbaDbOrphanUser -SqlInstance $destination -Database $h.$destination[1] | Out-File $( Join-Path -Path $env:TEMP -ChildPath "RepairOrphans.$($destination).$(get-date -f yyyy-MM-dd).log")
        'ok' 
        
        ## post restore ops
        Measure-Command{
            $h.$destination[1] | ForEach-Object { 
                "Set recovery mode and Remove replication {0}"  -f $_ | Out-Default
                
                ## NB Try catch this MF next time
                Invoke-Sqlcmd -ServerInstance $destination -Query "exec sp_killall '$_'; alter database [$_] set recovery simple ; exec sp_removeDbReplication '$_' ; " -QueryTimeout 10000 | Out-Default
                ## end try catch
            }  
        }| Select-Object @{n="Total time elapsed";e={$_.Hours,"h" , $_.Minutes,"m",$_.Seconds,"s"-join " "}}
        "ok" 
    } # end process

    End {         
        Stop-Transcript 
    } 
} 

Function New-AggregateMissSub { 
[CmdLetbinding()]
    Param ( 
        [parameter(Mandatory=$true,position=0,ValueFromPipeline=$true)]
        [string] $table ,
        [parameter(Mandatory=$true,position=1)]
        [string] $srv ,
        [parameter(Mandatory=$false,position=1)]
        [string] $dbn = 'Arizona' 
        ) 
    Begin { 
        if ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
            $DebugPreference = "Continue" 
        } 
        else { 
            $DebugPreference = "SilentlyContinue" 
        } 
    } 
    
    Process { 
        box "$table @ $srv" 

        ## init vars
        Write-Debug "Init vars" 
        [string]$centrale = Get-Publisher -srv $srv 
        Write-Debug "Centrale = $centrale"         

        $pk = (Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query "SELECT dbo.fn_Get_Table_PK('$table') AS [pk]" ).pk 
        if([string]::IsNullOrEmpty($pk)) { 
            throw "Pk not found for table [$table]" 
            return 1
        } 
        Write-Debug "Pk = $pk" 

        $file =  "d:\temp\bcp_missing_x.dat" 
        Write-Debug "File = $file" 

        $Publication = 'Document' 

        $sub = (Invoke-Sqlcmd -ServerInstance $srv  -Query "select Arizona.dbo.fn_get_current_sub() as [sub]"  ).sub
        Write-Debug "Subsidiary = $sub " 
        Write-Debug "ok" 

        ##creer la table a la centrale
        Write-Debug "Process aggregate table @ $centrale" 
        $table_centrale = "tempdb.dbo.missing_$($table)_mags"
        $qry = "IF OBJECT_ID('$table_centrale') IS NULL
            CREATE TABLE $table_centrale ($pk INT, subsidiary_id INT ) " 
        Write-Debug "Qry create table centrale :
            $qry " 
        Invoke-Sqlcmd -ServerInstance $centrale -Query $qry -vb -ea Stop 
        Write-Debug "ok" 

        # check table
        write-verbose "Check table $table" 
        Verify-Table -srv $srv -Publication $Publication -table $table -check_missing_at_sub
        Write-Verbose "ok" 

        # get missing lines and copy to temp table
        Write-Verbose "Get missing lines and write to temp table" 
        $qry = "
        IF OBJECT_ID('tempdb.dbo.missing') IS NOT NULL
            DROP TABLE tempdb.dbo.missing;
        CREATE TABLE tempdb.dbo.missing ($pk INT, subsidiary_id INT ) ;
        WITH missing AS (
            SELECT CAST($pk AS INT) AS $pk
            FROM tempdb.dbo.$table
            EXCEPT
            SELECT $pk
            FROM Arizona.dbo.$table
        )
        INSERT tempdb.dbo.missing
        SELECT * , '$sub'
        FROM missing;
        "
        Write-Debug "Missing lines qry:
            $qry " 
        Invoke-Sqlcmd -ServerInstance $srv -Query $qry -vb -ea Stop 
        Write-Verbose "ok" 

        # bcp out temp table (@ mag)
        write-debug 'BCP OUT' 
        Write-Verbose "BCP out temp table (@ mag) " 
        $arglist = @(
            'dbo.missing',
            "out $file",
            "-S $srv",
            "-d TempDB",
            "-T " , 
            "-N " , 
            "-e d:\temp\bcp_out_error.txt"
        )
        Start-Process -FilePath bcp.exe -ArgumentList $arglist -RedirectStandardOutput d:\temp\bcp_out.log -wait -NoNewWindow 
        Write-Verbose "ok" 

        # bcp in @ Centrale
        Write-Verbose "BCP In @ centrale" 
        $qry = "DELETE $table_centrale WHERE subsidiary_id = $sub ; " 
        Write-Debug $qry 
        Invoke-Sqlcmd -ServerInstance $centrale -Query $qry -vb -ea Stop 
        
        $arglist = @(
            "$table_centrale",
            "in $file",
            "-S $centrale",
            #"-d TempDB",
            "-T " , 
            "-N " , 
            "-e d:\temp\bcp_error.txt"
        )
        Start-Process -FilePath bcp.exe -ArgumentList $arglist -RedirectStandardOutput d:\temp\bcp_in.log -wait -NoNewWindow 
        Write-Verbose "ok" 
    
        ##print info note
        $rc = (Invoke-Sqlcmd -ServerInstance $centrale -Query "select count(*) as rc from $table_centrale where subsidiary_id = '$sub' ").rc 
        "{4} Missing {0} rows for {1} copied into {2} (sub {3}) " -f $srv  , $table , $table_centrale, $sub , $rc 
    }
} 
#Set-Alias -Name Aggregate-MissSub -Value Aggregate_MissSub

Function Test-EmptyString{ 
<#
.SYNOPSIS
Throws error (param #2) if string (param #1) is empty
 
.Description
Throws error (param #2) if string (param #1) is empty
 
.PARAMETER $string
String to test
 
.PARAMETER $errorMessage
Message to throw if string is empty
 
 
.EXAMPLE
Test-EmptyString 'abc' , 'error Msg'
#> 

[CmdLetbinding()]
    Param (
        [string]$string , 
        [string]$errorMessage = 'String is empty' 
    )
    Process{ 
        #$string.gettype().FullName
        #$errorMessage.GetType().FullName
        if ([string]::IsNullOrEmpty($string)) { 
            Throw $errorMessage   
            return 
        }     
    }
} 
Function Enable-SSISQLAdmin  { 
<#
.SYNOPSIS
Enables account GPPH\SSI_SQLAdm on Rec or Prod
 
.Description
Enables account GPPH\SSI_SQLAdm on Rec or Prod
 
.PARAMETER Scope
 
.EXAMPLE
Enable-SSISQLAdmin -Scope REC
#> 

[CmdLetbinding()]
    Param (
        [parameter(Mandatory=$false,position=0)]
        [ValidateSet('Rec', 'Prod', ignorecase=$True)]
        [string] $Scope = 'Rec' 
    ) #end param
    Begin {
            if ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
                $DebugPreference = "Continue" 
            } 
            else { 
                $DebugPreference = "SilentlyContinue" 
            } 
            #$sl = @()
    } 
    Process{ 
        # prepare qry
        $qry  = 'IF EXISTS ( SELECT * FROM sys.server_principals AS sp WHERE sp.name = ''GPPH\SSI-SQLAdm'' AND sp.is_disabled = 1 )
            EXEC sys.sp_ExecuteSQL N''ALTER LOGIN [GPPH\SSI-SQLAdm] ENABLE'' ' 
        Write-Debug $qry 
        
        #get servers
        $servers = Get-VisiServers -Scope $scope -IncludeCentrale 
        if ($scope -eq 'Rec' ) { 
            $servers += 'SRV-DISTRIBR' 
        } 
        else { 
            $servers += 'SRV-DISTRIB' 
        }                 

        # proceed to enable account
        $servers | ForEach-Object { 
             Write-Verbose $_ 
             Invoke-Sqlcmd -ServerInstance $_ -Query $qry 
             Write-Verbose 'ok' 
        } 

    } 
} 


Function Get-VisiCaisses { 
<#
.SYNOPSIS
Returns caisses from Visilab environments
 
.Description
Based on view Arizona.dbo.v_caisses.
*** Note:
System_site and CR_point_of_sale must be correctly populated for this function to return proper results
 
.PARAMETER Scope
 
.EXAMPLE
Get-VisiCaisses DEV1
 
.EXAMPLE
GET-VisiCaisses REC
 
.EXAMPLE
GET-VisiCaisses PROD
  
#> 

[CmdLetbinding()]
    Param (
        [parameter(Mandatory=$false,position=0)]
        [ValidateSet('Dev1','Dev2', 'Rec', 'Prod' , ignorecase=$True)]
        [string] $scope = 'DEV1' 

    ) #end param
    Begin {
            if ( $PSCmdlet.MyInvocation.BoundParameters["Debug"].IsPresent ) { 
                $DebugPreference = "Continue" 
            } 
            else { 
                $DebugPreference = "SilentlyContinue" 
            } 
    } 
    Process{ 
        switch -regex ($scope) { 
            'DEV1' { $srv = 'DEV1OCM';break }
            'DEV2' { $srv = 'DEV2OCM';break }
            'REC'  { $srv = 'SRV-ONECMR';break }
            'PROD' { $srv = 'SRV-ONECM';break }
        } 
        Write-debug "Srv: $srv" 
        $qry = "select * from v_caisses order by CRPOS_NAME ASC "
        Write-Debug $qry 
        Invoke-Sqlcmd -ServerInstance:$srv -database Arizona -Query $qry 
    } # end process
} 


function Write-Box {  
<#
.SYNOPSIS
Visi Write-Box function
.Description
Displays a text in a box
 
.PARAMETER str
 
.EXAMPLE
Write-Box 'myText'
 
#> 
[CmdLetbinding()]
    Param(
        [parameter(Mandatory=$true, position=0)]
        [ValidateNotNullOrEmpty()]
        [string]  $str  
    ) # end param
    Process { 
        $ul = '┌'
        $lr ='┘'
        $vt = '│'
        $ho = '─'
        $ll='└'
        $ur='┐'
        $cr = [char]10+[char]13
        $result  = $($ul + $ho * $($str.Length+2)+ $ur  + $cr + $vt + ' ' + $str + ' ' + $vt +$cr +$ll + $ho * $($str.Length+2) + $lr ) 
        #return $result
        Write-Output $result
    } # end process

} #end function


function Get-Ou { 
<#
.SYNOPSIS
Resolve OU name or number
 
.Description
Matches input string with OU_short_name or OU_code and returns full OU info
 
.PARAMETER str
 
.EXAMPLE
#
Get-OU 512
 
.EXAMPLE
#
Get-OU Rive
 
#> 
[CmdLetbinding()]
    Param(
            [parameter(Mandatory=$false, position=0)]
            [string]  $str  ## string to match
    )
    Begin { 
        If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"] ) { $DebugPreference = "Continue" } 
        else { $DebugPreference = "SilentlyContinue" } 
    } 
    Process { 
        $qry = "SELECT ou.Organizational_unit_id, ou.OU_code, ou.ou_short_name, s.SUB_code, s.SUB_short_name , s.subsidiary_id
        FROM Arizona.dbo.Organizational_unit AS ou
        JOIN Arizona.dbo.Subsidiary AS s ON s.Subsidiary_ID = ou.OU_subsidiary
        where ou_code like '%$str%'
        OR ou_short_name like '%$str%'
        ORDER BY ou_code ASC "
        Write-Debug $qry 
        Invoke-Sqlcmd -ServerInstance SRV-ONECM -Query $qry 
    } #end process
} #end function



Function New-VisiSQLAudit { 
<#
.SYNOPSIS
Deploy audit objets on a table
 
.Description
Deploy audit objets on a table
 
.PARAMETER Table
Table to be audited.
Pipeline parameter.
 
.PARAMETER SQLInstance
SQL Instance where the table to be audited lives
Default $env:COMPUTERNAME
 
.PARAMETER Database
Database where the table to be audited can be found
Default Arizona
 
.PARAMETER AuditDatabase
Target database for Audit table.
This is where the Audit_[table] will be created.
Default VisiAudit
 
.PARAMETER NoClobber
When false, script will drop and recreate an existing audit table
Default TRUE
 
.EXAMPLE
#
New-VisiSQLAudit -SQLInstance SRV-MAG2 -table dbo.Telecom
 
.EXAMPLE
#
@('Country') | New-VisiSQLAudit -SQLInstance SRV-MAG3
 
.EXAMPLE
# default Freq = 13
(Get-Publications -Publisher SRV-ONECMR).name | Update-RepliJobSchedule -Publisher SRV-ONECMR
 
#> 
[CmdLetbinding()]

    Param (
        [parameter(Mandatory=$false,position=0,ValueFromPipeline=$true)]
        [ValidateNotNullOrEmpty() ]
        [string] $Table   
        , 
        [parameter(Mandatory=$false,position=1)]
        [ValidateNotNullOrEmpty() ]
        [string] $SQLInstance = $env:COMPUTERNAME 
        ,
        [parameter(Mandatory=$false,position=2)]
        [ValidateNotNullOrEmpty() ]
        [string] $Database = 'Arizona' 
        ,
        [parameter(Mandatory=$false,position=3)]
        [ValidateNotNullOrEmpty() ]
        [string] $AuditDatabase = 'VisiAudit' 
        ,
        [parameter(Mandatory=$false,position=4)]
        [boolean] $NoClobber = $TRUE 

        ) # end param
    
    Begin  { 
            If ( $PSCmdlet.MyInvocation.BoundParameters["Debug"] ) { 
                $DebugPreference = "Continue" 
            } 
            else { 
                $DebugPreference = "SilentlyContinue" 
            } 
            Write-Verbose "Visi Audit Objects 2.0" 
            $stopwatch =  [system.diagnostics.stopwatch]::StartNew()
    } 
        
    Process { 

        $srv = $sqlInstance 
        $tgt_dbn = $AuditDatabase
        $dbn = $database 

        if ($table -match '\.' ) { 
            $schema = ($table -split '\.')[0]
            $tableShortName = ($tbl -split '\.')[1]
        }
        else { 
            $schema = 'dbo' 
            $tableShortName = $table
        } 
        $tableLongName = $( $schema +'.' + $tableShortName ) 
        $auditTableShortName = $('Audit_' + $tableShortName) 
        $auditTableLongName = $($schema + '.' + $auditTableShortName) 
        $triggerShortName = $('t_IUD_Audit_' + $tableShortName) 
        $triggerLongName = $($schema +'.' + $triggerShortName) 
        
        Write-Debug $("Schema = {0} " -f $schema ) 
        Write-Debug $("Dbn = {0}" -f $dbn ) 
        Write-Debug $("TableShortName = {0} | TableLongName = {1} " -f $tableShortName , $tableLongName )
        Write-Debug $("auditTableShortName = {0} | auditTableLongName = {1} " -f $auditTableShortName , $auditTableLongName )
        Write-Debug $("triggerShortName = {0} | triggerLongName = {1} " -f $triggerShortName , $triggerLongName )

        $SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $srv -ErrorAction Stop 
        #$scriptr = New-Object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
        $enc = New-Object("System.Text.UTF8Encoding")

        $optDrop = New-Object ('Microsoft.SqlServer.Management.Smo.ScriptingOptions') -Property @{ 
                ScriptDrops = $true 
                IncludeIfNotExists = $true 
                SchemaQualify = $true 
                ScriptSchema = $true 
            } 
        $optCreate = New-Object ('Microsoft.SqlServer.Management.Smo.ScriptingOptions') -Property @{ 
                ScriptDrops = $false 
                IncludeHeaders = $false 
                IncludeIfNotExists = $true 
                SchemaQualify = $true 
                ScriptSchema = $true 
                Encoding=$enc 
                NoCollation =$true 
                DRIAll = $false
                ConvertUserDefinedDataTypesToBaseType = $true 
                NoIdentities = $true ## do not script identity properties
            } 
        
        [string]$qry = "--audit SCript generated by new-visisqlaudit " 

        #####################
        ## xFer schemas
        #####################
        Write-Debug "Transfer schemas" 

        $SMOserver.Databases[$dbn].Schemas | ForEach-Object { 
            $qry = $_.Script($optCreate) 
            #Write-Debug $qry
            Invoke-Sqlcmd -ServerInstance $srv -Database $tgt_dbn -Query $qry 
        } 
        Write-Debug "OK " 


        #####################
        ## audit Table
        #####################
        Write-Debug "Create audit table"

        $tblx = $SMOserver.Databases[$dbn].Tables[$tableShortName, $schema]  
        #Write-Debug $tblx.Name
        
        ## drop audit table
        If ( $NoClobber -eq $false) { 
            $qry += $tblx.Script($optDrop) -replace $tableShortName , $('Audit_'+$tableShortName) 
            $qry += "`n--`n" 
        } 

        ## create audit table
        $qry += $tblx.Script($optCreate) -ireplace [regex]::Escape("[$tableShortName]") , "[$auditTableShortName]" 

        #audit table system columns
        $qry += "`nIf COL_LENGTH('$auditTableLongName','Audit_Id') IS NULL ALTER TABLE $auditTableLongName ADD Audit_Id INT IDENTITY PRIMARY KEY ;"
        $qry += "`nIf COL_LENGTH('$auditTableLongName','Server_name') IS NULL ALTER TABLE $auditTableLongName ADD Server_name SYSNAME NOT NULL DEFAULT @@servername ;"
        $qry += "`nIf COL_LENGTH('$auditTableLongName','Host') IS NULL ALTER TABLE $auditTableLongName ADD Host SYSNAME NOT NULL DEFAULT HOST_NAME();"
        $qry += "`nIf COL_LENGTH('$auditTableLongName','Login_name') IS NULL ALTER TABLE $auditTableLongName ADD Login_name SYSNAME NOT NULL DEFAULT SUSER_SNAME();"
        $qry += "`nIf COL_LENGTH('$auditTableLongName','Proc_name') IS NULL ALTER TABLE $auditTableLongName ADD Proc_name NVARCHAR(4000) NULL ;"
        $qry += "`nIf COL_LENGTH('$auditTableLongName','Time_stamp') IS NULL ALTER TABLE $auditTableLongName ADD Time_stamp DATETIME2(7) NOT NULL DEFAULT CURRENT_TIMESTAMP ;"
        $qry += "`nIf COL_LENGTH('$auditTableLongName','SPID') IS NULL ALTER TABLE $auditTableLongName ADD SPID INT NOT NULL DEFAULT @@SPID;"
        $qry += "`nIf COL_LENGTH('$auditTableLongName','Event_type') IS NULL ALTER TABLE $auditTableLongName ADD Event_type CHAR(2) NOT NULL; "
        $qry += "`nIf COL_LENGTH('$auditTableLongName','Program_name') IS NULL ALTER TABLE $auditTableLongName ADD [Program_name] VARCHAR(MAX) NULL;"
        $qry += "`nIf COL_LENGTH('$auditTableLongName','BMC_user_profile_id') IS NULL ALTER TABLE $auditTableLongName ADD [BMC_user_profile_id] VARCHAR(100) NULL;"
        Write-Debug $qry 
        Invoke-Sqlcmd -ServerInstance $srv -Database $tgt_dbn -Query $qry -vb -ea Stop 

        ## audit synonym
        Write-Verbose $("Synonym = {0}" -f $auditTableLongName ) 
        Write-Debug $("`n"+'#'*50 + "`nAudit Synonym `n" +'#'*50) 


        $qry = "
        IF OBJECT_ID('$auditTableLongName', 'SN' ) IS NULL
            EXEC sys.sp_executeSQL N'CREATE SYNONYM $auditTableLongName FOR VisiAudit.$auditTableLongName'" 
        write-debug $qry 
        Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $qry -vb -ea Stop 

        ## audit trigger
        Write-Verbose $("Trigger = {0}" -f $triggerLongName ) 

        ## column list
        Write-Debug $("`n"+'#'*50 + "`nCreate audit trigger`n" +'#'*50) 
        $table_col_list_qry = "
        SELECT STUFF(
            (SELECT ',' + c.name
            FROM sys.[columns] AS c
            WHERE c.[object_id] = OBJECT_ID('$tableShortName','U')
            AND c.system_type_id not in (34,35,99,189)
            FOR XML PATH(''),TYPE ).value('.','NVARCHAR(MAX)') ,1, 1 ,'') AS col_list " 
        Write-Debug $table_col_list_qry 
        $table_col_list = (Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $table_col_list_qry -ErrorAction Stop -Verbose).col_list 
        Write-Debug "Table col list = $table_col_list " 
        

        ## audit trigger
        $trig_qry = "
        IF OBJECT_ID('$triggerLongName', 'TR') IS NULL
            EXEC sys.sp_executeSQL N'CREATE TRIGGER $triggerLongName ON $tableLongName FOR INSERT,DELETE, UPDATE AS RETURN ' ;
        GO
        ALTER TRIGGER $triggerLongName ON $tableLongName FOR INSERT, DELETE, UPDATE AS
        SET NOCOUNT ON
        DECLARE @event_type CHAR;
        IF EXISTS(SELECT * FROM inserted)
            IF EXISTS(SELECT * FROM deleted)
                SELECT @event_type = 'U'
            ELSE
                SELECT @event_type = 'I'
        ELSE
            IF EXISTS(SELECT * FROM deleted)
                SELECT @event_type = 'D'
            ELSE
                --no rows affected - cannot determine event
                SELECT @event_type = '?'
        --
        DECLARE @table_name SYSNAME ;
        SELECT @table_name = OBJECT_NAME(parent_id)
        FROM sys.triggers AS t
        WHERE t.[object_id] = @@procid;
 
        DECLARE @host_name SYSNAME;
        SELECT @host_name = des1.[host_name]
        FROM sys.dm_exec_sessions AS des1
        WHERE des1.session_id = @@spid;
           DECLARE @proc NVARCHAR(4000);
        DECLARE @Buffer table ( EventType nvarchar(30), Parms int, EventInfo nvarchar(4000) ) ;
        INSERT INTO @Buffer ( EventType, Parms, EventInfo )
        EXEC ('DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS ');
        SELECT @proc = EventInfo FROM @Buffer;
        DECLARE @program_name SYSNAME ;
        SELECT @program_name = program_name from sys.dm_exec_sessions where session_id = @@spid ;
        DECLARE @bmc_user_profile_Id INt = NULL;
        " 
        if ($dbn -eq 'Arizona') { 
            $trig_qry += "SELECT @bmc_user_profile_id = aci.APSCI_bmc_user_profile FROM Arizona.dbo.APS_connection_info AS aci WITH (NOLOCK) WHERE aci.APSCI_SQL_spid = @@SPID ; " 
        } 

        $trig_qry += "BEGIN TRY
            INSERT INTO $auditTableLongName (
            Event_type,
            [Host],
            [Proc_name] ,
            [Program_name] ,
            [BMC_User_profile_Id],
            $table_col_list
            )
            SELECT
                CASE @event_type WHEN 'U' THEN 'U1' ELSE @event_type END ,
                @host_name,
                @proc,
                @program_name,
                @bmc_user_profile_id,
                $table_col_list
            FROM DELETED ;
 
            INSERT INTO $auditTableLongName (
                Event_type,
                [Host],
                [proc_name] ,
                [Program_name] ,
                [BMC_User_profile_Id],
                $table_col_list
            )
            SELECT
                CASE @event_type WHEN 'U' THEN 'U2' ELSE @event_type END ,
                @host_name,
                @proc,
                @program_name,
                @bmc_user_profile_id,
                $table_col_list
            FROM INSERTED
            JOIN sys.dm_exec_sessions AS des1 ON @@spid = des1.session_id ;
        END TRY
        BEGIN CATCH
            --replace later when visisys.log is moved out of Arizona
            --EXEC dbo.sp_Save_VisiSys_Log @Domain = '$auditTableLongName', @Module = '$triggerLongName', @Message = 'audit failed', @CloseImmediately = 1 ;
        END CATCH
        GO
         
        EXEC sp_setTriggerOrder @triggerName = '$triggerLongName' , @order = 'First',@stmttype = 'UPDATE';
        EXEC sp_setTriggerOrder @triggerName = '$triggerLongName' , @order = 'First',@stmttype = 'INSERT';
        EXEC sp_setTriggerOrder @triggerName = '$triggerLongName' , @order = 'First',@stmttype = 'DELETE';
        "


        #"Create audit triggers for $table_name"
        Write-Debug $("Creating trigger: [{0}]" -f $triggerLongName ) 
        Write-Debug  $trig_qry  
        
        Invoke-Sqlcmd -ServerInstance $srv -Database $dbn -Query $trig_qry  -ErrorAction Stop 





    }  #end process

    End { 
        $stopwatch.Stop()
        write-verbose $("Elapsed seconds = {0} " -f ($stopwatch.Elapsed).TotalSeconds) 
     }

} #end function

















### ALIASES

Set-Alias -Name xou -Value Get-ou 
Set-Alias -Name box -value Write-Box
#### export members
#### WARNING only members with a DASH will be exported , the others are PRIVATE functions
## export moved to psd1