Details: Written by: Charles Hancock; Category: Static content; Read Time: 4 mins; Hits: 3

CLIENT DASHBOARD - SATURN / LIGHTPOINT MEDICAL

1. Current sales tracking

{source}

<?php

defined('_JEXEC') or die('Restricted Access');

//use Joomla\CMS\Factory;
//use Joomla\CMS\HTML\HTMLHelper;
//use Joomla\CMS\Language\Text;

// These two are functionally equivalent
//echo HtmlHelper::date('now', Text::_('DATE_FORMAT_FILTER_DATETIME'));

//$timezone = Factory::getUser()->getTimezone();
//echo Factory::getDate()->setTimezone($timezone)->format(Text::_('DATE_FORMAT_FILTER_DATETIME'));

$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'", "ap.id 'ap_id'","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();

$today = mktime(date("G"), date("i"), 0, date("m"), date("d"), date("Y"));

echo "<p>Timestamp (GMT): ".date("G:i a - l d M, Y", $today). "</p>";

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: center; 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) {

if ($row['ap_id'] == "4d80e0c3-07e6-0025-b614-6290eaab088b")
{

echo "

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

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

}

else {
echo "<td style=\"text-align: center;\"> - </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}

2. Dynamic sales reports

a) Alphabetical order

b) Ordered by activity date (descending)

3. Useful links

(e.g. data room): anglo-suisse.com etc.

Details: Written by: Charles Hancock; Category: Static content; Read Time: 6 mins; Hits: 5

CLIENT DASHBOARD - TRUTH ENGINE

1. Current sales tracking

{source}

<?php

defined('_JEXEC') or die('Restricted Access');

//use Joomla\CMS\Factory;
//use Joomla\CMS\HTML\HTMLHelper;
//use Joomla\CMS\Language\Text;

// These two are functionally equivalent
//echo HtmlHelper::date('now', Text::_('DATE_FORMAT_FILTER_DATETIME'));

//$timezone = Factory::getUser()->getTimezone();
//echo Factory::getDate()->setTimezone($timezone)->format(Text::_('DATE_FORMAT_FILTER_DATETIME'));

$query = $db->getQuery(true)

->select (array ("ap.name 'ap_name'", "ap.id 'ap_id'","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();

$today = mktime(date("G"), date("i"), 0, date("m"), date("d"), date("Y"));

echo "<p>Timestamp (GMT): ".date("G:i a - l d M, Y", $today). "</p>";

foreach ($results as $row) {

if ($row['ap_id'] == "48f1394c-be65-75d0-cebe-5d9df32be5d3")
{

echo "

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

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

}

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

}

}
}

echo "</tbody></table>";

{/source}

2. Dynamic sales reports

3. Useful links (e.g. data room): anglo-suisse.com etc.

Slack workspace: https://truthengineandascl.slack.com
G-Drive folder shared with TE: https://drive.google.com/open?id=1nrPajGOqP3ZpM4DJ3pVpdC13twCoCSnm
Nextcloud VDR: https://anglo-suisse.com/vdr/index.php/s/gPg8iZw4JLD7B9d
Dashboard: https://anglo-suisse.com/index.php/mettrr-truth-engine
https://truthengine.com/login
Truth Engine summary investor deck - 25 August 2022 (v1)

Details: Written by: Charles Hancock; Category: Static content; Read Time: 4 mins; Hits: 3

CLIENT DASHBOARD - TRUTH ENGINE

1. Current sales tracking

{source}<?php

defined('_JEXEC') or die('Restricted Access');

//use Joomla\CMS\Factory;
//use Joomla\CMS\HTML\HTMLHelper;
//use Joomla\CMS\Language\Text;

// These two are functionally equivalent
//echo HtmlHelper::date('now', Text::_('DATE_FORMAT_FILTER_DATETIME'));

//$timezone = Factory::getUser()->getTimezone();
//echo Factory::getDate()->setTimezone($timezone)->format(Text::_('DATE_FORMAT_FILTER_DATETIME'));

$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'", "ap.id 'ap_id'","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();

$today = mktime(date("G"), date("i"), 0, date("m"), date("d"), date("Y"));

echo "Timestamp (GMT): ".date("G:i a - l d M, Y", $today);

echo "

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

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

}

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

}

}
}

echo "</tbody></table>";
?>

{/source}

2) Live reports > 1. PDF ordered by account name | 2. PDF ordered by activity

3) Useful links (e.g. data room): anglo-suisse.com etc.

Details: Written by: Charles Hancock; Category: Static content; Read Time: 1 min; Published: 16 May 2021; Hits: 96

Details: Written by: Charles Hancock; Category: Static content; Read Time: 3 mins; Published: 16 May 2021; Hits: 88

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.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', 'ASC');

$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;"><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>Qualified</b></td>
<td style="text-align: center;"><b>Mgmt calls</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) {
if($row['name']=="easyFood shares")
{
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['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>
</td></tr>";
}
}
echo "</tbody></table>";
?>{/source}

2) Dynamic sales reports: a) Alphabetical order | b) Ordered by activity date (descending)

CLIENT DASHBOARD - SATURN / LIGHTPOINT MEDICAL

1. Current sales tracking

2. Dynamic sales reports

a) Alphabetical order

b) Ordered by activity date (descending)

3. Useful links

(e.g. data room): anglo-suisse.com etc.

CLIENT DASHBOARD - TRUTH ENGINE

1. Current sales tracking

2. Dynamic sales reports

3. Useful links (e.g. data room): anglo-suisse.com etc.

Dashboard - CH remainder test

Client dashboard - easyFood

More Articles …