MANAGEMENT DASHBOARD

 

1. Client dashboards

• Thornwood Hill Insurance

• Divine Power

• DineRock

• piHub

• Lionsoul

• HAL

• GGP

• Zayda

• Aeristech

• TRIRF

• Beitel Group

• Axxes Capital

• Lurra Capital

• MD One

• Avealto

• Swallis DSA

• Saturn / Lightpoint medical

• Truth Engine
  
  
• GSX Group
  
  
• Lakeward
  
  
• TrustMe
  
  
• Wunder Mobility dashboard
  
  
• Blitzscaling Ventures Fund dashboard
  
  
• easyHire dashboard
  
  
• Revolut dashboard

 

2. Sales lead 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 'ap_name'",

                                "apc.temp_symbol_c 'currency'",

                                "DATE_FORMAT(ap.date_entered, '%d-%b-%Y') 's_date'", 

                                "COUNT(*) 'interested'"

                                ,"SUM(CASE WHEN cc.ascl_responded_c = 1 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)  'closed' "

                                ,"SUM(cc.ascl_exp_ticket_float_c)  'exp_ticket_f' "
 )
 ) 

               ->from($db->quoteName('cases_cstm', 'cc'))

               ->join('LEFT', $db->quoteName( 'aos_products_cstm', 'apc') . ' ON (' .$db->quoteName('apc.id_c') . ' = ' .$db->quoteName('cc.aos_products_id_c') . ')')

               ->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);

//echo $db->replacePrefix((string) $query);

$results = $db->loadAssocList();
echo '<table style="border-color: #000000; background-color: #eeeeee;" border="1" cellpadding="10"><tbody>
<tr>
<td style="text-align: left; font-weight: bold;">Client/product</td>
<td width="125px" style="text-align: right; font-weight: bold;">Start date</td>
<td style="text-align: right; font-weight: bold;">Indications</td>
<td style="text-align: center; font-weight: bold;">Interested</td>
<td style="text-align: center; font-weight: bold;">Replied</td>
<td style="text-align: center; font-weight: bold;">Info sent</td>
<td style="text-align: center; font-weight: bold;">Qual. calls</td>
<td style="text-align: center; font-weight: bold;">Qualified</td>
<td style="text-align: center; font-weight: bold;">Mgmt calls</td>
<td style="text-align: center; font-weight: bold;">Closed</td></tr>';

foreach ($results as $row) {
echo "

   <tr>
   <td style=\"text-align: left;\">" . $row['ap_name'] . "</td>
   <td style=\"text-align: right;\">" . $row['s_date'] ."</td>";

   if (!empty($row['currency'])){
   echo "<td style=\"text-align: right;\">" . $row['currency'] . $row['exp_ticket_f'] ."m</td>"

   ;}

   else {
   echo "<td style=\"text-align: right;\"> - </td>"

   ;}

   echo "<td style=\"text-align: center;\">" . $row['interested'] ."<br /></td>
   <td style=\"text-align: center;\">" . $row['replied'] . "<br />(" . round ($row['replied']/$row['interested'],3)*100 . "%)</td>
   <td style=\"text-align: center;\">" . $row['info'] ."<br />(" . round ($row['info']/$row['interested'],3)*100 . "%)</td>
   <td style=\"text-align: center;\">" . $row['q_call'] ."<br />(" . round ($row['q_call']/$row['interested'],3)*100 . "%)</td>
   <td style=\"text-align: center;\">" . $row['q'] ."<br />(" . round ($row['q']/$row['interested'],3)*100 . "%)</td>
   <td style=\"text-align: center;\">" . $row['m_call'] ."<br />(" . round ($row['m_call']/$row['interested'],3)*100 . "%)</td>
   <td style=\"text-align: center;\">" . $row['closed'] ."<br />(" . round ($row['closed']/$row['interested'],3)*100 . "%)</td>
   </td></tr>

 

   ";
}

echo "</tbody></table>";
?>{/source}

(Backlog: add a calculated field to show response rate) 

 

4. Legacy Client reports (before client dashboards)

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

5) GSX Group > click to view the PDF and download if required

6) Lakeward > Alphabetical order | Ordered by activity date (descending)

7) TPX > click to view the PDF and download if required

8) easyFood > 1. PDF ordered by account name |  2. PDF ordered by activity