= 0; $i--) { $years[] = $currentYear - $i; } $salesData = []; $decliningSales = []; $companyDetails = []; // Fetch status and action options separately $statusOptions = []; $actionOptions = []; $s = "SELECT id, description, type , colourcode FROM viamedquotecontactstockstatuslist ORDER BY id ASC"; $stmt = pdoquery($s); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); foreach ($rows as $row) { if ((int)$row['type'] === 2) { $statusOptions[$row['id']] = $row['description']; $statusOptions[$row['id'] . '_color'] = !empty($row['colourcode']) ? $row['colourcode'] : '#cce5ff'; // default blue } elseif ((int)$row['type'] === 1) { $actionOptions[$row['id']] = $row['description']; $actionOptions[$row['id'] . '_color'] = !empty($row['colourcode']) ? $row['colourcode'] : '#cce5ff'; // default blue } } // Fetch latest used status per company for this stock and typedata='LostCustomer' $latestStatusPerCompany = []; $s = "SELECT companyid, statusid , addedon FROM viamedquotecontactstockstatus WHERE stock_ref = ? AND hostcompanyid = ? AND typedata = 'LostCustomer' ORDER BY addedon DESC"; $stmt = pdoquery($s, array($stkref, $hostcompanyid)); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); foreach ($rows as $row) { $cid = $row['companyid']; if (!isset($latestStatusPerCompany[$cid])) { $latestStatusPerCompany[$cid] = [ 'statusid' => $row['statusid'], 'addedon' => $row['addedon'] ]; } } // Fetch latest used action per company for this stock and typedata='LostCustomerAction' $latestActionPerCompany = []; $s = "SELECT companyid, statusid , addedon FROM viamedquotecontactstockstatus WHERE stock_ref = ? AND hostcompanyid = ? AND typedata = 'LostCustomerAction' ORDER BY addedon DESC"; $stmt = pdoquery($s, array($stkref, $hostcompanyid)); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); foreach ($rows as $row) { $cid = $row['companyid']; if (!isset($latestActionPerCompany[$cid])) { $latestActionPerCompany[$cid] = [ 'statusid' => $row['statusid'], 'addedon' => $row['addedon'] ]; } } // Query sales data (now grouping by company_id) // Query sales data (grouping by company_id) foreach ($years as $year) { $startDate = mktime(0, 0, 0, 1, 1, $year); $endDate = mktime(23, 59, 59, 12, 31, $year); $s = "SELECT vqoi.companyid, COUNT(vqoid.barcode) AS total_items_sold FROM viamedquote_orders_invoices vqoi JOIN viamedquote_orders_invoices_details vqoid ON vqoid.invoiceref = vqoi.invoiceref WHERE vqoi.hostcompanyid = ? AND vqoid.stockref = ? AND vqoi.invoicecommitedon BETWEEN ? AND ? GROUP BY vqoi.companyid"; //print "$s
"; // Debugging: Print SQL query $stmt = pdoquery($s, array($hostcompanyid, $stkref, $startDate, $endDate)); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); foreach ($rows as $row) { $company_id = $row['companyid']; $salesData[$company_id]['sales'][$year] = $row['total_items_sold']; } } // Identify declining sales (compare last 2 years) $previousYear = $currentYear - 2; $lastYear = $currentYear - 1; foreach ($salesData as $company_id => $data) { $salesPrev = isset($data['sales'][$previousYear]) ? $data['sales'][$previousYear] : 0; $salesLast = isset($data['sales'][$lastYear]) ? $data['sales'][$lastYear] : 0; if ($salesLast < $salesPrev) { $decliningSales[$company_id] = true; } } // Fetch company details and order alphabetically $s = "SELECT ID, COMPANY, blacklist, hideme, preferedcid FROM viamedquoteaddress ORDER BY COMPANY ASC"; $stmt = pdoquery($s); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); foreach ($rows as $row) { $company_id = $row['ID']; $companyDetails[$company_id] = [ 'company_name' => $row['COMPANY'], 'blacklist' => trim($row['blacklist']), 'hideme' => trim($row['hideme']), 'preferedcid' => (int) $row['preferedcid'] ]; } // Buttons for filtering echo "

Lost Sales for $stkref ($hostcompanyid) - Last 5 Years

"; echo " "; echo " "; echo ""; echo ""; echo ""; foreach ($years as $year) { echo ""; } echo ""; // Display data sorted by company name foreach ($companyDetails as $company_id => $companyInfo) { if (!isset($salesData[$company_id])) { continue; } // Skip hidden companies if (!empty($companyInfo['hideme'])) { continue; } $companyName = $companyInfo['company_name']; $blacklist = $companyInfo['blacklist']; $preferedcid = $companyInfo['preferedcid']; // Determine row class for filtering $rowClass = "row-all"; if (isset($decliningSales[$company_id])) { $rowClass .= " row-losing"; if (!empty($blacklist)) { $rowClass .= " row-losing-blacklist"; } } echo ""; echo ""; // Account Status Column echo ""; // Sales Data Columns foreach ($years as $year) { $sales = isset($salesData[$company_id]['sales'][$year]) ? $salesData[$company_id]['sales'][$year] : 0; echo ""; } // Highlight declining sales if (isset($decliningSales[$company_id])) { echo ""; } else { echo ""; } $currentStatusId = isset($latestStatusPerCompany[$company_id]) ? $latestStatusPerCompany[$company_id]['statusid'] : ''; $currentStatusDate = isset($latestStatusPerCompany[$company_id]) ? $latestStatusPerCompany[$company_id]['addedon'] : ''; echo ""; $currentActionId = isset($latestActionPerCompany[$company_id]) ? $latestActionPerCompany[$company_id]['statusid'] : ''; $currentActionDate = isset($latestActionPerCompany[$company_id]) ? $latestActionPerCompany[$company_id]['addedon'] : ''; echo ""; echo ""; } echo "
CIDCompanyAccount Status$year SalesTrend Status Action
".linkcrm($x,$company_id,'')."$company_id$companyName"; // Show merged account notice if ($preferedcid > 0) { echo "Use CID $preferedcid
"; } // Show blacklist icon if applicable if (!empty($blacklist)) { echo "⚠"; } echo "		$sales⬇ Losing Sales✔ Stable"; $statusBgColor = isset($statusOptions[$currentStatusId . '_color']) ? $statusOptions[$currentStatusId . '_color'] : '#cce5ff'; echo ""; if (!empty($currentStatusDate)) { echo "
Set on: ".date("d M Y",$currentStatusDate)."
"; } echo "		"; $actionBgColor = isset($actionOptions[$currentActionId . '_color']) ? $actionOptions[$currentActionId . '_color'] : '#cce5ff'; echo ""; if (!empty($currentActionDate)) { echo "
Set on: ".date("d M Y",$currentActionDate)."
"; } echo "
"; ?>
Test Debug Area