public function updateRooms($mysqli, $rooms, $reportId, $userId) { $isUpdateSuccess = true; //[Start] UPDATE ROOM AND ROOM ITEMS if (count($rooms) > 0) { $string_room_item_id = ''; for ($i = 0; $i < count($rooms); $i++) { for ($j = 0; $j < count($rooms[$i]->roomItems); $j++) { $string_room_item_id .= $rooms[$i]->roomItems[$j]->id . ","; } } $string_room_item_id = substr($string_room_item_id, 0, -1); $report_room_item_comments_obj = new Dynamo("report_room_item_comments"); $query = "SELECT * FROM report_room_item_comments WHERE room_item_id IN ({$string_room_item_id}) ORDER BY room_item_id,`order`"; $array_comments = $report_room_item_comments_obj->customFetchQueryWithIdDefault($query, 'room_item_id'); $query = "SELECT * FROM report_images WHERE room_item_id IN ({$string_room_item_id}) ORDER BY room_item_id,`order`"; $array_images = $report_room_item_comments_obj->customFetchQueryWithIdDefault($query, 'room_item_id'); for ($i = 0; $i < count($rooms); $i++) { for ($j = 0; $j < count($rooms[$i]->roomItems); $j++) { if (trim($rooms[$i]->roomItems[$j]->comment) != '') { $rooms[$i]->roomItems[$j]->comment = $array_comments[$rooms[$i]->roomItems[$j]->id]; $rooms[$i]->roomItems[$j]->images = $array_images[$rooms[$i]->roomItems[$j]->id]; } } } } foreach ($rooms as $room) { if ($room->isNew == 1) { $isUpdateSuccess = $this->insertRooms($mysqli, $room, $reportId, $userId); if (!$isUpdateSuccess) { break; } } else { $status_check = true; for ($i = 0; $i < count($room->roomItems); $i++) { if ($room->roomItems[$i]->statusId == 2 || $room->roomItems[$i]->statusId == 3) { $status_check = false; break; } } $this->roomsEmailBody .= '<table width="100%" bgcolor="#ffffff">'; $this->roomsEmailBody .= '<tr>'; if ($status_check) { $this->roomsEmailBody .= '<th colspan="3" style="text-align: left; font: bold 14px Arial; color: #000" valign="top">' . $room->roomName . ' - <span style="color:#468847;font-weight:normal;">Complete</span></th>'; } else { $this->roomsEmailBody .= '<th colspan="3" style="text-align: left; font: bold 14px Arial; color: #000" valign="top">' . $room->roomName . '</th>'; } $this->roomsEmailBody .= '</tr>'; $host_url = "http://" . $_SERVER['HTTP_HOST'] . rtrim(dirname(dirname($_SERVER['PHP_SELF'])), '/\\'); //The only thing that needs updating is just the status (Complete, Pending, and Incomplete) and comments for a "SAVED" room. foreach ($room->roomItems as $punchlist) { $updateRoomSql = "UPDATE report_room_items\n\t\t\t\t\t\t\t\t\t\t SET status_id = ?\n\t\t\t\t\t\t\t\t\t WHERE id = ?"; if ($updateRoomStmt = $mysqli->prepare($updateRoomSql)) { $updateRoomStmt->bind_param("ii", $punchlist->statusId, $punchlist->id); if ($updateRoomStmt->execute()) { //UPDATE ROOM ITEM COMMENT /*if(!empty($punchlist->comment) && isset($punchlist->comment)) { $updateRoomCommentSql = "UPDATE report_room_item_comments SET comment = ? ,date = NOW() WHERE room_item_id = ? AND report_id = ? "; if ($updateRoomCommentStmt = $mysqli->prepare($updateRoomCommentSql)) { $updateRoomCommentStmt->bind_param("sii", $punchlist->comment, $punchlist->id, $reportId); $updateRoomCommentStmt->execute(); //If comment is not existing, add it on the room item. if($mysqli->affected_rows == 0) { $insertRoomCommentSql = "INSERT INTO report_room_item_comments(comment, user_id, room_item_id, report_id, date) VALUES(?, ?, ?, ?, NOW())"; if($insertRoomCommentStmt = $mysqli->prepare($insertRoomCommentSql)) { $insertRoomCommentStmt->bind_param("siii", $punchlist->comment, $userId, $punchlist->id, $reportId); $insertRoomCommentStmt->execute(); $insertRoomCommentStmt->close(); } else { $isUpdateSuccess = false; break; } } $updateRoomCommentStmt->close(); } else { $isUpdateSuccess = false; break; } }*/ $itemStatus = ''; $itemStatusColor = ''; if ($punchlist->statusId == 4) { $itemStatus = 'N/A'; $itemStatusColor = 'green'; } else { if ($punchlist->statusId == 3) { $itemStatus = 'Incomplete'; $itemStatusColor = 'red'; } else { if ($punchlist->statusId == 2) { $itemStatus = 'Pending Review'; $itemStatusColor = 'orange'; } else { if ($punchlist->statusId == 1) { $itemStatus = 'Complete'; $itemStatusColor = 'green'; } } } } if ($punchlist->isParent == 1) { $itemStatus = ''; } $style = ""; $padding_left = ""; if ($punchlist->isEstimate == 1) { $style = " style='background-color:#f9f9f9'"; $padding_left = " "; } if ($punchlist->statusId == 2 || $punchlist->statusId == 3) { $this->roomsEmailBody .= "<tr{$style}>"; $this->roomsEmailBody .= ' <td style="text-align: left; font: normal 12px Arial; color: #000" valign="top">' . $padding_left . $punchlist->name . '</td>'; $this->roomsEmailBody .= ' <td style="text-align: left; font: normal 12px Arial; color: #000;" valign="top">'; if (count($punchlist->comment) > 0) { $this->roomsEmailBody .= '<table border="0" cellpadding="2" cellspacing="2">'; for ($i = 0; $i < count($punchlist->comment); $i++) { $this->roomsEmailBody .= '<tr><td valign="top">'; if (count($punchlist->images) > 0) { for ($j = 0; $j < count($punchlist->images); $j++) { if ($punchlist->images[$j]['order'] == $punchlist->comment[$i]['order']) { $this->roomsEmailBody .= "<a href='" . $host_url . "/images/report_uploads/" . $punchlist->images[$j]['image_name'] . "'><img src='" . $host_url . "/images/report_uploads/" . $punchlist->images[$j]['image_name'] . "' height='75' /></a> <br />"; } } } $this->roomsEmailBody .= '</td><td valign="top">'; if (trim($punchlist->comment[$i]['comment']) != '') { $this->roomsEmailBody .= "<img src='" . $host_url . "/images/comments.png' align='top' /> <span style='vertical-align:top'>" . $punchlist->comment[$i]['comment'] . "</span>"; } $this->roomsEmailBody .= '</td></tr>'; } $this->roomsEmailBody .= '</table>'; } $this->roomsEmailBody .= '</td>'; $this->roomsEmailBody .= ' <td style="text-align: right; font: normal 12px Arial; color: ' . $itemStatusColor . '; width: 100px" valign="top">' . $itemStatus . '</td>'; $this->roomsEmailBody .= '</tr>'; } } else { $isUpdateSuccess = false; break; } $updateRoomStmt->close(); } else { $isUpdateSuccess = false; break; } } $this->roomsEmailBody .= '</table>'; $this->roomsEmailBody .= '<br/>'; } } //[End] UPDATE ROOM AND ITEMS return $isUpdateSuccess; }