Introduction
User Process Automation/Desktop Automation is gaining popularity nowadays.Openspan, Blue Prism,IE Spy,Spy ++ etc are the popular tools available for devlopers for Automation Development and testing. All these tools work on Screen Scraping Technology.
Before we start of any Automation Project, Requirements Analysis activity to be carried out which is slightly different from conventional SDLC Requirement phase.
- Calculate the total time taken to complete the entire process
- Identification of Repetetive steps involved in a Computerised User Process
- Study on how many mouse clicks we can save
- Perform Time & Motion study and estimate the potential saving if we Automate
- Assess the Applications involved part of the process and activity of copy & pasting the data between aapplications to be studied
High Level Automation Design of a typical Mortgage Assessment Process
Assuming we have a call center agent in back office process receives 100 Customer prospect list in the form of Excel and explaining the mortgage calculations on call with the Customer.
- Pick the customer on the fly from the excel sheet and perform search in CRM application
- Find out the customer details in Mortgage Prospect Web Site(CRM Application)
- Pick the Loan Amount from Lead Generator and paste the mortgage vaue in the EMI Calculator
- Explain the EMI details to the Customer.
Automation Flow Sequence
- Launch required Applications once in a shift.
- Loading the Excel Records into the DataGridView.
- Pick the First Customer.
- Take inputs on "Interest Rate" and "Loan Term" from Customer
- Click on "Calculate EMI"/ "Query EMI"
- Capturing Customer Name in the DataGrid Cell Button
- Paste Customer Name in Lead Generator and Search
- Get the Mortgage value from Lead Generator
- Paste Loan Term,Mortgage Value,Interest Rate in EMI Calculator
Code with Solution-Case study
private void LaunchApplications()
{
string URL = "http://yourInternalSite.com/lead-generation.aspx";
object o = null;
SHDocVw.InternetExplorer ie = new SHDocVw.InternetExplorerClass();
app = (SHDocVw.WebBrowser)ie;
app.Visible = true;
app.Navigate(URL, ref o, ref o, ref o, ref o);
while (app.Busy)
{
htmldoc = (HtmlDocument)app.Document;
}
System.Threading.Thread.Sleep(2000);
System.Diagnostics.Process.Start(@"D:\Automation\EMI_Calculator.exe");
}
Import the Mortgage Prospect Excel List into the Automation form
private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
OpenFileDialog openFileDialog1 = new OpenFileDialog();
openFileDialog1.InitialDirectory = @"C:\";
openFileDialog1.Title = "Browse Text Files";
openFileDialog1.CheckFileExists = true;
openFileDialog1.CheckPathExists = true;
openFileDialog1.DefaultExt = "xlsx";
openFileDialog1.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
openFileDialog1.FilterIndex = 2;
openFileDialog1.RestoreDirectory = true;
openFileDialog1.ReadOnlyChecked = true;
openFileDialog1.ShowReadOnly = true;
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
textBox1.Text = openFileDialog1.FileName;
}
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.DataSet DtSet;
System.Data.OleDb.OleDbDataAdapter MyCommand;
MyConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='D:\Dell Drivers\custList.xlsx';Extended Properties=Excel 12.0;");
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
MyCommand.TableMappings.Add("Table", "Net-informations.com");
DtSet = new System.Data.DataSet();
MyCommand.Fill(DtSet);
dataGridView1.DataSource = DtSet.Tables[0];
MyConnection.Close();
}
}
Next step in the User Process is click on the selected customer to find out EMI of the Customer
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
var senderGrid = (DataGridView)sender;
if (senderGrid.Columns[e.ColumnIndex] is DataGridViewButtonColumn &&
e.RowIndex >= 0)
{
string selstrCustomerName= Convert.ToString(senderGrid[1, e.RowIndex].Value);
pasteNameIntoLeadGenerator(selstrCustomerName);
}
}
search for the Customer in Mortgage Lead Prospect Web Application and find out Customer Application Form and get the Loan Amount.
private void pasteNameIntoLeadGenerator(string selstrCustomerName)
{
var elems = wb.Document.GetElementByTagName("INPUT");
foreach(HtmlElement elem in elems){
if(elem.GetAttribute("name") == "txtCustomerName" && elem.GetAttribute("type") == "text"){
elem.setAttribute("value", selstrCustomerName)
}
}
System.Threading.Thread.Sleep(2000)
foreach(HtmlElement elem in elems){
if(elem.GetAttribute("type") == "button" && elem.GetAttribute("name") == "go"){
elem.InvokeMember("click");
}
}
System.Threading.Thread.Sleep(2000)
foreach(HtmlElement elem in elems){
if(elem.GetAttribute("name") == "txtLoanAmount" && elem.GetAttribute("type") == "text")
{
string LoanAmount=elem.getAttribute("value")
}
}
}
Finally the Automation will paste the Loan Amount in EMI Calculator with Interest Amount, Loan Term.
To paste values first we need identify EMI Calculator Screen and Spy ++ is used to obtain control Ids
[DllImport("user32.dll", EntryPoint="FindWindow", SetLastError = true)]
static extern IntPtr FindWindowByCaption(IntPtr ZeroOnly, string lpWindowName);
[DllImport("user32.dll", SetLastError = false)]
public static extern IntPtr GetDlgItem(IntPtr hDlg, int nIDDlgItem);
[DllImport("user32.dll", CharSet = CharSet.Auto, SetLastError = false)]
public static extern IntPtr SendMessage(HandleRef hWnd, uint Msg, IntPtr wParam, string lParam);
public const uint WM_SETTEXT = 0x000C;
private void InteropSetText(IntPtr iptrHWndDialog, int iControlID, string strTextToSet)
{
IntPtr iptrHWndControl = GetDlgItem(iptrHWndDialog, iControlID);
HandleRef hrefHWndTarget = new HandleRef(null, iptrHWndControl);
SendMessage(hrefHWndTarget, WM_SETTEXT, IntPtr.Zero, strTextToSet);
}
IntPtr handle = FindWindowByCaption(IntPtr.Zero, "EMI Calculator");
int decValueofControl = int.Parse(0001042A, System.Globalization.NumberStyles.HexNumber);
InteropSetText(handle, decValueofControl,LoanAmount);
Simillarly paste the values of Loan Term, Interest Rate from Automation Form to EMI Calculator
EMI Calculator calculates monthly EMI, Total Interest Payable and Total Payment in this case.
Finally We build our tactical automation solution to see if it reduces no of mouse clicks by agent resulting lesser operating cycles and optimization of “As is User Process”
Points of Interest
Basic steps or Principles in Automation Development remains same in any of the mentioned tools
- Interrrogation or Unique identification of the control of web or window or console based application
- Read and write the values of the controls and pasting between Applications
- Perform the required Manual Activites(usually performed by User) and the same has to be performed by Automation in the intended sequence.