MANAGEMENT DASHBOARD - SALES LEADS
1. Sales tracking (link to SuiteCRM)
{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.qual_call_drop_c = 'Call/meet proposed' THEN 1 ELSE 0 END) 'call_meet_proposed'",
"SUM(CASE WHEN cc.qual_call_drop_c = 'Call/meet agreed' THEN 1 ELSE 0 END) 'call_meet_agreed'",
"SUM(CASE WHEN cc.qual_call_drop_c = 'Call/meet confirmed' THEN 1 ELSE 0 END) 'call_meet_confirmed'",
"SUM(CASE WHEN cc.qual_call_drop_c = 'Call/meet held - points outstanding' THEN 1 ELSE 0 END) 'call_meet_points_outstanding'",
"SUM(CASE WHEN cc.qual_call_drop_c = 'Call/meet held - follow up complete' THEN 1 ELSE 0 END) 'call_meet_follow_up_complete'",
"SUM(CASE WHEN cc.qual_call_drop_c = 'No further interest' THEN 1 ELSE 0 END) 'no_further_interest'",
"SUM(CASE WHEN cc.ascl_responded_c = 1 THEN 1 ELSE 0 END) 'response' ",
"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 cc.nda_drop_c = 'NDA_signed' THEN 1 ELSE 0 END) 'ndasigned' ",
"SUM(CASE WHEN cc.data_room_drop_c = 'DR_accessed' THEN 1 ELSE 0 END) 'dataroomaccess' ",
"SUM(CASE WHEN cc.subscription_drop_c = 'Subscription_signed' THEN 1 ELSE 0 END) 'Subscribed' ",
"SUM(cc.ascl_exp_ticket_c) 'Value' ",
"SUM(CASE WHEN cases.state = 'Closed' THEN 1 ELSE 0 END) 'Regret' ",
"SUM(CASE WHEN cases.state = 'Open' THEN 1 ELSE 0 END) 'Remaining interested' "
))
->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 DESC');
$db->setQuery($query);
$results = $db->loadAssocList();
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>Interested</b></td>
<td style="text-align: center;"><b>Call/meet proposed</b></td>
<td style="text-align: center;"><b>Call/meet agreed</b></td>
<td style="text-align: center;"><b>Call/meet confirmed</b></td>
<td style="text-align: center;"><b>Call/meet held - points outstanding</b></td>
<td style="text-align: center;"><b>Call/meet held - follow up complete</b></td>
<td style="text-align: center;"><b>No further interest</b></td>
<td style="text-align: center;"><b>Responded</b></td>
<td style="text-align: center;"><b>Info sent</b></td>
<td style="text-align: center;"><b>Qual. calls held</b></td>
<td style="text-align: center;"><b>Qualified</b></td>
<td style="text-align: center;"><b>Mgmt calls held</b></td>
<td style="text-align: center;"><b>NDA signed</b></td>
<td style="text-align: center;"><b>Dataroom accessed</b></td>
<td style="text-align: center;"><b>Subscribed</b></td>
<td style="text-align: center;"><b>Value $m</b></td>
<td style="text-align: center;"><b>Regret</b></td>
<td style="text-align: center;"><b>Remaining interested</b></td></tr>';
foreach ($results as $row) {
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['call_meet_proposed'] ."</td>
<td style=\"text-align: center;\">" . $row['call_meet_agreed'] ."</td>
<td style=\"text-align: center;\">" . $row['call_meet_confirmed'] ."</td>
<td style=\"text-align: center;\">" . $row['call_meet_points_outstanding'] ."</td>
<td style=\"text-align: center;\">" . $row['call_meet_follow_up_complete'] ."</td>
<td style=\"text-align: center;\">" . $row['no_further_interest'] ."</td>
<td style=\"text-align: center;\">" . $row['response'] ."</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['ndasigned'] ."</td>
<td style=\"text-align: center;\">" . $row['dataroomaccess'] ."</td>
<td style=\"text-align: center;\">" . $row['Subscribed'] ."</td>
<td style=\"text-align: center;\">" . $row['Value'] ."</td>
<td style=\"text-align: center;\">" . $row['Regret'] ."</td>
<td style=\"text-align: center;\">" . $row['Remaining interested'] ."</td></tr>";
}
echo '</tbody></table>';
?>
?>{/source}
2. Dashboard B
Client reports (TESTING)
1) Blitzscaling > click to view the PDF and download if required
2) Rockaway > click to view the PDF and download if required
3) Protos > click to view the PDF and download if required
4) Lloyd Jones > click to view the PDF and download if required