Dashboard - CH chart testing page

Details: Written by: Charles Hancock; Category: Static content; Published: 16 May 2021; Hits: 92

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

//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;">Product</td>
<td width="125px" style="text-align: left;">Start date</td>
<td style="text-align: center;">Interested</td>
<td style="text-align: center;">Info sent</td>
<td style="text-align: center;">Qual. calls held</td>
<td style="text-align: center;">Qualified</td>
<td style="text-align: center;">Mgmt calls held</td>

<td style="text-align: center;">NDA signed</td>

<td style="text-align: center;">Dataroom accessed</td>

<td style="text-align: center;">Subscribed</td>

<td style="text-align: center;">Value $m</td>

<td style="text-align: center;">Regret</td>

<td style="text-align: center;">Remaining interested</td></tr>';

//print_r($results);

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['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>