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;
}
}
