In my previous post I described about a utility which uploads my tests into Testlink. Once my tests are uploaded, I need to execute them, right.. ? But executing tests one by one from Testlink UI is quite slow process and somehow degrades productivity of the QA person. (as compare to when we use excel sheet for test cases tracking and mark Pass/Fail in the excel sheet itself, of which my team is habitual).
To overcome this, I wrote another utility which uploads tests results from excel into Testlink and your lead/manager can access reports from testlink directly. Excel should have following columns:
||Project Name||Test Suite Title||Test Case Title||Test Case Summary||Pre conditions||Step Actions||Expected Results||Priority||Keywords||TestPlan Name||Build Name||Comments||testlink login name||
1. Test Status may have values (PASS, FAIL, BLOCKED). Any other value will update test status as 'Not Run'.
2. Test Plan name should exactly match with as in Testlink
3. Build name should exactly match with as in Testlink
4. Comments can be used to specify bug number
5. User name should be testlink login name
So the whole process of uploading tests and their results goes as below:
1. QA creates tests in above mentioned column format (Project Name, suite name, status, testplan name, qa build name, comments, user name can left blank for now).
2. Create a test suite in Testlink
3. Update testlink project name and test suite name in above excel.
4. Upload excel from php of my previous post
5. Create a test plan (e.g. Release X.0) and QA Build (eg. QA1) in testlink
6. Once you execute your tests, update the excel with test status, testplan name, qa build name, comments , user name (testlink login name)
7. Upload the excel from php of my this post (see below)
8. That’s it, now anyone can access test reports from testlink UI.
To overcome this, I wrote another utility which uploads tests results from excel into Testlink and your lead/manager can access reports from testlink directly. Excel should have following columns:
||Project Name||Test Suite Title||Test Case Title||Test Case Summary||Pre conditions||Step Actions||Expected Results||Priority||Keywords||TestPlan Name||Build Name||Comments||testlink login name||
1. Test Status may have values (PASS, FAIL, BLOCKED). Any other value will update test status as 'Not Run'.
2. Test Plan name should exactly match with as in Testlink
3. Build name should exactly match with as in Testlink
4. Comments can be used to specify bug number
5. User name should be testlink login name
So the whole process of uploading tests and their results goes as below:
1. QA creates tests in above mentioned column format (Project Name, suite name, status, testplan name, qa build name, comments, user name can left blank for now).
2. Create a test suite in Testlink
3. Update testlink project name and test suite name in above excel.
4. Upload excel from php of my previous post
5. Create a test plan (e.g. Release X.0) and QA Build (eg. QA1) in testlink
6. Once you execute your tests, update the excel with test status, testplan name, qa build name, comments , user name (testlink login name)
7. Upload the excel from php of my this post (see below)
8. That’s it, now anyone can access test reports from testlink UI.
<?php
// Test CVS
// Vishnu Agrawal
ini_set("display_errors", "1");
error_reporting(E_ALL ^ E_NOTICE);
date_default_timezone_set('America/Los_Angeles');
if($_FILES['xls_file']['tmp_name'])
{
move_uploaded_file($_FILES['xls_file']['tmp_name'],'uploads/'.$_FILES['xls_file']['name']);
$xls_file = 'uploads/'.$_FILES['xls_file']['name'];
include('../config_db.inc.php');
mysql_connect(DB_HOST,DB_USER,DB_PASS);
mysql_select_db(DB_NAME);
require_once 'Excel/reader.php';
// ExcelFile($filename, $encoding);
$data = new Spreadsheet_Excel_Reader();
// Set output Encoding.
$data->setOutputEncoding('CP1251');
/***
* if you want you can change 'iconv' to mb_convert_encoding:
* $data->setUTFEncoder('mb');
*
**/
/***
* By default rows & cols indeces start with 1
* For change initial index use:
* $data->setRowColOffset(0);
*
**/
/***
* Some function for formatting output.
* $data->setDefaultFormat('%.2f');
* setDefaultFormat - set format for columns with unknown formatting
*
* $data->setColumnFormat(4, '%.3f');
* setColumnFormat - set format for column (apply only to number fields)
*
**/
$data->read($xls_file);
/***
$data->sheets[0]['numRows'] - count rows
$data->sheets[0]['numCols'] - count columns
$data->sheets[0]['cells'][$i][$j] - data from $i-row $j-column
$data->sheets[0]['cellsInfo'][$i][$j] - extended info about cell
$data->sheets[0]['cellsInfo'][$i][$j]['type'] = "date" | "number" | "unknown"
if 'type' == "unknown" - use 'raw' value, because cell contain value with format '0.00';
$data->sheets[0]['cellsInfo'][$i][$j]['raw'] = value if cell without format
$data->sheets[0]['cellsInfo'][$i][$j]['colspan']
$data->sheets[0]['cellsInfo'][$i][$j]['rowspan']
**/
$values = '';
for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
$values[] = $data->sheets[0]['cells'][$i];
}
$rownum = 0;
$test_update = 0;
$test_update_existing = 0;
$test_update_fail = 0;
foreach ($values as $colnum)
{
$rownum++;
if($rownum > 1)
{
$excel_project_name = trim($colnum[1]);
$excel_suite_name = trim($colnum[2]);
$excel_tc_title = trim($colnum[3]);
$excel_tc_summary = trim($colnum[4]);
$excel_pre_condition = trim($colnum[5]);
$excel_tc_step = trim($colnum[6]);
$excel_tc_result = trim($colnum[7]);
$excel_tc_priority = trim($colnum[8]);
$excel_tc_keywords = trim($colnum[9]);
$excel_tc_test_result = trim($colnum[10]);
$excel_tc_testplan = trim($colnum[11]);
$excel_tc_build = trim($colnum[12]);
$excel_tc_comment = trim($colnum[13]);
$excel_tc_username = trim($colnum[14]);
if (!$excel_project_name) {
if($excel_suite_name && $excel_tc_title) {
echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: Testlink project name is not Specified</font>\");
$test_update_fail++;
}
continue;
}
if (!$excel_suite_name) {
echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: Testlink Suite name is not Specified</font>\");
$test_update_fail++;
continue;
}
if (!$excel_tc_title) {
echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: Testlink tese case title is not Specified</font>\");
$test_update_fail++;
continue;
}
if (!$excel_tc_testplan) {
echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: Testlink test plan name is not Specified</font>\");
$test_update_fail++;
continue;
}
if (!$excel_tc_build) {
echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: Testlink test build name is not Specified</font>\");
$test_update_fail++;
continue;
}
// Get Project id - Start
$query_project = mysql_query("SELECT testprojects.id, nodes_hierarchy.name FROM testprojects, nodes_hierarchy WHERE testprojects.id = nodes_hierarchy.id AND nodes_hierarchy.name='$excel_project_name'");
if(mysql_num_rows($query_project) != 1) {
echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: " . mysql_num_rows($query_project) . " test project found with name <b>$excel_project_name</b></font>\");
$test_update_fail++;
continue;
}
$res_project = mysql_fetch_assoc($query_project);
$project_id = $res_project["id"];
//echo " Project id is " . $project_id . "\" ;
// Get Project id - End
// Get testplan id - Start
$query_testplan = mysql_query("SELECT testplans.*, NH.name FROM testplans testplans, nodes_hierarchy NH WHERE testplans.id=NH.id AND NH.name = '$excel_tc_testplan' AND NH.parent_id=$project_id");
if(mysql_num_rows($query_testplan) != 1) {
echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: " . mysql_num_rows($query_testplan) . " test project found with name <b>$excel_tc_testplan</b></font>\");
$test_update_fail++;
continue;
}
$res_testplan = mysql_fetch_assoc($query_testplan);
$testplan_id = $res_testplan["id"];
//echo " testplan id is " . $testplan_id . "\" ;
// Get testplan id - End
// Get build id - Start
$query_build = mysql_query("SELECT builds.id, builds.name, builds.notes FROM builds builds WHERE builds.testplan_id = $testplan_id AND builds.name= '$excel_tc_build' AND builds.active = 1 AND builds.is_open = 1");
if(mysql_num_rows($query_build) != 1) {
echo "<font color=#ff0000>Test result upload for test row $rownum failed. REASON: " . mysql_num_rows($query_build) . " build found with name <b>$excel_tc_build</b>. Either build name do not exists or build is not active/open</font>\";
$test_update_fail++;
continue;
}
$res_build = mysql_fetch_assoc($query_build);
$build_id = $res_build["id"];
//echo " build id is " . $build_id . "\" ;
// Get build id - End
// Get tester id - Start
$query_tester = mysql_query("SELECT * from users where login = '$excel_tc_username'");
if(mysql_num_rows($query_tester) != 1) {
echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: " . mysql_num_rows($query_tester) ." Users with name <b>$excel_tc_username</b></font>\");
$test_update_fail++;
continue;
}
$res_tester = mysql_fetch_assoc($query_tester);
$tester_id = $res_tester["id"];
//echo " tester id is " . $tester_id . "\" ;
// Get tester id - End
// Get test case status - Start
if (strcasecmp($excel_tc_test_result, 'PASS') == 0) {
$test_status = 'p';
}
elseif(strcasecmp($excel_tc_test_result, 'FAIL') == 0) {
$test_status = 'f';
}
elseif(strcasecmp($excel_tc_test_result, 'BLOCKED') == 0) {
$test_status = 'b';
}
else {
echo ("<font color=#ff0000>Incorrect test result <b>$excel_tc_test_result</b> in row $rownum</font>\");
$test_status = 'n';
}
// Get test case status - End
// Get test Suite id - Start
$query_testsuitenames = mysql_query("SELECT TS.*, NH.name, NH.parent_id FROM testsuites TS JOIN nodes_hierarchy NH ON NH.id = TS.id WHERE NH.name = '$excel_suite_name'");
$count = 0;
$test_suite_id = 0;
while($suites = mysql_fetch_assoc($query_testsuitenames)) {
$query_get_project = mysql_query("SELECT _id FROM (SELECT @r AS _id, (SELECT @r := parent_id FROM nodes_hierarchy WHERE id = _id) AS parent FROM ( SELECT @r := $suites[id] ) vars, nodes_hierarchy h WHERE @r IS NOT NULL) AS t WHERE t.parent IS NULL");
$res_get_project = mysql_fetch_assoc($query_get_project);
if(count($res_get_project) == 1) {
if($res_get_project[_id] == $project_id) {
$count++;
$test_suite_id = $suites[id];
}
}
}
if($count != 1 && $test_suite_id ==0) {
echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: " . $count . " suite name found with name <b>$excel_suite_name</b> </font>\");
$test_update_fail++;
continue;
}
//echo " suite id is " . $test_suite_id . "\" ;
// Get test Suite id - End
//Get test case details - Start
$query_testcase = mysql_query("SELECT DISTINCT NH_TCASE.id,NH_TCASE.name,NH_TCASE_PARENT.id AS parent_id, NH_TCASE_PARENT.name AS tsuite_name, TCV.version, TCV.tc_external_id, TCV.id AS tcversion_id FROM nodes_hierarchy NH_TCASE, nodes_hierarchy NH_TCASE_PARENT, nodes_hierarchy NH_TCVERSIONS, tcversions TCV WHERE NH_TCASE.node_type_id = 3 AND NH_TCASE.name = '$excel_tc_title' AND TCV.id=NH_TCVERSIONS.id AND NH_TCVERSIONS.parent_id=NH_TCASE.id AND NH_TCASE_PARENT.id=NH_TCASE.parent_id AND NH_TCASE_PARENT.id = $test_suite_id AND NH_TCASE_PARENT.node_type_id = 2 ORDER BY TCV.version desc LIMIT 1");
if(mysql_num_rows($query_testcase) != 1) {
echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: " . mysql_num_rows($query_testcase) . " test cases found with name <b>$excel_tc_title</b></font>\");
$test_update_fail++;
continue;
}
$res_testcase = mysql_fetch_assoc($query_testcase);
$testcase_id = $res_testcase["id"];
$testcase_version = $res_testcase["version"];
$testcase_version_id = $res_testcase["tcversion_id"];
//echo "Test case details " . $testcase_id . "<>" . $testcase_version . "<>" . $testcase_version_id . "\";
//Get test case details - End
$test_notes = $excel_tc_comment;
$query_tcversion = mysql_query("SELECT * from testplan_tcversions where testplan_id = $testplan_id AND tcversion_id = $testcase_version_id");
if (mysql_errno()) {
echo "MySQL error ".mysql_errno().": ".mysql_error()."\n\When selecting data from testplan_tcversions\";
$test_update_fail++;
continue;
}
//Check if test case is already linked with test plan, we don't need to update the table again
if(mysql_num_rows($query_tcversion) == 0) {
mysql_query("BEGIN");
$objQuery1 = mysql_query("INSERT INTO testplan_tcversions(testplan_id, tcversion_id, author_id) VALUES($testplan_id, $testcase_version_id, $tester_id)");
$case_id = mysql_insert_id();
$objQuery2 = mysql_query("INSERT INTO user_assignments(feature_id, user_id, build_id, assigner_id) VALUES($case_id, $tester_id, $build_id, $tester_id)");
if(($objQuery1) and ($objQuery2)) {
//*** Commit Transaction ***//
mysql_query("COMMIT");
}
else {
//*** RollBack Transaction ***//
mysql_query("ROLLBACK");
echo "ERROR :: Rollbacked transaction of updating user assignment for row $rownum ...";
$test_update_fail++;
continue;
}
}
// Convert current Timestamp into a string, safe for MySql
$currentTSExecution = date("Y-m-d H:i:s", time());
if (strcasecmp($test_status, 'n') != 0) { //update test status only if test status is not 'n'
$query_executions = mysql_query("SELECT * from executions WHERE build_id = $build_id AND testplan_id = $testplan_id AND tcversion_id = $testcase_version_id AND tcversion_number = $testcase_version");
if (mysql_errno()) {
echo "MySQL error ".mysql_errno().": ".mysql_error()."\n\When selecting data from executions\";
$test_update_fail++;
continue;
}
//Check if test case is not already updated in executions, then we need to add a new entry otherwise update existing entry
if(mysql_num_rows($query_executions) == 0) {
mysql_query("INSERT INTO executions(build_id, tester_id, execution_ts, status, testplan_id, tcversion_id, tcversion_number, notes) VALUES($build_id, $tester_id, '$currentTSExecution', '$test_status', $testplan_id, $testcase_version_id, $testcase_version, '$test_notes')");
if (mysql_errno()) {
echo "MySQL error ".mysql_errno().": ".mysql_error()."\n\When insertng data into executions\";
$test_update_fail++;
continue;
}
}
elseif(mysql_num_rows($query_executions) == 1) {
mysql_query("UPDATE executions SET tester_id = $tester_id, execution_ts = '$currentTSExecution', status = '$test_status', notes = '$test_notes' WHERE build_id = $build_id AND testplan_id = $testplan_id AND tcversion_id = $testcase_version_id AND tcversion_number = $testcase_version");
if (mysql_errno()) {
echo "MySQL error ".mysql_errno().": ".mysql_error()."\n\When updating data into executions\";
$test_update_fail++;
continue;
}
$test_update_existing++;
$test_update--;
}
else {
echo ("<font color=#ff0000>Test result upload for test row $rownum failed. REASON: " . mysql_num_rows($query_executions) . " execution status found ...</font>\");
$test_update_fail++;
continue;
}
}
$test_update++;
}
}
echo "<font color=#0000ff>Tests updated (New): \$test_update</b> </font>\";
echo "<font color=#0000ff>Tests updated (Existing): <b>$test_update_existing</b> </font>\";
echo "<font color=#0000ff>Tests update Failed: <b>$test_update_fail</b> </font>\";
}
?>
<!DOCTYPE unspecified PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Testlink Testcase Results Uploader</title>
</head>
<body>
<form name="frm" action="" method="post" enctype="multipart/form-data">
<table>
<tr><td>Use this page to upload test results from Excel sheet to Testlink, Excel file should have following columns.</td></tr>
</table>
<table border="1">
<tr>
<th>Test Project Name from Testlink</th>
<th>Test Suite Name from Testlink</th>
<th>Test Case Title</th>
<th>Test Case Summary</th>
<th>Pre Conditions</th>
<th>Step Actions</th>
<th>Expected Results</th>
<th>Priority</th>
<th>Keywords</th>
<th>Test Status</th>
<th>Testplan Name from Testlink</th>
<th>Build Name from Testlink</th>
<th>Comments (can be used to specify Bug#)</th>
<th>Username (Testlink login name)</th>
</tr>
</table>
<p>
<table>
<tr>
<th align="left">NOTE:</th>
</tr>
<tr>
<td>
<ol>
<li>Tests should be in the first sheet of the excel file</li>
<li>First row will be ignored as it will have column headers</li>
<li>Test Project name should exactly match with Testlink project name in which you want to upload your tests. </li>
<li>Test Suite Title name should exactly match with Testlink test suite name in which you want to upload your tests. </li>
<li>Test Case Title is the title which will be used to list test in the testlink tree</li>
<li>Multiple keywords are supported (comma separated)</li>
<li>Priority column may have values Low/Medium/High. If no value (or wrong value) is specified, test will be assigned Medium priority.</li>
<li>Test Status may have values (PASS, FAIL, BLOCKED). Any other value will update test status as 'Not Run'.</li>
<li>Test Plan name should exactly match with as in Testlink</li>
<li>Build name should exactly match with as in Testlink</li>
<li>Comments can be used to specify bug number</li>
<li>User name should be testlink login name</li>
<li>If test case title already exists for the given suite in testlink, new test will not be added, existing will be updated</li>
</ol>
</td>
</tr>
</table>
<table>
<tr>
<td><b>Testlink Test Result Uploader:</b></td>
</tr>
<tr>
<td><input type="file" name="xls_file" /> <a href="pub_dashboard.xls">Sample file format for testcase</a> </td>
</tr>
<tr>
<td><input type="submit" name="submit" value="submit" /> </td>
</tr>
<tr>
<td></td>
</tr>
</table>
</form>
</body>
</html>
Posts
