[CmdletBinding()] Param ( [Parameter(Mandatory = $True)] [long] $ListID, [Parameter(Mandatory = $True)] [long] $SurveyID, [Parameter(Mandatory = $False)] [string] $DoPanelField, [Parameter(Mandatory = $False)] [string] $ListsDatabaseConnectionString, [Parameter(Mandatory = $False)] [string] $WebProdURL ) #verify parameters if ([string]::IsNullOrEmpty($DoPanelField)) { $DoPanelField = 'DoPanelLink' } if ([string]::IsNullOrEmpty($ListsDatabaseConnectionString)) { $ListsDatabaseConnectionString = 'Provider=sqloledb;Data Source=10.0.0.67;Initial Catalog=Lists;Trusted_Connection=no;User Id=xxxx;Password=yyyy' } if ([string]::IsNullOrEmpty($WebProdURL)) { $WebProdURL = 'http://10.0.0.67/webprod/cgi-bin/AskiaExt.dll' } #function to encrypt a number function EncryptNumber($number) { $url = $WebProdURL + '?Action=EncryptNumber&Encrypt=1&Number=' + $number + '&ErrorPage=None' $progressPreference = 'silentlyContinue' $request = Invoke-WebRequest -URI $url -MaximumRedirection 0 -ErrorAction Ignore if ($request.StatusCode -eq 302) { $redirectLocation = $request.Headers.Location $output = $redirectLocation.Substring($redirectLocation.Length - 16) $output } } #encrypt constant values $ListIdKey16 = EncryptNumber($ListID) $SurveyIDKey16 = EncryptNumber($SurveyID) #open database connection $connection = new-object System.Data.OleDb.OleDbConnection($ListsDatabaseConnectionString) $connection.Open() #determine the number of contacts to update $totalContacts = 0 $sqlQuery = "SELECT count(*) FROM AskList" + $ListID $cmdReadContactCount = new-object System.Data.OleDb.OleDbCommand($sqlQuery, $connection) $reader = $cmdReadContactCount.ExecuteReader() if ($reader.Read()) { $totalContacts = $reader.GetValue(0) } $reader.Close() #create comands $sqlQuery = "SELECT AskInterview FROM AskList" + $ListID $cmdReadContacts = new-object System.Data.OleDb.OleDbCommand($sqlQuery, $connection) $sqlQuery = "UPDATE AskList" + $ListID + " SET [" + $DoPanelField + "] = ? WHERE AskInterview = ?" $cmdWriteDoPanelLink = new-object System.Data.OleDb.OleDbCommand($sqlQuery, $connection) $cmdWriteDoPanelLink.Parameters.Add("", [System.Data.OleDb.OleDbType]::VarWChar) | Out-Null #DoPanelLink $cmdWriteDoPanelLink.Parameters.Add("", [System.Data.OleDb.OleDbType]::Integer) | Out-Null #AskInterview #loop through all contacts $reader = $cmdReadContacts.ExecuteReader() $activity = "Updating list " + $ListID $contactsProcessed = 0 while ($reader.Read()) { #generate the DoPanel link $AskInterview = $reader.GetValue(0) $ContactIdKey16 = EncryptNumber($AskInterview) $DoPanelLink = $WebProdURL + '?Action=DoPanel&Survey=' + $SurveyIDKey16 + '&PanelId=' + $ContactIdKey16 + '@' + $ListIdKey16 #update the field $cmdWriteDoPanelLink.Parameters[0].Value = $DoPanelLink $cmdWriteDoPanelLink.Parameters[1].Value = $AskInterview $cmdWriteDoPanelLink.ExecuteNonQuery() | Out-Null; #write progress $contactsProcessed++ Write-Progress -Activity $activity -percentComplete ( 100 * $contactsProcessed / $totalContacts) } $reader.Close() #done $connection.Close() Write-Progress -Activity "Updating list" -Completed