Excel to testlink results upload

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.

<?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" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<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>

Excel to Testlink upload

Once you setup testlink, the first question comes in mind that how do I upload my existing test sheets into Excel. Tetslink 1.9.2 supports uploading excel sheets but before that you need to create few sample tests into testlink and then export it and then use the imported xsd to convert your excel sheet. For me, its quite cumbersome process and I wanted that we have some sort of upload functionality so that my QA team don't have to make much effort to upload their existing tests. I found a utility which is php based and supports excel upload by a simple web interface. the utility can be downloaded from http://code.google.com/p/testlink-1-9-xls-test-case-uploader/downloads/list

This utility was great but it was limited to few columns of excel but I needed additional columns plus some small modification in the existing column. With reference to their php, I modified it to support columns as per our requirement. Here is the modified version of their php.

This modified version of utility supports 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||

Few Notes:

Tests should be in the first sheet of the excel file First row will be ignored as it will have column headers Test Project name should exactly match with Testlink project name in which you want to upload your tests. Test Suite Title name should exactly match with Testlink test suite name in which you want to upload your tests. Test Case Title is the title which will be used to list test in the testlink tree Multiple keywords are supported (comma separated) Priority column may have values Low/Medium/High. If no value (or wrong value) is specified, test will be assigned Medium priority. Test Status, Test Plan, Build Name, Comments and User Name columns are ignored as they are used to update test results If test case title already exists for the given suite in testlink, new test will not be added, existing will be updated Utility has been tested on Testlink version 1.9.2/1.9.3 To Use this utility, you need to install original utility from above mentioned link and then replace their index.php with the below given php file

<?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];
    }
    /*echo '<pre>';
    print_r($values);*/
    $rownum = 0;
    $case_count = 0;
    $test_new = 0;
    $test_update = 0;
    $test_add_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]);
            
            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>\<br\>");                    
                    $test_add_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>\<br\>");
                $test_add_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>\<br\>");
                $test_add_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>\<br\>");
                $test_add_fail++;
                continue;
            }
            $res_project = mysql_fetch_assoc($query_project);
            $project_id = $res_project["id"];
            //echo " Project id is " . $project_id . "\<br\>" ;
            // Get Project id - 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;
            $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++;
                        $suite_id = $suites[id];
                    }
                }
            }
            
            if($count != 1 && $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>\<br\>");
                $test_add_fail++;
                continue;
            }            
            //echo " suite id is " . $suite_id . "\<br\>" ;
            // Get test Suite id - End
            
            $priority_str = $excel_tc_priority;
            $priority_id = 2;
            if (strcasecmp($priority_str,"Low") == 0) $priority_id = 1;
            if (strcasecmp($priority_str,"High") == 0) $priority_id = 3;
                
            $query_test = 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 = $suite_id AND NH_TCASE_PARENT.node_type_id = 2  ORDER BY TCV.version desc LIMIT 1");
            if (mysql_errno()) { 
                echo "MySQL error ".mysql_errno().": ".mysql_error()."\n\<br\>When selecting suite id\<br\>"; 
                $test_add_fail++;
                continue;
            }
            $res_test = mysql_fetch_array($query_test);
            
            if(count($res_test)>1)    
            {
                //Test case already exists in the test suite, then we need to just update it
                $testcase_id = $res_test["id"];
                $testcase_version_id = $res_test["tcversion_id"];                
                
                mysql_query("UPDATE tcversions SET importance = $priority_id, summary = '".mysql_real_escape_string(str_replace("\n", "<p>", $excel_tc_summary))."', preconditions = '".mysql_real_escape_string(str_replace("\n", "<p>", $excel_pre_condition))."' WHERE id = $testcase_version_id");
                if (mysql_errno()) { 
                    echo "MySQL error ".mysql_errno().": ".mysql_error()."\n\<br\>When UPDATE tcversions for row $rownum ...\<br\>"; 
                    $test_add_fail++;
                    continue;
                }
                
                if($excel_tc_step||$excel_tc_result)
                {
                    $query_test_step_parent = mysql_query("SELECT TCSTEPS.id, MAX(TCSTEPS.step_number) AS max_step FROM tcsteps TCSTEPS JOIN nodes_hierarchy NH_STEPS ON NH_STEPS.id = TCSTEPS.id WHERE NH_STEPS.parent_id=$testcase_version_id");
                    if (mysql_errno()) { 
                        echo "MySQL error ".mysql_errno().": ".mysql_error()."\n\<br\>When select test case steps for row $rownum ...\<br\>";            
                    }
                    else {
                        $res_test_step_parent = mysql_fetch_array($query_test_step_parent);
                        mysql_query("UPDATE tcsteps SET actions = '".mysql_real_escape_string(str_replace("\n", "<p>", $excel_tc_step))."', expected_results = '".mysql_real_escape_string(str_replace("\n", "<p>", $excel_tc_result))."' WHERE id = $res_test_step_parent[id]");
                        if (mysql_errno()) { 
                            echo "MySQL error ".mysql_errno().": ".mysql_error()."\n\<br\>When UPDATE test case steps for row $rownum ...\<br\>"; 
                        }
                    }
                }
                
                if($excel_tc_keywords)
                {
                    //First remove all existing keywords of this test and then add again
                    mysql_query("DELETE from testcase_keywords where testcase_id = $testcase_id");
                    $keywords = explode(',',mysql_real_escape_string($excel_tc_keywords));
                    foreach ($keywords as $keyword) {
                        $key = trim($keyword);
                        $query_keyword = mysql_query("SELECT id FROM keywords WHERE keyword='$key'");
                        $res_keyword = mysql_fetch_assoc($query_keyword);
                        $keyword_id = $res_keyword["id"];
                        if($keyword_id){
                            mysql_query("INSERT INTO testcase_keywords (testcase_id, keyword_id) VALUES ($testcase_id,$keyword_id)");
                        }
                    }
                }
                $test_update++;
            }
            else  { 
                //This is a new test and we need to add it in test suite
                //*** Start Transaction ***//  
                mysql_query("BEGIN"); 
                $objQuery1 = mysql_query("INSERT INTO nodes_hierarchy (parent_id,node_type_id,node_order,name) VALUES ($suite_id,3,100,'".mysql_real_escape_string($excel_tc_title)."')");
                $case_id = mysql_insert_id();
                $objQuery2 = mysql_query("INSERT INTO nodes_hierarchy (parent_id,node_type_id,node_order) VALUES ($case_id,4,0)"); 
                $case_des_id = mysql_insert_id();
                
                $objQuery3 = mysql_query("INSERT INTO tcversions (id,importance,summary,preconditions) VALUES ($case_des_id, $priority_id,'".mysql_real_escape_string(str_replace("\n", "<p>", $excel_tc_summary))."','".mysql_real_escape_string(str_replace("\n", "<p>", $excel_pre_condition))."')");
                
                if(($objQuery1) and ($objQuery2) and ($objQuery3))  {  
                    //*** Commit Transaction ***//  
                    mysql_query("COMMIT");  
                }  
                else  {  
                    //*** RollBack Transaction ***//  
                    mysql_query("ROLLBACK");  
                    echo "ERROR :: Rollbacked transaction of new tests for row $rownum ...";
                    $test_add_fail++;
                    continue;
                }  
                                                        
                if($excel_tc_step||$excel_tc_result)
                {
                    //*** Start Transaction ***//  
                    mysql_query("BEGIN");
                    $objQuery1 = mysql_query("INSERT INTO nodes_hierarchy (parent_id,node_type_id,node_order) VALUES ($case_des_id,9,0)");
                    $case_step_id = mysql_insert_id(); 
                    $objQuery2 = mysql_query("INSERT INTO tcsteps (id,actions,expected_results) VALUES ($case_step_id,'".mysql_real_escape_string(str_replace("\n", "<p>", $excel_tc_step))."','".mysql_real_escape_string(str_replace("\n", "<p>", $excel_tc_result))."')");
                    if(($objQuery1) and ($objQuery2))  
                    {  
                        //*** Commit Transaction ***//  
                        mysql_query("COMMIT");  
                    }  
                    else  
                    {  
                        //*** RollBack Transaction ***//  
                        mysql_query("ROLLBACK");  
                        echo "ERROR :: Rollbacked transaction of test step updation for row $rownum ...";                        
                    } 
                }
                
                if($excel_tc_keywords)
                {
                    $keywords = explode(',',mysql_real_escape_string($excel_tc_keywords));
                    foreach ($keywords as $keyword) {
                        $key = trim($keyword);
                        $query_keyword = mysql_query("SELECT id FROM keywords WHERE keyword='$key'");
                        $res_keyword = mysql_fetch_assoc($query_keyword);
                        $keyword_id = $res_keyword["id"];
                        if($keyword_id){
                            mysql_query("INSERT INTO testcase_keywords (testcase_id, keyword_id) VALUES ($case_id,$keyword_id)");
                        }
                    }
                }
            $test_new++;
            }
        }
    }    
    $case_count = $test_new + $test_update;    
    echo "<font color=#993300>Total <b>$case_count</b> test cases updated successfully.</font>\<br\>";
    echo "<font color=#0000ff>New tests : <b>$test_new</b></font>\<br\>";
    echo "<font color=#0000ff>Tests updated : <b>$test_update</b> </font>\<br\>";
    echo "<font color=#0000ff>Tests update failed: <b>$test_add_fail</b> </font>\<br\>";
}
?>
<!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 XLS Uploader</title>
</head>
<body>
<form name="frm" action="" method="post" enctype="multipart/form-data">

<table>
<tr><td>Use this page to import test cases 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, Test Plan, Build Name, Comments and User Name columns are ignored as they are used to update test results</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 XLS Uploader:</b></td>
</tr>
<tr>
    <td><input type="file" name="xls_file" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<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>

Setup Test case management tool - TestLink

These days I am working on introducing TestLink as test case management tool. Tetslink have few software pre-requisites, here is the step by step guide to setup TestLink for your team.

1. Download and install Apache Web Server
2. Download and install PHP
3. Configure Apache
4. Download and Install MySQL server
5. Download and install TestLink
6. Configure TestLink

Detailed installation instruction for step 1-4 can be found @ http://webdevcodex.com/tutorial-installing-apache2-php5-mysql5-phpmyadmin3-windows-7-vista/

Download and unzip testlink in apache web directory and open http://localhost/Tetslink. If it is the first time installation it will open installation page. Follow the instructions and Testlnk is installed.

Once you open the testlink login page or login, you might get following error:

"PHP Warning: strftime(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function"

To resolve this issue, edit <php install dir>\php.ini and add "date.timezone = PRC"

Sometimes you also get warning message in UI, to eliminate those warning message from UI

make change in <Testlink install dir>\config.inc.php and change "$tlCfg->config_check_warning_mode = 'SILENT';"

Testplan plugin in Confluence WIKI

Recently in our organization, we started to use Confluence WIKI for documentation purpose. This wiki has a plugin called TestPlan. I liked this plugin very much as it allows us to maintain our test cases and get the test case execution status.
Below is the screenshot of sample testplan:





Below is the wiki markup for above sample:
{testplan:Release includeJiraLinks=false useFailMacro=false}
^Feature1
//This is comment
Test1
Test2
^Feature2
//This is comment
Test3
Test4
{testplan}

Finding bugs in the application

A QA engineer always thinks of a bug in the application and always try to find them. Finding bugs in any application is really a challenging work and if you find bugs, it gives you the satisfaction of your job. Below are few tips by which you can find bugs in the application:


1. Understand the application/module in depth before starting the testing
2. Prepare good functional test cases before starting the testing.
3. Create all possible type of test data before testing.
4. Test the system in different environments.
5. When you tired or bored by testing same module or application, do MONKEY TESTING, really it works. I tried it many times and got good bugs :)
6. Try some standard test cases for which you found the bugs in some different application.
7. Test the application with dedication and passion.
8. Think alike of different users while testing the application.


Doing QA is like being a goalkeeper. You can make a hundred brilliant saves but the only shot that people remember is the one that gets past you.

Testing Links

http://googletesting.blogspot.com/

http://linuxpoison.wordpress.com/2007/03/12/software-testing-faq/

http://softwaretestingguide.blogspot.com/

http://quality-assurance-software-testing.blogspot.com/

http://techiecorner.blogspot.com/2005/12/software-testing-interview-questions.html

http://en.wikipedia.org/wiki/Software_testing

http://www.sqablogs.com/jstrazzere/908/

http://www.softwareqatest.com/

http://sqa.fyicenter.com/art/index.html

http://www.software-testing.com/web/Testing_resources_Center.html

http://www.allinterview.com

http://onestoptesting.com

Testing FAQs1

Differentiate between gmail and yahoomail?
Before current yahoo beta version was launched, gmail was only one to use AJAX.

What will you do if you are trying to install a software and its not getting installed
if something is not working then we do google to find solution for things that normally would take more time if done through normal procedure.

How do you solve in case you find a Bug in the telephone?
First I will get in touch with the Customer who made the complain and send engineer to test in case the connection is proper and also check in case the instrument is working fine . In case instrument is not working fine it will be replaced else it will be rectified in the telephone exchange from which the line was given to the customer

How do you raise a bug in case in yahoo conference call you are not able to view messages delivered by the other person at your end?
Take the screen shot of the messages screen of the other person where he is sending messages and also the screen shot of my end where I am not able to view those messages and describe the situation to the developer for resolution of the problem

What are the different types of testing that can be done on an application?
Black box covering functional and other types of testing
White Box testing checking for logic and conditional loops
Gray box testing in case HTML and other technologies are involved in application

What is the purpose of using java script in the application?
Web pages have several input boxes we use Java script to write validations and other functions

How will you test a lock?

• Lock should open only with its key. Test with selected samples of key (local made ,same company, same make but different key, other brands, carved keys, master key )
• Should open smoothly showing no signs of stiffness or rough/wear tear with intensive/stressing use of Lock. Concept is Locks interior surface detoriate /oxidize with passage of time so locks should have resistant surface.
• Lock should have standards displayed like number of levers and tested benchmarks/symbols specified on lock.
• Lock should be robust, subjected to force or massive hit should not get deformed or detoriate from its working condition.

How will you test a browser?

• URL should retrieve home page of given website.
• Should support Http and Https protocols
• Should directly retrieve response if input variable is given in URL (like URL?variable_name=input typed in address bar)
• Should display html, images.
• Should prompt the user for ActiveX controls installations if required for correct functioning of web page.
• Functionality for Stop, Refresh , Favourites, and other options.
• Should allow for saving of page on edit (File options)
• Should work equally for both domain names and IPs

How to test outlook mail (specially send-receive functionality)

• To test based on configuration to receive mails from mail server.
• System should check for new mails in the mail server for the user’s account

1. to test user authentication and authorization as per definition.
2. For example - Test to confirm system should d/l only for the specific user’s a/c etc

• System should follow filters / rules defined during download the mails to local system.

How to test Calculator application of windows.

• Launch the application as per configuration and requirement.
• Test different types of numbers involved in calculation like

1. +ve/-ve integers, decimals, no of digits etc in details
2. using boundary value analysis
3. How the application is capable to handle continues combination of calculations

• How the application is capable to handle errors, for example “overflow of values”
• How the application is capable to accept and react different Input methods (keyboard/mouse).