MANAGEMENT DASHBOARD - OPPORTUNITIES
1. Sales tracking
{source}<?php
defined('_JEXEC') or die('Restricted Access');
$option = array(); //prevent problems
$option['driver'] = 'mysql'; // Database driver name
$option['host'] = 'localhost'; // Database host name
$option['user'] = 'readOnly'; // User for database authentication
$option['password'] = 'kDvgI4cC4oING4go'; // Password for database authentication
$option['database'] = 'suitecrm'; // Database name
$option['prefix'] = ''; // Database prefix (may be empty)
$db = JDatabaseDriver::getInstance( $option );
$query = $db->getQuery(true)
->select (array ("ap.name", "DATE_FORMAT(ap.date_entered, '%d-%b-%Y') 's_date'", "COUNT(*) 'interested'","SUM(CASE WHEN cc.interest_dd_c = 'To send info' THEN 1 ELSE 0 END) 'replied' ","SUM(CASE WHEN cc.ascl_info_sent_c = 1 THEN 1 ELSE 0 END) 'info' ","SUM(CASE WHEN cc.ascl_q_call_c = 1 THEN 1 ELSE 0 END) 'q_call' ","SUM(CASE WHEN cc.ascl_qualified_c = 1 THEN 1 ELSE 0 END) 'q' ","SUM(CASE WHEN cc.ascl_m_call_c = 1 THEN 1 ELSE 0 END) 'm_call' ","SUM(CASE WHEN cases.state = 'Closed' THEN 1 ELSE 0 END) 'Regret' ","cc.data_room_drop_c","ascl_exp_ticket_c"
)
) ->from($db->quoteName('cases_cstm', 'cc'))->join('LEFT', $db->quoteName( 'aos_products', 'ap') . ' ON (' .$db->quoteName('ap.id') . ' = ' .$db->quoteName('cc.aos_products_id_c') . ')')->join('LEFT', $db->quoteName( 'cases', 'c') . ' ON (' .$db->quoteName('cc.aos_products_id_c') . ' = ' .$db->quoteName('c.id') . ')')->join('LEFT', $db->quoteName( 'cases') . ' ON (' .$db->quoteName('cases.id') . ' = ' .$db->quoteName('cc.id_c') . ')')->group('aos_products_id_c') ->order('ap.date_entered', 'ASC');
$db->setQuery($query);
//echo $db->replacePrefix((string) $query);
$results = $db->loadAssocList();
print_r($results);
echo '<table style="border-color: #000000; background-color: #eeeeee;" border="1" cellpadding="10"><tbody>
<tr>
<td style="text-align: left;"><b>Product</b></td>
<td width="125px" style="text-align: left;"><b>Start date</b></td>
<td style="text-align: center;"><b>Targets</b></td>
<td style="text-align: center;"><b>Info sent</b></td>
<td style="text-align: center;"><b>Qual. calls</b></td>
<td style="text-align: center;"><b>Qualified</b></td>
<td style="text-align: center;"><b>Mgmt calls</b></td>
<td style="text-align: center;"><b>NDA</b></td></tr>';
foreach ($results as $row) {if($row['name']=="German Propco"){echo "<tr><td style=\"text-align: left;\">" . $row['name'] ."</td><td style=\"text-align: left;\">" . $row['s_date'] ."</td><td style=\"text-align: center;\">" . $row['interested'] ."</td><td style=\"text-align: center;\">" . $row['info'] ."</td><td style=\"text-align: center;\">" . $row['q_call'] ."</td><td style=\"text-align: center;\">" . $row['q'] ."</td><td style=\"text-align: center;\">" . $row['m_call'] ."</td><td style=\"text-align: center;\">" . $row['Regret'] ."</td></td></tr>";}
}
echo "</tbody></table>";
?>{/source}
2) Generate dynamic sales reports: a) Alphabetical order | b) Ordered by activity date (descending)