Location: PHPKode > projects > Druid Defect Control > defectcontrol_0.9.4/tts/storedqueries_data.php
<?php 

require_once('usercheck.php');


/*
 * some definitions for later use
 */
$weekAgo = date("Y/m/d", time() - 7 * 24*3600);
$monthAgo = date("Y/m/d", time() - 30 * 24*3600);


/*
 * Query text is rendered 'as is', so you could include html tags in the captions
 */
$storedQueries = array ( 

    "standard" => array (
        "Captions" => array (
            1 => "All defects by submission date",
            2 => "All assigned defects by submission date",
            10 => "All unassigned defects by submission date",
            3 => "All open defects by submission date",
            4 => "All new defects by submission date",
            'separator1' => "&nbsp;",
            8 => "All my assigned defects (assigned to $ddts_username, any state)",
            9 => "My open defects",
            'separator2' => "&nbsp;",
            11 => "My pending defects by priority (assigned or open to $ddts_username)",
        ),
        
        "Queries" => array (
            1 => "SELECT d.iid as defectid, sheadline as headline, ddetectdate as detectiondate, s.sname as status, p.sname as project FROM defect d, defectstatus s, project p WHERE d.iiddefectstatus = s.iid  AND d.iidproject = p.iid ORDER BY ddetectdate",
            2 => "SELECT d.iid as defectid, sheadline as headline, d.ddetectdate as detectiondate, u.sname as assigned, s.sname as status, p.sname as project FROM defect d, defectstatus s, users u, project p WHERE d.iiddefectstatus = s.iid  AND d.iidproject = p.iid AND d.iidassigned = u.iid ORDER BY ddetectdate",
            3 => "SELECT d.iid as defectid, sheadline as headline, d.ddetectdate as detectiondate, u.sname as assigned, s.sname as status, p.sname as project FROM defect d, defectstatus s, users u, project p WHERE d.iiddefectstatus = s.iid  AND d.iidproject = p.iid AND d.iidassigned = u.iid AND s.sname = 'Open' ORDER BY ddetectdate",
            4 => "SELECT d.iid as defectid, sheadline as headline, d.ddetectdate as detectiondate, s.sname  as status, p.sname as project FROM defect d, defectstatus s, project p WHERE d.iiddefectstatus = s.iid  AND d.iidproject = p.iid AND s.sname = 'New' ORDER BY ddetectdate",
            8 => "SELECT d.iid as defectid, sheadline as headline, s.sname  as status, p.sname as project FROM defect d, defectstatus s, users u, project p WHERE d.iiddefectstatus = s.iid AND d.iidassigned = u.iid  AND d.iidproject = p.iid AND u.sname = '$ddts_username' ORDER BY s.sname",
            9 => "SELECT d.iid as defectid, sheadline as headline, s.sname  as status, p.sname as project FROM defect d, defectstatus s, users u, project p WHERE d.iiddefectstatus = s.iid AND d.iidassigned = u.iid AND s.sname = 'Open'  AND d.iidproject = p.iid AND u.sname = '$ddts_username' ORDER BY ddetectdate",
            10 => "SELECT d.iid as defectid, sheadline as headline, d.ddetectdate as detectdate, s.sname  as status, p.sname as project FROM defect d, defectstatus s, project p WHERE d.iiddefectstatus = s.iid  AND d.iidproject = p.iid AND d.iidassigned = NULL ORDER BY s.sname",
            11 => "SELECT d.iid as defectid, sheadline as headline, p.sname as priority, s.sname as status, t.sname as type, pr.sname as project FROM defect d, defectstatus s, priority p, defecttype t, users u, project pr WHERE d.iiddefectstatus = s.iid AND d.iidpriority = p.iid AND d.iiddefecttype = t.iid AND d.iidassigned = u.iid AND d.iidproject = pr.iid AND u.sname = '$ddts_username' AND (s.sname = 'Open' OR s.sname = 'Assigned') ORDER BY p.iid DESC",
        )
    ),
    
    /*        <li><font size="2">Total entries submitted per engineer</font></li>
              <li><font color="#FF0000" size="2">Total entries resolved per engineer</font></li>
              <li><font color="#FF0000" size="2">Total resolution hours per engineer<font size="1"> 
              <li><font size="2">Total resolution hours per month</font></li>
     */
    
    "management" => array (
        "Captions" => array (
            1 => "Detail of all pending defects",
            2 => "Total pending defects last month (from $monthAgo)",
            3 => "Total pending defects last week (from $weekAgo)",
            4 => "Total solved defects",
            5 => "Total solved defects last month (from $monthAgo)",
            6 => "Total solved defects last week (from $weekAgo)",
            'separator1' => "&nbsp;",
            7 => "All pending defects from last month (from $monthAgo)",
            8 => "All solved defects last month (from $monthAgo)",
            'separator2' => "&nbsp;",
            9 => "Solved but not verified defects",
            
        ),
        
        "Queries" => array (
            1 => "SELECT d.iid as defectid, d.sheadline as headline, p.sname as project, s.sname as software FROM defect d, project p, software s  WHERE d.iidsoftware = s.iid AND d.iidproject = p.iid AND d.iiddefectstatus < 3 ORDER BY d.iid",
            2 => "SELECT p.sname as project, s.sname as software, COUNT(d.iid) as total FROM defect d, project p, software s WHERE d.iidsoftware = s.iid AND d.iidproject = p.iid AND d.iiddefectstatus < 3 AND d.ddetectdate >= '$monthAgo' GROUP by p.sname, s.sname",
            3 => "SELECT p.sname as project, s.sname as software, COUNT(d.iid) as total FROM defect d, project p, software s WHERE d.iidsoftware = s.iid AND d.iidproject = p.iid AND d.iiddefectstatus < 3 AND d.ddetectdate >= '$weekAgo' GROUP by p.sname, s.sname",
            4 => "SELECT p.sname as project, s.sname as software, COUNT(d.iid) as total FROM defect d, project p, software s WHERE d.iidsoftware = s.iid AND d.iidproject = p.iid AND d.iiddefectstatus BETWEEN 3 AND 4 GROUP by p.sname, s.sname",
            5 => "SELECT p.sname as project, s.sname as software, COUNT(d.iid) as total FROM defect d, project p, software s WHERE d.iidsoftware = s.iid AND d.iidproject = p.iid AND d.iiddefectstatus BETWEEN 3 AND 4 AND d.dresolutiondate >= '$monthAgo' GROUP by p.sname, s.sname",
            6 => "SELECT p.sname as project, s.sname as software, COUNT(d.iid) as total FROM defect d, project p, software s WHERE d.iidsoftware = s.iid AND d.iidproject = p.iid AND d.iiddefectstatus BETWEEN 3 and 4 AND d.dresolutiondate >= '$weekAgo' GROUP by p.sname, s.sname",
            7 => "SELECT d.iid as defectid, d.sheadline as headline, p.sname as project, s.sname as software FROM defect d, project p, software s WHERE d.iidsoftware = s.iid AND d.iidproject = p.iid AND d.iiddefectstatus < 3 AND d.ddetectdate >= '$monthAgo'  ORDER BY d.iid",
            8 => "SELECT d.iid as defectid, d.sheadline as headline, d.dresolutiondate as resolved, p.sname as project, s.sname as software FROM defect d, project p, software s WHERE d.iidsoftware = s.iid AND d.iidproject = p.iid AND d.iiddefectstatus BETWEEN 3 AND 4 AND d.ddetectdate >= '$monthAgo'  ORDER BY d.iid",
            9 => "SELECT d.iid as defectid, d.sheadline as headline, d.dresolutiondate as resolved, p.sname as project, s.sname as software FROM defect d, project p, software s WHERE d.iidsoftware = s.iid AND d.iidproject = p.iid AND d.iiddefectstatus = 3  ORDER BY d.iid",

        )
    )
    
);

?>
Return current item: Druid Defect Control