CLIENT DASHBOARD - Axxes Capital 2024

 

1. Current sales tracking (stats reset July 2024)

{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.id 'ap_id'",
"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_float_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;"><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>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>';

//print_r($results);

foreach ($results as $row) {

if ($row['ap_id'] == "3483bc4f-6831-4ffb-e834-649aa50ca6f4")
{
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['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 2024 sales reports (links to PDF)

1. Alphabetical order (2024)
2. Ordered by activity date, descending (2024)
3. Ordered by type and name (2024)
   
   
   

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

• Internal Nextcloud VDR admin: https://anglo-suisse.com/vdr/index.php/f/1266
• Internal Nextcloud VDR user: https://anglo-suisse.com/vdr/index.php/s/R2Z6e7FkbzWM95Z
  
• MMost workspace: http://a.anglo-suisse.com:8065/asc/channels/dcp_axxes_dagrosa
  
• Product ID:3483bc4f-6831-4ffb-e834-649aa50ca6f4
• Dashboard: https://anglo-suisse.com/index.php/axxes-dcp-dash
  
• G-Drive shared folder:
• Slack workspace: 
• Teams workspace: