#######################################################
#    Script PowerShell - Remote Control 3AM ASIA EMEA UK   
#
# CMD :
# C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
# -File P:\Test\Powershell\Script\Fusion_DailyUsage_SourceAM_csv.ps1

# https://blog.netwrix.com/2018/05/17/powershell-file-management/

#######################################################

#Répertoire partagé : \\smb15-nas1.par.emea.cib\ISS_DATA\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Connections out of biz hours\SourceFileAM\

#Répertoire partagé
#$Chemin = "Z:\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Connections out of biz hours\SourceFileAM\"
#$Chemin_NameFile = "Z:\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Connections out of biz hours\SourceFileAM\MERGED_Remote_Access_ReportAM.csv"


#Répertoire partagé - PULSE
$Chemin = "\\smb15-nas1.par.emea.cib\ISS_DATA\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Connections out of biz hours\SourceFileAM\"
$Chemin_NameFile = "\\smb15-nas1.par.emea.cib\ISS_DATA\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Connections out of biz hours\SourceFileAM\MERGED_Remote_Access_ReportAM.csv"
$folder_Bad = "\\smb15-nas1.par.emea.cib\ISS_DATA\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Connections out of biz hours\SourceFileAM\Bad csv files\"


# Step 01 : Saving Attachements from Excel on Outlook + Email sent to alert for starting process
$excel= new-object -comobject excel.application
$excel.Visible = $false
$classeur=$excel.workbooks.open("\\smb15-nas1.par.emea.cib\ISS_DATA\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Connections out of biz hours\Settings\SendingEmail_AM_ASIA_EMEA_UK.xlsm")

# Exécution de la macro
$excel.Run('Starting_Process')

# get-process *excel* | stop-process -force

# Counting Files on Folder :
$CountFiles = (Get-ChildItem -Path $Chemin -File).Count
Write-Host $CountFiles "Files on 'SourceFileAM' Folder" -ForegroundColor Green

# Step - 1 : On déplace les fichiers Movis dans le dossier "Bad csv Files"
# On supprime les fichiers du jour s'il existe.
$today_ = (Get-Date -Format yyyy-MM-dd)
Get-ChildItem $folder_Bad*$today_.csv -file -r | remove-item

# On déplace
Get-ChildItem -Path $Chemin -Filter "*Movis*" | Move-Item -Destination $folder_Bad


# Counting Files on Folder :
$CountFiles = (Get-ChildItem -Path $Chemin -File).Count
Write-Host $CountFiles "Files on 'SourceFileAM' Folder. Some Movis Files have been moved" -ForegroundColor Green


# Step 0 : On vérifie si le fichier remote en date du jour existe, si oui... on supprime.
$today = (Get-Date -Format yyyyMMdd)
$FileName = "\\smb15-nas1.par.emea.cib\ISS_DATA\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Connections out of biz hours\SourceFileAM\" + "$today" + "_Remote_Access_ReportAM.csv"
If (Test-Path $FileName){
    Remove-Item $FileName
 }

# Step 1 : Filtrant les fichiers moins d'une x heure(s)... créant un fichier MERGED_Remote_Access_ReportAM.csv
Get-Childitem -Path $Chemin*.csv | where { !($_.CreationTime -lt (get-Date).AddHours(-10))} | ForEach-Object {
     Import-Csv $_.FullName
# } |Select-Object ApplicationCode,SessionConnected,SessionLogoff,UserName,Domain,Profile |Export-Csv -Path "Z:\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Connections out of biz hours\SourceFileAM\MERGED_Remote_Access_ReportAM.csv" -NoTypeInformation
} |Select-Object appCode,applicationCode,sessionConnectedTimeInGMT,sessionLogoffTimeInGMT,userName,Domain,profile |Export-Csv -Path $Chemin_NameFile -NoTypeInformation


# Step 2 : On préfixe avec la date YYYYMMDD en remplacant "MERGED" par la date du jour -> Soit YYYYMMDD_Remote_Access_ReportAM.csv
Get-ChildItem -path $Chemin -recurse -include @("*.csv") | rename-item -newname { $_.name  -replace "MERGED",$(get-date -Format yyyyMMdd)}


# Step 3
# # # # # # Lancer le fichier
$excel= new-object -comobject excel.application
$excel.Visible = $true
# $Chemin_ControlFile = "Z:\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Connections out of biz hours\SourceFileAM\YYYYMMDD - AUTO - MOVIS PULSE EMEA connections out of biz hours with Mail.xlsm"
# $classeur=$excel.workbooks.open("P:\Test\Conception\FileNameTemplate.xlsx")
$classeur=$excel.workbooks.open("\\smb15-nas1.par.emea.cib\ISS_DATA\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Connections out of biz hours\YYYYMMDD - AUTO - MOVIS PULSE EMEA connections out of biz hours with Mail.xlsm")
# $classeur=$excel.workbooks.open($Chemin_ControlFile)


get-process *excel* | stop-process -force



# Step 4 : On renomme le fichier de controle du jour pour les applications APPcode traités... ici ASIA EMEA UK
$Chemin1 = "\\smb15-nas1.par.emea.cib\ISS_DATA\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Connections out of biz hours\"
$FileTtemp = " - AUTO - MOVIS PULSE EMEA connections out of biz hours with Mail.xlsm"
$today = (Get-Date -Format yyyyMMdd)
$folderFileTemp = $Chemin1 + "$today" + $FileTtemp
$OutputFile = "$today" + " - PULSE ASIA EMEA UK connections out of biz hours No Mail.xlsm"
# $Path_OutputFile = "$Chemin1" + "$today" + " - PULSE ASIA EMEA UK connections out of biz hours No Mail.xlsm"
$Path_OutputFile = "$Chemin1" + "$OutputFile"

            # Avant : On vérifie si le fichier existe, si oui... on supprime.
                If (Test-Path $Path_OutputFile){
                    Remove-Item $Path_OutputFile
                    }

# Rename-Item -Path $folderFileTemp -NewName "new_20200918 - PULSE ASIA EMEA UK connections out of biz hours with Mail.txt"
Rename-Item -Path $folderFileTemp -NewName $OutputFile




# Step 5 : Déplacer les fichiers csv du jour dans Archivage
$Chemin = "\\smb15-nas1.par.emea.cib\ISS_DATA\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Connections out of biz hours\SourceFileAM\"
$folder = "\\smb15-nas1.par.emea.cib\ISS_DATA\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Connections out of biz hours\SourceFileAM\Archivage\ASIA EMEA UK\"

# On supprime les fichiers du jour s'il existe.
$today_ = (Get-Date -Format yyyy-MM-dd)
Get-ChildItem $folder*$today_.csv -file -r | remove-item

$folderFile = "$folder" + "$today" + "_Remote_Access_ReportAM.csv"

            # Avant : On vérifie si le fichier fusinné existe, si oui... on supprime.
                If (Test-Path $folderFile){
                    Remove-Item $folderFile
                    }


# Déplace les fichiers :
Move-Item -Path $Chemin"*.csv" -Destination $folder

 

 

 

 

#######################################################
#    Script PowerShell - Remote Control 3AM AMERICA   
#
# CMD :
# C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
# -File P:\Test\Powershell\Script\Fusion_DailyUsage_SourceAM_AMERICA_csv.ps1
#
# https://blog.netwrix.com/2018/05/17/powershell-file-management/
#
####################################################### -- ###### Provided by Baptiste GEORGE ######



#Parameters
$Chemin = "\\smb15-nas1.par.emea.cib\ISS_DATA\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Connections out of biz hours\SourceFileAM\"
$Chemin_NameFile = "\\smb15-nas1.par.emea.cib\ISS_DATA\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Connections out of biz hours\SourceFileAM\MERGED_Remote_Access_ReportAM.csv"



# # # # # # Step 1 : Saving Attachements from Excel on Outlook + Email sent to alert for starting process

$excel= new-object -comobject excel.application
$excel.Visible = $false
$excel.DisplayAlerts = $false

$classeur=$excel.workbooks.open("\\smb15-nas1.par.emea.cib\ISS_DATA\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Connections out of biz hours\Settings\SendingEmail_AM_AMERICA.xlsm")

# Exécution de la macro
$excel.Run('Starting_Process')

$excel.Workbooks.Close()
$excel.Quit()
get-process *excel* | stop-process -force  # Kill Excel Session



# # # # # # Step 2 : On vérifie si le fichier remote en date du jour existe, si oui... on supprime

$today = (Get-Date -Format yyyyMMdd)
$FileName = "\\smb15-nas1.par.emea.cib\ISS_DATA\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Connections out of biz hours\SourceFileAM\" + "$today" + "_Remote_Access_ReportAM.csv"
If (Test-Path $FileName){
    Remove-Item $FileName
 }

    # Step 2.1 : Filtrant les fichiers moins d'une x heure(s)... créant un fichier MERGED_Remote_Access_ReportAM.csv
Get-Childitem -Path $Chemin*.csv | where { !($_.CreationTime -lt (get-Date).AddHours(-10))} | ForEach-Object {
     Import-Csv $_.FullName
    # } |Select-Object ApplicationCode,SessionConnected,SessionLogoff,UserName,Domain,Profile |Export-Csv -Path "Z:\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Connections out of biz hours\SourceFileAM\MERGED_Remote_Access_ReportAM.csv" -NoTypeInformation
} |Select-Object appCode,applicationCode,sessionConnectedTimeInGMT,sessionLogoffTimeInGMT,userName,Domain,profile |Export-Csv -Path $Chemin_NameFile -NoTypeInformation


    # Step 2.2 : On préfixe avec la date YYYYMMDD en remplacant "MERGED" par la date du jour -> Soit YYYYMMDD_Remote_Access_ReportAM.csv
Get-ChildItem -path $Chemin -recurse -include @("*.csv") | rename-item -newname { $_.name  -replace "MERGED",$(get-date -Format yyyyMMdd)}




# # # # # # Step 3 : Lancer le fichier de Contrôle

$excel= new-object -comobject excel.application
$excel.Visible = $true
$excel.DisplayAlerts = $false

$classeur=$excel.workbooks.open("\\smb15-nas1.par.emea.cib\ISS_DATA\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Connections out of biz hours\YYYYMMDD - AUTO - MOVIS PULSE EMEA connections out of biz hours with Mail.xlsm")

$excel.Workbooks.Close()
$excel.Quit()
get-process *excel* | stop-process -force  # Kill Excel Session




# # # # # # Step 4 : On renomme le fichier de controle du jour pour les application Appcode traités... ici AMERICA

$Chemin1 = "\\smb15-nas1.par.emea.cib\ISS_DATA\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Connections out of biz hours\"
$FileTtemp = " - AUTO - MOVIS PULSE EMEA connections out of biz hours with Mail.xlsm"
$today = (Get-Date -Format yyyyMMdd)
$folderFileTemp = $Chemin1 + "$today" + $FileTtemp
$OutputFile = "$today" + " - PULSE AMERICA connections out of biz hours No Mail.xlsm"
# $Path_OutputFile = "$Chemin1" + "$today" + " - PULSE AMERICA connections out of biz hours No Mail.xlsm"
$Path_OutputFile = "$Chemin1" + "$OutputFile"



            # Step 4.1 : Avant : On vérifie si le fichier existe, si oui... on supprime.
                If (Test-Path $Path_OutputFile){
                    Remove-Item $Path_OutputFile
                    }

            # Step 4.2 : Rename-Item -Path $folderFileTemp -NewName "new_20200918 - PULSE AMERICA connections out of biz hours with Mail.txt"
            Rename-Item -Path $folderFileTemp -NewName $OutputFile




# # # # # # Step 5 : Déplacer les fichiers csv du jour dans Archivage

$Chemin = "\\smb15-nas1.par.emea.cib\ISS_DATA\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Connections out of biz hours\SourceFileAM\"
$folder = "\\smb15-nas1.par.emea.cib\ISS_DATA\08 - I3C\91 - Controles Pulse\MOVIS PULSE EMEA Connections out of biz hours\SourceFileAM\Archivage\AMERICA\"

        # On supprime les fichiers du jour s'il existe.
        $today_ = (Get-Date -Format yyyy-MM-dd)
        Get-ChildItem $folder*$today_.csv -file -r | remove-item

        $folderFile = "$folder" + "$today" + "_Remote_Access_ReportAM.csv"

        # Step 5.1 : Avant : On vérifie si le fichier fusinné existe, si oui... on supprime.
                If (Test-Path $folderFile){
                    Remove-Item $folderFile
                    }

        # Step 5.2 : Déplace les fichiers :
        Move-Item -Path $Chemin"*.csv" -Destination $folder