In the SqlFunctions module, we have two functions. Get-DailyProductsSalesQuery will return the contents of the SQL file. Note that within the function, we get a reference to the module. We then use the FileList property to get the full path of the SQL file:

function Get-DailyProductSalesQuery{
 $module = Get-Module SalesDashboardModule;

 $filePath = $module.FileList | Where { $_ -like '*DailyProductSalesQuery*'}
 
 $sql = [System.IO.File]::ReadAllText($filePath).Trim();

 Write-Debug "Sql: $($sql)";
 return $sql;
}

Before returning the SQL script, we use Write-Debug to show the message on the console. The output of this command will be visible only when running in debug mode.

The remaining function, Get-DailyProductSalesTotals, will retrieve the results of the query as rows. Using CmdletBinding, we enable a few additional features for the function (functions with this attribute are called advanced functions). First, we can make use of the -Verbose and -Debug parameters to configure the visibility of the Write-Verbose and Write-Debug statements within it:

function Get-DailyProductSalesTotals {
 [CmdletBinding()]
 param (
        [Parameter(
                  Mandatory = $false,
                  HelpMessage = 'Enter a date to start from e.g.:(Get-Date("MM/DD/YYYY"))')]
        [Alias('StartDate','Date')]
        [DateTime]$dateFilter = ([System.Data.SqlTypes.SqlDateTime]::MinValue.Value)
 )
 
 Write-Debug "$($dateFilter)";

 $sql = Get-DailyProductSalesQuery;
 $sqlParams = "FILTERDATE=$($dateFilter.ToString('yyyy-MM-dd'))"
 
 Write-Debug "$($sqlParams)";

 return Invoke-Sqlcmd -Query $sql -ServerInstance "Sql17tests" -Variable $sqlParams;
}

Parameters can also be defined with several attributes to help the user. The StartDate parameter is defined as an optional Date input. The parameter can be set as StartDate (or Date through the use of the Alias attribute) and has a default value. This parameter is used to filter all dates before its value from the results. When this parameter is not set, its default value is used, effectively including all rows (the SqlDateTime.MinValue value usually defaults to 1/1/1753).

In the following script, we call the function with the -Debug parameter and without a StartDate parameter:

PS C:> $rows = Get-DailyProductSalesTotals -Debug
DEBUG: 01/01/1753 00:00:00

DEBUG: Sql: USE [AdventureWorksLT2012]
 DECLARE @startDate as date;

 SET @startDate = cast('$(FILTERDATE)' as date);

 SELECT p.[Name] ProductName, sh.OrderDate ,SUM(OrderQty) Quantity 
 FROM SalesLT.SalesOrderDetail sd
  JOIN SalesLT.SalesOrderHeader sh ON sd.SalesOrderID = sh.SalesOrderID
  JOIN SalesLT.Product p ON sd.ProductID = p.ProductID
 WHERE sh.OrderDate >= @startDate
 GROUP BY p.[Name], sh.OrderDate
 ORDER BY p.[Name]

DEBUG: FILTERDATE=1753-01-01

PS C:> $rows.Count
142

PS C:> $rows
ProductName           OrderDate               Quantity
-----------           ---------               --------
AWC Logo Cap          6/1/2004 12:00:00 AM    52
Bike Wash - Dissolver 6/1/2004 12:00:00 AM    55
Chain                 6/1/2004 12:00:00 AM    8
...

Invoke-Sqlcmd will call the database with the SQL query and return the matching records. The Variable parameter takes an array of variables that will be replaced in the SQL query. The passed array accepts only those string values that will be replaced in the SQL query. Note that in the SQL query, we have the interpolation of the FILTERDATE variable. To minimize an opportunistic SQL injection attack, we set the value of the FILTERDATE variable into a SQL variable @startDate of type date. In addition, the input parameter in the PowerShell function is also Date, which minimizes the risk even further (in the case of a string filter, you could replace Invoke-Sqlcmd with the .NET SqlCommand class that allows the use of SQL parameters, adding an additional layer of parameter validation and supporting additional data types).

To access SharePoint Online, we will need to store user credentials (application access tokens are a better approach but are beyond the scope of this book). The Get-SalesDashboardCreds function will prompt for credentials and save them as a serialized string on the filesystem. By default, PSCredentials stores the password as SecureString. The credentials are encrypted and can only be used by the account that created it (within the machine where it was created):

# get credentials from a file or prompt the user
function Get-SalesDashboardCreds(){
 $credPaths = 'C:	emppass1.txt'
 if (Test-Path -Path $credPaths){ #file exists
 Write-Host "Loading credentials from file: $credPaths" -BackgroundColor Yellow -ForegroundColor Black
 
 $rawCreds = Get-Content $credPaths
 
 # load from file
 $creds = [System.Management.Automation.PSSerializer]::Deserialize($rawCreds )
 }else{
  $creds = Get-Credential # prompt user for credentials
  [System.Management.Automation.PSSerializer]::Serialize($creds) | 
   Out-File $credPaths # Save to file
 }
 return $creds;
}

The Get-SalesDashboardContext function simply uses the credentials to create ClientContext. The context will be used to read and write to the SharePoint list:

function Get-SalesDashboardContext ($siteUrl){
 $creds = Get-SalesDashboardCreds;
 Write-Debug "$($siteUrl) $($user)"
 
 $clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl);

 $credentials = New-Object    Microsoft.SharePoint.Client.SharePointOnlineCredentials($creds.UserName, $creds.Password);
 $clientContext.Credentials = $credentials;
 return $clientContext;
}