Vision Global DataDumpScript: Difference between revisions
(Created page with "'''Distributor Script'''<br/> <# 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" $Arc...") |
No edit summary |
||
| (4 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
'''Distributor Script'''<br/> | '''Distributor Script''' | ||
<# | |||
<br /><# | |||
Author: Scott Graves | |||
#> | |||
Date: 4-21-2016 | |||
Usage: Processes complete replacement of Distributor and DistributorDetail Tables | |||
<nowiki>#</nowiki>> | |||
$StartTime = get-date -Format "yyyy-mm-dd HH:mm:ss" | $StartTime = get-date -Format "yyyy-mm-dd HH:mm:ss" | ||
| Line 11: | Line 16: | ||
$Server = ".\Prodigix" | $Server = ".\Prodigix" | ||
$DataBase = "ClientDb" | $DataBase = "ClientDb" | ||
$Username = "oauser" | $Username = "oauser" | ||
$Password = "admin" | $Password = "admin" | ||
$FtpFile = "c:\files\import\commission\distributors\Distributors.csv" | $FtpFile = "c:\files\import\commission\distributors\Distributors.csv" | ||
$ImportFile = "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") | $ArchiveFile = "c:\files\import\commission\distributors\archive\Distributors_{0}.csv" -f (get-date -Format "yyyy-MM-dd HH-mm-ss") | ||
If (Test-Path $FtpFile) | 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 | Else | ||
{ | { | ||
Write-Output "File Does Not Exist" | |||
} | } | ||
| Line 48: | Line 65: | ||
Write-Output $EndTime | Write-Output $EndTime | ||
<br/><br/><br/> | <nowiki><br/></nowiki><nowiki><br/></nowiki><nowiki><br/></nowiki> | ||
'''Orders Script''' | |||
<# | <# | ||
Author: Scott Graves | |||
#> | Date: 4-21-2016 | ||
Usage: Processes import into Orders, OrderLines and payment Tables | |||
<nowiki>#</nowiki>> | |||
$StartTime = get-date -Format "yyyy-mm-dd HH:mm:ss" | $StartTime = get-date -Format "yyyy-mm-dd HH:mm:ss" | ||
| Line 62: | Line 90: | ||
$Server = ".\Prodigix" | $Server = ".\Prodigix" | ||
$DataBase = "ClientDb" | $DataBase = "ClientDb" | ||
$Username = "oauser" | $Username = "oauser" | ||
$Password = "admin" | $Password = "admin" | ||
$FtpFile = "c:\files\import\commission\orders\Orders.csv" | $FtpFile = "c:\files\import\commission\orders\Orders.csv" | ||
$ImportFile = "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") | $ArchiveFile = "c:\files\import\commission\orders\archive\Orders_{0}.csv" -f (get-date -Format "yyyy-MM-dd HH-mm-ss") | ||
If (Test-Path $FtpFile) | 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 | Else | ||
{ | { | ||
Write-Output "File Does Not Exist" | |||
} | } | ||
Latest revision as of 21:26, 24 March 2023
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