#cls

# This script creates a hashtable from the COIDs & Facilities List which gives us the COID and the COID's ID value
# which we need to update the Employee Removal List's COID column, which is a lookup column, dynamically. We need
# this value to use to update or add a new item to the List.
# This script also updates the SharePoint Online List Employee Access Removal with values from the HR csv file running as a
# scheduled task each night.

# Load SharePoint CSOM Assemblies
Add-Type -Path "C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions15ISAPIMicrosoft.SharePoint.Client.dll"
Add-Type -Path "C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions15ISAPIMicrosoft.SharePoint.Client.Runtime.dll"

# Variables for Processing
$SiteUrl = "sharepoint.com"
$ListName="Employee Rewards"
$hashTable = @{}
$ListLookup = "COIDs & Facs"

# Location on the local computer to where the file is stored
$ImportFile ="c:tempHR.csv"

# Username and password of an account that can access the Office 365 site

$UserName = "user@sharepoint.com"
$Password = "password"

# Define the array to hold all items that are flagged for change
# this will be used to create a log file when we are done
$curItems = @()

# Get the current month number and the 4 digit year so we can filter the csv file for items in the current month and year
$curMonth = Get-Date -UFormat "%m"
$curYear = Get-Date -UFormat "%Y"

# These variables are used to dynamically get the current month and year so we only process the data for each month
$curDate = "$curMonth/*/"+$curYear

#Setup Credentials to connect
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))


function GetLookupValues
{
    #Set up the context
    $lookupCTX = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
    $lookupCTX.Credentials = $Credentials

    $lookupList = $lookupCTX.Web.Lists.GetByTitle($ListLookup)
    $lookupItems = $lookupList.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
    $lookupCTX.Load($lookupItems)
    $lookupCTX.ExecuteQuery()
    #$items.Count
    foreach($item in $lookupItems)
    {
        $ID = $item["ID"].ToString()
        $Facility = $item["Facility"]
        $COID = $item["C0ID"].ToString()
        $hashTable.Add($ID,$COID)
        #Write-Host "ID: $ID | Facility Name: $Facility | COID: $COID"

    }
    #$hashTable
    AddData($hashTable)
}

function AddData($hashTable)
{
    #Set up the context
    $Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
    $Context.Credentials = $credentials

    # Get the List
    $List = $Context.web.Lists.GetByTitle($ListName)

    # Get the Data from the CSV file and add a header to it
    $data = Import-Csv $ImportFile -Header coid,uid,nameLast,nameFirst,departmentID,departmentDesc,jobTitleID,jobTitle,labels,blank2,email,itemIsARecord,startDate,reasonCode,classificationIDDate,status,nameFull,address1,address2,city,state,zip,description

    # Loop through each row in the csv file
    foreach($row in $data) {

    # Create a variable for the start date
    $startDate = $row.startDate

    # TODO: Add a try/catch block to handle exceptions

    # Filter for the current month and year
    if($startDate -like $curDate)
    {
        # Take care of formatting issues of the names look proper
        $fullName = $row.nameFirst.substring(0,1).toUpper()+$row.nameFirst.substring(1).toLower() + " " + $row.nameLast.substring(0,1).toUpper()+$row.nameLast.substring(1).toLower()

        # Add items to the List
        $ListItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
        $Item = $List.AddItem($ListItemInfo)
        $Item["COID"] = $row.COID
        # In order to set the value of the COID we have to use the ID we have in our
        # $hashTable variable, this maps to the actual COID we want to update
        # this is bc we are using a lookup column in our list
        $match = $hashTable.GetEnumerator()| Where-Object {$_.value -eq $row.coid}
        if($match)
        {
            # Output the employees name, the current COID, the current value in the hashtable, (these two should match)
            # the hashtable Key has the SharePoint List ID of the current COID, which we will use to create a string
            # dynamicaly and to make sure the values are what we are expecting.
            $fullName
            $row.coid
            $match.Value
            $mKey = $match.Key
            $mKey
            # Create the value to update the COID column in the format "ID;#Value"
            $coidValue = $mKey.ToString() + ";#" + $row.coid.ToString()
            #$coidValue
            $Item["C0ID"] = $row.coid
            $Item["Facility"] = $coidValue.ToString()
            # If the column name in the list has a space then this special char set is added to the column name: _x0020_
            $Item["Full_x0020_Name"] = $fullName
            $Item["Department"] = $row.departmentID
            $Item["Department_x0020_Description"] = $row.departmentDesc
            $Item["Body"] = $row.description
            $Item["Email"] = $row.email
            $Item["Job_x0020_Title"] = $row.jobTitle
            $Item["First_x0020_Name"] = $row.nameFirst.substring(0,1).toUpper()+$row.nameFirst.substring(1).toLower()
            $Item["Last_x0020_Name"] = $row.nameLast.substring(0,1).toUpper()+$row.nameLast.substring(1).toLower()
            $Item["Reason_x0020_Code"] = $row.reasonCode
            $Item["StartDate"] = $row.startDate
            $Item["Status"] = $row.status
            $Item["UID"] = $row.uid
            $Item.Update()
            $Context.ExecuteQuery()

            # Add items to the array of what is going to be updated
            $curItems += @($row)
        }
        else
        {
            # Log the error
        }
    }

}
# Find out how many records were updated
$importedItems = $curItems.Count
Write-host "$importedItems items to be imported into the Employee Removal SharePoint List. Log file is in the C:Temp directory"
# Create a log file with the users identified to be updated
$today = Get-Date -UFormat "%Y.%m.%d"
$logfilePath = "C:temp"
$logFile = $logfilePath+$today + "-HR-Updates.csv"
$curItems | Export-Csv -Path $logFile -NoTypeInformation -Force
}
# Call the function

GetLookupValues