Customer Analysis Tool CAT

########################################################################
# Customer Analysis Tool C.A.T.
#
# Campaigns
#
# Author: Nigel Glenwood Microsoft
# Description:
# Analyze Customer Data Quickly to Improve Resolution Times of Issues
# and Provide overall better customer service
#
# Technologies Utilized:
# .net for windows forms buttons etc...
# SQL
# SQL Server
# Powershell
# Primal Forms By Sapien
# \\transfer\transfer
##########################################################################

##########################################################################
# Initial Setup Section
# Nigel NOtes
# setup a binding source
# setup sql adapater
##########################################################################

# Nigel notes, check that powershell can run, may need to set execution policy

#get-executionpolicy

#$a = read-host "hit enter to continue"

#set-Executionpolicy remotesigned
#set-Executionpolicy unrestricted

# Nige Note: load assemblies so we can utilize bindingsource, windows forms etc...
#region Import the Assemblies
[reflection.assembly]::loadwithpartialname("System.Windows.Forms") | Out-Null
[reflection.assembly]::loadwithpartialname("System.Drawing") | Out-Null
#endregion


$bindingSource1 = new-object System.Windows.Forms.BindingSource
$bindingSource2 = new-object System.Windows.Forms.BindingSource
$dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

# Nigel Notes
# setup connection String to SQL create datatable to house data then place the data into datagrid gui

#testing
#$connString = "Server=aittiis173;Database=master;Integrated Security=SSPI;"

#Production
$connString = "Server=by2mtza407;Database=adcenter_campaign_common;Integrated Security=SSPI;Connect Timeout=6000000"

#Generated Form Function

function GenerateForm {

########################################################################
# Generated By: niglen
########################################################################



#region Generated Form Objects
$form1 = New-Object System.Windows.Forms.Form
#
# progress bar is just a GUI status bar basically that you can increment
# pogramatically to give illusion that system is working good if query may take a long time
#
$progressBar1 = New-Object System.Windows.Forms.ProgressBar
$button1 = New-Object System.Windows.Forms.Button
$dataGrid1 = New-Object System.Windows.Forms.DataGrid
$dataGrid2 = New-Object System.Windows.Forms.DataGrid

$textBoxaccountID = New-Object System.Windows.Forms.TextBox
$label1 = New-Object System.Windows.Forms.Label
$pictureBox1 = New-Object System.Windows.Forms.PictureBox
$InitialFormWindowState = New-Object System.Windows.Forms.FormWindowState
#endregion Generated Form Objects

####################################################################################################################################################
# EVENTS
####################################################################################################################################################
#----------------------------------------------
#Nigel Custom Code
#----------------------------------------------
$button1_OnClick=
{
$progressBar1.PerformStep()
# build query
$mydata = $textBoxaccountID.text


# use x0940170 with p4 and it brings back data good as a test

$query = "

use AdCenter_Campaign_p4
declare @the_campaign int
set @the_campaign = 20431270

select top 200 * from campaign
join status on campaign.PauseStatusId = status.statusid
where accountid in (select accountid from account where AccountNumber = '$mydata')
and lifecyclestatusId = 121 and PauseStatusId = 125"


# above line is end of the query

#select top 10 AccountId, AccountNumber, AccountName, FinancialStatusId, modifiedbyuserid, modifiedDTim, [Status].Statusname, [Status].statusdesc from

#account join status on Account.FinancialStatusId = [Status].StatusId where AccountID = '$mydata' order by ModifiedDTim DESC"

$progressBar1.PerformStep()
$progressBar1.PerformStep()


$progressBar1.PerformStep()
#below for testing
#$query = "select * from sys.objects"
# run query and place into datatable
$dataAdapter.SelectCommand = new-object System.Data.SqlClient.SqlCommand ($query,$connString)
# Set the timeout to 999 Seconds
$dataAdapter.SelectCommand.CommandTimeout=999



$commandBuilder = new-object System.Data.SqlClient.SqlCommandBuilder $dataAdapter
$progressBar1.PerformStep()
$progressBar1.PerformStep()
$dt = New-Object System.Data.DataTable
[void]$dataAdapter.fill($dt)
$progressBar1.PerformStep()
$bindingSource1.DataSource = $dt
$progressBar1.PerformStep()
# $dataGrid1.AutoResizeColumns([System.Windows.Forms.DataGridViewAutoSizeColumnsMode]::AllCellsExceptHeader)
$progressBar1.PerformStep()
# Place data into the Datagrid on the Form.
$dataGrid1.DataSource = $bindingSource1

#write-host $dt | format-table | out-string

# Query #2 Test NIGEL

$query = "select top 100 * from account
where AccountNumber = '$mydata'"
# run query and place into datatable
$dataAdapter.SelectCommand = new-object System.Data.SqlClient.SqlCommand ($query,$connString)
# Set the timeout to 999 Seconds
$dataAdapter.SelectCommand.CommandTimeout=999



$commandBuilder = new-object System.Data.SqlClient.SqlCommandBuilder $dataAdapter
$progressBar1.PerformStep()
$progressBar1.PerformStep()

$dt2 = New-Object System.Data.DataTable
[void]$dataAdapter.fill($dt2)
$progressBar1.PerformStep()
$bindingSource2.DataSource = $dt2
$progressBar1.PerformStep()

# Place data into the Datagrid on the Form.

$dataGrid2.DataSource = $bindingSource2




$results = $dt.select() | Out-String

# write it out to console, can easily write out to a file if you want.
write-host $results

$results.Count

#added code

foreach ($objItem in $results) {
write-host writing value
write-host $objItem.$_
}







$progressBar1.PerformStep()

$progressbar1.Value=0




}

$OnLoadForm_StateCorrection=
{#Correct the initial state of the form to prevent the .Net maximized form issue
$form1.WindowState = $InitialFormWindowState
}

#----------------------------------------------
#region Generated Form Code
$form1.BackgroundImageLayout = 0
$form1.Text = "Proactive Advertising Monitoring P.A.M."
$form1.Name = "form1"
$form1.BackgroundImage = [System.Drawing.Image]::FromFile('.\background.png')
$form1.DataBindings.DefaultDataSourceUpdateMode = 0
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 1400
$System_Drawing_Size.Height = 784
$form1.ClientSize = $System_Drawing_Size

$progressBar1.DataBindings.DefaultDataSourceUpdateMode = 0
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 243
$System_Drawing_Size.Height = 23
$progressBar1.Size = $System_Drawing_Size
$progressBar1.TabIndex = 5
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 922
$System_Drawing_Point.Y = 117
$progressBar1.Location = $System_Drawing_Point
$progressBar1.Name = "progressBar1"
$progressBar1.Step = 10

#$progressBar1.PerformStep()

$form1.Controls.Add($progressBar1)

$button1.TabIndex = 4
$button1.Name = "button1"
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 41
$System_Drawing_Size.Height = 39
$button1.Size = $System_Drawing_Size
$button1.UseVisualStyleBackColor = $True
$button1.BackgroundImage = [System.Drawing.Image]::FromFile('.\Search.PNG')

$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 323
$System_Drawing_Point.Y = 109
$button1.Location = $System_Drawing_Point
$button1.DataBindings.DefaultDataSourceUpdateMode = 0
$button1.BackgroundImageLayout = 0
$button1.add_Click($button1_OnClick)

$form1.Controls.Add($button1)

$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 800
$System_Drawing_Size.Height = 80
$dataGrid1.Size = $System_Drawing_Size
$dataGrid1.DataBindings.DefaultDataSourceUpdateMode = 0
$dataGrid1.HeaderForeColor = [System.Drawing.Color]::FromArgb(255,0,0,0)
$dataGrid1.Name = "dataGrid1"
$dataGrid1.DataMember = ""
$dataGrid1.TabIndex = 3
#$dataGrid1.BackColor = [System.Drawing.Color]::FromArgb(255,191,205,219)
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 13
$System_Drawing_Point.Y = 161
$dataGrid1.Location = $System_Drawing_Point
$dataGrid1.BackgroundColor = [System.Drawing.Color]::FromArgb(255,255,255,255)

$form1.Controls.Add($dataGrid1)


# new code nigel


# this just adds all the GUI to the form


$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 800
$System_Drawing_Size.Height = 120
$dataGrid2.Size = $System_Drawing_Size
$dataGrid2.DataBindings.DefaultDataSourceUpdateMode = 0
$dataGrid2.HeaderForeColor = [System.Drawing.Color]::FromArgb(255,0,0,0)
$dataGrid2.Name = "dataGrid2"
$dataGrid2.DataMember = ""
$dataGrid2.TabIndex = 3
#$dataGrid2.BackColor = [System.Drawing.Color]::FromArgb(212,131,215,200)
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 13
$System_Drawing_Point.Y = 261
$dataGrid2.Location = $System_Drawing_Point
$dataGrid2.BackgroundColor = [System.Drawing.Color]::FromArgb(255,255,255,255)


$form1.Controls.Add($dataGrid2)

$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 137
$System_Drawing_Size.Height = 30
$textBoxaccountID.Size = $System_Drawing_Size
$textBoxaccountID.DataBindings.DefaultDataSourceUpdateMode = 0
$textBoxaccountID.Name = "textBoxaccountID"
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 110
$System_Drawing_Point.Y = 121
$textBoxaccountID.Location = $System_Drawing_Point
$textBoxaccountID.TabIndex = 2

$textBoxaccountID.text = 'x0940170'

$form1.Controls.Add($textBoxaccountID)

$label1.TabIndex = 1
$label1.BackColor = [System.Drawing.Color]::FromArgb(255,255,255,255)
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 120
$System_Drawing_Size.Height = 33
$label1.Size = $System_Drawing_Size
$label1.FlatStyle = 1
$label1.Text = "Enter Account Number:"
$label1.Font = New-Object System.Drawing.Font("Georgia",9.75,0,3,0)

$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 13
$System_Drawing_Point.Y = 115
$label1.Location = $System_Drawing_Point
$label1.DataBindings.DefaultDataSourceUpdateMode = 0
$label1.Name = "label1"

$form1.Controls.Add($label1)

$pictureBox1.TabIndex = 0
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 1179
$System_Drawing_Size.Height = 103
$pictureBox1.Size = $System_Drawing_Size
$pictureBox1.ImageLocation = ".\Microsoft Advertising.png"


$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = -2
$System_Drawing_Point.Y = 0
$pictureBox1.Location = $System_Drawing_Point

$pictureBox1.InitialImage = [System.Drawing.Image]::FromFile('.\Microsoft Advertising.png')
$pictureBox1.TabStop = $False
$pictureBox1.Name = "pictureBox1"
$pictureBox1.DataBindings.DefaultDataSourceUpdateMode = 0

$form1.Controls.Add($pictureBox1)

#endregion Generated Form Code

#Save the initial state of the form
$InitialFormWindowState = $form1.WindowState
#Init the OnLoad event to correct the initial state of the form
$form1.add_Load($OnLoadForm_StateCorrection)
#Show the Form
$form1.ShowDialog()| Out-Null

} #End Function


#Call the Function to generate the form nigel

GenerateForm

# END OF CODE





How to use:

1) Open the attached file, and copy the text
2) In the PHX TS server, normally tk2tscorp.phx.gbl open notepad
3) Paste the code
4) Save as .ps1 (note the location, should be in your documents)
5) Right click on the document, and state run with powershell
a. Or open cmd prompt, navigate to the folder
b. Powerhsell
c. .\mypowershellscript.ps1
6) The cmd script will allow you to see all the errors if the script is really hosed
7) Adjust the sql and connection string as needed.