/p078_dealerd1/p077_DealLog1/GM_Main/GM_Dashboard/ajax/Sale_Departments_Table.php

Large files files are truncated, but you can click here to view the full file

<?php
include_once ($_SERVER['DOCUMENT_ROOT']."/p093_includes/bootstrap.php");
include_once('../include/functions.php');
authorize('Dashboard/GM');

$bar = new qsBar(array('periodcombo' => 'D' . Date::format('Y-m-01')));
$bar->doPeriodCombo('periodcombo');

$dptWhere = "`dpt`.`p077_sort_code` < 999 AND `dpt`.`Department_Types_ID` = 1";
$sql = "SELECT * FROM `cDepartment` `dpt`
        WHERE $dptWhere AND `dpt`.`Dpt_Active` = 'Y'
        ORDER BY `dpt`.`GM_Sort_Code` ASC";
$db->query($sql);
$db->setResultSetKey('DMS_DPT_Code');
$departments     = $db->fetchAllObjects('Department');
$departmentNames = array();
foreach ($departments as $department)
{
    $departmentNames[] = $department->Short_Desc;
}

$soldOutOfStock = new GM\Dashboard\SoldOutOfStock($_REQUEST);
$soldOutOfStock = $soldOutOfStock->getReport();

$data                                               = array();
$data['Recon Cars']                                 = array_fill_keys($departmentNames, 0);
$data['Stock Cars']                                 = array_fill_keys($departmentNames, 0);
$data['Deal Situation']                             = array_fill_keys($departmentNames, 0);
$data['Sign ups']                                   = array_fill_keys($departmentNames, array('Today' => 0, 'Yesterday' => 0, 'DayBefore' => 0));
$data['Orphans (Net) >30min']                       = array_fill_keys($departmentNames, 0);
$data['Orphans >1hr']                               = array_fill_keys($departmentNames, 0);
$data['Orphans >16hrs']                             = array_fill_keys($departmentNames, 0);
$data['Internet Escalated']                         = array_fill_keys($departmentNames, 0);
$data['Lost >1day']                                 = array_fill_keys($departmentNames, 0);
$data['Recon Current']                              = array_fill_keys($departmentNames, 0);
$data['Avg Sales Days']                             = array_fill_keys($departmentNames, 0);
$data['Stock Cost']                                 = array_fill_keys($departmentNames, 0);
$data['Stock Units']                                = array_fill_keys($departmentNames, 0);
$data['Days Supply']                                = array_fill_keys($departmentNames, 0);
$data['Retail LMS Enquiry']                         = array_fill_keys($departmentNames, 0);
$data['Wholesale < 10 Days']                        = array_fill_keys($departmentNames, 0);
$data['Wholesale 11 - 20 Days']                     = array_fill_keys($departmentNames, 0);
$data['Wholesale > 20 Days']                        = array_fill_keys($departmentNames, 0);
$data['Deliveries Today']                           = array_fill_keys($departmentNames, array('Today' => 0, 'Yesterday' => 0, 'DayBefore' => 0));
$data['Sales2Service Handover']                     = array_fill_keys($departmentNames, array('Y' => 0, 'All' => 0));
$data['Call Center Penetration']                    = array_fill_keys($departmentNames, array('Surveyed' => 0, 'Total' => 0));
$data['NPS']                                        = array_fill_keys($departmentNames, array('Promoters' => 0, 'Detractors' => 0, 'Total' => 0));
$data['Parts Not Fitted on Delivery (Outstanding)'] = array_fill_keys($departmentNames, 0);
$data['Parts Not Fitted on Delivery (Booked)']      = array_fill_keys($departmentNames, 0);
$data['Target Gross']                               = array_fill_keys($departmentNames, 0);
$data['Target Units']                               = array_fill_keys($departmentNames, 0);
$data['Weekly Dots Gross']                          = array_fill_keys($departmentNames, 0);
$data['Weekly Dots Units']                          = array_fill_keys($departmentNames, 0);
$data['ITU (Individual)']                           = array_fill_keys($departmentNames, 0);
$data['Total Completed']                            = array_fill_keys($departmentNames, 0);
$data['Total Scheduled']                            = array_fill_keys($departmentNames, 0);
$data['Total Waiting Manager Approval']             = array_fill_keys($departmentNames, 0);
$data['Total Waiting HR Approval']                  = array_fill_keys($departmentNames, 0);

$today     = Date::format('Y-m-d');
$yesterday = Date::format("Y-m-d", strtotime('-1 day'));
$daybefore = Date::format("Y-m-d", strtotime('-2 day'));

if (date("D", strtotime('-1 day')) == 'Sun')
{
    $yesterday = Date::format("Y-m-d", strtotime('-2 day'));
    $daybefore = Date::format("Y-m-d", strtotime('-3 day'));
}
elseif (date("D", strtotime('-2 day')) == 'Sun')
{
    $yesterday = Date::format("Y-m-d", strtotime('-1 day'));
    $daybefore = Date::format("Y-m-d", strtotime('-3 day'));
}


/* Recon Cars */
$sql = "SELECT `dpt`.`Short_Desc`,
               `dpt`.`DMS_DPT_Code`,
               COUNT(*) as `Count`
        FROM `p133_Recon` `r`
        INNER JOIN `p133_Recon_Status` `rs` ON `rs`.`Recon_Stat_Code` = `r`.`p133_Recon_Status`
        INNER JOIN `p109_Veh_Stk` `veh` ON `veh`.`p109_STKNO` = `r`.`p133_Stock_no`
        INNER JOIN `cDepartment` `dpt` ON `dpt`.`DMS_DPT_Code` = `r`.`p133_cDepartment_Sales` AND `dpt`.`Department_Types_ID` = 1 AND `dpt`.`Dpt_Active` = 'Y'
        WHERE (`rs`.`Is_Current` = 'Y' OR (`r`.`p133_Recon_Status` = 'Instock' AND `veh`.`Status` < 90)) AND `r`.`p133_Stock_Car` = 'N' AND `rs`.`p133_Recon_Time_Count_As_ID` = 1
        GROUP BY `r`.`p133_cDepartment_Sales`";
$db->query($sql);
while ($row = $db->fetchRow())
{
    if (isset($data['Recon Cars'][$row['Short_Desc']]))
    {
        $data['Recon Cars'][$row['Short_Desc']] = $row['Count'];
    }
}

/* Stock Cars */
$sql = "SELECT `dpt`.`Short_Desc`,
               `dpt`.`DMS_DPT_Code`,
               COUNT(*) as `Count`
        FROM `p133_Recon` `r`
        INNER JOIN `p133_Recon_Status` `rs` ON `rs`.`Recon_Stat_Code` = `r`.`p133_Recon_Status`
        INNER JOIN `p109_Veh_Stk` `veh` ON `veh`.`p109_STKNO` = `r`.`p133_Stock_no`
        INNER JOIN `cDepartment` `dpt` ON `dpt`.`DMS_DPT_Code` = `r`.`p133_cDepartment_Sales` AND `dpt`.`Department_Types_ID` = 1 AND `dpt`.`Dpt_Active` = 'Y'
        WHERE (`rs`.`Is_Current` = 'Y' OR (`r`.`p133_Recon_Status` = 'Instock' AND `veh`.`Status` < 90)) AND `r`.`p133_Stock_Car` = 'Y' AND `rs`.`p133_Recon_Time_Count_As_ID` = 1
        GROUP BY `r`.`p133_cDepartment_Sales`";
$db->query($sql);
while ($row = $db->fetchRow())
{
    if (isset($data['Stock Cars'][$row['Short_Desc']]))
    {
        $data['Stock Cars'][$row['Short_Desc']] = $row['Count'];
    }
}

/* Deal Situation Cars */
$where = "`d`.`SM_Delivery_Month` = '{$_REQUEST['period']}'";
$sql = "SELECT `dpt`.`Short_Desc`,
               `dpt`.`DMS_DPT_Code`,
               COUNT(*) as `Count`
        FROM `p077_Deal_Log` `d`
        INNER JOIN `p077_Deal_Situation_Status` `dss` ON `dss`.`Deal_Situation_Status` = `d`.`p077_Deal_Situation`
        INNER JOIN `p109_Veh_Stk` `vs` ON `vs`.`p109_STKNO` = `d`.`p077_Stock_Num`
        INNER JOIN `cDepartment` `dpt` ON `dpt`.`DMS_DPT_Code` = `d`.`p077_DPTID` AND `dpt`.`Department_Types_ID` = 1 AND `dpt`.`Dpt_Active` = 'Y'
        WHERE $where
        GROUP BY `dpt`.`DMS_DPT_Code`";
$db->query($sql);
while ($row = $db->fetchRow())
{
    if (isset($data['Deal Situation'][$row['Short_Desc']]))
    {
        $data['Deal Situation'][$row['Short_Desc']] = $row['Count'];
    }
}

/* Sign ups */
$sql = "SELECT `dpt`.`Short_Desc`,
               SUM(DATE(`d`.`p077_Sign_up_Date`) = '%%1') as `Today`,
               SUM(DATE(`d`.`p077_Sign_up_Date`) = '%%2') as `Yesterday`,
               SUM(DATE(`d`.`p077_Sign_up_Date`) = '%%3') as `DayBefore`,
               `d`.`sale_cat`
        FROM `p077_Deal_Log` `d`
        INNER JOIN `cDepartment` `dpt` ON (`dpt`.`DMS_DPT_Code` = `d`.`p077_DPTID` AND `dpt`.`Department_Types_ID` = 1 AND `dpt`.`Dpt_Active` = 'Y')
        WHERE `d`.`sale_cat` <> 'D' AND `d`.`SM_Signup_Month` = '%%4'
        GROUP BY `d`.`p077_DPTID`";
$db->query($sql, $today, $yesterday, $daybefore, $_REQUEST['period']);
while ($row = $db->fetchRow())
{
    $data['Sign ups'][$row['Short_Desc']]['Today']     = $row['Today'];
    $data['Sign ups'][$row['Short_Desc']]['Yesterday'] = $row['Yesterday'];
    $data['Sign ups'][$row['Short_Desc']]['DayBefore'] = $row['DayBefore'];
}

/* Orphans >1hr */
$sql = "SELECT `dpt`.`Short_Desc`,
               `l`.`L_Day`,
               `l`.`p108_Lead_Type_ID`
        FROM `p108_Leads` `l`
        INNER JOIN `cDepartment` `dpt` ON (`dpt`.`cDepartment_ID` = `l`.`L_cDepartment_ID` AND `dpt`.`Department_Types_ID` = 1 AND `dpt`.`Dpt_Active` = 'Y')
        WHERE `l`.`p108_Lead_Status_ID` = '1'";
$db->query($sql);
while ($row = $db->fetchRow())
{
    $hours = (time() - strtotime($row['L_Day'])) / 3600;
    $minutes = $hours - (int) $hours;
    if ($minutes < 0.5)
    {
        $hours = floor($hours);
    }

    if (isset($data['Orphans >1hr'][$row['Short_Desc']]) && $hours >= 1)
    {
        $data['Orphans >1hr'][$row['Short_Desc']]++;
    }

    if (isset($data['Orphans >16hrs'][$row['Short_Desc']]) && $hours >= 16)
    {
        $data['Orphans >16hrs'][$row['Short_Desc']]++;
    }

    if (isset($data['Orphans (Net) >30min'][$row['Short_Desc']]) && $hours >= 0.5 && $row['p108_Lead_Type_ID'] == 3)
    {
        $data['Orphans (Net) >30min'][$row['Short_Desc']]++;
    }

}

/* Internet Escalated */
$sql = "SELECT `dpt`.`Short_Desc`,
               `l`.`p108_Lead_Type_ID`,
               COUNT(*) as `Count`
        FROM `p108_Leads` `l`
        INNER JOIN `cDepartment` `dpt` ON (`dpt`.`cDepartment_ID` = `l`.`L_cDepartment_ID` AND `dpt`.`Department_Types_ID` = 1 AND `dpt`.`Dpt_Active` = 'Y')
        WHERE DATE(`l`.`L_Day`) = CURDATE() AND `l`.`Escalation_SMS` = 'Y' AND `l`.`p108_Lead_Type_ID` = 3
        GROUP BY `l`.`L_cDepartment_ID`";
$db->query($sql);
while ($row = $db->fetchRow())
{
    $data['Internet Escalated'][$row['Short_Desc']] = $row['Count'];
}

$lostDays = 1;
if (Date::format('D') == 'Mon')
{
    $lostDays = 3;
}
/* Lost >1day */
$sql = "SELECT `dpt`.`Short_Desc`,
               SUM(DATEDIFF(NOW(), `l`.`L_Day`) > $lostDays) as `Days`
        FROM `p108_Leads` `l`
        INNER JOIN `cDepartment` `dpt` ON (`dpt`.`cDepartment_ID` = `l`.`L_cDepartment_ID` AND `dpt`.`Department_Types_ID` = 1 AND `dpt`.`Dpt_Active` = 'Y')
        WHERE `l`.`p108_Lead_Status_ID` = '11'
        GROUP BY `l`.`L_cDepartment_ID`";
$db->query($sql);
while ($row = $db->fetchRow())
{
    if (isset($data["Lost >$lostDays day"][$row['Short_Desc']]))
    {
        $data["Lost >$lostDays day"][$row['Short_Desc']] = $row['Days'];
    }
}

/* Avg Sales Days */
$sql = "SELECT SUM(`rdl`.`Status_Days_Adjusted`) as `Days`,
               `dpt`.`Short_Desc`,
               COUNT(DISTINCT `r`.`p133_Recon_ID`) as `Count`
        FROM `p133_Recon` `r`
        INNER JOIN `cDepartment` `dpt` ON `dpt`.`DMS_DPT_Code` = `r`.`p133_cDepartment_Sales` AND $dptWhere AND `dpt`.`Dpt_Active` = 'Y'
        INNER JOIN `p133_Recon_Days_Log` `rdl` ON `rdl`.`Recon_Master_Log_ID` = `r`.`p133_Recon_ID`
        INNER JOIN `p133_Recon_Status` `rs` ON `r`.`p133_Recon_Status` = `rs`.`Recon_Stat_Code`
        INNER JOIN `M012_Tasks` `t` ON `t`.`M012_Task_Driver_Parm5` = `r`.`p133_Stock_no`
        WHERE `rs`.`p133_Recon_Status_ID` = '6' AND `t`.`M012_Task_Type` = 'Used Recon' AND `rs`.`Is_Recon` = 'Y' AND `r`.`p133_Stock_Car` = 'N'
                AND DATE_FORMAT(`r`.`p133_Recon_Completed`, '%Y-%m-01') = DATE_FORMAT('{$_REQUEST['period']}', '%Y-%m-01')
        GROUP BY `r`.`p133_cDepartment_Sales`";
$db->query($sql);
while ($row = $db->fetchRow())
{
    if (empty($data['Avg Sales Days'][$row['Short_Desc']]))
    {
        $data['Avg Sales Days'][$row['Short_Desc']] = array('Count' => 0, 'Days' => 0);
    }

    $data['Avg Sales Days'][$row['Short_Desc']]['Count'] = $row['Count'];
    $data['Avg Sales Days'][$row['Short_Desc']]['Days'] = $row['Days'];
}

/* Recon Current */
$sql = "SELECT SUM(`rdl`.`Status_Days_Adjusted`) as `Days`,
               `dpt`.`Short_Desc`,
               `rtca`.`p133_Recon_Time_Count_As_ID`,
               `rtca`.`p133_Target`,
               `r`.`p133_Recon_ID`,
               `r`.`p133_Stock_no`
        FROM `p133_Recon` `r`
        INNER JOIN `cDepartment` `dpt` ON `dpt`.`DMS_DPT_Code` = `r`.`p133_cDepartment_Sales` AND `dpt`.`Department_Types_ID` = 1 AND `dpt`.`Dpt_Active` = 'Y'
        INNER JOIN `p133_Recon_Days_Log` `rdl` ON `rdl`.`Recon_Master_Log_ID` = `r`.`p133_Recon_ID`
        INNER JOIN `p133_Recon_Time_Count_As` `rtca` ON `rtca`.`p133_Recon_Time_Count_As_ID` = `rdl`.`p133_Recon_Time_Count_As_ID`
        INNER JOIN `p133_Recon_Status` `rs` ON `r`.`p133_Recon_Status` = `rs`.`Recon_Stat_Code`
        INNER JOIN `M012_Tasks` `t` ON `t`.`M012_Task_Driver_Parm5` = `r`.`p133_Stock_no`
        WHERE `rs`.`p133_Recon_Status_ID` = '6' AND `t`.`M012_Task_Type` = 'Used Recon' AND `rs`.`Is_Recon` = 'Y' AND `r`.`p133_Stock_Car` = 'N'
                AND DATE_FORMAT(`r`.`p133_Recon_Completed`, '%Y-%m-01') = DATE_FORMAT('{$_REQUEST['period']}', '%Y-%m-01')
        GROUP BY `r`.`p133_Recon_ID`, `rtca`.`p133_Recon_Time_Count_As_ID`
        HAVING `Days` > `rtca`.`p133_Target`";

$sql = "SELECT * FROM ($sql) t
        GROUP BY `t`.`p133_Recon_ID`";
$db->query($sql);
while ($row = $db->fetchRow())
{
    $data['Recon Current'][$row['Short_Desc']]++;
}

/* Retail LMS Enquiry */
$sql = "SELECT `dpt`.`Short_Desc`,
               SUM(`l`.`p108_Lead_Type_ID` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 12)) as `Count`
        FROM `p108_Leads` `l`
        LEFT JOIN `cDepartment` `dpt` ON (`l`.`L_cDepartment_ID` = `dpt`.`cDepartment_ID`  AND `dpt`.`Department_Types_ID` = 1 AND `dpt`.`Dpt_Active` = 'Y')
        INNER JOIN `p108_Lead_Status` `ls` on (`l`.`p108_Lead_Status_ID` = `ls`.`p108_Lead_Status_ID`)
        WHERE `l`.`p108_Lead_Type_ID` > 0 AND 1 AND DATE(`l`.`L_Day`) = '%%1' AND `ls`.`Counts_Pen` = 'Y'
        GROUP BY `l`.`L_cDepartment_ID`";
$db->query($sql, Date::format('Y-m-d'));
while ($row = $db->fetchRow())
{
    $data['Retail LMS Enquiry'][$row['Short_Desc']] = $row['Count'];
}

/* Stock Cost & Stock Units */
$exportCodes = array();
foreach($departments as $department)
{
    $exportCodes[] = "DL\\\\DB\\\\SM\\\\{$department->DMS_DPT_Code}\\\\Target\\\\DelTarget";
}

$exportCodes = implode("', '", $exportCodes);

$sql = "SELECT * FROM `b005_MSTDET_Share`
        WHERE `b005_CODE` IN ('$exportCodes') AND DATE_FORMAT(`ASAT`, '%Y-%m-01') = '%%1'";
$db->query($sql, $_REQUEST['period']);

$deliveryTargets = array();
while ($row = $db->fetchRow())
{
    $DMS_DPT_Code = substr(substr($row['b005_CODE'], 9), 0, -17);
    $deliveryTargets[$DMS_DPT_Code] = $row['Result'];
}

$sql = "SELECT `dpt`.`Short_Desc`,
               COUNT(*) as `Units`,
               SUM(`vs`.`cost`) as `Cost`,
               `dpt`.`DMS_DPT_Code`
        FROM `p109_Veh_Stk` `vs`
        INNER JOIN `cDepartment` `dpt` ON `dpt`.`DMS_DPT_Code` = `vs`.`Dept` AND `dpt`.`Department_Types_ID` = 1 AND `dpt`.`Dpt_Active` = 'Y'
        WHERE `vs`.`p109_Current` = 'Y' AND `vs`.`Status` IN (10, 15) AND `vs`.`On_Order` = 'N' AND `vs`.`Stock_Type` <> 'D' AND `vs`.`Days` >= 0
        GROUP BY `Dept`
        ORDER BY `sort_code`";
$db->query($sql);
while ($row = $db->fetchRow())
{
    $data['Stock Cost'][$row['Short_Desc']] = $row['Cost'];
    $data['Stock Units'][$row['Short_Desc']] = $row['Units'];

    if (!empty($deliveryTargets[$row['DMS_DPT_Code']]) && $deliveryTargets[$row['DMS_DPT_Code']] > 0)
    {
        $data['Days Supply'][$row['Short_Desc']] = round($row['Units'] / $deliveryTargets[$row['DMS_DPT_Code']] * 30);
    }
}

/* Wholesale */
$sql = "SELECT `dpt`.`Short_Desc`,
               DATEDIFF(NOW(), `vehs`.`IntoStock_Date`) as `Days`
        FROM `p109_Veh_Stk` `vehs`
        INNER JOIN `cDepartment` `dpt` ON `dpt`.`DMS_DPT_Code` = `vehs`.`Dept` AND `dpt`.`Dpt_Active` = 'Y'
        WHERE `vehs`.`Dept` IN (180, 380) AND `vehs`.`On_Order` = 'N'";
$db->query($sql);
while ($row = $db->fetchRow())
{
    if ($row['Days'] < 10)
    {
        $data['Wholesale < 10 Days'][$row['Short_Desc']]++;
    }
    elseif ($row['Days'] >= 10 && $row['Days'] < 20)
    {
        $data['Wholesale 11 - 20 Days'][$row['Short_Desc']]++;
    }
    elseif ($row['Days'] >= 20)
    {
        $data['Wholesale > 20 Days'][$row['Short_Desc']]++;
    }
}

/* Deliveries Today */
$sql = "SELECT `dpt`.`Short_Desc`,
               SUM(DATE(`d`.`Cust_Handover_Date`) = '%%1') AS `Today`,
               SUM(DATE(`d`.`Cust_Handover_Date`) = '%%2') AS `Yesterday`,
               SUM(DATE(`d`.`Cust_Handover_Date`) = '%%3') AS `DayBefore`
        FROM `p077_Deal_Log` `d`
        INNER JOIN `cDepartment` `dpt` ON `dpt`.`DMS_DPT_Code` = `d`.`p077_DPTID` AND `dpt`.`Department_Types_ID` = 1 AND `sale_cat` = 'R' AND `dpt`.`Dpt_Active` = 'Y'
        GROUP BY `d`.`p077_DPTID`";
$db->query($sql, $today, $yesterday, $daybefore);
while ($row = $db->fetchRow())
{
    $data['Deliveries Today'][$row['Short_Desc']]['Today']     = $row['Today'];
    $data['Deliveries Today'][$row['Short_Desc']]['Yesterday'] = $row['Yesterday'];
    $data['Deliveries Today'][$row['Short_Desc']]['DayBefore'] = $row['DayBefore'];
}

/* Sales2Service Handover */
$sql = "SELECT `dpt`.`Short_Desc`,
               SUM(`d`.`STS_Handover` = 'Y') as `Y`,
               SUM(`d`.`STS_Handover` IN ('Y', 'N', 'DOS', 'NS')) as `All`
        FROM `p077_Deal_Log` `d`
        INNER JOIN `cDepartment` `dpt` ON `dpt`.`DMS_DPT_Code` = `d`.`p077_DPTID` AND `dpt`.`Department_Types_ID` = 1 AND `dpt`.`Dpt_Active` = 'Y'
        WHERE DATE(`d`.`Cust_Handover_Date`) = CURDATE()
        GROUP BY `d`.`p077_DPTID`";
$db->query($sql);
while ($row = $db->fetchRow())
{
    $data['Sales2Service Handover'][$row['Short_Desc']]['Y']   = $row['Today'];
    $data['Sales2Service Handover'][$row['Short_Desc']]['All'] = $row['All'];
}

/* Call Center Penetration */
$sql = "SELECT SUM(`nm`.`p101_NPS_Score` BETWEEN 9 AND 10) as `Promoters`,
               SUM(`nm`.`p101_NPS_Score` BETWEEN 7 AND 8) as `Passives`,
               SUM(`nm`.`p101_NPS_Score` BETWEEN 0 AND 6) as `Detractors`,
               COUNT(*) as `Total`,
               `dpt`.`Short_Desc`
        FROM `p101_NPS_Master` nm
        INNER JOIN `cDepartment` `dpt` ON `dpt`.`DMS_DPT_Code` = `nm`.`p101_AOC2` AND `dpt`.`Department_Types_ID` = 1 AND `dpt`.`Dpt_Active` = 'Y'
        WHERE DATE_FORMAT(`nm`.`p101_Interaction_Date`, '%Y-%m-01') = '%%1' AND `nm`.`p101_AOC1` = 'Sales'
        GROUP BY `dpt`.`DMS_DPT_Code`";
$db->query($sql, $_REQUEST['period']);
while ($row = $db->fetchRow())
{
    $data['Call Center Penetration'][$row['Short_Desc']]['Surveyed'] = $row['Promoters'] + $row['Passives'] + $row['Detractors'];
    $data['Call Center Penetration'][$row['Short_Desc']]['Total']    = $row['Total'];
}

/* NPS */
$sql = "SELECT SUM(`nm`.`p101_NPS_Score` BETWEEN 9 AND 10) as `Promoters`,
               SUM(`nm`.`p101_NPS_Score` BETWEEN 0 AND 6) as `Detractors`,
               SUM(`nm`.`p101_NPS_Score` > -1) as `Total`,
               `dpt`.`Short_Desc`
        FROM `p101_NPS_Master` nm
        INNER JOIN `cDepartment` `dpt` ON `dpt`.`DMS_DPT_Code` = `nm`.`p101_AOC2` AND `dpt`.`Department_Types_ID` = 1 AND `dpt`.`Dpt_Active` = 'Y'
        WHERE DATE(`nm`.`p101_Surveyed`) = CURDATE() AND `nm`.`p101_AOC1` = 'Sales' AND `nm`.`p101_NPS_Status` = 'Completed'
        GROUP BY `dpt`.`DMS_DPT_Code`";
$db->query($sql);
while ($row = $db->fetchRow())
{
    $data['NPS'][$row['Short_Desc']]['Promoters']  = $row['Promoters'];
    $data['NPS'][$row['Short_Desc']]['Detractors'] = $row['Detractors'];
    $data['NPS'][$row['Short_Desc']]['Total']      = $row['Total'];
}

/* Parts Not Fitted on Delivery (Outstanding) */
$sql = "SELECT `dpt`.`Short_Desc`,
               `dpt`.`DMS_DPT_Code`
        FROM `p077_Deal_Log` `d`
        INNER JOIN `p077_Deal_DET` `det` ON `det`.`p077_Deal_Log_ID` = `d`.`p077_Deal_Log_ID`
        INNER JOIN `p109_Veh_Stk` `veh` ON `veh`.`p109_STKNO` = `d`.`p077_Stock_Num`
        INNER JOIN `p077_Deal_PNFD_Status` `pnfds` USING(`p077_Deal_PNFD_Status_ID`)
        INNER JOIN `cDepartment` `dpt` ON `dpt`.`DMS_DPT_Code` = `d`.`p077_DPTID` AND `dpt`.`Department_Types_ID` = 1 AND `dpt`.`Dpt_Active` = 'Y'
        WHERE `pnfds`.`p077_PNFD_Completed_Status` = 'N' AND `pnfds`.`p077_Deal_PNFD_Status_ID` <> 4
        GROUP BY `d`.`p077_Deal_Log_ID`";
$sql = "SELECT *,
               COUNT(*) as `Count`
        FROM ($sql) t
        GROUP BY `t`.`DMS_DPT_Code`";
$db->query($sql);
while ($row = $db->fetchRow())
{
    if (isset($data['Parts Not Fitted on Delivery (Outstanding)'][$row['Short_Desc']]))
    {
        $data['Parts Not Fitted on Delivery (Outstanding)'][$row['Short_Desc']] = $row['Count'];
    }
}

/* Parts Not Fitted on Delivery (Booked) */
$sql = "SELECT `dpt`.`Short_Desc`,
               `dpt`.`DMS_DPT_Code`
        FROM `p077_Deal_Log` `d`
        INNER JOIN `p077_Deal_DET` `det` ON `det`.`p077_Deal_Log_ID` = `d`.`p077_Deal_Log_ID`
        INNER JOIN `p109_Veh_Stk` `veh` ON `veh`.`p109_STKNO` = `d`.`p077_Stock_Num`
        INNER JOIN `p077_Deal_PNFD_Status` `pnfds` USING(`p077_Deal_PNFD_Status_ID`)
        INNER JOIN `cDepartment` `dpt` ON `dpt`.`DMS_DPT_Code` = `d`.`p077_DPTID` AND `dpt`.`Department_Types_ID` = 1 AND `dpt`.`Dpt_Active` = 'Y'
        WHERE `pnfds`.`p077_Deal_PNFD_Status_ID` = 4
        GROUP BY `d`.`p077_Deal_Log_ID`";
$sql = "SELECT *,
               COUNT(*) as `Count`
        FROM ($sql) t
        GROUP BY `t`.`DMS_DPT_Code`";
$db->query($sql);
while ($row = $db->fetchRow())
{
    if (isset($data['Parts Not Fitted on Delivery (Booked)'][$row['Short_Desc']]))
    {
        $data['Parts Not Fitted on Delivery (Booked)'][$row['Short_Desc']] = $row['Count'];
    }
}

/* Target Gross */
$exportCodes = array();
foreach($departments as $department)
{
    $exportCodes[] = "DL\\\\DB\\\\SM\\\\{$department->DMS_DPT_Code}\\\\Target\\\\TOT_EX_FI";
}

$exportCodes = implode("', '", $exportCodes);

$sql = "SELECT * FROM `b005_MSTDET_Share`
        WHERE `b005_CODE` IN ('$exportCodes') AND DATE_FORMAT(`ASAT`, '%Y-%m-01') = '%%1'";
$db->query($sql, $_REQUEST['period']);

while ($row = $db->fetchRow())
{
    $b005_CODE = $row['b005_CODE'];
    $DMS_DPT_Code = substr(substr($b005_CODE, 9), 0, -17);
    $Short_Desc = $departments[$DMS_DPT_Code]->Short_Desc;
    $data['Target Gross'][$Short_Desc] = $row['Result'];
}

/* Target Units */
$exportCodes = array();
foreach($departments as $department)
{
    $exportCodes[] = "DL\\\\DB\\\\SM\\\\{$department->DMS_DPT_Code}\\\\Target\\\\DelTarget";
}

$exportCodes = implode("', '", $exportCodes);

$sql = "SELECT * FROM `b005_MSTDET_Share`
        WHERE `b005_CODE` IN ('$exportCodes') AND DATE_FORMAT(`ASAT`, '%Y-%m-01') = '%%1'";
$db->query($sql, $_REQUEST['period']);

while ($row = $db->fetchRow())
{
    $b005_CODE = $row['b005_CODE'];
    $DMS_DPT_Code = substr(substr($b005_CODE, 9), 0, -17);
    $Short_Desc = $departments[$DMS_DPT_Code]->Short_Desc;
    $data['Target Units'][$Short_Desc] = $row['Result'];
}

/* Weekly Dots Gross */
$exportCodes = array();
foreach($departments as $department)
{
    $exportCodes[] = "DL\\\\DB\\\\DPT\\\\{$department->DMS_DPT_Code}\\\\Dots\\\\Week1\\\\TOT_EX_FI";
    $exportCodes[] = "DL\\\\DB\\\\DPT\\\\{$department->DMS_DPT_Code}\\\\Dots\\\\Week2\\\\TOT_EX_FI";
    $exportCodes[] = "DL\\\\DB\\\\DPT\\\\{$department->DMS_DPT_Code}\\\\Dots\\\\Week3\\\\TOT_EX_FI";
    $exportCodes[] = "DL\\\\DB\\\\DPT\\\\{$department->DMS_DPT_Code}\\\\Dots\\\\Week4\\\\TOT_EX_FI";
}

$exportCodes = implode("', '", $exportCodes);

$sql = "SELECT * FROM `b005_MSTDET_Share`
        WHERE `b005_CODE` IN ('$exportCodes') AND DATE_FORMAT(`ASAT`, '%Y-%m-01') = '%%1'";
$db->query($sql, $_REQUEST['period']);

while ($row = $db->fetchRow())
{
    $b005_CODE = $row['b005_CODE'];
    $tokens = explode('\\', $b005_CODE);
    $DMS_DPT_Code = $tokens[3];
    $Short_Desc = $departments[$DMS_DPT_Code]->Short_Desc;
    $data['Weekly Dots Gross'][$Short_Desc] += $row['Result'];
}

/* Weekly Dots Units */
$exportCodes = array();
foreach($departments as $department)
{
    $exportCodes[] = "DL\\\\DB\\\\DPT\\\\{$department->DMS_DPT_Code}\\\\Dots\\\\Week1\\\\DelTarget";
    $exportCodes[] = "DL\\\\DB\\\\DPT\\\\{$department->DMS_DPT_Code}\\\\Dots\\\\Week2\\\\DelTarget";
    $exportCodes[] = "DL\\\\DB\\\\DPT\\\\{$department->DMS_DPT_Code}\\\\Dots\\\\Week3\\\\DelTarget";
    $exportCodes[] = "DL\\\\DB\\\\DPT\\\\{$department->DMS_DPT_Code}\\\\Dots\\\\Week4\\\\DelTarget";
}

$exportCodes = implode("', '", $exportCodes);

$sql = "SELECT * FROM `b005_MSTDET_Share`
        WHERE `b005_CODE` IN ('$exportCodes') AND DATE_FORMAT(`ASAT`, '%Y-%m-01') = '%%1'";
$db->query($sql, $_REQUEST['period']);

while ($row = $db->fetchRow())
{
    $b005_CODE = $row['b005_CODE'];
    $tokens = explode('\\', $b005_CODE);
    $DMS_DPT_Code = $tokens[3];
    $Short_Desc = $departments[$DMS_DPT_Code]->Short_Desc;
    $data['Weekly Dots Units'][$Short_Desc] += $row['Result'];
}

/* ITU (Individual) */
$departmentCodes = implode("', '", array_keys($departments));
$sql = "SELECT `p`.`cPeople_ID`,
               `p`.`SLMID`,
               `dpt`.`DMS_DPT_Code`
        FROM `cPeople` `p`
        INNER JOIN `cDepartment` `dpt` ON `dpt`.`cDepartment_ID` = `p`.`cDepartment` AND `dpt`.`Dpt_Active` = 'Y'
        WHERE `p`.`Active` <> 0 AND `dpt`.`DMS_DPT_Code` IN ('$departmentCodes')
        ORDER BY `p`.`cPeople_ID` ASC";
$db->query($sql);
$peopleDepartments = array();
$shareCodes = array();
while ($row = $db->fetchRow())
{
    $shareCodes[] = "DL\\\\DB\\\\SM\\\\{$row['SLMID']}\\\\Target\\\\DelTarget";
    $peopleDepartments[$row['SLMID']] = $row['DMS_DPT_Code'];
}

$shareCodes = implode("', '", $shareCodes);

$sql = "SELECT * FROM `b005_MSTDET_Share`
        WHERE `b005_CODE` IN ('$shareCodes') AND DATE_FORMAT(`ASAT`, '%Y-%m-01') = '%%1'";
$db->query($sql, $_REQUEST['period']);

while ($row = $db->fetchRow())
{
    $b005_CODE = $row['b005_CODE'];
    $SLMID = substr(substr($b005_CODE, 9), 0, -17);
    $DMS_DPT_Code = $peopleDepartments[$SLMID];
    $Short_Desc = $departments[$DMS_DPT_Code]->Short_Desc;

    if (!isset($data['ITU (Individual)'][$Short_Desc]))
    {
        $data['ITU (Individual)'][$Short_Desc] = 0;
    }

    $data['ITU (Individual)'][$Short_Desc] += $row['Result'];
}

/* Total Completed */
$sql = "SELECT `dpt`.`Short_Desc`,
               SUM(`h`.`completed_date` <> '0000-00-00 00:00:00' AND DATE(`p`.`Review_Due`) >= DATE(NOW())) as `Total_Completed`,
               SUM(DATE(`p`.`Review_Due`) > DATE(NOW())) as `Total_Scheduled`,
               SUM(`h`.`p115_Status` = 'Completed' AND `h`.`hr_status` = 'Ready') as `Total_Waiting_HR_Approval`,
               SUM(`h`.`p115_Status` = 'Completed' AND `h`.`mgrs_mgr_status` = 'Ready') as `Total_Waiting_Manager_Approval`,
               COUNT(*) as `Count`

        FROM `cDepartment` `dpt`
        INNER JOIN `cPeople` `p` ON `p`.`PhoneBook_cDepartment_ID` = `dpt`.`cDepartment_ID`
        INNER JOIN `p115_Per_R_Header` `h` ON `h`.`employee_id` = `p`.`cPeople_ID`
        WHERE `dpt`.`DMS_DPT_Code` IN ('$departmentCodes') AND `p`.`p115_Include` = 'Y' AND `p`.`Active` <> 0 AND `dpt`.`Show_Per_Review_Summary` = 'Y' AND `dpt`.`Dpt_Active` = 'Y'
        GROUP BY `dpt`.`DMS_DPT_Code`";

$db->query($sql);
while ($row = $db->fetchRow())
{
    $data['Total Completed'][$row['Short_Desc']] += $row['Total_Completed'];
    $data['Total Scheduled'][$row['Short_Desc']] += $row['Total_Scheduled'];
    $data['Total Waiting Manager Approval'][$row['Short_Desc']] += $row['Total_Waiting_Manager_Approval'];
    $data['Total Waiting HR Approval'][$row['Short_Desc']] += $row['Total_Waiting_HR_Approval'];
}
?>
<h3>Sales Departments</h3>
<table class="list-table highlight-row" cellpadding="0" cellspacing="0" id="sales-departments-table">
    <tr>
        <th>&nbsp;</th>
        <th>Total</th>
    <?php foreach ($departments as $department): ?>
        <th><?php echo $department->Short_Desc; ?></th>
    <?php endforeach ?>
    </tr>

    <?php $name = 'Sign ups'; $totals = array('Today' => 0, 'Yesterday' => 0, 'DayBefore' => 0) ?>
    <tr>
        <td><?php echo $name; ?></td>
    <?php ob_start() ?>
    <?php foreach ($departments as $department): ?>
        <td class="align-center">
        <?php if (isset($data[$name][$department->Short_Desc])): ?>
            <?php if ($data[$name][$department->Short_Desc]['Today'] > 0 || $data[$name][$department->Short_Desc]['Yesterday'] > 0 || $data[$name][$department->Short_Desc]['DayBefore'] > 0): ?>
                <a target="_blank" href="/p078_dealerd1/p077_DealLog1/SM_Main/SM_Stats_Daily.php?periodcombo=S<?php echo Date::format('Y-m-01') ?>&category=&person=&orderby=1&department=<?php echo $department->DMS_DPT_Code; ?>"><?php echo number_format($data[$name][$department->Short_Desc]['Today'], 0); ?> / <?php echo number_format($data[$name][$department->Short_Desc]['Yesterday'], 0); ?> / <?php echo number_format($data[$name][$department->Short_Desc]['DayBefore'], 0); ?></a>
            <?php else: ?>
                &nbsp;
            <?php endif ?>

            <?php $totals['Today'] += $data[$name][$department->Short_Desc]['Today']; ?>
            <?php $totals['Yesterday'] += $data[$name][$department->Short_Desc]['Yesterday']; ?>
            <?php $totals['DayBefore'] += $data[$name][$department->Short_Desc]['DayBefore']; ?>
        <?php else: ?>
            &nbsp;
        <?php endif ?>
        </td>
    <?php endforeach ?>
    <?php $html = ob_get_clean() ?>

        <td class="align-center bold">
            <?php if ($totals['Today'] > 0 || $totals['Yesterday'] > 0 || $totals['DayBefore'] > 0): ?>
                <a target="_blank" href="/p078_dealerd1/p077_DealLog1/SM_Main/SM_Stats_Daily.php?periodcombo=S<?php echo Date::format('Y-m-01') ?>&category=&person=&orderby=1&department="><?php echo number_format($totals['Today'], 0); ?> / <?php echo number_format($totals['Yesterday'], 0); ?> / <?php echo number_format($totals['DayBefore'], 0); ?></a>
            <?php else: ?>
                &nbsp;
            <?php endif ?>
        </td>

        <?php echo $html; ?>
    </tr>

    <tr><td colspan="<?php echo count($departments) + 2; ?>">&nbsp;</td></tr>

    <?php $name = 'Retail LMS Enquiry'; $total = 0 ?>
    <tr>
        <td><?php echo $name; ?></td>
    <?php ob_start() ?>
    <?php foreach ($departments as $department): ?>
        <td class="align-center">
        <?php if (isset($data[$name][$department->Short_Desc])): ?>
            <?php if ($data[$name][$department->Short_Desc] > 0): ?>
                <a target="_blank" href="/p078_dealerd1/p108_Lead_Management/Stats/Traffic_Stats.php?status=%40AllCounts&salesperson=&category=&start=<?php echo Date::format('d/m/Y'); ?>&end=<?php echo Date::format('d/m/Y'); ?>&type=0&leadTypeId=&advertising=&postcode=%40All&department=<?php echo $department->cDepartment_ID; ?>"><?php echo number_format($data[$name][$department->Short_Desc], 0); ?></a>
            <?php else: ?>
                &nbsp;
            <?php endif ?>
            <?php $total += $data[$name][$department->Short_Desc] ?>
        <?php else: ?>
            &nbsp;
        <?php endif ?>
        </td>
    <?php endforeach ?>
    <?php $html = ob_get_clean() ?>

        <td class="align-center bold">
            <?php if ($total > 0): ?>
                <a target="_blank" href="/p078_dealerd1/p108_Lead_Management/Stats/Traffic_Stats.php?status=%40AllCounts&salesperson=&category=&start=<?php echo Date::format('d/m/Y'); ?>&end=<?php echo Date::format('d/m/Y'); ?>&type=0&leadTypeId=&advertising=&postcode=%40All&department="><?php echo number_format($total, 0); ?></a>
            <?php else: ?>
                &nbsp;
            <?php endif ?>
        </td>

        <?php echo $html; ?>
    </tr>

    <tr><td colspan="<?php echo count($departments) + 2; ?>">&nbsp;</td></tr>

    <?php $name = 'Orphans (Net) >30min'; $total = 0 ?>
    <tr>
        <td><?php echo $name; ?></td>
    <?php ob_start() ?>
    <?php foreach ($departments as $department): ?>
        <td class="align-center">
        <?php if (isset($data[$name][$department->Short_Desc])): ?>
            <?php if ($data[$name][$department->Short_Desc] > 0): ?>
                <a target="_blank" href="/p078_dealerd1/p108_Lead_Management/Diary/Diary_Log.php?department=<?php echo $department->cDepartment_ID; ?>&status=1&salesperson=%40All&leadTypeId=3&category=&search=&deal=&publication=&mediaType=&campaign=&reminderOn=%40All&dataQ=&from_search=&refund_request=@All"><?php echo number_format($data[$name][$department->Short_Desc], 0); ?></a>
            <?php else: ?>
                &nbsp;
            <?php endif ?>
            <?php $total += $data[$name][$department->Short_Desc]; ?>
        <?php else: ?>
            0
        <?php endif ?>
        </td>
    <?php endforeach ?>
    <?php $html = ob_get_clean() ?>

        <td class="align-center bold">
            <?php if ($total > 0): ?>
                <a target="_blank" href="/p078_dealerd1/p108_Lead_Management/Diary/Diary_Log.php?department=&status=1&salesperson=%40All&leadTypeId=3&category=&search=&deal=&publication=&mediaType=&campaign=&reminderOn=%40All&dataQ=&from_search=&refund_request=@All"><?php echo number_format($total, 0); ?></a>
            <?php else: ?>
                &nbsp;
            <?php endif ?>
        </td>

        <?php echo $html; ?>
    </tr>

    <?php $name = 'Orphans >1hr'; $total = 0 ?>
    <tr>
        <td><?php echo $name; ?></td>
    <?php ob_start() ?>
    <?php foreach ($departments as $department): ?>
        <td class="align-center">
        <?php if (isset($data[$name][$department->Short_Desc])): ?>
            <?php if ($data[$name][$department->Short_Desc] > 0): ?>
                <a target="_blank" href="/p078_dealerd1/p108_Lead_Management/Diary/Diary_Log.php?department=<?php echo $department->cDepartment_ID; ?>&status=1&salesperson=%40All&leadTypeId=&category=&search=&deal=&publication=&mediaType=&campaign=&reminderOn=%40All&dataQ=&from_search=&refund_request=@All"><?php echo number_format($data[$name][$department->Short_Desc], 0); ?></a>
            <?php else: ?>
                &nbsp;
            <?php endif ?>
            <?php $total += $data[$name][$department->Short_Desc]; ?>
        <?php else: ?>
            0
        <?php endif ?>
        </td>
    <?php endforeach ?>
    <?php $html = ob_get_clean() ?>

        <td class="align-center bold">
            <?php if ($total > 0): ?>
                <a target="_blank" href="/p078_dealerd1/p108_Lead_Management/Diary/Diary_Log.php?department=&status=1&salesperson=%40All&leadTypeId=&category=&search=&deal=&publication=&mediaType=&campaign=&reminderOn=%40All&dataQ=&from_search=&refund_request=@All"><?php echo number_format($total, 0); ?></a>
            <?php else: ?>
                &nbsp;
            <?php endif ?>
        </td>

        <?php echo $html; ?>
    </tr>

    <?php $name = 'Orphans >16hrs'; $total = 0 ?>
    <tr>
        <td><?php echo $name; ?></td>
    <?php ob_start() ?>
    <?php foreach ($departments as $department): ?>
        <td class="align-center">
        <?php if (isset($data[$name][$department->Short_Desc])): ?>
            <?php if ($data[$name][$department->Short_Desc] > 0): ?>
                <a target="_blank" href="/p078_dealerd1/p108_Lead_Management/Diary/Diary_Log.php?department=<?php echo $department->cDepartment_ID; ?>&status=1&salesperson=%40All&leadTypeId=&category=&search=&deal=&publication=&mediaType=&campaign=&reminderOn=%40All&dataQ=&from_search=&refund_request=@All"><?php echo number_format($data[$name][$department->Short_Desc], 0); ?></a>
            <?php else: ?>
                &nbsp;
            <?php endif ?>
            <?php $total += $data[$name][$department->Short_Desc]; ?>
        <?php else: ?>
            0
        <?php endif ?>
        </td>
    <?php endforeach ?>
    <?php $html = ob_get_clean() ?>

        <td class="align-center bold">
            <?php if ($total > 0): ?>
                <a target="_blank" href="/p078_dealerd1/p108_Lead_Management/Diary/Diary_Log.php?department=&status=1&salesperson=%40All&leadTypeId=&category=&search=&deal=&publication=&mediaType=&campaign=&reminderOn=%40All&dataQ=&from_search=&refund_request=@All"><?php echo number_format($total, 0); ?></a>
            <?php else: ?>
                &nbsp;
            <?php endif; ?>
        </td>

        <?php echo $html; ?>
    </tr>

    <?php $name = 'Internet Escalated'; $total = 0 ?>
    <tr>
        <td><?php echo $name; ?></td>
    <?php ob_start() ?>
    <?php foreach ($departments as $department): ?>
        <td class="align-center">
        <?php if (isset($data[$name][$department->Short_Desc])): ?>
            <?php if ($data[$name][$department->Short_Desc] > 0): ?>
                <a target="_blank" href="/p078_dealerd1/p108_Lead_Management/Diary/Diary_Log.php?department=<?php echo $department->cDepartment_ID; ?>&status=@AllActive&salesperson=@All&leadTypeId=3&category=&search=&deal=&publication=&mediaType=&campaign=&reminderOn=@All&dataQ=&from_search=&refund_request=@All"><?php echo number_format($data[$name][$department->Short_Desc], 0); ?></a>
            <?php else: ?>
                &nbsp;
            <?php endif ?>
            <?php $total += $data[$name][$department->Short_Desc]; ?>
        <?php else: ?>
            0
        <?php endif ?>
        </td>
    <?php endforeach ?>
    <?php $html = ob_get_clean() ?>

        <td class="align-center bold">
            <?php if ($total > 0): ?>
                <a target="_blank" href="/p078_dealerd1/p108_Lead_Management/Diary/Diary_Log.php?department=&status=@AllActive&salesperson=@All&leadTypeId=3&category=&search=&deal=&publication=&mediaType=&campaign=&reminderOn=@All&dataQ=&from_search=&refund_request=@All"><?php echo number_format($total, 0); ?></a>
            <?php else: ?>
                &nbsp;
            <?php endif; ?>
        </td>

        <?php echo $html; ?>
    </tr>

    <tr><td colspan="<?php echo count($departments) + 2; ?>">&nbsp;</td></tr>

    <?php $name = "Lost >$lostDays day"; $total = 0 ?>
    <tr>
        <td><?php echo $name; ?></td>
    <?php ob_start() ?>
    <?php foreach ($departments as $department): ?>
        <td class="align-center">
        <?php if (isset($data[$name][$department->Short_Desc])): ?>
            <?php if ($data[$name][$department->Short_Desc] > 0): ?>
                <a target="_blank" href="/p078_dealerd1/p108_Lead_Management/Diary/Diary_Log.php?department=<?php echo $department->cDepartment_ID; ?>&status=11&salesperson=%40All&leadTypeId=&category=&search=&deal=&publication=&mediaType=&campaign=&reminderOn=%40All&dataQ=&from_search=&refund_request=@All"><?php echo number_format($data[$name][$department->Short_Desc], 0); ?></a>
            <?php else: ?>
                &nbsp;
            <?php endif; ?>
            <?php $total += $data[$name][$department->Short_Desc]; ?>
        <?php else: ?>
            0
        <?php endif ?>
        </td>
    <?php endforeach ?>
    <?php $html = ob_get_clean() ?>

        <td class="align-center bold">
            <?php if ($total > 0): ?>
                <a target="_blank" href="/p078_dealerd1/p108_Lead_Management/Diary/Diary_Log.php?department=&status=11&salesperson=%40All&leadTypeId=&category=&search=&deal=&publication=&mediaType=&campaign=&reminderOn=%40All&dataQ=&from_search=&refund_request=@All"><?php echo number_format($total, 0); ?></a>
            <?php else: ?>
                &nbsp;
            <?php endif ?>
        </td>

        <?php echo $html; ?>
    </tr>

    <tr><td colspan="<?php echo count($departments) + 2; ?>">&nbsp;</td></tr>

    <?php $name = 'Target Gross'; $total = 0 ?>
    <tr>
        <td><?php echo $name; ?></td>
    <?php ob_start() ?>
    <?php foreach ($departments as $department): ?>
        <td class="align-center">
        <?php if (isset($data[$name][$department->Short_Desc]) && $data[$name][$department->Short_Desc] > 0): ?>
            <a target="blank" href="/p078_dealerd1/Dashboard1/Sales_Dashboard/Targets.php?month=<?php echo $_REQUEST['period']; ?>&department=<?php echo $department->DMS_DPT_Code; ?>">$<?php echo number_format($data[$name][$department->Short_Desc], 0); ?></a>
            <?php $total += $data[$name][$department->Short_Desc]; ?>
        <?php else: ?>
            $0
        <?php endif ?>
        </td>
    <?php endforeach ?>
        <?php $html = ob_get_clean() ?>

        <td class="align-center bold">
        <?php if ($total > 0): ?>
            <a target="blank" href="/p078_dealerd1/Dashboard1/Sales_Dashboard/Targets.php?month=<?php echo $_REQUEST['period']; ?>&department=@All">$<?php echo number_format($total, 0); ?></a>
        <?php else: ?>
            $0
        <?php endif ?>
        </td>

        <?php echo $html; ?>
    </tr>

    <?php $name = 'Target Units'; $total = 0 ?>
    <tr>
        <td><?php echo $name; ?></td>
    <?php ob_start() ?>
    <?php foreach ($departments as $department): ?>
        <td class="align-center">
        <?php if (isset($data[$name][$department->Short_Desc]) && $data[$name][$department->Short_Desc] > 0): ?>
            <a target="blank" href="/p078_dealerd1/Dashboard1/Sales_Dashboard/Targets.php?month=<?php echo $_REQUEST['period']; ?>&department=<?php echo $department->DMS_DPT_Code; ?>"><?php echo number_format($data[$name][$department->Short_Desc], 0); ?></a>
            <?php $total += $data[$name][$department->Short_Desc]; ?>
        <?php else: ?>
            0
        <?php endif ?>
        </td>
    <?php endforeach ?>
        <?php $html = ob_get_clean() ?>

        <td class="align-center bold">
        <?php if ($total > 0): ?>
            <a target="blank" href="/p078_dealerd1/Dashboard1/Sales_Dashboard/Targets.php?month=<?php echo $_REQUEST['period']; ?>&department=@All"><?php echo number_format($total, 0); ?></a>
        <?php else: ?>
            0
        <?php endif ?>
        </td>

        <?php echo $html; ?>
    </tr>

    <?php $name = 'Weekly Dots Gross'; $total = 0 ?>
    <tr>
        <td><?php echo $name; ?></td>
    <?php ob_start() ?>
    <?php foreach ($departments as $department): ?>
        <td class="align-center">
        <?php if (isset($data[$name][$department->Short_Desc]) && $data[$name][$department->Short_Desc] > 0): ?>
            <a target="blank" href="/p078_dealerd1/Dashboard1/Sales_Dashboard/Weekly_Targets.php?month=<?php echo $_REQUEST['period']; ?>">$<?php echo number_format($data[$name][$department->Short_Desc], 0); ?></a>
            <?php $total += $data[$name][$department->Short_Desc]; ?>
        <?php else: ?>
            $0
        <?php endif ?>
        </td>
    <?php endforeach ?>
        <?php $html = ob_get_clean() ?>

        <td class="align-center bold">
        <?php if ($total > 0): ?>
            <a target="blank" href="/p078_dealerd1/Dashboard1/Sales_Dashboard/Weekly_Targets.php?month=<?php echo $_REQUEST['period']; ?>">$<?php echo number_format($total, 0); ?></a>
        <?php else: ?>
            $0
        <?php endif ?>
        </td>

        <?php echo $html; ?>
    </tr>

    <?php $name = 'Weekly Dots Units'; $total = 0 ?>
    <tr>
        <td><?php echo $name; ?></td>
    <?php ob_start() ?>
    <?php foreach ($departments as $department): ?>
        <td class="align-center">
        <?php if (isset($data[$name][$department->Short_Desc]) && $data[$name][$department->Short_Desc] > 0): ?>
            <a target="blank" href="/p078_dealerd1/Dashboard1/Sales_Dashboard/Weekly_Targets.php?month=<?php echo $_REQUEST['period']; ?>"><?php echo number_format($data[$name][$department->Short_Desc], 0); ?></a>
            <?php $total += $data[$name][$department->Short_Desc]; ?>
        <?php else: ?>
            0
        <?php endif ?>
        </td>
    <?php endforeach ?>
        <?php $html = ob_get_clean() ?>

        <td class="align-center bold">
        <?php if ($total > 0): ?>
            <a target="blank" href="/p078_dealerd1/Dashboard1/Sales_Dashboard/Weekly_Targets.php?month=<?php echo $_REQUEST['period']; ?>"><?php echo number_format($total, 0); ?></a>
        <?php else: ?>
            0
        <?php endif ?>
        </td>

        <?php echo $html; ?>
    </tr>

    <?php $name = 'ITU (Individual)'; $total = 0 ?>
    <tr>
        <td><?php echo $name; ?></td>
    <?php ob_start() ?>
    <?php foreach ($departments as $department): ?>
        <td class="align-center">
        <?php if (isset($data[$name][$department->Short_Desc]) && $data[$name][$department->Short_Desc] > 0): ?>
            <a target="blank" href="/p078_dealerd1/Dashboard1/Sales_Dashboard/Targets.php?month=<?php echo $_REQUEST['period']; ?>&department=<?php echo $department->DMS_DPT_Code; ?>"><?php echo number_format($data[$name][$department->Short_Desc], 0); ?></a>
            <?php $total += $data[$name][$department->Short_Desc]; ?>
        <?php else: ?>
            0
        <?php endif ?>
        </td>
    <?php endforeach ?>
        <?php $html = ob_get_clean() ?>

        <td class="align-center bold">
        <?php if ($total > 0): ?>
            <a target="blank" href="/p078_dealerd1/Dashboard1/Sales_Dashboard/Targets.php?month=<?php echo $_REQUEST['period']; ?>&department=@All"><?php echo number_format($total, 0); ?></a>
        <?php else: ?>
            0
        <?php endif ?>
        </td>

        <?php echo $html; ?>
    </tr>

    <?php $name = 'GSM Dashboard'; $total = 0 ?>
    <tr>
        <td><?php echo $name; ?></td>

        <td class="align-center">
            [<a target="_blank" href="/p078_dealerd1/Dashboard1/GSM_Dashboard/index.php?month=<?php echo $_REQUEST['period']; ?>&department=@All">Dash</a>]
        </td>

    <?php foreach ($departments as $department): ?>
        <?php $departmentFilter = '@All'; ?>
        <?php if (!empty($department->Franchise)): ?>
            <?php $departmentFilter = '$Franchise-' . $department->Franchise; ?>
        <?php elseif ($department->Location_Group == 'Booval'): ?>
            <?php $departmentFilter = '@1' ?>
        <?php elseif ($department->Location_Group == 'Laidley'): ?>
            <?php $departmentFilter = '@2' ?>
        <?php endif ?>
        <td class="align-center">
            [<a target="_blank" href="/p078_dealerd1/Dashboard1/GSM_Dashboard/index.php?month=<?php echo $_REQUEST['period']; ?>&department=<?php echo $departmentFilter ?>">Dash</a>]
        </td>
    <?php endforeach ?>
    </tr>

    <?php $name = 'Deal Situation'; $total = 0 ?>
    <tr>
        <td><?php echo $name; ?></td>
    <?php ob_start() ?>
    <?php foreach ($departments as $department): ?>
        <td class="align-center">
        <?php if (isset($data[$name][$department->Short_Desc])): ?>
            <?php if ($data[$name][$department->Short_Desc] > 0): ?>
                <a target="_blank" href="/p078_dealerd1/p077_DealLog1/PD_Main/Deal_Situation_Overview.php?month=<?php echo $_REQUEST['period']; ?>&category=@All&person=@All&fin_manager=@All&show_for=@All&department=<?php echo $department->DMS_DPT_Code; ?>&reference_no=@Stock"><?php echo number_format($data[$name][$department->Short_Desc], 0); ?></a>
            <?php else: ?>
                &nbsp;
            <?php endif; ?>
            <?php $total += $data[$name][$department->Short_Desc]; ?>
        <?php else: ?>
            &nbsp;
        <?php endif ?>
        </td>
    <?php endforeach ?>
        <?php $html = ob_get_clean() ?>

        <td class="align-center bold">
            <?php if ($total > 0): ?>
                <a target="_blank" href="/p078_dealerd1/p077_DealLog1/PD_Main/Deal_Situation_Overview.php?month=<?php echo $_REQUEST['period']; ?>&category=@All&reference_no=@Stock&person=@All&fin_manager=@All&show_for=@All&department=@All"><?php echo number_format($total, 0); ?></a>
            <?php else: ?>
                &nbsp;
            <?php endif ?>
        </td>

        <?php echo $html; ?>
    </tr>

    <?php $total = array('Count' => 0, 'Total' => 0); ?>
    <tr>
        <td>Sold Out Of Stock</td>
        <?php ob_start() ?>
        <?php foreach ($departments as $department): ?>
            <td class="align-center">
                <?php if (isset($soldOutOfStock[$department->DMS_DPT_Code])): ?>
                    <?php if ($soldOutOfStock[$department->DMS_DPT_Code]['Count'] > 0): ?>
                        <a target="_blank" href="/p078_dealerd1/p077_DealLog1/SM_Main/SM_DealLog.php?periodcombo=<?php echo $_REQUEST['periodcombo']; ?>&department=<?php echo $department->DMS_DPT_Code; ?>&person=@All&sp_type=@All&rdrclass=@All&frn=@All&range=@All&cust_handover=@All"><?php echo number_to_percent($soldOutOfStock[$department->DMS_DPT_Code]['Count'] / $soldOutOfStock[$department->DMS_DPT_Code]['Total'], 0); ?></a>
                    <?php else: ?>
                        &nbsp;
                    <?php endif; ?>
                    <?php $total['Count'] += $soldOutOfStock[$department->DMS_DPT_Code]['Count']; ?>
                    <?php $total['Total'] += $soldOutOfStock[$department->DMS_DPT_Code]['Total']; ?>
                <?php else: ?>
                    &nbsp;
                <?php endif ?>
            </td>
        <?php endforeach ?>
        <?php $html = ob_get_clean() ?>

        <td class="align-center bold">
            <?php if ($total['Count'] > 0): ?>
                <a target="_blank" href="/p078_dealerd1/p077_DealLog1/SM_Main/SM_DealLog.php?periodcombo=<?php echo $_REQUEST['periodcombo']; ?>&department=@All&person=@All&sp_type=@All&rdrclass=@All&frn=@All&range=@All&cust_handover=@All"><?php echo number_to_percent($total['Count'] / $total['Total'], 0); ?></a>
            <?php else: ?>
                &nbsp;
            <?php endif ?>
        </td>

        <?php echo $html; ?>
    </tr>

    <tr><td colspan="<?php echo count($departments) + 2; ?>">&nbsp;</td></tr>
    <tr><td colspan="<?php echo count($departments) + 2; ?>">&nbsp;</td></tr>
    <tr><td colspan="<?php echo count($departments) + 2; ?>"…