#######################################################
#    GLOBAL Script
#    ***   ReCertification   ***
#
# CMD :
# C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
# https://gallery.technet.microsoft.com/scriptcenter/Outlook-Automation-using-d7584688
# -File P:\Test\Powershell\Script\Recertification\ManagingEmails
###################################


Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Start scanning Emails on Inbox 'ISEC-ISS'..." -ForegroundColor Cyan

# Warning Email
$Outlook = New-Object -ComObject Outlook.Application
$Mail = $Outlook.CreateItem(0)
$Mail.To = "ISEC-ISS-Controls@ca-cib.com" #"ISEC-ISS-Controls@ca-cib.com"
$Mail.Subject = "Recertification: Processing Emails started..."
$Mail.Body = "~~~~~~~ \\\\ START //// ~~~~~~~"
$Mail.Send()



# Date
$date = get-date -format d
$date = $date.ToString().Replace(“/”, “-”)
$time = get-date -format t
# $time = $time.ToString().Replace(":", "-")
$time = $time.ToString().Replace(" ", "")
Write-Host "Date:" $date $time -ForegroundColor Cyan


# Input by User 1 :
Write-Host "How old are emails (received time)? #0=Today; 1=From yesterday; ..." -BackgroundColor DarkMagenta
$NumberOfDays = Read-Host "Days"

# Input by User 2 :
Write-Host "What is the subject of emails are we looking for" -BackgroundColor DarkMagenta

Write-Host "RE: [POUR ACTION]" -ForegroundColor Cyan
Write-Host "RE: [RAPPEL] [POUR ACTION]" -ForegroundColor Cyan

Write-Host "RE: [ACTION REQUIRED]" -ForegroundColor Cyan
Write-Host "RE: [REMINDER] [ACTION REQUIRED]" -ForegroundColor Cyan  

Write-Host "RE: [LAST REMINDER] [ACTION REQUIRED]" -ForegroundColor Cyan
Write-Host "RE: [DERNIER RAPPEL] [POUR ACTION]" -ForegroundColor Cyan

$Subject1 = Read-Host "Subject 1?"
$Subject2 = Read-Host "Subject 2?"
$Subject3 = Read-Host "Subject 3?"
$Subject4 = Read-Host "Subject 4?"
# # # # #



# Parameter Emails "received time:"
$Date = [DateTime]::Now.AddDays(-$NumberOfDays)
$ReceivedDays =  $Date.tostring("MM/dd/yyyy")

# Parameter Subject:
# $subjectComparisonExpression = "Message de sécurité pour les utilisateurs du Système d’Information de CACIB / Security message for all CACIB Information System users"




# Connecting to Outlook SharedMailBox
$ol = New-Object -ComObject Outlook.Application
$ns = $ol.GetNamespace('mapi')
$mb = $ns.Stores['baptiste.george-prestataire@ca-cib.com'].GetRootFolder()
$inbox = $mb.Folders['Inbox']
$recipient =  $ns.CreateRecipient('ISEC-ISS-Controls@ca-cib.com')

if($recipient.Resolve()){
   # Write-Host 'OK'
   $shared = $ns.GetSharedDefaultFolder($recipient, [Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)
}else{
    Write-Host 'Unable to resolve. Can not connect to sharedMailBox ISEC-ISS!'
    }





Write-Host "Query is running" -ForegroundColor Cyan




################################### Filter Emails #######################################
# $messages = $inbox.items
# $messages = $shared.items | Where-Object -FilterScript {$_.senton -Ge "$ReceivedDays" -and $_.Subject -eq $Subject1}
$messages = $shared.items | Where-Object -FilterScript {
       $_.senton -Ge "$ReceivedDays" -and ( ($_.Subject.SubString(0,[math]::min($Subject1.length,$_.Subject.length) ) -eq $Subject1) -or
                                             ($_.Subject.SubString(0,[math]::min($Subject2.length,$_.Subject.length) ) -eq $Subject2) -or
                                              ($_.Subject.SubString(0,[math]::min($Subject3.length,$_.Subject.length) ) -eq $Subject3) -or
                                               ($_.Subject.SubString(0,[math]::min($Subject4.length,$_.Subject.length) ) -eq $Subject4)
)}


# Query by User result :
# Count Emails into Inbox :
write-host "Query Result:" $messages.count "Emails were found in Inbox 'ISEC-ISS'" -ForegroundColor Green
$messcount = $messages.count



# Init:  
$countprocessed = 0
$countprocessed_PJ = 0  

# for ($c = $mailboxFolders.Items.count; $c -ge 1;$c--) {
################################### Starting #######################################
foreach($message in $messages){
# $From = $message.SenderEmailAddress
$msubject = $message.subject
Write-Host "Email found:" -BackgroundColor Blue
Write-Host $msubject -BackgroundColor Blue
$mBody = $message.body
# Write-Host $mBody




################################### Save Attchement #######################################
 
$filepath = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Fichiers Retour\"

$Check_PJ = 0
$message.attachments | foreach {
   
   $attr = $_.filename
   # add-content $log1 "Attachment: $attr"
   $a = $_.filename
   If ($a.Contains(".xlsx")) {
   Write-Host $_.filename " --> The file has been saved on folder!"-BackgroundColor Green
   $_.saveasfile((Join-Path $filepath $a))
   $Check_PJ ++
   $countprocessed_PJ ++

                            }
                             
                           
  }

# In case you want to resend the file, into a new mail   
$attachment = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Fichiers Retour\" + $a


### C'était ici

  # Moving Emails to Questions as emails doesn't contain file.xlxs
  If ($Check_PJ -eq 0) {Write-Host "Warning: No file found" -BackgroundColor Yellow
 
   $MoveTarget = $shared.Folders.item("23 - Recertification").Folders.item("Temp_Question")
   [void]$message.Move($MoveTarget)
   }
   
       else {



################################### Moving ALL EMAILS to Temp Folder #######################################
    
   $MoveTarget1 = $shared.Folders.item("23 - Recertification").Folders.item("Processed")
   [void]$message.Move($MoveTarget1)
   }
 



$countprocessed++



} # End



#Synthèse
    Write-Host "Result :" -ForegroundColor Cyan
If ( $countprocessed - $countprocessed_PJ -ge 1) {
  $count_Empty_emails = $countprocessed - $countprocessed_PJ
  Write-Host "-->" $count_Empty_emails "Email(s) do not contain any file!" -ForegroundColor Yellow
}
 
Write-Host "-->" $countprocessed_PJ "Files have been successfully saved on folder!" -ForegroundColor Green

write-host "-->" $messages.count "Emails have been moved to specific folders!" -ForegroundColor Green



Write-Host "End processing Emails from Inbox" -ForegroundColor Cyan
Write-Host "\\\ --- ///" -ForegroundColor Cyan










# Playing wav sound
$musicPath = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Sounds\Powershell Notification.wav"
$Song = New-Object System.Media.SoundPlayer
$Song.SoundLocation = $musicPath
$Song.Play()



####################################################### PROMPT : DO YOU WANT TO PERFORM NEXT STEP ?
$Title = "Recertification Process"
$Info = "Do you want to perform next step?"
 
$options = [System.Management.Automation.Host.ChoiceDescription[]] @("&Yes", "&No")
[int]$defaultchoice = 0
$opt = $host.UI.PromptForChoice($Title , $Info , $Options,$defaultchoice)
switch($opt)
{
# Option Yes
0 {
Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Start scanning emails from 'Temp' Folder..." -ForegroundColor Cyan

$ScriptRoute = "P:\Test\Powershell\Script\Recertification\"
$ScriptName = "ScanningEmailsTempFolder.ps1"

# The script to call
$CallingScript = "$ScriptRoute"+"$ScriptName"
Write-Host $CallingScript

# Execute script at location:
&"$CallingScript"
}

# option No
1 { Write-Host "Ending process" -ForegroundColor Green
   Break
   }
}
####################################################### END PROMPT



 

#######################################################
#    Script ReCertification
#    ***   Save Attachements   ***
#
# CMD :
# C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
# https://gallery.technet.microsoft.com/scriptcenter/Outlook-Automation-using-d7584688
# -File P:\Test\Powershell\Script\Recertification\ScanningEmailsTempFolder
###################################





# Connecting to Outlook SharedMailBox
$ol = New-Object -ComObject Outlook.Application
$ns = $ol.GetNamespace('mapi')
$mb = $ns.Stores['baptiste.george-prestataire@ca-cib.com'].GetRootFolder()
$inbox = $mb.Folders['Inbox']
$recipient =  $ns.CreateRecipient('ISEC-ISS-Controls@ca-cib.com')

if($recipient.Resolve()){
   # Write-Host 'OK'
   $shared = $ns.GetSharedDefaultFolder($recipient, [Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)
}else{
    Write-Host 'Unable to resolve. Can not connect to sharedMailBox ISEC-ISS!'
    }



# Write-Host "Query is running" -ForegroundColor Cyan




################################### Filter Emails #######################################
# $messages = $inbox.items
# $messages = $shared.items | Where-Object -FilterScript {$_.senton -Ge "$ReceivedDays" -and $_.Subject -eq $Subject1}
$messages = $shared.Folders.item("23 - Recertification").Folders.item("Temp").items




# Count Emails into Inbox :
$messcount = $messages.count
write-host "Result :" -ForegroundColor Cyan
write-host "-->" $messcount "Emails were found in 'Temp' Folder" -ForegroundColor Green


# Init:  
$countprocessed = 0
$countprocessed_PJ = 0  





################################### Starting #######################################


foreach($message in $messages){

# $From = $message.SenderEmailAddress
$msubject = $message.subject
Write-Host "Email found:" -BackgroundColor Blue
Write-Host $msubject -BackgroundColor Blue
$mBody = $message.body
# Write-Host $mBody




################################### Save Attchement #######################################
 
$filepath = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Fichiers Retour\"

$Check_PJ = 0
$message.attachments | foreach {
   
   $attr = $_.filename
   # add-content $log1 "Attachment: $attr"
   $a = $_.filename
   If ($a.Contains(".xlsx")) {
   Write-Host $_.filename " --> The file has been saved on folder!"-BackgroundColor Green
   $_.saveasfile((Join-Path $filepath $a))
   $Check_PJ ++
   $countprocessed_PJ ++

                            }
                             
                           
  }









$countprocessed++


 
} # End



#Synthèse
    Write-Host "Result" -ForegroundColor Cyan
If ( $countprocessed - $countprocessed_PJ -ge 1) {
  $count_Empty_emails = $countprocessed - $countprocessed_PJ
  Write-Host $count_Empty_emails "Email(s) do not contain any file!" -BackgroundColor Yellow
}
 
Write-Host "-->" $countprocessed_PJ "Files have been successfully saved on folder!" -ForegroundColor Green









# Il faut faire un backward Loop pour ne pas itérer sur la collection pour réussir à .Move les emails dans un autre folder.
# Alors que quand on fait .Move dans Foreach $message depuis le Inbox... le problème ne se pose pas! : (
# En tout cas ca marche.

# ==> En fait il faut fair un backward quand on travaille à partir d'un folder... Mais Il faut le faire en loop croissant quand on travaille à partir de l'inbox!


$messages = $shared.Folders.item("23 - Recertification").Folders.item("Temp").items

# Count Emails into Inbox :
$messcount = $messages.count

    $moveTarget = $shared.Folders.item("23 - Recertification").Folders.item("Processed")
     
for ($c = $messcount; $c -ge 1;$c--) {
   [void]$messages[$c].Move($moveTarget) # L'Index
}

Write-Host "-->" $messcount "Emails moved to 'Processed' Folder" -ForegroundColor Green



   Write-Host "End processing Emails from 'Temp' Folder" -ForegroundColor Cyan
   Write-Host "\\\ --- ///" -ForegroundColor Cyan





Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Start scanning files saved on Folder..." -ForegroundColor Cyan



$Pathfiles = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Fichiers Retour\"
$PJSaved = (Get-ChildItem -File $Pathfiles | Measure-Object).Count

   Write-Host "Result : " -ForegroundColor Cyan
   Write-Host $PJSaved "Files found on folder" -ForegroundColor Green
   Write-Host "\\\ --- ///" -ForegroundColor Cyan


# Playing wav sound
$musicPath = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Sounds\Powershell Notification.wav"
$Song = New-Object System.Media.SoundPlayer
$Song.SoundLocation = $musicPath
$Song.Play()



####################################################### PROMPT : DO YOU WANT TO PERFORM NEXT STEP ?
$Title = "Recertification Process"
$Info = "Do you want to perform next step?"
 
$options = [System.Management.Automation.Host.ChoiceDescription[]] @("&Yes", "&No")
[int]$defaultchoice = 0
$opt = $host.UI.PromptForChoice($Title , $Info , $Options,$defaultchoice)
switch($opt)
{
# Option Yes
0 {
   Write-Host "/// --- \\\" -ForegroundColor Cyan
   Write-Host "Step 2 is running..." -ForegroundColor Green
   Write-Host "Let's check files name..." -ForegroundColor Green

$ScriptRoute = "P:\Test\Powershell\Script\Recertification\"
$ScriptName = "RenameFilesWithDataFromSheet.ps1"

# The script to call
$CallingScript = "$ScriptRoute"+"$ScriptName"
Write-Host $CallingScript

# Execute script at location:
&"$CallingScript"
}

# option No
1 { Write-Host "Ending process : Shutdown by User!" -ForegroundColor Green
   Break
   }
}
####################################################### END PROMPT

#######################################################
#    Script ReCertification
#      Stepx RenameFiesWithDataFromSheet
#        Renommer un fichier avec le "prénom.nom - Rows.xlsx" _> "baptiste.george - 4.xlsx"
#
#
# CMD :
# C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
# -File P:\Test\Powershell\Script\Recertification\RenameFilesWithDataFromSheet.ps1
#######################################################
 
 
$Pathfiles = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Fichiers Retour\"

$folderDestination = "$Pathfiles" + "_Rejected\"

$Excel = New-Object -ComObject Excel.Application

Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Start processing files..." -ForegroundColor Cyan


$Files = Get-ChildItem $Pathfiles"*.xlsx" | Select -Expand FullName

$counter = 1
ForEach($File in $Files){
Write-Progress -Activity "Checking: $file" -Status "File $counter of $($files.count)" -PercentComplete ($counter*100/$files.count)

$Workbook = $Excel.Workbooks.Open($File)


   $WorkSheets = $WorkBook.WorkSheets | where {$_.name -eq "ISS"}
   # On traite les fichiers seulement si les fichiers contiennent la feuille ISS
    if($WorkSheets) {



   $WorkSheet = $WorkBook.Sheets.Item("ISS")
   $Rows = ($WorkSheet.UsedRange.Rows).count-1

       $val1 = $Workbook.Sheets.Item("ISS").Cells.item(2,7).Value2
       # Write-Host $val1

       $Position = $val1.indexof("@")
       # Write-host $Position

       $len = $val1.Length
       # Write-host $len

       if ($val1 -eq "-") { Write-Host "Error : " -BackgroundColor Magenta } else {
       $val = $val1.substring(0,$val1.length -($len-$Position)) }
       # Write-host $val


       $Workbook.close($false)

       $NewNameFile = "$val" + " - " + "$Rows"

       $Path_NewNameFile = "$Pathfiles"+"$NewNameFile"+".xlsx"

       # Write-host $File




      if ($val1 -eq "-") { Write-Host "Manager is missing : "$File -BackgroundColor Magenta } Else {


  if ($File -eq "$Path_NewNameFile"){Write-Host $NewNameFile'.xlsx --> No need to rename this file' -BackgroundColor Blue }
   Else{

              # Vérification Si le fichier n'existe pas déjà
               If (Test-Path $Path_NewNameFile){
                   Write-Host "Error : " -BackgroundColor Yellow
                   Write-Host $File -BackgroundColor Yellow
                   Write-Host $NewNameFile'.xlsx already exists' -BackgroundColor Yellow
                                   }

                   Else{ # Sinon on peut renommer le file

       Rename-Item -Path $File -NewName $NewNameFile'.xlsx'
       Write-Host "Processing File : " -BackgroundColor Green
       Write-Host $File -BackgroundColor Green
       Write-Host $NewNameFile'.xlsx --> This file has been renamed successfully' -BackgroundColor Green
                       }

     }

    }
   


# End ISS Worksheet
} Else { Write-Host "Warning :" -BackgroundColor Red
        Write-Host $File' --> This file has been rejected!'  -BackgroundColor Red
               # Déplacer les fichiers :
                 $Workbook.close($false)
                 Move-Item -Path $File -Destination $folderDestination -Force}

 

     $counter++
   }

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


   # Playing wav sound
$musicPath = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Sounds\Powershell Notification.wav"
$Song = New-Object System.Media.SoundPlayer
$Song.SoundLocation = $musicPath
$Song.Play()

   Write-Host "End process" -ForegroundColor Cyan
   Write-Host "\\\ --- ///" -ForegroundColor Cyan





####################################################### PROMPT : DO YOU WANT TO PERFORM NEXT STEP ?
$Title = "Recertification Process"
$Info = "Do you want to perform next step?"
 
$options = [System.Management.Automation.Host.ChoiceDescription[]] @("&Yes", "&No")
[int]$defaultchoice = 0
$opt = $host.UI.PromptForChoice($Title , $Info , $Options,$defaultchoice)
switch($opt)
{
# Option Yes
0 {
   Write-Host "/// --- \\\" -ForegroundColor Cyan
   Write-Host "Step 3 is running :" -ForegroundColor Green
   Write-Host "Let's check if files are completed..." -ForegroundColor Green

$ScriptRoute = "P:\Test\Powershell\Script\Recertification\"
$ScriptName = "Step5_Recertif_Files_ToBeRemoved.ps1"

# The script to call
$CallingScript = "$ScriptRoute"+"$ScriptName"
Write-Host $CallingScript

# Execute script at location:
&"$CallingScript"
}

# option No
1 { Write-Host "Ending process" -ForegroundColor Green
   Break
   }
}
####################################################### END PROMPT




 

#######################################################
#    Script ReCertification
#      Step5_Recertif_Files_Incompleted
#        Recertification Déplacer des fichiers incomplets:
#
#
# CMD :
# C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
# -File P:\Test\Powershell\Script\Recertification\Step5_Recertif_Files_ToBeRemoved.ps1
#######################################################

Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Start processing files..." -ForegroundColor Cyan

# Déplace les fichiers .xlsx qui ont un text = "removed" dans la colonne E de l'onglet 2
 
$Pathfiles = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Fichiers Retour\"
$Destination = "$Pathfiles" + "_Not Completed\"
$SearchText = '' # 'removed'

$Excel = New-Object -ComObject Excel.Application

$Files = Get-ChildItem $Pathfiles"*.xlsx" | Select -Expand FullName
$counter = 1

$counterFileMoved = 0
ForEach($File in $Files){
   Write-Progress -Activity "Checking: $file" -Status "File $counter of $($files.count)" -PercentComplete ($counter*100/$files.count)
   $Workbook = $Excel.Workbooks.Open($File)

   $WorkSheet = $WorkBook.Sheets.Item("ISS")
   $Rows = ($WorkSheet.UsedRange.Rows).count
   $Range = "E1:E" + "$Rows"
   # Write-Host $Rows

   # If($Workbook.Sheets.Item(1).Range("E:E").Find($SearchText)){
   If($Workbook.Sheets.Item("ISS").Range($Range).Find($SearchText)){

   $counterFileMoved++
       # Pas de text trouvé, on ferme le fichier
       $Workbook.Close($false)
       # Si le texte recherché est trouvé alors on déplace les fichiers dans un nouveau dossier : )
       # Moved $file to $destination"
       Move-Item -Path $File -Destination $Destination -Force
       Write-Host $counterFileMoved "Files has been moved to 'Not_Completed' Folder" -BackgroundColor Yellow
       # break
       
   }

   else {
   $workbook.close($false)
   }

   $counter++
}


# Synthèse du traitement

If( $counterFileMoved -eq 0 ){  Write-Host "All files are completed !!!" -BackgroundColor Green} else

   {  
   $Resultat = $counter-$counterFileMoved-1
   Write-Host "$Resultat" "files are completed !!!" -BackgroundColor Green
   }

   Write-Host "End process" -ForegroundColor Cyan
   Write-Host "\\\ --- ///" -ForegroundColor Cyan





#######################################################
#    ***   On .txt -> List all the files into folder   ***
# -File P:\Test\Powershell\Script\Recertification\GLOBAL_PROCESS_Recertification.ps1
#######################################################

Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Start creating txt file..." -ForegroundColor Cyan

$Folder = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Fichiers Retour\" #__TEST\"
$FolderResultat = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Fichiers Retour\"
$OutFile = "__ListeFichiersEnRetour.txt"
$FolderOutFile = "$FolderResultat" + "$OutFile"

           # Avant : On vérifie si le fichier existe, si oui... on supprime.
               If (Test-Path $FolderOutFile){
                   Remove-Item $FolderOutFile
                   }
# Create txt file                 
Get-ChildItem -Path $Folder*.xlsx | %{ADD-content -path $FolderOutFile -value $_.FullName}


# Playing wav sound
$musicPath = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Sounds\Powershell Notification.wav"
$Song = New-Object System.Media.SoundPlayer
$Song.SoundLocation = $musicPath
$Song.Play()

Write-Host "End process" -ForegroundColor Cyan
Write-Host "\\\ --- ///" -ForegroundColor Cyan






####################################################### PROMPT : DO YOU WANT TO PERFORM NEXT STEP ?
$Title = "Recertification Process"
$Info = "Do you want to perform next step?"
 
$options = [System.Management.Automation.Host.ChoiceDescription[]] @("&Yes", "&No")
[int]$defaultchoice = 0
$opt = $host.UI.PromptForChoice($Title , $Info , $Options,$defaultchoice)
switch($opt)
{
# Option Yes
0 {
   Write-Host "/// --- \\\" -ForegroundColor Cyan
   Write-Host "Step 4 is running..." -ForegroundColor Green
   Write-Host "Let's merge files..." -ForegroundColor Green

$ScriptRoute = "P:\Test\Powershell\Script\Recertification\"
$ScriptName = "MergeCompletedFiles.ps1"

# The script to call
$CallingScript = "$ScriptRoute"+"$ScriptName"
Write-Host $CallingScript

# Execute script at location:
&"$CallingScript"
}

# option No
1 { Write-Host "Ending process" -ForegroundColor Green
   Break
   }
}
####################################################### END PROMPT

#######################################################
#    Script - ReCertification - Merging Files
#    ***   Merging Files   ***
#
# CMD :
# C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
# -File P:\Test\Powershell\Script\Recertification\MergeCompletedFiles.ps1
#######################################################

Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Starting Merging Files" -ForegroundColor Cyan
# Write-Host "Minimize Windows and fill the prompt" -ForegroundColor Yellow

# Step 01 : Call Excel File
$excel= new-object -comobject excel.application
$excel.Visible = $false
$excel.DisplayAlerts = $false

$classeur=$excel.workbooks.open("\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\_Lot_Concat\_Fusion_Fichier_Excel.xlsm")

# Test pouir lancer une macro
$excel.Run('Fusion_classeur_Excel')


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


# Playing wav sound
$musicPath = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Sounds\Powershell Notification.wav"
$Song = New-Object System.Media.SoundPlayer
$Song.SoundLocation = $musicPath
$Song.Play()

Write-Host "All files have been merged successfully" -ForegroundColor Green
Write-Host "End process" -ForegroundColor Cyan
Write-Host "\\\ --- ///" -ForegroundColor Cyan






####################################################### PROMPT : DO YOU WANT TO PERFORM NEXT STEP ?
$Title = "Recertification Process : List Email Address"
$Info = "Do you want to perform next step?"
 
$options = [System.Management.Automation.Host.ChoiceDescription[]] @("&Yes", "&No")
[int]$defaultchoice = 0
$opt = $host.UI.PromptForChoice($Title , $Info , $Options,$defaultchoice)
switch($opt)
{
# Option Yes
0 {
   Write-Host "/// --- \\\" -ForegroundColor Cyan
   Write-Host "Step 5 is running..." -ForegroundColor Green
   Write-Host "Let's list Email Address..." -ForegroundColor Green

$ScriptRoute = "P:\Test\Powershell\Script\Recertification\"
$ScriptName = "ListEmailAddress.ps1"

# The script to call
$CallingScript = "$ScriptRoute"+"$ScriptName"
Write-Host $CallingScript

# Execute script at location:
&"$CallingScript"
}

# option No
1 { Write-Host "Ending process" -ForegroundColor Green
   Break
   }
}
####################################################### END PROMPT

#######################################################
#    Script ReCertification
#    ***   Step7_Recertif List Email Address   ***
#
# CMD :
# C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
# -File P:\Test\Powershell\Script\Recertification\ListEmailAddress.ps1
#######################################################

# Write-Host "/// --- \\\" -ForegroundColor Cyan
# Write-Host "Start listing Email address..." -ForegroundColor Cyan


$ol = New-Object -ComObject Outlook.Application
$ns = $ol.GetNamespace('mapi')
$mb = $ns.Stores['baptiste.george-prestataire@ca-cib.com'].GetRootFolder()
$inbox = $mb.Folders['Inbox']
$recipient =  $ns.CreateRecipient('ISEC-ISS-Controls@ca-cib.com')

if($recipient.Resolve()){
   # Write-Host 'OK'
   $shared = $ns.GetSharedDefaultFolder($recipient, [Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)
}else{
   Write-Host 'Unable to resolve. Can not connect to sharedMailBox ISEC-ISS!'
}

Write-Host "Start creating txt file..." -ForegroundColor Cyan

# Input by User 1 :
# Write-Host "What is the Subfolder?" -BackgroundColor DarkMagenta
# $Subfolder = Read-Host "Name"

# Input by User 2 :
# Write-Host "What is the Lot?" -BackgroundColor DarkMagenta
# $Lot = Read-Host "Number"


# Go to Subfolder :
$targetfolder1 = $shared.Folders | where-object { $_.name -eq "23 - Recertification" }
$targetfolder2 = $targetfolder1.Folders | where-object { $_.name -eq "Processed" }

# $shared = $ns.GetSharedDefaultFolder($recipient, [Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)

# Txt File
$FolderOutFile ="\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Emails Retour\_EmailAddress.txt"

# Delete file :
If (Test-Path $FolderOutFile){Remove-Item $FolderOutFile}

#checks 15 newest messages
# $shared.items | select -first 15 | foreach {  # Inbox of SharedMailBox
$targetfolder2.items | select -first 1000 | foreach {


$Position = $_.SenderEmailAddress.indexof("@") #If = -1 --> Introuvable
# Write-Host = $Position
$date = Get-Date -Format "yyyy-MM-dd"
# Write-Host = $date

If ($Position -eq -1) {$Email_SenderName = $_.Sender.GetExchangeUser().PrimarySmtpAddress} else {$Email_SenderName = $_.SenderEmailAddress}

   #"$($_.Sender.GetExchangeUser().PrimarySmtpAddress);1;$Lot;$date;$($_.Subject)" | out-file $FolderOutFile -Append else #
   
   "$($Email_SenderName);$($_.Subject)" | out-file $FolderOutFile -Append
                                                 

}

Write-Host "End process" -ForegroundColor Cyan
Write-Host "\\\ --- ///" -ForegroundColor Cyan



# Happy End
# Playing wav sound
$musicPath = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Sounds\Powershell Notification.wav"
$Song = New-Object System.Media.SoundPlayer
$Song.SoundLocation = $musicPath
$Song.Play()



####################################################### PROMPT : DO YOU WANT TO PERFORM NEXT STEP ?
$Title = "Recertification Process"
$Info = "Do you want to perform next step?"
 
$options = [System.Management.Automation.Host.ChoiceDescription[]] @("&Yes", "&No")
[int]$defaultchoice = 0
$opt = $host.UI.PromptForChoice($Title , $Info , $Options,$defaultchoice)
switch($opt)
{
# Option Yes
0 {
Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Start scanning emails from 'Processed' Folder..." -ForegroundColor Cyan

$ScriptRoute = "P:\Test\Powershell\Script\Recertification\"
$ScriptName = "ListSenderEmails.ps1"

# The script to call
$CallingScript = "$ScriptRoute"+"$ScriptName"
Write-Host $CallingScript

# Execute script at location:
&"$CallingScript"
}

# option No
1 { Write-Host "Ending process" -ForegroundColor Green
   Break
   }
}
####################################################### END PROMPT


 

#######################################################
#    Script ReCertification
#    ***   Scanning Emails to extract data   ***
#     Saving Emails as .msg -> But can't open it! ; (
#
# CMD :
# C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
# https://gallery.technet.microsoft.com/scriptcenter/Outlook-Automation-using-d7584688
# -File P:\Test\Powershell\Script\Recertification\ListSenderEmails.ps1
###################################



Write-Host "/// --- \\\" -ForegroundColor Cyan
write-host "Scanning Emails to extract data :" -ForegroundColor Cyan

# Input by User 1 :
# Write-Host "Lot1 (traité)" -ForegroundColor Cyan
# Write-Host "What folder on Outlook?" -BackgroundColor DarkMagenta
# $OutlookFolder = Read-Host "Folder"
$OutlookFolder = "Processed"


# Folder to save .msg
# $FolderDest = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\_Lot1\Test\"

# Txt File
$FolderOutFile ="\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Emails Retour\_InfoFromEmails_"+"$OutlookFolder"+".txt"

# Delete file :
If (Test-Path $FolderOutFile){Remove-Item $FolderOutFile}




# Connecting to Outlook SharedMailBox
$ol = New-Object -ComObject Outlook.Application
$ns = $ol.GetNamespace('mapi')
$mb = $ns.Stores['baptiste.george-prestataire@ca-cib.com'].GetRootFolder()
$inbox = $mb.Folders['Inbox']
$recipient =  $ns.CreateRecipient('ISEC-ISS-Controls@ca-cib.com')

if($recipient.Resolve()){
   # Write-Host 'OK'
   $shared = $ns.GetSharedDefaultFolder($recipient, [Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)
}else{
    Write-Host 'Unable to resolve. Can not connect to sharedMailBox ISEC-ISS!'
    }


################################### Filter Emails #######################################
# $messages = $inbox.items
# $messages = $shared.items | Where-Object -FilterScript {$_.senton -Ge "$ReceivedDays" -and $_.Subject -eq $Subject1}
$messages = $shared.Folders.item("23 - Recertification").Folders.item($OutlookFolder).items # | select  -First 5

# Count Emails into Inbox :
$messcount = $messages.count
write-host "Result :" -ForegroundColor Cyan
write-host "-->" $messcount "Emails were found in 'Processed' Folder" -ForegroundColor Green

# txt FILE :
# Create Header ;
$("Sender;Nbre;Lot;Sent On;Subject;File1.xlsx;File2.xlsx;Msg1.xlsx;Msg2.xlsx") | out-file $FolderOutFile -Append



################################### Extraction Emails (sender, subject, body) #######################################

foreach($message in $messages){

# $From = $message.SenderEmailAddress
$msubject = $message.subject
Write-Host "Email found : Extracting data!" -BackgroundColor DarkGreen
Write-Host "Subject : " $msubject -BackgroundColor Green
$mBody = $message.body
# Write-Host $mBody
$mBody = $message.body
$mSentOn = $message.senton.ToString().Replace(“/”, “-”)  
# Write-Host $mSentOn


################################### Get Description of Attchements xlsx & msg #######################################
 
# init
$Check_PJ = 0
$FileName1 =""
$FileName2 =""

$Check_Msg = 0
$FileNameMsg1 =""
$FileNameMsg2 =""

$message.attachments | foreach {
   
   $attr = $_.filename
   # add-content $log1 "Attachment: $attr"
   $a = $_.filename
    
       If ($a.Contains(".xlsx")) {
   # Write-Host $_.filename -BackgroundColor Green
   $Check_PJ ++
   If ( $Check_PJ -eq 1 ) { $FileName1 = $_.filename  
   Write-Host " - " $FileName1 -BackgroundColor Blue}
   If ( $Check_PJ -eq 2 ) { $FileName2 = $_.filename  
   Write-Host " - " $FileName2 -BackgroundColor Blue}
               }

       If ($a.Contains(".msg")) {
   # Write-Host $_.filename -BackgroundColor Green
   $Check_Msg ++
   If ( $Check_msg -eq 1 ) { $FileNameMsg1 = $_.filename  
   Write-Host " - " $FileNameMsg1 -BackgroundColor Cyan}
   If ( $Check_msg -eq 2 ) { $FileNameMsg2 = $_.filename  
   Write-Host " - " $FileNameMsg2 -BackgroundColor Cyan}
                }

                            
                                                       
       }



$Position = $message.SenderEmailAddress.indexof("@") #If = -1 --> Introuvable
# Write-Host = $Position

If ($Position -eq -1) {$Email_SenderName = $message.Sender.GetExchangeUser().PrimarySmtpAddress} else {$Email_SenderName = $message.SenderEmailAddress}

# Write into txt File :
"$($Email_SenderName);1;Lot9;$($mSentOn);$($msubject);$FileName1;$FileName2;$FileNameMsg1;$FileNameMsg2" | out-file $FolderOutFile -Append





################################### Save Emails as .msg #######################################
# Test :
# $PathFileName = "$FolderDest" + "$Email_SenderName" +".msg"
# $message.SaveAs($PathFileName, $olSaveType::olMSG)

# La savaugarde fonctionne... mais impossible d'ouvrir le fichier .msg dans le folder : (
 
}





# Waring Email
$Outlook = New-Object -ComObject Outlook.Application
$Mail = $Outlook.CreateItem(0)
$Mail.To = "ISEC-ISS-Controls@ca-cib.com" #"ISEC-ISS-Controls@ca-cib.com"
$Mail.Subject = "Recertification: Processing Emails has run successfully..."
$Mail.Body = "~~~~~~~ //// END \\\\ ~~~~~~~"
$Mail.Send()



# Playing wav sound
$musicPath = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\01 - Préparation\Baptiste\Sounds\Powershell Notification.wav"
$Song = New-Object System.Media.SoundPlayer
$Song.SoundLocation = $musicPath
$Song.Play()


Write-Host "End process" -ForegroundColor Cyan
Write-Host "\\\ --- ///" -ForegroundColor Cyan

# END
Write-Host ""
Write-Host "Thank you for watching... All jobs have been performed successfully" -ForegroundColor Green

#######################################################
#    Script ReCertification
#    ***   Recertif Copy Files to Folder   ***
#
# CMD :
# C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
# -File P:\Test\Powershell\Script\Recertification\CopyFilesToFolder.ps1
#######################################################

##### FR CAMPAIGN :

# Param
$PathFiles = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\05 - Mails envoyés\01_FR\"
$FolderDest = $PathFiles+"_01 - EY\"


Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Step 1 is running :" -ForegroundColor Green
$NbrEmails = (Get-ChildItem -File $Pathfiles | Measure-Object).Count
Write-Host "Start scanning" $NbrEmails "Emails from FR campaign" -ForegroundColor Green

# Param List to check :
$Current = Get-Content '\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\05 - Mails envoyés\FR_ListEmail.txt'

# Processing Files :
$Count = 0
foreach ($currentVal in $Current){

$File = $currentVal

$CopyFile = $PathFiles+$File
$PasteFile = $FolderDest+$File

If (Test-Path $CopyFile){
Copy-Item -Path $CopyFile -Destination $PasteFile -Force
Write-Host $File "has been copied successfully!" -BackgroundColor Blue
$Count++
}

}

Write-Host "Result : " -ForegroundColor Green
Write-Host $Count "Emails has been copied succesfully" -ForegroundColor Green
Write-Host "End process" -ForegroundColor Cyan
Write-Host "\\\ --- ///" -ForegroundColor Cyan




##### EN CAMPAIGN :

# Param
$PathFiles = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\05 - Mails envoyés\02_EN\"
$FolderDest = $PathFiles+"_01 - EY\"


Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Step 2 is running :" -ForegroundColor Green
$NbrEmails = (Get-ChildItem -File $Pathfiles | Measure-Object).Count
Write-Host "Start scanning" $NbrEmails "Emails from EN campaign" -ForegroundColor Green

# Param List to check :
$Current = Get-Content '\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\05 - Mails envoyés\EN_ListEmail.txt'

# Processing Files :
$Count = 0
foreach ($currentVal in $Current){

$File = $currentVal

$CopyFile = $PathFiles+$File
$PasteFile = $FolderDest+$File

If (Test-Path $CopyFile){
Copy-Item -Path $CopyFile -Destination $PasteFile -Force
Write-Host $File "has been copied successfully!" -BackgroundColor Blue
$Count++
}

}

Write-Host "Result : " -ForegroundColor Green
Write-Host $Count "Emails has been copied succesfully" -ForegroundColor Green
Write-Host "End process" -ForegroundColor Cyan
Write-Host "\\\ --- ///" -ForegroundColor Cyan






##### ENTITE CAMPAIGN :

# Param
$PathFiles = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\05 - Mails envoyés\03_Entité\"
$FolderDest = $PathFiles+"_01 - EY\"


Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Step 3 is running :" -ForegroundColor Green
$NbrEmails = (Get-ChildItem -File $Pathfiles | Measure-Object).Count
Write-Host "Start scanning" $NbrEmails "Emails from Entité campaign" -ForegroundColor Green

# Param List to check :
$Current = Get-Content '\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\05 - Mails envoyés\Entité_ListEmail.txt'

# Processing Files :
$Count = 0
foreach ($currentVal in $Current){

$File = $currentVal

$CopyFile = $PathFiles+$File
$PasteFile = $FolderDest+$File

If (Test-Path $CopyFile){
Copy-Item -Path $CopyFile -Destination $PasteFile -Force
Write-Host $File "has been copied successfully!" -BackgroundColor Blue
$Count++
}

}

Write-Host "Result : " -ForegroundColor Green
Write-Host $Count "Emails has been copied succesfully" -ForegroundColor Green
Write-Host "End process" -ForegroundColor Cyan
Write-Host "\\\ --- ///" -ForegroundColor Cyan






##### EN CAMPAIGN :

# Param
$PathFiles = "\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\05 - Mails envoyés\02_EN\"
$FolderDest = $PathFiles+"_02 - UAR\"


Write-Host "/// --- \\\" -ForegroundColor Cyan
Write-Host "Step 2 is running :" -ForegroundColor Green
$NbrEmails = (Get-ChildItem -File $Pathfiles | Measure-Object).Count
Write-Host "Start scanning" $NbrEmails "Emails from EN campaign" -ForegroundColor Green

# Param List to check :
$Current = Get-Content '\\smb15-nas1.par.emea.cib\ISS_DATA\05 - Controls\I3C\2020\Task #6 - Recertification campaign\05 - Mails envoyés\EN_ListEmail_UAR.txt'

# Processing Files :
$Count = 0
foreach ($currentVal in $Current){

$File = $currentVal

$CopyFile = $PathFiles+$File
$PasteFile = $FolderDest+$File

If (Test-Path $CopyFile){
Copy-Item -Path $CopyFile -Destination $PasteFile -Force
Write-Host $File "has been copied successfully!" -BackgroundColor Blue
$Count++
}

}

Write-Host "Result : " -ForegroundColor Green
Write-Host $Count "Emails has been copied succesfully" -ForegroundColor Green
Write-Host "End process" -ForegroundColor Cyan
Write-Host "\\\ --- ///" -ForegroundColor Cyan