Vision Global DataDumpScript

Distributor Script

<#

  Author: Scott Graves

  Date: 4-21-2016

  Usage: Processes complete replacement of Distributor and DistributorDetail Tables

#>

$StartTime = get-date -Format "yyyy-mm-dd HH:mm:ss"

Write-Output $StartTime

$Server = ".\Prodigix"

$DataBase = "ClientDb"

$Username = "oauser"

$Password = "admin"

$FtpFile = "c:\files\import\commission\distributors\Distributors.csv"

$ImportFile = "c:\files\import\commission\distributors\Distributors.csv"

$ArchiveFile = "c:\files\import\commission\distributors\archive\Distributors_{0}.csv" -f (get-date -Format "yyyy-MM-dd HH-mm-ss")

If (Test-Path $FtpFile)

{

    Invoke-Sqlcmd2 –ServerInstance $Server -Database $DataBase -Query "INSERT INTO DistributorImportHistory (ImportStart) VALUES (GETDATE())" -Username $Username -Password $Password

    <#Move-Item $FtpFile $ImportFile#>

    Invoke-Sqlcmd2 –ServerInstance $Server -Database $DataBase -Query "TRUNCATE TABLE DistributorImportRecords" -Username $Username -Password $Password

    $DataImport = Import-Csv -Delimiter (",") -Path $ImportFile

    $DataTable = $DataImport | Out-DataTable

    Write-DataTable –ServerInstance $Server -Database $DataBase –TableName DistributorImportRecords -Data $DataTable -Username $Username -Password $Password

    Move-Item $ImportFile $ArchiveFile

   

    Invoke-Sqlcmd2 –ServerInstance $Server -Database $DataBase -Query "exec Distributor_Import_Process" -Username $Username -Password $Password -QueryTimeout 9600 -ConnectionTimeout 9600

}

Else

{

    Write-Output "File Does Not Exist"

}

$EndTime = get-date -Format "yyyy-mm-dd HH:mm:ss"

Write-Output $EndTime

<br/><br/><br/>

Orders Script

<#

  Author: Scott Graves

  Date: 4-21-2016

  Usage: Processes import into Orders, OrderLines and payment Tables

#>

$StartTime = get-date -Format "yyyy-mm-dd HH:mm:ss"

Write-Output $StartTime

$Server = ".\Prodigix"

$DataBase = "ClientDb"

$Username = "oauser"

$Password = "admin"

$FtpFile = "c:\files\import\commission\orders\Orders.csv"

$ImportFile = "c:\files\import\commission\orders\Orders.csv"

$ArchiveFile = "c:\files\import\commission\orders\archive\Orders_{0}.csv" -f (get-date -Format "yyyy-MM-dd HH-mm-ss")

If (Test-Path $FtpFile)

{

    Invoke-Sqlcmd2 –ServerInstance $Server -Database $DataBase -Query "INSERT INTO OrderImportHistory (ImportStart) VALUES (GETDATE())" -Username $Username -Password $Password

    <#Move-Item $FtpFile $ImportFile#>

    Invoke-Sqlcmd2 –ServerInstance $Server -Database $DataBase -Query "TRUNCATE TABLE OrderImportRecords" -Username $Username -Password $Password

    $DataImport = Import-Csv -Delimiter (",") -Path $ImportFile

    $DataTable = $DataImport | Out-DataTable

    Write-DataTable –ServerInstance $Server -Database $DataBase –TableName OrderImportRecords -Data $DataTable -Username $Username -Password $Password

    Move-Item $ImportFile $ArchiveFile

   

    Invoke-Sqlcmd2 –ServerInstance $Server -Database $DataBase -Query "exec Order_Import_Process" -Username $Username -Password $Password -QueryTimeout 9600 -ConnectionTimeout 9600

}

Else

{

    Write-Output "File Does Not Exist"

}

$EndTime = get-date -Format "yyyy-mm-dd HH:mm:ss"

Write-Output $EndTime