Joywork/presentation-api/Models/FrameModel.php

<?php

ini_set('memory_limit', '512M');

class FrameModel extends BaseModel
{
  public function addFrame($insertData)
  {
    $rf_region_id = $insertData['rf_region_id'];
    $agency_id = $insertData['agency_id'];
    $sql = "INSERT INTO presentation_frame (rf_region_id, agency_id) VALUES ($rf_region_id, $agency_id)";
    return $this->db->query($sql);
  }

    public function getMainColor($params) {
        $token = isset($params['token']) ? self::clearInput($params['token']) : null;

        if (!$token) {
            return ['error' => 'Token is required'];
        }

        $sql = "SELECT color FROM presentation_frame WHERE token = '$token' ";
        $query = $this->db->query($sql);
        if ($res = $this->db->fetch_assoc($query)) {
            return $res;
        }
        return 'frame not found';
    }

    public function getFrame($params, $query) {
        $token = isset($params['token']) ? self::clearInput($params['token']) : null;

        if (!$token) {
            return ['error' => 'Token is required'];
        }

        $frame = "SELECT * FROM presentation_frame pf WHERE pf.token = '$token' AND pf.end_date >= CURDATE()";
        $frameQuery = $this->db->query($frame);

        if ($frameRes = $this->db->fetch_assoc($frameQuery)) {
            $frame_id = $frameRes['id'];
            $rf_region_id = $frameRes['rf_region_id'];
            $agency_id = $frameRes['agency_id'];
            $agency = "SELECT users.first_name, last_name, middle_name, phone, photo, email, user_logo, phoneAgency, site, agency_name 
                 FROM users WHERE id = '$agency_id'";
            $agencyQuery = $this->db->query($agency);
            $agencyData = [];

            if ($agencyRes = $this->db->fetch_assoc($agencyQuery)) {
                $agencyData = $agencyRes;
            }
        } else {
            return 'ended';
        }

        $price_from = isset($query['price_from']) ? self::clearInput($query['price_from']) : null;
        $price_to = isset($query['price_to']) ? self::clearInput($query['price_to']) : null;
        $building_dl = isset($query['building_dl']) ? self::clearInput($query['building_dl']) : null;
        $current_page = isset($query['page']) ? self::clearInput($query['page']) : null;
        $room_type_id = isset($query['room_type_id']) ? self::clearInput($query['room_type_id']) : null;


        $room_type_ids = array_map('intval', explode(',', $room_type_id));

        $sql_filter = '';

        if (!empty($price_from) && is_numeric($price_from)) {
            $sql_filter .= " AND a.price >= $price_from";
        }

        if (!empty($price_to) && is_numeric($price_to)) {
            $sql_filter .= " AND a.price <= $price_to";
        }

        if (!empty($room_type_id)) {
            $room_type_id_list = implode(',', array_map('intval', $room_type_ids));
            $sql_filter .= " AND a.room_type_id IN ($room_type_id_list)";
        }

        if (!empty($building_dl) && $building_dl !== "ready") {
            if ($this->isRealDate($building_dl)) {
                $sql_filter .= " AND DATE(buildings.deadline) <= '$building_dl'";
            }
        } else if ($building_dl === "ready") {
            $sql_filter .= " AND DATE(buildings.deadline) < now()";
        }


        $per_page = 21;
        $offset = $per_page * ($current_page - 1);

        $builder_filter = '';

        if ($token === '926d7e26ee8da62b9bc742ee0eba282f') {
            $allowed_builder_ids = [
                810,2419,826,4199,831,4235,2464,2201,6213,3866,3354,1518
            ];
            $builders_list = implode(',', array_map('intval', $allowed_builder_ids));
            $builder_filter = " AND b.builder_id IN ($builders_list)";
        }

        $blocks = "SELECT b.*, r.name AS region_name
               FROM blocks b
               LEFT JOIN regions r ON r.id = b.region_id
               JOIN apartments a ON a.block_id = b.id
               JOIN buildings ON buildings.id = a.building_id
               WHERE r.rf_region_id = '$rf_region_id'
               $builder_filter 
               $sql_filter
               GROUP BY b.id
               LIMIT $per_page
               OFFSET $offset";

        $blocks_count = "SELECT count(DISTINCT b.id) as total_count
               FROM blocks b
               LEFT JOIN regions r ON r.id = b.region_id
               JOIN apartments a ON a.block_id = b.id
               JOIN buildings ON buildings.id = a.building_id
               WHERE r.rf_region_id = '$rf_region_id'
               $builder_filter 
               $sql_filter";

        $blocks_count_query = $this->db->query($blocks_count);
        $count_row = $this->db->fetch_assoc($blocks_count_query);

        $blocksQuery = $this->db->query($blocks);
        $blocksData = [];

        while ($blocksRes = $this->db->fetch_assoc($blocksQuery)) {
            $block_id = $blocksRes['id'];

            // Получение изображений блока
            $block_img = "SELECT image FROM block_images WHERE model_id = '$block_id'";
            $block_imgQuery = $this->db->query($block_img);
            $block_imgArr = [];

            while ($block_imgRes = $this->db->fetch_assoc($block_imgQuery)) {
                $block_imgArr[] = $block_imgRes['image'];
            }

            // Получение данных по метро
            $metro = "SELECT s.name as metro, bs.distance 
                FROM block_subways bs, subways s 
                WHERE s.id = bs.subway_id 
                AND bs.block_id='$block_id'";
            $metroQuery = $this->db->query($metro);
            $metroArr = [];

            while ($metroRes = $this->db->fetch_assoc($metroQuery)) {
                $metroArr[] = $metroRes;
            }

            // Получение квартир
            $apartments = "SELECT room_types.name AS room_type_name, decoration_types.name AS decoration, a.*, 
                            buildings.corp AS building_corp, buildings.floors AS building_floors, buildings.deadline AS building_deadline 
                     FROM apartments a
                     JOIN buildings ON buildings.id = a.building_id
                     JOIN decoration_types ON decoration_types.id = a.decoration_id
                     JOIN room_types ON room_types.id = a.room_type_id
                     WHERE a.block_id = '$block_id' 
                     $sql_filter";
            $apartmentsQuery = $this->db->query($apartments);
            $apartmentsArr = [];

            while ($apartmentsRes = $this->db->fetch_assoc($apartmentsQuery)) {
                $apartmentsRes['visible'] = true;
                $apartmentsArr[] = $apartmentsRes;
            }

            $blocksRes['apartments'] = $apartmentsArr;
            $blocksRes['block_img'] = $block_imgArr;
            $blocksRes['metros'] = $metroArr;
            $blocksData[] = $blocksRes;
        }
        return [
            'frame_id' => $frame_id,
            'agent' => $agencyData,
            'blocks' => $blocksData,
            'count_blocks' => $count_row['total_count'],
            'query' => $query,
            'agency_id' => $agency_id,
        ];
    }

  public function getMapBlocks($params, $query) {
      $token = isset($params['token']) ? self::clearInput($params['token']) : null;

      if (!$token) {
          return ['error' => 'Token is required'];
      }

      $frame = "SELECT * FROM presentation_frame pf WHERE pf.token = '$token'";
      $frameQuery = $this->db->query($frame);

      if ($frameRes = $this->db->fetch_assoc($frameQuery)) {
          $frame_id = $frameRes['id'];
          $rf_region_id = $frameRes['rf_region_id'];
          $agency_id = $frameRes['agency_id'];
      }

      $price_from = isset($query['price_from']) ? self::clearInput($query['price_from']) : null;
      $price_to = isset($query['price_to']) ? self::clearInput($query['price_to']) : null;
      $building_dl = isset($query['building_dl']) ? self::clearInput($query['building_dl']) : null;
      $room_type_id = isset($query['room_type_id']) ? self::clearInput($query['room_type_id']) : null;

      $room_type_ids = array_map('intval', explode(',', $room_type_id));

      $sql_filter = '';

      if (!empty($price_from) && is_numeric($price_from)) {
          $sql_filter .= " AND a.price >= $price_from";
      }

      if (!empty($price_to) && is_numeric($price_to)) {
          $sql_filter .= " AND a.price <= $price_to";
      }

      if (!empty($room_type_id)) {
          $room_type_id_list = implode(',', array_map('intval', $room_type_ids));
          $sql_filter .= " AND a.room_type_id IN ($room_type_id_list)";
      }

      if (!empty($building_dl) && $building_dl !== "ready") {
          if ($this->isRealDate($building_dl)) {
              $sql_filter .= " AND DATE(buildings.deadline) <= '$building_dl'";
          }
      } else if ($building_dl === "ready") {
          $sql_filter .= " AND DATE(buildings.deadline) < now()";
      }

      $blocks_map = "SELECT b.id, b.image, b.latitude, b.longitude
               FROM blocks b
               LEFT JOIN regions r ON r.id = b.region_id
               JOIN apartments a ON a.block_id = b.id
               JOIN buildings ON buildings.id = a.building_id
               WHERE r.rf_region_id = '$rf_region_id'
               $sql_filter
               GROUP BY b.id";

      $blocks_map_query = $this->db->query($blocks_map);
      $blocks_map_data = [];

      while ($blocksMapRes = $this->db->fetch_assoc($blocks_map_query)) {
          $block_id = $blocksMapRes['id'];

          $room_type_apartments_count = "SELECT room_types.id ,room_types.name AS room_type_name, count(a.id) as apartments_count 
                 FROM apartments a
                 JOIN buildings ON buildings.id = a.building_id
                 JOIN decoration_types ON decoration_types.id = a.decoration_id
                 JOIN room_types ON room_types.id = a.room_type_id
                 WHERE a.block_id = '$block_id' 
                 $sql_filter
                 GROUP BY room_type_name";
          $apartmentsQuery = $this->db->query($room_type_apartments_count);
          $apartmentsArr = [];

          while ($apartmentsRes = $this->db->fetch_assoc($apartmentsQuery)) {
              //   $apartmentsRes['visible'] = true;
              $apartmentsArr[] = $apartmentsRes;
          }
          $blocksMapRes['room_type_apartments_count'] = $apartmentsArr;
          $blocks_map_data[] = $blocksMapRes;
      }
      return $blocks_map_data;
  }

  public function addRequest($insertData){
      $token = isset($insertData['token']) ? self::clearInput($insertData['token']) : null;
      $full_name = isset($insertData['full_name']) ? self::clearInput($insertData['full_name']) : null;
      $phone_number = isset($insertData['phone_number']) ? self::clearInput($insertData['phone_number']) : null;
      $apartment_id = isset($insertData['apartment_id']) ? self::clearInput($insertData['apartment_id']) : null;

      $apartment_info = "SELECT a.block_id, a.room_type_id, a.space_total, a.number, rt.name AS room_type_name, b.name AS block_name
                           FROM apartments a
                           LEFT JOIN room_types rt ON a.room_type_id = rt.id
                           LEFT JOIN blocks b ON a.block_id = b.id
                           WHERE a.id = '$apartment_id'";

      $apartment_info_query = $this->db->query($apartment_info);
      $apartment_info_res = $this->db->fetch_assoc($apartment_info_query);

      $block_name = $apartment_info_res['block_name'];
      $room_type = $apartment_info_res['room_type_name'];
      $total_space = $apartment_info_res['space_total'];
      $apartment_number = $apartment_info_res['number'];
      $description = "Заявка с каталога новостроек по объекту в {$block_name}, Тип {$room_type}, Общая площадь {$total_space} м2, № кв {$apartment_number}";
    
      $agency_id_query =  $this->db->query("SELECT agency_id FROM presentation_frame p WHERE p.token = '$token'");

      $agency_res = $this->db->fetch_assoc($agency_id_query);
      $agency_id = $this->arrToString($agency_res);

      $confirm = 1;

      $funnel_id = 0;
      if($agency_id == 13339){
          $funnel_id = 1437;
      }

      $client_sql = "INSERT INTO clients (`fio`, `phone`, `opis`, `id_agent`, `date_add`, `who_work`, `confirm`, `funnel_id`) 
                   VALUES ('$full_name', '$phone_number', '$description', '$agency_id', NOW(), '$agency_id', '$confirm', '$funnel_id' )";
      $this->db->query($client_sql);
      $new_client_id = $this->db->insert_id();

      if($new_client_id > 0 && $funnel_id > 0){
          $client = new \Clients;
          $client->get_class_etap($new_client_id, $funnel_id);

      }
    
      $requisition_sql = "INSERT INTO requisitions (`name`, `user_id`, `client_id`, `who_work`, `type_id`, `description`, `stage`, `funnel_id`) 
                        VALUES ('Подбор объекта для $full_name', '$agency_id', '$new_client_id', '$agency_id', 1, '$description', 1, '$funnel_id')";
    
      $this->db->query($requisition_sql);

      $new_req_id = $this->db->insert_id();

      if($new_req_id > 0 && $funnel_id > 0){
          $reqClass = new \Requisitions(null, false);
          $reqClass->get_class_etap($new_req_id, $funnel_id);
      }

      if($new_req_id > 0 && $agency_id == 13339){
          $comment = "Заявка с каталога новостроек";
            
          $sql_add = "INSERT INTO `events_clients` (`user_id`, `req_id`, `from_id`, `event`, `dop_text`, `read`) ".
              "VALUES ({$agency_id}, {$new_req_id}, 13354, 'add', '{$comment}', 2)";
          $this->db->query($sql_add);
      }

      if($new_req_id > 0 && ($agency_id == 22132 || $agency_id == 22325 || $agency_id == 22463)){
          $comment = "Заявка с каталога новостроек";
            
          $sql_add = "INSERT INTO `events_clients` (`user_id`, `req_id`, `from_id`, `event`, `dop_text`, `read`) ".
              "VALUES ({$agency_id}, {$new_req_id}, {$agency_id}, 'add', '{$comment}', 2)";
          $this->db->query($sql_add);
      }

      return true;
  }

  public function getBlock($params, $query) {
      $price_from = isset($query['price_from']) ? self::clearInput($query['price_from']) : null;
      $price_to = isset($query['price_to']) ? self::clearInput($query['price_to']) : null;
      $building_dl = isset($query['building_dl']) ? self::clearInput($query['building_dl']) : null;
      $room_type_id = isset($query['room_type_id']) ? self::clearInput($query['room_type_id']) : null;


      $room_type_ids = array_map('intval', explode(',', $room_type_id));
      $sql_filter = '';

      if (!empty($price_from) && is_numeric($price_from)) {
          $sql_filter .= " AND a.price >= $price_from";
      }

      if (!empty($price_to) && is_numeric($price_to)) {
          $sql_filter .= " AND a.price <= $price_to";
      }

      if (!empty($room_type_id)) {
          $room_type_id_list = implode(',', array_map('intval', $room_type_ids));
          $sql_filter .= " AND a.room_type_id IN ($room_type_id_list)";
      }

      if (!empty($building_dl) && $building_dl !== "ready") {
          if ($this->isRealDate($building_dl)) {
              $sql_filter .= " AND DATE(buildings.deadline) <= '$building_dl'";
          }
      } else if ($building_dl === "ready") {
          $sql_filter .= " AND DATE(buildings.deadline) < now()";
      }
      $block_id = $params['block_id'];
      $token = $params['token'];
      $blocksQuery = $this->db->query("SELECT b.*, r.name AS region_name
               FROM blocks b
               LEFT JOIN regions r ON r.id = b.region_id
               JOIN apartments a ON a.block_id = b.id
               JOIN buildings ON buildings.id = a.building_id
               WHERE b.id = '$block_id'
               ");
      $block = $this->db->fetch_assoc($blocksQuery);
      if (!$block) {
          return ['status' => false];
      }

      // Получение изображений блока
      $block_img = "SELECT image FROM block_images WHERE model_id = '$block_id'";
      $block_imgQuery = $this->db->query($block_img);
      $block_imgArr = [];

      while ($block_imgRes = $this->db->fetch_assoc($block_imgQuery)) {
          $block_imgArr[] = $block_imgRes['image'];
      }

      // Получение данных по метро
      $metro = "SELECT s.name as metro, bs.distance 
              FROM block_subways bs, subways s 
              WHERE s.id = bs.subway_id 
              AND bs.block_id='$block_id'";

      $metroQuery = $this->db->query($metro);
      $metroArr = [];

      while ($metroRes = $this->db->fetch_assoc($metroQuery)) {
          $metroArr[] = $metroRes;
      }

      // Получение квартир
      $apartments = "SELECT room_types.name AS room_type_name, decoration_types.name AS decoration, a.*, 
                          buildings.corp AS building_corp, buildings.floors AS building_floors, buildings.deadline AS building_deadline 
                  FROM apartments a
                  JOIN buildings ON buildings.id = a.building_id
                  JOIN decoration_types ON decoration_types.id = a.decoration_id
                  JOIN room_types ON room_types.id = a.room_type_id
                  WHERE a.block_id = '$block_id'
                  $sql_filter";

      $apartmentsQuery = $this->db->query($apartments);
      $apartmentsArr = [];

      // die($token);
      $agency = "SELECT users.first_name, users.last_name, users.middle_name, users.phone, users.photo, users.email, users.user_logo, users.phoneAgency, users.site, users.agency_name 
                  FROM users 
                  JOIN presentation_frame ON presentation_frame.agency_id = users.id
                  WHERE presentation_frame.token  = '$token'";
      $agencyQuery = $this->db->query($agency);
      $agencyData = null;

      if ($agencyRes = $this->db->fetch_assoc($agencyQuery)) {
          $agencyData = $agencyRes;
      }

      while ($apartmentsRes = $this->db->fetch_assoc($apartmentsQuery)) {
          $apartmentsRes['visible'] = true;
          $apartmentsArr[] = $apartmentsRes;
      }

      $block['apartments'] = $apartmentsArr;
      $block['block_img'] = $block_imgArr;
      $block['metros'] = $metroArr;

      return [
          'block' =>  $block,
          'agency' => $agencyData,
      ];
  }

    function isRealDate($date) {
        if (false === strtotime($date)) {
            return false;
        }
        list($year, $month, $day) = explode('-', $date);
        return checkdate($month, $day, $year);
    }

    public function getDeadlines($token, $blockId = null) {

        $filter = '';
        if ($blockId) {
            $filter = " AND b.id = '$blockId' ";
        }

        $deadlineQuery = $this->db->query("SELECT DATE_FORMAT(buildings.deadline, '%Y-%m-%d') as label, buildings.deadline as value
      FROM buildings
      JOIN apartments a ON a.building_id = buildings.id
      JOIN blocks b ON b.id = a.block_id
      LEFT JOIN regions r ON r.id = b.region_id
      JOIN presentation_frame frame ON frame.token = '$token'
      WHERE r.rf_region_id = frame.rf_region_id
      $filter
      GROUP BY label
      ORDER BY buildings.deadline ASC");

        $result = [];
        while ($row = $this->db->fetch_assoc($deadlineQuery)) {
            $result[] = $row;
        }
        return $result;
    }
}