/
Get-ArticleSql.ps1
165 lines (138 loc) · 5.75 KB
/
Get-ArticleSql.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
function Get-ArticleSql {
[CmdletBinding()]
param(
[parameter(Mandatory = $false)]
[ValidateScript({ Test-ValidateSelect -strParam $_ })]
[string]$select = (Get-DefaultSelectArticle)
,
[parameter(Mandatory = $false)]
[string[]]$filter
,
[Parameter(Mandatory = $false)]
[ValidateScript({ Test-ValidateStringCase $_ })]
[string]$strCase = 'none'
,
[parameter(Mandatory = $false)]
[ValidateScript({ Test-ValidateMapping -strValue $_ -mapping (Get-MappingArticleKeys) })]
[string]$alias = 'articleNo'
,
[parameter(Mandatory = $false)]
[string]$order= $alias
,
[parameter(Mandatory = $false)]
[switch]$noIdAlias
,
[parameter(Mandatory = $false)]
[ValidateRange(0, [int]::MaxValue)]
[Nullable[int]]$limit
,
[parameter(Mandatory = $false)]
[switch]$reorder
,
[parameter(Mandatory = $false)]
[switch]$revers
)
begin {
Write-Verbose -Message ((Get-ResStr 'STARTING_FUNCTION') -f $myInvocation.Mycommand)
$alias = Test-ValidateMapping -strValue $alias -mapping (Get-MappingArticleKeys)
$order = Test-ValidateMapping -strValue $order -mapping (Get-MappingArticleKeys)
New-Variable -Name 'arrOrder' -Scope 'Private' -Value ([string[]]@())
New-Variable -Name 'arrSelect' -Scope 'Private' -Value ([System.Object[]]@())
New-Variable -Name 'fieldList' -Scope 'Private' -Value ([string]'')
New-Variable -Name 'i' -Scope 'Private' -Value ([int32]0)
New-Variable -Name 'sqlFilter' -Scope 'Private' -Value ([string]'')
New-Variable -Name 'sqlLimit' -Scope 'Private' -Value ([string]'')
New-Variable -Name 'sqlMaster' -Scope 'Private' -Value ([string]'')
New-Variable -Name 'sqlOrder' -Scope 'Private' -Value ([string]'')
New-Variable -Name 'sqlRevers' -Scope 'Private' -Value ([string]'')
New-Variable -Name 'sqlSelect' -Scope 'Private' -Value ([string]'')
$initialVariables = Get-CurrentVariables -Debug:$DebugPreference
}
process {
if ($filter) {
[string]$sqlFilter= "WHERE ( RTrim(LTrim(IsNull($alias,''))) <> '') AND $($filter -join(' AND ')) "
} else {
[string]$sqlFilter= "WHERE ( RTrim(LTrim(IsNull($alias,''))) <> '')"
}
if ($order) {
[string]$sqlOrder = "ORDER BY dummy.$order"
} else {
[string]$sqlOrder = ''
}
$sqlSelect = $select
if ($sqlSelect -ne '*') {
if (! ($SqlSelect.Contains($alias, [System.StringComparison]::InvariantCultureIgnoreCase))) {
$sqlSelect = "$alias,$sqlSelect"
}
if ($order) {
[string[]]$arrOrder = $order -split ',' | ForEach-Object { $_.Trim() }
[string[]]$arrSelect = $select -split ',' | ForEach-Object { $_.Trim() }
$arrOrderNoDup = $arrOrder | Where-Object { !($arrSelect -contains $_) }
$arrSelectNoDup = $arrSelect | Where-Object { !($arrOrder -contains $_) }
$fieldList = $arrOrderNoDup -join ','
if ($fieldList) {
$sqlSelect = "$fieldList, $($arrSelectNoDup -join ',')"
}
}
}
if (! ($null -eq $limit)) {
$sqlLimit = "TOP $limit"
} else {
$SqlLimit = ''
}
if ($revers) {
$sqlRevers = 'DESC'
} else {
$sqlRevers = ''
}
$arrSelect = $sqlSelect -split ','
$arrSelect = $arrSelect | ForEach-Object { $_.Trim() }
if ($reorder) {
$arrSelect = $arrSelect | Sort-Object
}
$arrSelect = $arrSelect | ForEach-Object { Convert-StringCase -value $_ -strCase $strcase }
$arrSelect = $arrSelect | Get-Unique
$sqlSelect = $arrSelect -join ','
if (! ($noIdAlias)) {
$sqlSelect = "$alias [ID.ALIAS], $sqlSelect"
}
[string]$sqlMaster = @"
SELECT $sqlSelect FROM (
SELECT
$sqlLimit
/* KEYS */
Id, ArtNummer, IsNull(Barcode,'') [Barcode], [Uid],
/* IDENTIFIER */
IsNull(ArtMatch,'') [ArtMatch], IsNull(ArtNummerErsatz,'') [ArtNummerErsatz],
IsNull(ArtNummerHersteller,'') [ArtNummerHersteller],
/* GROUPS */
IsNull(RabattGr,'') [RabattGr], IsNull(WarenGr,'') [WarenGr],
IsNull(ErloesGr,'') [ErloesGr], VerpackEh, PreisEh, MengenEh,
/* CURRENCY */
Waehrung, MwStGr, MwStSatz, EkNetto, IsNull(Ek2Netto,0.0)[Ek2Netto], BruttoFlg,
Vk, VkNetto, VkBrutto,
/* SHOP */
IsNull(ShopExportDatum,'1900-01-01') [ShopExportDatum], ShopFreigabeFlg,
/* FLAGS */
AuslaufFlg, NeuFlg, SonderFlg, LoeschFlg,
/* INTRASTAT */
IsNull(Upper(UrsprungsLand),'')[UrsprungsLand],
IsNull(UrsprungsRegion,'') [UrsprungsRegion], IsNull(WarenNr,'') [WarenNr],
/* DESCRIPTION */
IsNull(Ultrakurztext,'') [UltraKurztext], IsNull(Kurztext1,'') [Kurztext1],
IsNull(Kurztext2,'') [Kurztext2], IsNull(Info,'') [Info], IsNull(Langtext,'') [Langtext],
/* OTHER */
Gewicht, Volumen, IsNull(Warnung,'') [Warnung], IsNull(Lagerplatz,'') [Lagerplatz], LagerTyp,
ChangeDate
FROM Artikel
$sqlFilter
) Dummy
$sqlOrder $sqlRevers
"@
}
end {
Get-CurrentVariables -InitialVariables $initialVariables -Debug:$DebugPreference
Return [string[]]@($sqlMaster)
}
# Test: Get-ArticleSql -filter "ArtNummer='130100'"
}