{source}<?php
defined('_JEXEC') or die('Restricted Access');

// Read product id from URL
$productId = isset($_GET['product']) ? trim($_GET['product']) : '';
if ($productId === '') {
echo '<p style="color:#000000;">No product specified.</p>';
return;
}

// Connect to SuiteCRM database (same as dashboard)
$option = array();
$option['driver'] = 'mysql';
$option['host'] = 'localhost';
$option['user'] = 'readOnly';
$option['password'] = 'kDvgI4cC4oING4go';
$option['database'] = 'suitecrm';
$option['prefix'] = '';
$db = JDatabaseDriver::getInstance($option);

// Get product name for heading
$queryProduct = $db->getQuery(true)
->select($db->quoteName('name'))
->from($db->quoteName('aos_products'))
->where($db->quoteName('id') . ' = ' . $db->quote($productId));
$db->setQuery($queryProduct);
$productName = (string) $db->loadResult();

// Build query (exclude deleted SuiteCRM rows)
$query = $db->getQuery(true)
->select(array(
'cases.id',
'cases.name AS lead_name',
"DATE_FORMAT(cases.date_entered, '%d %b %Y') AS created_date",
'cc.ascl_summary_notes_c AS client_report_notes',
'cc.ascl_next_steps_c AS next_steps',
'cc.contact_id_c AS main_contact_id',
'cc.ascl_next_call_date_c AS next_call_date',
'cc.next_meeting_time_c AS next_meeting_time',
'cc.interest_dd_c',
'cc.ascl_info_sent_c AS sent_flag',
'cc.ascl_q_call_c AS q_call_flag',
'cc.qual_call_drop_c AS q_call_status',
'cc.ascl_qualified_c AS qualified_flag',
'cc.man_call_drop_c AS m_call_status',
'cc.ascl_m_call_c AS m_call_flag',
'cc.nda_drop_c AS nda_status',
'cc.data_room_drop_c AS dataroom_status',
'cc.subscription_drop_c AS subscription_status',
'cc.ascl_exp_ticket_c AS indicative_ticket',
'cc.questions_outstanding_c AS outstanding_questions',
'cc.ascl_sales_stage_c AS sales_stage', 

'cc.ascl_call_date_c AS upcoming'
))
->from($db->quoteName('cases'))
->join('LEFT', $db->quoteName('cases_cstm', 'cc') . ' ON (' . $db->quoteName('cases.id') . ' = ' . $db->quoteName('cc.id_c') . ')')
->where($db->quoteName('cc.aos_products_id_c') . ' = ' . $db->quote($productId))
->where($db->quoteName('cases.deleted') . ' = 0')

->where($db->quoteName('cc.ascl_call_date_c') . ' IS NOT NULL')

->where(
$db->quoteName('cc.ascl_call_date_c') . " <> ''")

->where(
$db->quoteName('cc.ascl_call_date_c') . ' > CURDATE()'
)
->order($db->quoteName('cases.date_modified') . ' DESC');

$db->setQuery($query);
$rows = $db->loadAssocList();

$count = count($rows);

// Heading with note about alpha sort
echo '<h3 style="color:#000000;">Sales leads (Upcoming meetings/calls): ' . htmlspecialchars($productName ?: 'Unknown product', ENT_QUOTES, 'UTF-8') . ' '.$count.'</h3>';

if (!$rows) {
echo '<p style="color:#000000;">No Sales leads found for this product.</p>';
return;
}

// Fixed layout so percentages are enforced
echo '<table border="1" cellpadding="10" style="border-color:#000000; background-color:#eeeeee; width:100%; max-width:100%; border-collapse:collapse; table-layout:fixed; word-wrap:break-word; color:#000000;"><tbody>';
echo '<tr style="background-color:#dddddd; color:#000000;">'
. '<th style="text-align:left; width:10%; font-weight:bold;"><span>Name</span></th>'
. '<th style="text-align:left; width:65%; font-weight:bold;"><span>Client report notes</span></th>'
. '<th style="text-align:left; width:10%; font-weight:bold;"><span>Upcoming Meetings/Calls</span></th>'

. '</tr>';

foreach ($rows as $r) {
$nextCallPieces = array();
if (!empty($r['next_call_date'])) { $nextCallPieces[] = $r['next_call_date']; }
if (!empty($r['next_meeting_time'])) { $nextCallPieces[] = $r['next_meeting_time']; }
$nextCallDisplay = implode(' ', $nextCallPieces);

echo '<tr style="color:#000000;">'
. '<td style="text-align:left; width:10%; vertical-align:top;">' . htmlspecialchars($r['lead_name'] ?? '', ENT_QUOTES, 'UTF-8') . '</td>'
. '<td style="text-align:left; width:65%; vertical-align:top;">' . nl2br(htmlspecialchars($r['client_report_notes'] ?? '', ENT_QUOTES, 'UTF-8')) . '</td>'
. '<td style="text-align:left; width:10%; vertical-align:top;">' . nl2br(htmlspecialchars($r['upcoming'] ?? '', ENT_QUOTES, 'UTF-8')) . '</td>'

. '</tr>';
}

echo '</tbody></table>';

?>{/source}