How to optimize the checkerboard filling algorithm?

Question

Algorithms

Hello!
I ask for hints or advice :) in terms of optimizing the algorithm for compiling a chessboard. The bottom line is that the company is engaged in renting out apartments. A chessboard is a table whose rows are apartments and whose columns are daily dates. In the cell, the number indicates the cost of renting an apartment for the specified day, and the application for rent is highlighted in color depending on the status (booked / occupied) and the source of the application (booking, etc. in one color, the rest in another). In addition, 1-2 responsible employees are assigned to each apartment, according to which it is tedious to group apartments in a checkerboard pattern.
It looks like this:

Now the formation of a checkerboard is completely performed on the server according to the following algorithm:
1. An array of dates for the report period is formed
2. An array of unique combinations of employees responsible for apartments is formed
3. A cycle is launched through an array of unique combinations of employees responsible for apartments
4. Inside this cycle, a cycle is launched on apartments for which the current combination of employees is responsible
5. Inside the apartment cycle, a cycle is launched on dates from item 1
6. At each step of this cycle, an application for renting an apartment is searched for that falls on the current date from the cycle. The status and source of the application is also checked to form the color of the cell in a checkerboard.
7. Also, at each step, a special apartment price value from another table is searched. If it is not found, then the standard cost value is taken from the apartment card
8. The collected data array is transmitted via smarty to the checkerboard output.
The problem is that with the simultaneous work of 3-10 employees, this structure opens up to 2 minutes over a period of 30 days. The processor load is 100%.
Perhaps there are some ideas for optimizing the algorithm for collecting an array of data for display?

Answer 1

1. MySQL on the same server
2. php->smarty
3. below

// установка дат начала и окончания отчёта
if ($_REQUEST['date1']) $date1 = date("d.m.Y",strtotime(form_eng_time($_REQUEST['date1'])));
else $date1 = date("d.m.Y", mktime(0, 0, 0, date("m"), date("j")-2, date("Y")));                   
if ($_REQUEST['date2']) $date2 = date("d.m.Y",strtotime(form_eng_time($_REQUEST['date2'])));
else $date2 = date("d.m.Y", mktime(0, 0, 0, date("m"), date("j")+16, date("Y")));
$daysOfWeek = array('вс', 'пн', 'вт', 'ср', 'чт' , 'пт', 'сб');

    // массив с датами от $date1 до $date2
for ($i = strtotime($date1); $i <= strtotime($date2); $i+=86400) {
    $Report['ReportDate'] = date("d.m.Y", $i);
    $Report['isToday'] = ($Report['ReportDate']==date("d.m.Y")) ? "true" : "false";
    $Report['dayOfWeek'] = $daysOfWeek[date("w", $i)];
    $Report2['ReportDate'] = date("d.m", $i);
    $Report2['isToday'] = (date("d.m.Y", $i)==date("d.m.Y")) ? "true" : "false";
    $Report2['dayOfWeek'] = $daysOfWeek[date("w", $i)];
    $ReportDates[] = $Report;
    $ReportDates2[] = $Report2;
    $ReportDates_short[] = $Report['ReportDate'];    // массив для внутреннего цикла в шахматке
}

$arr = array('Забронировано', 'Заселён', 'Оплата', 'Продление', 'Переезд', 'Долг', 'Выезд', 'Закрыто'); // массив для статусов "платных" размещений
if ($_REQUEST['_showfree']=='true') $show_ = 'yes';

    // селект источников бронирования
$sourcesList = '<option></option>';
$res = data_select_field(130, 'id, f1740 AS name', "status=0 AND f1840='Да' ORDER BY f1740");
while ($row = sql_fetch_assoc($res)) {
    $i = $row['id'];
    $n = $row['name'];
    $sourcesList .= '<option value="'.$i.'">'.$n.'</option>';
}

    // формируем массив сочетаний по горничным, ответственным за квартиры
$maids = array();
$res = data_select_field(20, 'f2780 AS maid', "status=0");
while ($row = sql_fetch_assoc($res)) {
    $key = $row['maid'].'~~';
    if (!array_key_exists($key, $maids)) {
        $m = explode("-", $row['maid']);
        foreach ($m as $maid) {
            $maid = preg_replace('/\D/i', '', $maid);
            if ($maid) {
                $res_m = sql_query("SELECT fio FROM ".USERS_TABLE." WHERE id='".$maid."' LIMIT 1");
                $row_m = sql_fetch_assoc($res_m);
                $ms .= $row_m['fio'].'~~';  
            }      
        }        
        $maids[$key]['maids'] = $ms;
        $maids[$key]['color'] = ($i%2) ? 'NavajoWhite' : 'BurlyWood';     // разные цвета для чётных и нечётных
        $ms='';
        $i++;          
    }
}

    // сама шахматка
    // первый цикл по массиву сочетаний горничных
$last = 'start';
foreach ($maids as $key_maids=>$value_maids) {
    $maids = substr($key_maids, 0, -2);
        // цикл - по квартирам
    $res1 = data_select_field(20, 'id, f150 AS Name, f360 AS Price', "status=0 AND f2780='".$maids."' ORDER BY f3640");
    while ($row1 = sql_fetch_assoc($res1)) {
        if ($key_maids != $last) {
            $last = $key_maids;
            $ReportFlats_['flat'] = str_replace("~~", "<br>", substr($value_maids['maids'], 0, -2));
            $ReportFlats_['isMaid'] = 'true';
            $ReportFlats_['background'] = $value_maids['color'];
            $ReportFlats[] = $ReportFlats_;
            unset($ReportFlats_);
            $data1['isMaid'] = 'true';
            $lines[] = $data1;
            unset($data1);
            
        }
        $ReportFlats_['flat'] = $row1['Name'];
        $ReportFlats_['background'] = $value_maids['color'];    // раскрашиваем
        $flat = $row1['id'];
        foreach ($ReportDates_short as $reportDate) {
                // формируем даты в верном формате для вычислений
            $shortDate = date("Y-m-d", strtotime($reportDate));    // дата для "шапки" отчёта
            $date_start = date("Y-m-d H:i:s", strtotime($shortDate.' 12:00:01'));   
            $date_start_null = date("Y-m-d H:i:s", strtotime($shortDate.' 00:00:00'));
            $date_end = date("Y-m-d H:i:s", strtotime($shortDate.' 12:00:00')+86400);    
                // определяем стоимость квартиры на день из цикла
            $res_pp = data_select_field(180, 'f2170 AS customPrice', "status=0 AND f2150='".$flat."' AND f2160='".$date_start_null."' ORDER BY add_time DESC LIMIT 1");
            $row_pp = sql_fetch_assoc($res_pp);        
           
                // находим размещение на дату из цикла $reportDate
            $res3 = data_select_field(30, 
                                     'id, f260 AS account, f460 AS Stage, f280 AS startDate, f290 AS endDate, f470 AS comments, f1600 AS debt, f350 AS AllRevenue, f340 AS AddRevenue, f330 AS Additions, f1610 AS source, f1590 AS avance', 
                                     "status=0 AND f270='".$flat."' AND f280<='".$date_end."' AND f290>='".$date_start."' AND f460 NOT IN ('Отказ', 'Снял бронь', 'Передано партнёрам', 'Не заезд', 'Новая заявка', 'В работе', 'Клиент')");
            $row3 = sql_fetch_assoc($res3);
            $row3_startDate = date("Y-m-d 12:00:01", strtotime($row3['startDate']));
                // коды
            if (!$row3['id']) $isfree[$flat] = true;
            $data1[$reportDate]['tdId'] = 'td_'.$shortDate.'.'.$flat;    // Id ячейки td
            $data1[$reportDate]['orderId'] = $row3['id'];
            //$data1[$reportDate]['value'] = (in_array($row3['Stage'], $arr)) ? (($row_pp['customPrice']) ? form_local_number($row_pp['customPrice'], '0/10') : form_local_number($row1['Price'], '0/10')) : 0; // стоимость размещения
            //$data1[$reportDate]['value_nonformat'] = (in_array($row3['Stage'], $arr)) ? (($row_pp['customPrice']) ? $row_pp['customPrice'] : $row1['Price']) : 0;
            $data1[$reportDate]['price'] = ($row_pp['customPrice']) ? $row_pp['customPrice'] : $row1['Price']; // стоимость квартиры
                      
            $Result1_[$reportDate]++;    // прибавляем 1 для учёта квартир
                // учёт продлений, для этого находим предыдущее размещение
            $res3_ = data_select_field(30, 'id', "status=0 AND f270='".$flat."' AND f290='".$row3_startDate."' AND f460='Продление' AND f260='".$row3['account']."' LIMIT 1");
            $row3_ = sql_fetch_assoc($res3_);
            $isExtension = ($row3_['id']) ? 'true' : 'false';    // цвет LightGreen для продления
                // учёт забронированных
            if ($row3['Stage'] == 'Забронировано') {
                $data1[$reportDate]['color'] = ($row3_startDate == $date_start) ? ((!$row3['avance']) ? '#FA8072' : '#DC143C') : '#40E0D0';    // цвет Salmon для забронированных без аванса, Crimson для брони с авансом, 1 день проживания и Turqouse для забронированных, проживание 2+ день
                $Result1_[$reportDate]--;    // отнимаем 1 для учёта свободных квартир
            }
            if (in_array($row3['Stage'], $arr) && $row3['Stage'] != 'Забронировано') {
                $data1[$reportDate]['color'] = ($isExtension=='true') ? '#90EE90' : (($row3_startDate == $date_start) ? '#FFFF00' : '#00FF00');    // цвет LightGreen для продления, цвет Lime для проживания и цвет Yellow для первого дня проживания
                $Result1_[$reportDate]--;    // отнимаем 1 для учёта свободных квартир
            }
            $data1[$reportDate]['isDebt'] = ($row3['debt']>0) ? 'true' : 'false';
                    // учёт цвета для часовых заселений
            $data1[$reportDate]['color'] = (in_array('На часы', explode("\r\n", $row3['Additions']))) ? ((in_array($row3['Stage'], $arr) && $row3['Stage'] != 'Забронировано') ? '#FFA500' : '#4169E1') : $data1[$reportDate]['color'];    // цвет Orange для часовых заселений и цвет RoyalBlue для забронированных часов
                // учёт цвета для букинга
            if ($row3['source']=='1' && $row3['Stage'] == 'Забронировано') $data1[$reportDate]['color'] = ($row3_startDate==$date_start) ? '#4B0082' : '#483D8B';    // цвет Indigo для booking, 1 день проживания и DarkSlateBlue для booking, проживание 2+ день
                        // учёт дней роста цен
            if (!$data1[$reportDate]['orderId']) {
                $res_peak = data_select_field(140, 'f1820 AS value, f1830 AS comments', "status=0 AND f1810='".$date_start_null."' LIMIT 1");
                $row_peak = sql_fetch_assoc($res_peak);
                $data1[$reportDate]['color'] = ($row_peak['value']) ? '#C0C0C0' : '';    // светло-серый 
                if ($row_peak['value']) $data1[$reportDate]['tooltip'] = $row_peak['value'].'%'.(($row_peak['comments']) ? ', '.$row_peak['comments'] : '');
            }     
        }        
        if (!$show_ || ($show_=='yes' && $isfree[$flat])) $lines[] = $data1;
        $ReportFlats[] = $ReportFlats_;
        unset($data1);
        unset($ReportFlats_);
    }
}


$result = sql_select_field(SCHEMES_TABLE, "color3", "active='1'");
$row = sql_fetch_assoc($result);
   
    // отправляем блок данных через smarty на вывод отчёта
$smarty->assign("color3", $row['color3']);
$smarty->assign("date1", $date1);
$smarty->assign("date2", $date2);
$smarty->assign("lines", $lines);
$smarty->assign("show_", $show_);   
$smarty->assign("ReportDates", $ReportDates);  
$smarty->assign("ReportDates2", $ReportDates2);
$smarty->assign("ReportFlats", $ReportFlats); 
$smarty->assign("Result1", $Result1);
$smarty->assign("Result2", $Result2);
$smarty->assign("Result3", $Result3);
$smarty->assign("Result4", $Result4);
$smarty->assign("Result5", $Result5);
$smarty->assign("Result6", $Result6);
$smarty->assign("Result7", $Result7);
$smarty->assign("Result8", $Result8);
$smarty->assign("Result9", $Result9);
$smarty->assign("Result10", $Result10);
$smarty->assign("Result11", $Result11);
$smarty->assign("Result12", $Result12);
$smarty->assign("Result13", $Result13);
$smarty->assign("sourcesList", $sourcesList);

Answer 2

1. How is data stored?
2. In what language is all this implemented? (I guess it's php)
3. Where is the actual code?
I don’t understand PHP at all, and even more so I don’t represent the functions performed by Smarty, but in my unprofessional opinion, with the correct formation of the sql query, everything should be done on the fly in literally seconds. After all, you don’t have tens of thousands of apartments with hundreds of thousands of employees and a date range of a thousand years, which means that even if the algorithms are not optimized, the code should work quickly, not 2 minutes at all. Therefore, first of all, problems should be looked for in it.