Tag: Webservices

AutoHotkey GUI menu for API web service

For years we have relied on using a browser for interacting with our client (SilverPop) servers.  I had heard that SilverPop had an API for doing much of the same thing but was always “too busy” to take the time to investigate.  At some point I started playing with it and was amazed at how much time could be saved.  After you get passed the “hand-shake” between your computer and the server, the rest is pretty easy.  You simply pass XML to tell the remote servers what action you want to achieve.

I then created an AutoHotKey GUI menu for API web service  which streamlined submitting the XML.

Here is the script for AutoHotkey GUI menu for API web service

 
#SingleInstance, Force 
SplitPath,A_ahkPath,,Root
IniRead, RU,%A_MyDocuments%\fdsd.txt, Engage, RU ;gets Username
IniRead, DP,%A_MyDocuments%\fdfds.txt, Engage, DP ;Gets password

;***********************ADD Menu********************************.
Menu, Add_MU_0,Add, Add Contact to Contact List, Add_Contact_to_contact_List_TL
Menu, Add_MU_0,Add, Create Contact List, Add_Create_Contact_List_TL
Menu, Add_MU_0,Add, Import List, Add_Import_List_TL
Menu, Add_MU_0,Add, Opt-out email address, Add_Opt_Out_Recipient_TL

;***********************EXPORT MENU********************************.
Menu, Export_MU_0,Add, Get Aggregate Tracking for &Mailing, Export_Get_Aggregate_Tracking_For_Mailing_TL
Menu, Export_MU_0,Add, Get Aggregate Tracking for &User, Export_Get_Aggregate_Tracking_For_User_TL
Menu, Export_MU_0,Add, Get Aggregate Tracking for &Org, Export_Get_Aggregate_Tracking_For_Org_TL
Menu, Export_MU_0,Add, Get Aggregate Tracking for &Org with Top Domains, Export_Get_Aggregate_Tracking_For_Org_With_Top_Domains_TL
Menu, Export_MU_0,Add, 
Menu, Export_MU_0,Add, Export List, Export_List_TL

;***********************Get********************************.
Menu, Get_MU_0, Add, Get Sent Mailings for &Org, Get_Get_Sent_Mailings_For_Org_TL
Menu, Get_MU_0, Add, Get Sent Mailings for &User, Get_Get_Sent_Mailings_For_User_TL
Menu, Get_MU_0, Add, Get &Job Status, Get_Get_Job_status_TL
Menu, Get_MU_0, Add, Get &Recipient Data, Get_Select_Recipient_Data_TL

;***********************Misc********************************.
Menu, Misc_MU_0, Add, Add others here, Misc_Temp_TL

;***********************Main menu********************************.
Menu, MainMenu, Add, &Add, :Add_MU_0
Menu, MainMenu, Add, &Export, :Export_MU_0
Menu, MainMenu, Add, &Get, :Get_MU_0
Menu, MainMenu, Add
Menu, MainMenu, Add, &Misc, :Misc_MU_0
return
Browser_Forward::Reload
Browser_Back::
^t::Menu, MainMenu, Show  ; right mouse and windows

;***********************Add********************************.
Add_Contact_to_Contact_List_TL:
GoSub Grab_Contact_List_ID
GoSub Grab_Contact_ID
XMLFunc(xml:="<AddContactToContactList><CONTACT_LIST_ID>" . Contact_List_ID . "</CONTACT_LIST_ID><CONTACT_ID>" . Contact_ID . "</CONTACT_ID></AddContactToContactList>")
GoSub Evaluator
Return

Add_Create_Contact_List_TL:
GoSub Grab_Contact_List_Name
XMLFunc(xml:="<CreateContactList><DATABASE_ID>4324324</DATABASE_ID><CONTACT_LIST_NAME>" . Contact_List_Name . "</CONTACT_LIST_NAME><VISIBILITY>1</VISIBILITY></CreateContactList>")
RegExMatch(Response, "<CONTACT_LIST_ID>(\d{7,9})</CONTACT_LIST_ID>", List_ID) ;List_ID1 is Contact List
MsgBox % List_ID1 " is Contact list ID loaded", , 4
Return

Add_Import_List_TL:
GoSub Grab_Map_And_SourceFile
XMLFunc(xml:="<ImportList><MAP_FILE>" . Map_File . "</MAP_FILE><SOURCE_FILE>" . Source_File . "</SOURCE_FILE></ImportList>")
Return

Add_Opt_Out_Recipient_TL:
GoSub Grab_eMail
XMLFunc(xml:="<OptOutRecipient><LIST_ID>43243243</LIST_ID><EMAIL>" . eMail . "</EMAIL></OptOutRecipient>")
GoSub Evaluator

Return

Add_Create_Query_TL:
XMLFunc(xml:="<CreateQuery><QUERY_NAME>Has_Mobile_30_Zip</QUERY_NAME><PARENT_LIST_ID>432432</PARENT_LIST_ID><VISIBILITY>0</VISIBILITY><PARENT_FOLDER_ID>34243</PARENT_FOLDER_ID><ALLOW_FIELD_CHANGE>0</ALLOW_FIELD_CHANGE><SELECT_COLUMNS>Last_Name|First_Name</SELECT_COLUMNS><CRITERIA><TYPE>editable</TYPE><EXPRESSION><TYPE>TE</TYPE><COLUMN_NAME>Zip</COLUMN_NAME><OPERATORS><![CDATA[BETWEEN]]></OPERATORS><VALUES><![CDATA[30000|30999]]></VALUES><LEFT_PARENS>(</LEFT_PARENS></EXPRESSION><EXPRESSION><AND_OR>AND</AND_OR><TYPE>TE</TYPE><COLUMN_NAME>Mobile</COLUMN_NAME><OPERATORS><![CDATA[IS NOT null]]></OPERATORS><RIGHT_PARENS>)</RIGHT_PARENS></EXPRESSION></CRITERIA><BEHAVIOR><OPTION_OPERATOR>103</OPTION_OPERATOR><TYPE_OPERATOR>111</TYPE_OPERATOR><MAILING_ID>669614</MAILING_ID><REPORT_ID>502993</REPORT_ID></BEHAVIOR></CreateQuery>")
return

Add_Create_Table_TL:
XMLFunc(xml:="<CreateTable><TABLE_NAME>Purchases</TABLE_NAME><COLUMNS><COLUMN><NAME>Record Id</NAME><TYPE>NUMERIC</TYPE><IS_REQUIRED>true</IS_REQUIRED><KEY_COLUMN>true</KEY_COLUMN></COLUMN><COLUMN><NAME>Purchase Date</NAME><TYPE>DATE</TYPE><IS_REQUIRED>true</IS_REQUIRED></COLUMN><COLUMN><NAME>Product Id</NAME><TYPE>NUMERIC</TYPE><IS_REQUIRED>true</IS_REQUIRED></COLUMN></COLUMNS></CreateTable>")
Return

;*******************************************************.
;***********************Export********************************.
;*******************************************************.
Export_Get_Aggregate_Tracking_For_Mailing_TL:
gosub Grab_Mailing_ID
gosub Grab_Report_ID
XMLFunc(xml:="<GetAggregateTrackingForMailing><MAILING_ID>" . Mailing_ID . "</MAILING_ID><REPORT_ID>" . Report_ID . "</REPORT_ID></GetAggregateTrackingForMailing>")
return

Export_Get_Aggregate_Tracking_For_Org_TL:
Gosub Grab_Date
XMLFunc(xml:="<GetAggregateTrackingForOrg><DATE_START>" . Date_Start . "</DATE_START><DATE_END>" . Date_End . "</DATE_END><EXCLUDE_TEST_MAILINGS/></GetAggregateTrackingForOrg>")
GoSub ImportExcel
DropUnwantedColumns(Values:="NumSeeds,NumInboxMonitored,NumUniqueAttach,NumGrossAttach,NumUniqueClickstreams,NumGrossClickstreams,NumUniqueMedia,NumGrossMedia,NumGrossAbuse,NumGrossChangeAddress,NumGrossMailRestriction,NumGrossOther,NumConversions,NumConversionAmount,NumBounceHardFwd,NumBounceSoftFwd,NumConversionAmountFwd,NumAttachOpenFwd,NumClickFwd,NumUniqueForwardFwd,NumGrossForwardFwd,NumUniqueConversionsFwd,NumGrossConversionsFwd,NumUniqueClickstreamFwd,NumGrossClickstreamFwd,NumUniqueClickFwd,NumGrossClickFwd,NumUniqueAttachOpenFwd,NumGrossAttachOpenFwd,NumUniqueMediaFwd,NumGrossMediaFwd,NumUniqueOpenFwd,NumGrossOpenFwd,NumAbuseFwd,NumChangeAddressFwd,NumMailRestrictionFwd,NumMailBlockFwd,NumOtherFwd,NumSuppressedFwd")
GoSub DropBlankExcelColumns
GoSub SaveExcel
Return

Export_Get_Aggregate_Tracking_For_Org_With_Top_Domains_TL:
Gosub Grab_Date
XMLFunc(xml:="<GetAggregateTrackingForOrg><DATE_START>" . Date_Start . "</DATE_START><DATE_END>" . Date_End . "</DATE_END><EXCLUDE_TEST_MAILINGS/><TOP_DOMAIN/></GetAggregateTrackingForOrg>")
GoSub ImportExcel
DropUnwantedColumns(Values:="NumSeeds,NumInboxMonitored,NumUniqueAttach,NumGrossAttach,NumUniqueClickstreams,NumGrossClickstreams,NumUniqueMedia,NumGrossMedia,NumGrossAbuse,NumGrossChangeAddress,NumGrossMailRestriction,NumGrossOther,NumConversions,NumConversionAmount,NumBounceHardFwd,NumBounceSoftFwd,NumConversionAmountFwd,NumAttachOpenFwd,NumClickFwd,NumUniqueForwardFwd,NumGrossForwardFwd,NumUniqueConversionsFwd,NumGrossConversionsFwd,NumUniqueClickstreamFwd,NumGrossClickstreamFwd,NumUniqueClickFwd,NumGrossClickFwd,NumUniqueAttachOpenFwd,NumGrossAttachOpenFwd,NumUniqueMediaFwd,NumGrossMediaFwd,NumUniqueOpenFwd,NumGrossOpenFwd,NumAbuseFwd,NumChangeAddressFwd,NumMailRestrictionFwd,NumMailBlockFwd,NumOtherFwd,NumSuppressedFwd")

GoSub DropBlankExcelColumns
GoSub SaveExcel
Return

Export_Get_Aggregate_Tracking_For_User_TL:

XMLFunc(xml:="<GetAggregateTrackingForUser><DATE_START>" . Date_Start . "</DATE_START><DATE_END>" . Date_End . "</DATE_END></GetAggregateTrackingForUser>")
Return

Export_List_TL:

XMLFunc(xml:="<ExportList><LIST_ID>" . List_ID . "</LIST_ID><EXPORT_TYPE>ALL</EXPORT_TYPE><EXPORT_FORMAT>CSV</EXPORT_FORMAT><ADD_TO_STORED_FILES/><DATE_START>" . Date_Start . "</DATE_START><DATE_END>" . Date_End . "</DATE_END></ExportList>")
return
;*******************************************************.
;***********************GET********************************.
;*******************************************************.
Get_Get_Report_ID_By_Date_TL:
XMLFunc(xml:="<GetReportIdByDate><MAILING_ID>" . Mailing_ID . "</MAILING_ID><DATE_START>" . Date_Start . "</DATE_START><DATE_END>" . Date_End . "</DATE_END></GetReportIdByDate>")
return

Get_Get_Sent_Mailings_For_Org_TL:
Gosub Grab_Date
XMLFunc(xml:="<GetSentMailingsForOrg><DATE_START>" . Date_Start . "</DATE_START><DATE_END>" . Date_End . "</DATE_END><EXCLUDE_ZERO_SENT/><EXCLUDE_TEST_MAILINGS/></GetSentMailingsForOrg>")
GoSub ImportExcel
GoSub DropBlankExcelColumns
GoSub SaveExcel
return

Get_Get_Sent_Mailings_For_User_TL:

XMLFunc(xml:="<GetSentMailingsForUser><DATE_START>" . Date_Start . "</DATE_START><DATE_END>" . Date_End . "</DATE_END></GetSentMailingsForUser>")
GoSub ImportExcel
GoSub DropBlankExcelColumns
GoSub SaveExcel
return

GetAggregateTrackingForMailing_TL:
GoSub Grab_Mailing_ID
XMLFunc(xml:="<GetAggregateTrackingForMailing><MAILING_ID>" . Mail_ID . "</MAILING_ID><REPORT_ID>" . Report_ID . "</REPORT_ID> </GetAggregateTrackingForMailing>")
GoSub ImportExcel
GoSub DropBlankExcelColumns
GoSub SaveExcel
return

Get_Get_Job_status_TL:
GoSub Grab_Job_ID
XMLFunc(xml:="<GetJobStatus><JOB_ID>" . Job_ID . "</JOB_ID></GetJobStatus>")
Response:=WebRequest.ResponseText
MsgBox % Response
return

Get_Select_Recipient_Data_TL:
gosub Grab_Email
XMLFunc(xml:="<SelectRecipientData><LIST_ID>4324324</LIST_ID><EMAIL>" . email . "</EMAIL></SelectRecipientData>")
GoSub ImportExcel
;***********************Miscellaneous********************************.
Misc_Temp_TL:
return

Add_Update_Recipient_TL:
;add criteria
return




;*******************************************************.
;*******************************************************.
Login:
Pod:=4
URL:="http://api" . Pod . ".silverpop.com/XMLAPI"
Loginxml:="<Envelope><Body><Login><USERNAME>" . RU . "</USERNAME><PASSWORD>" . DP . "</PASSWORD></Login></Body></Envelope>"
WebRequest := ComObjCreate("WinHttp.WinHttpRequest.5.1")
WebRequest.Open("POST", URL)
WebRequest.SetRequestHeader("Content-Type", "text/xml;charset=UTF-8")
WebRequest.Send(Loginxml)
GoSub Evaluator ;turn this into function and pass user notification
RegExMatch(Response,"jsessionid=(\w+)\<",jid) ;obtain session ID
URL:="http://api" . Pod . ".silverpop.com/XMLAPI;jsessionid=" . JID1
return

SendXML:
XML:="<Envelope><Body>" . XML . "</Body></Envelope>"
Notify("Status","Sending request",4,"TS=10 TM=8 TF=Times New Roman GC_=Yellow SI_=1000")
WebRequest := ComObjCreate("WinHttp.WinHttpRequest.5.1")
WebRequest.Open("POST", URL)
WebRequest.SetRequestHeader("Content-Type", "text/xml;charset=UTF-8")
Clipboard:=xml ;added for testing 2/1/2013
MsgBox , 0, clipboard now contains, %xml% , 2 ;added for testing
WebRequest.Send(xml)

return

Evaluator:
MsgBox % Response:="<?xml version=""1.0"" encoding=""UTF-8"" ?>`n" . WebRequest.ResponseText
RegExMatch(Response,"<SUCCESS>(\w+)</SUCCESS>",Status)
If (status1!="TRUE"){
	MsgBox,,Fail, XML Send was not Successful
	Exit
	}
   else
   Notify("Status","Receive was Successful",4,"TS=10 TM=8 TF=Times New Roman GC_=Yellow SI_=1000")
Return

Append_CleanXML_Save:
StringReplace, Response, Response, <SUCCESS>TRUE</SUCCESS>,,
Notify("Status","Received info and Saving",2,"TS=10 TM=8 TF=Times New Roman GC_=Yellow SI_=1000")
Path=%A_Scriptdir%\mailing.xml
FileDelete, %Path%
FileAppend, %response%, %path%,Utf-8
Return

;***********************Import to Excel********************************.
ImportExcel:
Notify("Status","Importing file",6,"TS=10 TM=8 TF=Times New Roman GC_=Yellow SI_=1000")
Xls := ComObjCreate("Excel.Application") ;handle
Xls.Visible := True ;by default excel sheets are invisible

Path=%A_Scriptdir%\mailing.xml
Xls := Xls.Workbooks.OpenXML("B:\Progs\AutoHotkey_L\TI\Engage\API\mailing.xml", 1, 2) ;.LoadOption.2 ;import xml file
xls.activesheet.Name := "Data"
xls:=Excel_Get()
Xls.ActiveSheet.ListObjects("Table1").TableStyle := "TableStyleLight1" ; "" clears formatting
return



SaveExcel:
xls.DisplayAlerts := false ;doesn't ask if I care about overwriting the file
Today := A_MMM . " " . A_DD . ", " . A_YYYY
File := A_ScriptDir . "\Mailings.xlsx"
File := A_ScriptDir . "\Mailings " . Today . ".xlsx"
xls.ActiveWorkbook.Saveas(File, 51) ; 6=csv 51=2007 56=2003
xls.DisplayAlerts := true ;Turn back on warnings
return

DropBlankExcelColumns:
xls:=Excel_Get()
LR := xls.ActiveSheet.UsedRange.Rows.Count ;Last Row
LC := xls.ActiveSheet.UsedRange.Columns.Count ;Grabs last Column
Loop, %LC%
{
CL:=ColtoChar(LC-A_Index+1)
Max:=XLs.Max(xls.Range(CL . "2:" . CL . LR))
Text:=XLs.Range(CL . "2").Value ;
If (Max=0) AND (Text=0) OR (Text=""){
;	MsgBox % max " text " Text " in col " CL
	xls.Range(CL . ":" CL).Delete
	}
}
return

;*******************************************************.
;*******GUI menu for API web service****************GUIS********************************.
;*******************************************************.
Grab_Contact_ID:
RegExMatch(clipboard, "(\d){10}", Contact_ID) ;going to try and extract up to first line
Gui, +AlwaysOnTop -MaximizeBox -MinimizeBox
Gui, Add, text, , What is the Contact ID?
Gui, Add, edit, r1 vContact_ID, %Contact_ID%
Gui, Add, Button, Default, OK
Gui, Show
Gui +LastFound ;use this in conjunction with next line to have ahk wait for input before proceeding
WinWaitClose
return

Grab_Contact_List_ID:
RegExMatch(clipboard, "(\d){7}", Contact_List_ID) ;going to try and extract up to first line
Gui, +AlwaysOnTop -MaximizeBox -MinimizeBox
Gui, Add, text, , What is the Contact List ID?
Gui, Add, edit, r1 vContact_List_ID, %Contact_List_ID%
Gui, Add, Button, Default, OK
Gui, Show
Gui +LastFound ;use this in conjunction with next line to have ahk wait for input before proceeding
WinWaitClose
return

Grab_Contact_List_Name:
RegExMatch(clipboard, "(\d){8}", Contact_List_Name) ;going to try and extract up to first line
Gui, +AlwaysOnTop -MaximizeBox -MinimizeBox
Gui, Add, text, , What is the Contact List Name?
Gui, Add, edit, r1 vContact_List_Name, %Contact_List_Name%
Gui, Add, Button, Default, OK
Gui, Show
Gui +LastFound ;use this in conjunction with next line to have ahk wait for input before proceeding
WinWaitClose
return

Grab_Database_ID:
RegExMatch(clipboard, "(\d){8}", Database_ID) ;going to try and extract up to first line
Gui, +AlwaysOnTop -MaximizeBox -MinimizeBox
Gui, Add, text, , What is the eMail address?
Gui, Add, edit, r1 vDatabase_ID, %Database_ID%
Gui, Add, Button, Default, OK
Gui, Show
Gui +LastFound ;use this in conjunction with next line to have ahk wait for input before proceeding
WinWaitClose
Return

Grab_Date:
Gui, +AlwaysOnTop -MaximizeBox -MinimizeBox
Gui, Add, text, , Please select the start time
Gui, Add, DateTime, vDate_Start ;, LongDate
gui, add, text, , `nNow select the end time
Gui, Add, DateTime, vDate_End ;, LongDate
Gui, Add, Button, Default, OK
Gui, Show
Gui +LastFound ;use this in conjunction with next line to have ahk wait for input before proceeding
WinWaitClose
Date_Start:= RegExReplace(Date_Start,"(\d\d\d\d)(\d\d)(\d\d).*","$2/$3/$1 00:00:00")
Date_End:= RegExReplace(Date_End,"(\d\d\d\d)(\d\d)(\d\d).*","$2/$3/$1 23:59:59")
return

Grab_Email:
RegExMatch(Clipboard, "\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*", eMail)
Gui, +AlwaysOnTop -MaximizeBox -MinimizeBox
Gui, Add, text, , What is the eMail address?
Gui, Add, edit, r1 veMail, %eMail%
Gui, Add, Button, Default, OK
Gui, Show
Gui +LastFound ;use this in conjunction with next line to have ahk wait for input before proceeding
WinWaitClose
return

Grab_Job_ID:
RegExMatch(clipboard, "(\d){8}", Job_ID) ;going to try and extract up to first line
Gui, +AlwaysOnTop -MaximizeBox -MinimizeBox
Gui, Add, text, , What is the job ID?
Gui, Add, edit, r1 vJob_ID, %Job_ID% ;, MailID
Gui, Add, Button, Default, OK
Gui, Show
Gui +LastFound ;use this in conjunction with next line to have ahk wait for input before proceeding
WinWaitClose
return

Grab_List_ID:
RegExMatch(clipboard, "(\d){8}", List_ID) ;going to try and extract up to first line
Gui, +AlwaysOnTop -MaximizeBox -MinimizeBox
Gui, Add, text, , What is the List ID?
Gui, Add, edit, r1 vList_ID, %List_ID% ;
Gui, Add, Button, Default, OK
Gui, Show
Gui +LastFound ;use this in conjunction with next line to have ahk wait for input before proceeding
WinWaitClose
return

Grab_Mailing_ID:
RegExMatch(clipboard, "(\d){8}", Mail_ID) ;going to try and extract up to first line
Gui, +AlwaysOnTop -MaximizeBox -MinimizeBox
Gui, Add, text, , What is the Mail ID?
Gui, Add, edit, r1 vMail_ID, %Mail_ID% ;, MailID
Gui, Add, Button, Default, OK
Gui, Show
Gui +LastFound ;use this in conjunction with next line to have ahk wait for input before proceeding
WinWaitClose
return

Grab_Map_And_SourceFile:
RegExMatch(clipboard, "(\S+\.xml)", Map_File) ;filename ending in xml
RegExMatch(clipboard, "(\S+\.csv)", Source_File) ;filename ending in CSV or TXT
Gui, +AlwaysOnTop -MaximizeBox -MinimizeBox
Gui, Add, text, , What is the name of the Map File?
Gui, Add, edit, r1 Lowercase w150 vMap_File, %Map_File%
Gui, Add, text, , `nWhat is the name of the Source File?
Gui, Add, edit, r1 Lowercase w150 vSource_File, %Source_File%
Gui, Add, Button, Default, OK
Gui, Show, w200
Gui +LastFound ;use this in conjunction with next line to have ahk wait for input before proceeding
WinWaitClose
return


Grab_Report_ID:
RegExMatch(clipboard, "(\d){9}", Report_ID) ;going to try and extract up to first line
Gui, +AlwaysOnTop -MaximizeBox -MinimizeBox
gui, add, text, , `nWhat is the Report ID?
Gui, Add, edit, r1 vReport_ID, %Report_ID% ;, LongDate
Gui, Add, Button, Default, OK
Gui, Show
Gui +LastFound ;use this in conjunction with next line to have ahk wait for input before proceeding
WinWaitClose
return

ButtonOK:
gui, Submit
Gui, Destroy
return

;***********************Functions********************************.
XMLFunc(XML=""){
gosub Login
gosub SendXML
GoSub Evaluator
GoSub Append_CleanXML_Save
}

ColToChar(index) { ;Converting Columns to Numeric for Excel
	If(index <= 26)
	{
		return Chr(64+index)
	}
	Else If (index > 26)
	{
		return Chr((index-1)/26+64) . Chr(mod((index - 1),26)+65)
	}
}
DropUnwantedColumns(Values=""){
xls:=Excel_Get()
LC := xls.ActiveSheet.UsedRange.Columns.Count ;Grabs last Column
CL:=ColtoChar(LC-A_Index+1)

Loop, %LC%
{
	Header:=xls.Range(CL . "1").Value
	;~ MsgBox % Header
	Loop, parse, Values, `,
	{
		If (Header=A_LoopField)
			xls.Range(CL . ":" CL).Delete
	}
CL:=ColtoChar(LC-A_Index+1)
}
}