public function getReportDetails($reportId, $isJson = true, $array_users = false, $array_sub_contractors = false) { $result['success'] = false; $result['message'] = ''; $result['data'] = ''; $data = array(); $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME); if ($mysqli->connect_errno) { $result['message'] = "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error; } $propertyId = 0; $sql = "SELECT\t\n\t\t\t properties.id\n\t\t\t,properties.name\n\t\t\t,properties.address\n\t\t\t,properties.city\n\t\t\t,properties.state\n\t\t\t,properties.map_link\n\t\t\t,properties.emails\n\t\t\t,properties.property_type\n\t\t\t,properties.job_type\n\t\t\t,properties.community\n\t\t\t,reports.status_id\n\t\t\t,reports.date_reported\n\t\t\t,reports.reported_by\n\t\t\t,reports.subcontractor\n\t\tFROM reports\n\t\t\tINNER JOIN properties ON properties.id = reports.property_id\n\t\tWHERE reports.id = ?\n\t\t"; $placeholder = $reportId; if ($stmt = $mysqli->prepare($sql)) { $stmt->bind_param("i", $placeholder); if ($stmt->execute()) { $stmt->bind_result($propertyId, $propertyName, $propertyAddress, $propertyCity, $propertyState, $propertyMapLink, $propertyEmails, $propertyType, $propertyJobType, $propertyCommunity, $reportStatusId, $reportDate, $reported_by, $subcontractor); while ($stmt->fetch()) { if ($subcontractor == 1) { $data = array('propertyId' => $propertyId, 'propertyName' => $propertyName, 'propertyAddress' => $propertyAddress, 'propertyCity' => $propertyCity, 'propertyState' => $propertyState, 'propertyMapLink' => $propertyMapLink, 'propertyEmails' => $propertyEmails, 'propertyType' => $propertyType, 'propertyJobType' => $propertyJobType, 'propertyCommunity' => $propertyCommunity, 'reportStatusId' => $reportStatusId, 'firstName' => $array_sub_contractors[$reported_by]['first_name'], 'lastName' => $array_sub_contractors[$reported_by]['last_name'], 'reportDate' => $reportDate, 'reported_by' => $reported_by, 'subcontractor' => $subcontractor); } else { $data = array('propertyId' => $propertyId, 'propertyName' => $propertyName, 'propertyAddress' => $propertyAddress, 'propertyCity' => $propertyCity, 'propertyState' => $propertyState, 'propertyMapLink' => $propertyMapLink, 'propertyEmails' => $propertyEmails, 'propertyType' => $propertyType, 'propertyJobType' => $propertyJobType, 'propertyCommunity' => $propertyCommunity, 'reportStatusId' => $reportStatusId, 'firstName' => $array_users[$reported_by]['first_name'], 'lastName' => $array_users[$reported_by]['last_name'], 'reportDate' => $reportDate, 'reported_by' => $reported_by, 'subcontractor' => $subcontractor); } } } if ($propertyId == 0) { $propertyId = $_REQUEST['propertyId']; if (trim($propertyId) != '') { $placeholder = $propertyId; $sql = "SELECT\t\n\t\t\t\t\t\t\t\t properties.id\n\t\t\t\t\t\t\t\t,properties.name\n\t\t\t\t\t\t\t\t,properties.address\n\t\t\t\t\t\t\t\t,properties.city\n\t\t\t\t\t\t\t\t,properties.state\n\t\t\t\t\t\t\t\t,properties.map_link\n\t\t\t\t\t\t\t\t,properties.emails\n\t\t\t\t\t\t\t\t,properties.property_type\n\t\t\t\t\t\t\t\t,properties.job_type\n\t\t\t\t\t\t\t\t,properties.community\n\t\t\t\t\t\t\t\t,reports.status_id\n\t\t\t\t\t\t\t\t,reports.date_reported\n\t\t\t\t\t\t\t\t,reports.reported_by\n\t\t\t\t\t\t\t\t,reports.subcontractor\n\t\t\t\t\t\t\tFROM reports\n\t\t\t\t\t\t\tINNER JOIN properties ON properties.id = reports.property_id\n\t\t\t\t\t\t\tWHERE properties.id = ?\n\t\t\t\t\t\t\t"; $placeholder = $propertyId; if ($stmt = $mysqli->prepare($sql)) { $stmt->bind_param("i", $placeholder); if ($stmt->execute()) { $stmt->bind_result($propertyId, $propertyName, $propertyAddress, $propertyCity, $propertyState, $propertyMapLink, $propertyEmails, $propertyType, $propertyJobType, $propertyCommunity, $reportStatusId, $reportDate, $reported_by, $subcontractor); while ($stmt->fetch()) { if ($subcontractor == 1) { $data = array('propertyId' => $propertyId, 'propertyName' => $propertyName, 'propertyAddress' => $propertyAddress, 'propertyCity' => $propertyCity, 'propertyState' => $propertyState, 'propertyMapLink' => $propertyMapLink, 'propertyEmails' => $propertyEmails, 'propertyType' => $propertyType, 'propertyJobType' => $propertyJobType, 'propertyCommunity' => $propertyCommunity, 'reportStatusId' => $reportStatusId, 'firstName' => $array_sub_contractors[$reported_by]['first_name'], 'lastName' => $array_sub_contractors[$reported_by]['last_name'], 'reportDate' => $reportDate, 'reported_by' => $reported_by, 'subcontractor' => $subcontractor); } else { $data = array('propertyId' => $propertyId, 'propertyName' => $propertyName, 'propertyAddress' => $propertyAddress, 'propertyCity' => $propertyCity, 'propertyState' => $propertyState, 'propertyMapLink' => $propertyMapLink, 'propertyEmails' => $propertyEmails, 'propertyType' => $propertyType, 'propertyJobType' => $propertyJobType, 'propertyCommunity' => $propertyCommunity, 'reportStatusId' => $reportStatusId, 'firstName' => $array_users[$reported_by]['first_name'], 'lastName' => $array_users[$reported_by]['last_name'], 'reportDate' => $reportDate, 'reported_by' => $reported_by, 'subcontractor' => $subcontractor); } } } } } } $stmt->close(); //get estimates $query = "SELECT MAX(id) AS id FROM estimates WHERE property_id = " . $data['propertyId']; $estimatesObj = new Dynamo("estimates"); $arrayEstimate = $estimatesObj->customFetchOne($query); $estimateId = $arrayEstimate['id']; //GET ROOMS AND ITEMS $rooms = array(); $roomsSql = "SELECT\n\t\t\t\t\t\t\t report_rooms.id AS roomId\n\t\t\t\t\t\t\t,report_rooms.room_template_id\n\t\t\t\t\t\t\t,report_rooms.name AS roomName\n\t\t\t\t\t\tFROM report_rooms\n\t\t\t\t\t\tWHERE report_rooms.report_id = " . $reportId . "\n\t\t\t\t\t\tORDER BY report_rooms.id\n\t\t\t\t\t"; $rooms = array(); $dynamo = new Dynamo("report_rooms"); //all room information $rooms = $dynamo->customFetchQuery($roomsSql); if (count($rooms) > 0) { //get room ids $arrayRoomIds = array(); for ($i = 0; $i < count($rooms); $i++) { $rooms[$i]['roomTemplateId'] = $rooms[$i]['room_template_id']; $arrayRoomIds[] = $rooms[$i]['roomId']; $arrayRoomName[] = $rooms[$i]['roomName']; } $stringRoomIds = implode(",", $arrayRoomIds); $stringRoomName = "\"" . implode("\",\"", $arrayRoomName) . "\""; //get room items $roomItems = array(); $itemsSql = "SELECT\n\t\t\t\t\t\t\t\t report_room_items.id AS itemId\n\t\t\t\t\t\t\t\t,report_room_items.room_id\n\t\t\t\t\t\t\t\t,report_room_items.room_template_item_id AS itemTemplateId\n\t\t\t\t\t\t\t\t,report_room_items.name AS itemName\n\t\t\t\t\t\t\t\t,report_room_items.status_id AS statusId\n\t\t\t\t\t\t\t\t,report_room_items.is_estimate AS isEstimate\n\t\t\t\t\t\t\t\t,report_status.class AS statusClass\n\t\t\t\t\t\t\t\t,report_status.name AS statusName\n\t\t\t\t\t\t\t\t,room_template_items.work_category_id AS work_category_id\n\t\t\t\t\t\t\tFROM report_room_items\n\t\t\t\t\t\t\t LEFT JOIN report_status \n\t\t\t\t\t\t\t\t\t\t ON report_status.id = report_room_items.status_id\n\t\t\t\t\t\t\tINNER JOIN room_template_items ON room_template_items.id = report_room_items.room_template_item_id\n\t\t\t\t\t\t\tWHERE report_room_items.room_id IN (" . $stringRoomIds . ")\n\t\t\t\t\t\t\tORDER BY report_room_items.name\n\t\t\t\t\t\t"; $roomItems = $dynamo->customFetchQuery($itemsSql); if (count($roomItems) > 0) { $query = ''; $itemIdArray = array(); $arrayRoomName = array(); $arrayTemplateId = array(); for ($i = 0; $i < count($roomItems); $i++) { $itemIdArray[] = $roomItems[$i]['itemId']; $itemNameArray[] = $roomItems[$i]['itemName']; $arrayTemplateId[] = $roomItems[$i]['itemTemplateId']; if ($estimateId > 0 && $roomItems[$i]['isEstimate'] == 0) { for ($j = 0; $j < count($rooms); $j++) { if ($rooms[$j]['roomId'] == $roomItems[$i]['room_id']) { $arrayRoomName[] = $rooms[$j]['roomName']; //break; } } } } if (count($arrayRoomName) > 0) { //Get estimate line items $query = "SELECT estimate_room_items_units.id AS estimate_room_items_units_id,\n\t\t\t\t\t\testimate_room_items_units.status_id,room_template_estimates.item_name, \n\t\t\t\t\t\testimate_room_items.room_id,estimate_room_items.room_template_item_id\n\t\t\t\t\t\tFROM estimate_room_items_units INNER JOIN estimate_room_items \n\t\t\t\t\t\tON estimate_room_items_units.estimate_room_items_id = estimate_room_items.id\n\t\t\t\t\t\tINNER JOIN room_template_estimates \n\t\t\t\t\t\tON room_template_estimates.id = estimate_room_items_units.room_template_estimates_id\n\t\t\t\t\t\tINNER JOIN estimate_rooms ON estimate_rooms.id = estimate_room_items_units.room_id\n\t\t\t\t\t\tWHERE estimate_room_items.room_template_item_id IN(" . implode(",", $arrayTemplateId) . ") \n\t\t\t\t\t\tAND estimate_room_items.estimate_id = " . $estimateId . " \n\t\t\t\t\t\tAND estimate_room_items_units.estimate_id = " . $estimateId . " \n\t\t\t\t\t\tAND estimate_rooms.name IN(\"" . implode("\",\"", $arrayRoomName) . "\")"; $array_room_items_unit = array(); $array_room_items_unit = $dynamo->customFetchQuery($query); for ($i = 0; $i < count($array_room_items_unit); $i++) { for ($j = 0; $j < count($roomItems); $j++) { if ($array_room_items_unit[$i]['room_template_item_id'] == $roomItems[$j]['itemTemplateId'] && $roomItems[$j]['itemName'] != $array_room_items_unit[$i]['item_name']) { //if($array_room_items_unit[$i]['room_template_item_id'] == $roomItems[$j]['itemTemplateId']) $roomItems[$j]['arrayRoomItemsUnits'][] = $array_room_items_unit[$i]; } } } } $itemIdString = implode(",", $itemIdArray); $itemNameString = "\"" . implode("\",\"", $itemNameArray) . "\""; //GET THE LAST ITEM COMMENT $commentsSql = "SELECT\n\t\t\t\t\t\t\t\t\t\t report_room_item_comments.id\n\t\t\t\t\t\t\t\t\t\t,report_room_item_comments.comment\n\t\t\t\t\t\t\t\t\t\t,report_room_item_comments.date AS commentDate\n\t\t\t\t\t\t\t\t\t\t,report_room_item_comments.room_item_id\n\t\t\t\t\t\t\t\t\t\t,report_room_item_comments.order\n\t\t\t\t\t\t\t\t\tFROM report_room_item_comments\n\t\t\t\t\t\t\t\t\tWHERE report_room_item_comments.room_item_id IN(" . $itemIdString . ")\n\t\t\t\t\t\t\t\t\tORDER BY report_room_item_comments.`order`"; $arrComments = $dynamo->customFetchQuery($commentsSql); if (count($arrComments) > 0) { for ($i = 0; $i < count($arrComments); $i++) { for ($j = 0; $j < count($roomItems); $j++) { if ($arrComments[$i]['room_item_id'] == $roomItems[$j]['itemId']) { $arrComments[$i]['comment'] = str_replace("\"", "'", $arrComments[$i]['comment']); $arrComments[$i][1] = str_replace("\"", "'", $arrComments[$i][1]); $roomItems[$j]['comments'][] = $arrComments[$i]; } } } } //GET THE LAST ITEM IMAGE $commentsSql = "SELECT\n\t\t\t\t\t\t\t\t\t\t image_name\n\t\t\t\t\t\t\t\t\t\t,date AS imageUploadDate\n\t\t\t\t\t\t\t\t\t\t,room_item_id\n\t\t\t\t\t\t\t\t\t\t,`order`\n\t\t\t\t\t\t\t\t\tFROM report_images\n\t\t\t\t\t\t\t\t\tWHERE room_item_id IN(" . $itemIdString . ")\n\t\t\t\t\t\t\t\t\tORDER BY `order`"; $arrComments = $dynamo->customFetchQuery($commentsSql); if (count($arrComments) > 0) { for ($i = 0; $i < count($arrComments); $i++) { for ($j = 0; $j < count($roomItems); $j++) { if ($arrComments[$i]['room_item_id'] == $roomItems[$j]['itemId']) { $roomItems[$j]['images'][$arrComments[$i]['order']][] = $arrComments[$i]; } } } } $roomNameArray = array(); for ($i = 0; $i < count($roomItems); $i++) { for ($j = 0; $j < count($rooms); $j++) { if ($rooms[$j]['roomId'] == $roomItems[$i]['room_id']) { $roomNameArray[] = $rooms[$j]['roomName']; } } } if (count($roomNameArray) > 0) { $roomNameString = "\"" . implode("\",\"", $roomNameArray) . "\""; //GET ALL THE COMMENTS PER ITEM $commentsSql = "SELECT\n\t\t\t\t\t\t\t\t\t\t c.id AS itemId\n\t\t\t\t\t\t\t\t\t\t ,i.id AS roomItemId\n\t\t\t\t\t\t\t\t\t\t,i.name AS itemName\n\t\t\t\t\t\t\t\t\t\t,c.comment AS comment\n\t\t\t\t\t\t\t\t\t\t,c.order AS `order`\n\t\t\t\t\t\t\t\t\t\t,c.date AS commentDate\n\t\t\t\t\t\t\t\t\t\t,u.first_name AS firstName\n\t\t\t\t\t\t\t\t\t\t,u.last_name AS lastName\n\t\t\t\t\t\t\t\t\t\t,i.room_template_item_id AS room_template_item_id\n\t\t\t\t\t\t\t\t\t\t,rm.name AS room_name\n\t\t\t\t\t\t\t\t\tFROM report_room_item_comments c\n\t\t\t\t\t\t\t\t\t\t INNER JOIN report_room_items i ON i.id = c.room_item_id\n\t\t\t\t\t\t\t\t\t\t INNER JOIN report_rooms rm ON rm.id = i.room_id\n\t\t\t\t\t\t\t\t\t\t INNER JOIN reports r ON r.id = rm.report_id\n\t\t\t\t\t\t\t\t\t\t INNER JOIN users u ON u.id = c.user_id\n\t\t\t\t\t\t\t\t\tWHERE r.property_id = " . $propertyId . "\n\t\t\t\t\t\t\t\t\t AND i.room_template_item_id IN( " . implode(",", $arrayTemplateId) . ")\n\t\t\t\t\t\t\t\t\t AND r.is_submitted = 1\n\t\t\t\t\t\t\t\t\t AND i.name IN(" . $itemNameString . ")\n\t\t\t\t\t\t\t\t\t AND rm.name IN(" . $roomNameString . ") \n\t\t\t\t\t\t\t\t\t ORDER BY c.order"; $arrComments = $dynamo->customFetchQuery($commentsSql); if (count($arrComments) > 0) { for ($i = 0; $i < count($arrComments); $i++) { for ($j = 0; $j < count($roomItems); $j++) { if ($arrComments[$i]['room_template_item_id'] == $roomItems[$j]["itemTemplateId"]) { $arrComments[$i]['commentDate'] = date('m/d/Y g:ia', strtotime($arrComments[$i]['commentDate'])); $roomItems[$j]['comment_thread'][] = $arrComments[$i]; } } } } $imageSql = "SELECT\n\t\t\t\t\t\t\t\t\t\t\t c.id AS itemId\n\t\t\t\t\t\t\t\t\t\t\t,c.room_item_id AS roomItemId\n\t\t\t\t\t\t\t\t\t\t\t,i.name AS itemName\n\t\t\t\t\t\t\t\t\t\t\t,c.image_name AS imageName\n\t\t\t\t\t\t\t\t\t\t\t,c.order AS `order`\n\t\t\t\t\t\t\t\t\t\t\t,c.date\n\t\t\t\t\t\t\t\t\t\t\t,u.first_name AS firstName\n\t\t\t\t\t\t\t\t\t\t\t,u.last_name AS lastName\n\t\t\t\t\t\t\t\t\t\t\t,i.room_template_item_id AS room_template_item_id\n\t\t\t\t\t\t\t\t\t\t\t,rm.name AS room_name\n\t\t\t\t\t\t\t\t\t\tFROM report_images c\n\t\t\t\t\t\t\t\t\t\t\t INNER JOIN report_room_items i ON i.id = c.room_item_id\n\t\t\t\t\t\t\t\t\t\t\t INNER JOIN report_rooms rm ON rm.id = i.room_id\n\t\t\t\t\t\t\t\t\t\t\t INNER JOIN reports r ON r.id = rm.report_id\n\t\t\t\t\t\t\t\t\t\t\t INNER JOIN users u ON u.id = c.user_id\n\t\t\t\t\t\t\t\t\t\tWHERE r.property_id = " . $propertyId . "\n\t\t\t\t\t\t\t\t\t\t AND i.room_template_item_id IN( " . implode(",", $arrayTemplateId) . ")\n\t\t\t\t\t\t\t\t\t\t AND r.is_submitted = 1\n\t\t\t\t\t\t\t\t\t\t AND i.name IN(" . $itemNameString . ")\n\t\t\t\t\t\t\t\t\t\t AND rm.name IN(" . $roomNameString . ")\n\t\t\t\t\t\t\t\t\t\t ORDER BY c.order"; $arrComments = $dynamo->customFetchQuery($imageSql); if (count($arrComments) > 0) { for ($i = 0; $i < count($arrComments); $i++) { for ($j = 0; $j < count($roomItems); $j++) { if ($arrComments[$i]['room_template_item_id'] == $roomItems[$j]["itemTemplateId"]) { $arrComments[$i]['imageUploadDate'] = date('m/d/Y g:ia', strtotime($arrComments[$i]['date'])); $roomItems[$j]['image_thread'][] = $arrComments[$i]; } } } } } for ($i = 0; $i < count($roomItems); $i++) { for ($j = 0; $j < count($rooms); $j++) { if ($rooms[$j]['roomId'] == $roomItems[$i]['room_id']) { if (!$roomItems[$i]['arrayRoomItemsUnits']) { $roomItems[$i]['arrayRoomItemsUnits'] = array(); } if (!$roomItems[$i]['comments']) { $roomItems[$i]['comments'] = array(); } if (!$roomItems[$i]['images']) { $roomItems[$i]['images'] = array(); } if (!$roomItems[$i]['comment_thread']) { $roomItems[$i]['comment_thread'] = array(); } if (!$roomItems[$i]['image_thread']) { $roomItems[$i]['image_thread'] = array(); } $rooms[$j]['items'][] = $roomItems[$i]; } } } } $data['rooms'] = $rooms; } //GET REPORT COMMENTS $sqlReportComment = "SELECT\t\n\t\t\t\t\t\t\t\t\t report_comments.id,\n\t\t\t\t\t\t\t\t\t report_comments.comment,\n\t\t\t\t\t\t\t\t\t users.first_name,\n\t\t\t\t\t\t\t\t\t users.last_name,\n\t\t\t\t\t\t\t\t\t report_comments.date,\n\t\t\t\t\t\t\t\t\t reports.is_submitted,\n\t\t\t\t\t\t\t\t\t reports.is_saved\n\t\t\t\t\t\t\t\tFROM report_comments\n\t\t\t\t\t\t\t\t\t INNER JOIN users ON users.id = report_comments.user_id\n\t\t\t\t\t\t\t\t\t INNER JOIN reports ON reports.id = report_comments.report_id\n\t\t\t\t\t\t\t\tWHERE report_comments.property_id=?\n\t\t\t\t\t\t\t\t"; $arrReportComments = array(); if ($reportCommentStmt = $mysqli->prepare($sqlReportComment)) { $reportCommentStmt->bind_param("i", $propertyId); if ($reportCommentStmt->execute()) { $reportCommentStmt->bind_result($reportCommentId, $reportComment, $reportCommentFirstName, $reportCommentLastName, $reportCommentDate, $reportIsSubmitted, $reportIsSaved); $reportCommentStmt->store_result(); $reportCommentsCtr = 0; while ($reportCommentStmt->fetch()) { $arrReportComments[$reportCommentsCtr] = array('id' => $reportCommentId, 'comment' => $reportComment, 'user' => $reportCommentFirstName . ' ' . $reportCommentLastName, 'date' => date('m/d/Y g:ia', strtotime($reportCommentDate)), 'isSubmitted' => $reportIsSubmitted, 'isSaved' => $reportIsSaved); $reportCommentsCtr++; } $reportCommentStmt->free_result(); $reportCommentStmt->close(); } } $data['reportComments'] = $arrReportComments; //REPORT $reportDateSql = "SELECT date_reported FROM reports WHERE property_id=? ORDER BY date_reported ASC LIMIT 1"; if ($reportDateStmt = $mysqli->prepare($reportDateSql)) { $reportDateStmt->bind_param("i", $propertyId); if ($reportDateStmt->execute()) { $reportDateStmt->bind_result($reportFirstDate); $reportDateStmt->store_result(); $reportDateStmt->fetch(); $data['firstReportDate'] = $reportFirstDate; $reportDateStmt->free_result(); $reportDateStmt->close(); } } $result['success'] = true; $result['data'] = $data; } else { $result['message'] = "Sorry, there has been a problem processing your request."; } $mysqli->close(); if ($isJson) { header('Content-type: application/json'); echo json_encode($result); } else { return $data; } }