$arrayOtherEstimates = array();
$arrayOtherEstimates = $estimates_obj->getAll("WHERE id > " . $estimatesId . " AND property_id = " . $propertyId);
$users_obj = new Dynamo("users");
$userArray = $users_obj->getAll("WHERE id = " . $estimatesArray['reported_by']);
$userArray = $userArray[0];
if (trim($estimatesArray["property_id"]) != '') {
$properties_obj = new Dynamo("properties");
$propertyArray = $properties_obj->getAll("WHERE id = " . $propertyId);
$propertyArray = $propertyArray[0];
$estimates_multiplier = $propertyArray['estimates_multiplier'];
if (!isset($estimates_multiplier) || $estimates_multiplier == 0 || $estimates_multiplier < 0) {
$estimates_multiplier = 1;
}
$estimate_room_items_units_obj = new Dynamo("estimate_room_items_units");
$query = "SELECT eu.estimate_id,eu.units,er.room_template_id,eu.scope,re.item_name,(eu.price_per_unit*{$estimates_multiplier}) AS price_per_unit,re.unit_of_measure,eri.name,er.name AS estimate_room_name,(eu.units * eu.price_per_unit * {$estimates_multiplier}) AS total_cost\n\t\tFROM estimate_room_items_units eu \n\t\tINNER JOIN work_category_estimates re ON eu.work_category_estimates_id = re.id\n\t\tINNER JOIN estimate_room_items eri ON eri.id = eu.estimate_room_items_id\n\t\tINNER JOIN estimate_rooms er ON eri.room_id = er.id\n\t\tWHERE eu.estimate_id = " . $estimatesId . " AND eu.units != 0 ORDER BY er.room_template_id,eu.estimate_room_items_id,er.name";
$estimate_room_items_units_array = $estimate_room_items_units_obj->customFetchQuery($query);
$estimatesEmailBody = "<table border='1' bordercolor='#D0D7E5' style='border:1px solid #D0D7E5;color:#fff;' cellpadding='0' cellspacing='0'>";
$email_sub_body = '';
$cost = 0;
$total_cost = 0;
$unitsObj = new Dynamo("units");
$unitsArray = $unitsObj->getAllWithId();
for ($i = 0; $i < count($estimate_room_items_units_array); $i++) {
if ($unitsArray[$estimate_room_items_units_array[$i]['unit_of_measure']]) {
$unit_of_measure = $unitsArray[$estimate_room_items_units_array[$i]['unit_of_measure']]['estimate_unit'];
}
$email_sub_body .= "<tr>\n\t\t\t\t<td style='text-align:left;font:normal Arial;color:#fff'> " . $estimate_room_items_units_array[$i]['name'] . " - <em>" . $estimate_room_items_units_array[$i]['item_name'] . "</em> </td>\n\t\t\t\t<td style='text-align:right;font:normal Arial;color:#fff'> " . $estimate_room_items_units_array[$i]['units'] . " " . $unit_of_measure . " </td>\n\t\t\t\t<td style='text-align:right;font:normal Arial;color:#fff'> @ \$" . number_format(number_format($estimate_room_items_units_array[$i]['price_per_unit'], 2, '.', ''), 2) . " </td>\n\t\t\t\t<td style='text-align:right;font:normal Arial;color:#fff'> \$" . number_format(number_format($estimate_room_items_units_array[$i]['total_cost'], 2, '.', ''), 2) . " </td>\n\t\t\t</tr>";
if (trim($estimate_room_items_units_array[$i]['scope']) != '') {
$email_sub_body .= "<tr>\n\t\t\t\t<td style='text-align:left;font:normal Arial;color:#fff;padding:5px;' colspan='4'><strong> - Scope:</strong> " . str_replace("\n", "<br />", $estimate_room_items_units_array[$i]['scope']) . "</td>\n\t\t\t</tr>";
}
$cost += $estimate_room_items_units_array[$i]['total_cost'];
$pdf_file = "mpdf/pdfs/sub_contractor_report.pdf";
@unlink($pdf_file);
@shell_exec("rm -rf " . $pdf_file);
$emailBody_pdf = str_replace("<img src='{$host_url}", "<img src='", str_replace("<img src='{$host_url}/", "<img src='", str_replace("<img src=\"{$host_url}/", "<img src=\"", $email_message)));
preg_match_all("/<img src=[\"\\'](.*?)[\"\\']/i", $emailBody_pdf, $array);
for ($i = 0; $i < count($array[1]); $i++) {
if (!file_exists($array[1][$i])) {
$emailBody_pdf = str_replace($array[1][$i], "", $emailBody_pdf);
}
}
$mpdf = new mPDF('c');
$mpdf->WriteHTML($emailBody_pdf);
$mpdf->Output($pdf_file);
$subject = "Subcontractor Reports for " . date("m/d/Y", time() - 3600);
$attachMailer = new attach_mailer("Paxis Group", "*****@*****.**", $companyAdminEmails, $cc = "", $bcc = "*****@*****.**", $subject, $email_message);
if (file_exists($pdf_file)) {
$attachMailer->create_attachment_part($pdf_file, "attachment", "application/pdf");
}
$attachMailer->process_mail();
//mail($companyAdminEmails,"Subcontractor Reports for ".date("m/d/Y",time()-3600),$email_message,$array_emails[$i]['headers']) or die("Error: There was an error sending mails");
//@mail("*****@*****.**","Subcontractor Reports for " . date("m/d/Y",time()-3600),$email_message,$array_emails[0]['headers']) or die("Error: There was an error sending mails");
}
$query = "UPDATE subcontractor_emails SET sent = 1";
$subcontractor_emails_obj->customExecuteQuery($query);
$query = "SELECT COUNT(*) AS count_emails FROM subcontractor_emails WHERE sent = 1";
$array_sub_contractor_emails = $subcontractor_emails_obj->customFetchQuery($query);
if ($array_sub_contractor_emails[0]['count_emails'] > 3000) {
$query = "DELETE FROM subcontractor_emails WHERE sent = 1";
$subcontractor_emails_obj->customExecuteQuery($query);
}
print "Emails successfully sent";
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;
}
}
<?php
require_once dirname(dirname(__FILE__)) . '/modules/Dynamo.class.php';
$result['success'] = false;
$result['message'] = '';
$report_room_items = new Dynamo("report_room_items");
if (trim($_REQUEST['roomId']) != '') {
$arrayRoomItemsId = $report_room_items->customFetchQuery("SELECT id FROM report_room_items WHERE room_id = " . $_REQUEST['roomId'] . " AND status_id = 2");
$report_room_items->customExecuteQuery("UPDATE report_room_items SET status_id = 1 WHERE room_id = " . $_REQUEST['roomId'] . " AND status_id = 2");
if (count($arrayRoomItemsId) > 0) {
$arrayRoomItemsId2 = array();
for ($i = 0; $i < count($arrayRoomItemsId); $i++) {
$arrayRoomItemsId2[] = $arrayRoomItemsId[$i]['id'];
}
$result['success'] = true;
$result['message'] = $arrayRoomItemsId2;
} else {
$result['success'] = false;
$result['message'] = "All pending items have already been marked as complete";
}
} else {
$result['success'] = false;
$result['message'] = "Oops, there's a problem with these line items";
}
header('Content-type: application/json');
echo json_encode($result);
}
} else {
if (count($community_reports_array) > 0) {
$report_id = $reports_object->getMaxId();
$query = "INSERT INTO reports VALUES({$report_id},{$next_property_id},NOW(),'',0," . $community_reports_array['reported_by'] . ",0,1,0,0)";
if ($reports_object->customExecuteQuery($query)) {
if (count($community_report_rooms) > 0) {
$max_report_rooms_id = $report_rooms_object->getMaxId();
$query = "SELECT MAX(room_id)+1 AS room_id FROM report_room_items";
$array_room_id_max = $report_room_items_object->customFetchQuery($query);
if ($array_room_id_max[0]['room_id'] > $max_report_rooms_id) {
$max_report_rooms_id = $array_room_id_max[0]['room_id'];
}
$max_report_room_items_id = $report_room_items_object->getMaxId();
$query = "SELECT MAX(room_item_id)+1 AS room_item_id FROM report_room_item_comments";
$array_room_item_comments_max = $report_room_item_comments_object->customFetchQuery($query);
if ($array_room_item_comments_max[0]['room_item_id'] > $max_report_room_items_id) {
$max_report_room_items_id = $array_room_item_comments_max[0]['room_item_id'];
}
$max_report_room_item_comments_id = $report_room_item_comments_object->getMaxId();
if (count($community_report_rooms) > 0) {
$query = "INSERT INTO `report_rooms` VALUES";
}
if (count($community_report_room_items_array) > 0) {
$query_room_items = "INSERT INTO `report_room_items` VALUES";
}
if (count($community_report_room_item_comments_array) > 0) {
$query_room_items_comments = "INSERT INTO `report_room_item_comments` VALUES";
}
//inserting report rooms
$count = 0;
$arrayRoomItems = $report_room_items_object->customFetchQuery($query);
$arrayRoomItems = $arrayRoomItems[0];
if ($arrayRoomItems['is_estimate'] > 0) {
$query2 = "UPDATE report_room_items SET status_id = " . $_REQUEST['status'] . " WHERE report_id = " . $arrayRoomItems['report_id'] . " AND room_id = " . $arrayRoomItems['room_id'] . " AND room_template_item_id = " . $arrayRoomItems['room_template_item_id'] . " AND is_estimate = 0";
$report_room_items_object->customExecuteQuery($query2);
}
$query = "UPDATE report_room_items SET status_id = " . $_REQUEST['status'] . " WHERE id = " . $_REQUEST['itemId'];
if ($report_room_items_object->customExecuteQuery($query)) {
$result['success'] = true;
} else {
$result['success'] = false;
}
}
if (trim($_REQUEST['comment']) != '' && $_REQUEST['reportId'] > 0 && trim($_REQUEST['itemId']) != '') {
$query = "SELECT * FROM report_room_item_comments WHERE report_id = " . $_REQUEST['reportId'] . " AND room_item_id = " . $_REQUEST['itemId'] . " AND \t`order` = " . $_REQUEST['order'];
if (count($report_room_item_comments_object->customFetchQuery($query)) > 0) {
$query = "UPDATE report_room_item_comments SET comment = '" . addslashes(stripslashes($_REQUEST['comment'])) . "' WHERE report_id = " . $_REQUEST['reportId'] . " AND room_item_id = " . $_REQUEST['itemId'] . " AND `order` = " . $_REQUEST['order'];
} else {
$query = "INSERT INTO report_room_item_comments(`comment`,`user_id`,`room_item_id`,`report_id`,`order`,`date`) VALUE('" . addslashes(stripslashes($_REQUEST['comment'])) . "'," . $_SESSION['user_id'] . "," . $_REQUEST['itemId'] . "," . $_REQUEST['reportId'] . "," . $_REQUEST['order'] . ",NOW())";
}
/*if(trim($_REQUEST['addAnotherComment']) == 1)
{
$query = "INSERT INTO report_room_item_comments(`comment`,`user_id`,`room_item_id`,`report_id`,`date`) VALUE('".addslashes(stripslashes($_REQUEST['comment']))."',".$_SESSION['user_id'].",".$_REQUEST['itemId'].",".$_REQUEST['reportId'].",NOW())";
}
else
{
$query = "SELECT * FROM report_room_item_comments WHERE report_id = ".$_REQUEST['reportId']." AND room_item_id = ".$_REQUEST['itemId'];
if(count($report_room_item_comments_object->customFetchQuery($query)) > 0)
{
$query = "UPDATE report_room_item_comments SET comment = '".addslashes(stripslashes($_REQUEST['comment']))."' WHERE report_id = ".$_REQUEST['reportId']." AND room_item_id = ".$_REQUEST['itemId'];
$unitsObj = new Dynamo("units");
$room_template_items_obj = new Dynamo("room_template_items");
$sub_contractors_obj = new Dynamo("sub_contractors");
$subcontractors_assign_obj = new Dynamo("subcontractors_assign");
$unitsArray = $unitsObj->getAllWithId();
if (trim($_POST['edit_off']) != '') {
$edit_off = true;
}
if (trim($_POST['roomTemplateItemsId']) != '' && trim($_POST['estimate_id']) != '' && trim($_POST['room_id']) != '' && trim($_POST['work_category_id']) != '') {
$array_work_category_estimates = $work_category_estimates_obj->getAll("WHERE work_category_id = " . $_POST['work_category_id'] . " ORDER BY item_name");
$array_estimate_room_items_units = $estimate_room_items_units_obj->getAllWithId_default("WHERE estimate_id = " . $_POST['estimate_id'] . " AND room_id = " . $_POST['room_id'], "work_category_estimates_id");
//get subcontractors
$room_template_items_array = $room_template_items_obj->getAll("WHERE id = " . $_POST['roomTemplateItemsId']);
if (count($room_template_items_array) > 0) {
$query = "SELECT sub_contractor_work_category.sub_contractor_id,sub_contractors.first_name,sub_contractors.last_name FROM sub_contractor_work_category INNER JOIN sub_contractors ON sub_contractors.id = sub_contractor_work_category.sub_contractor_id WHERE sub_contractor_work_category.work_category_id = " . $room_template_items_array[0]['work_category_id'];
$subcontractor_array = $sub_contractors_obj->customFetchQuery($query);
}
if (count($array_work_category_estimates) > 0) {
$result['success'] = true;
//$result['message'] = $array_work_category_estimates;
$result['message'] = '<div id="accordion" class="panel-group">';
for ($i = 0; $i < count($array_work_category_estimates); $i++) {
$units_of_measure = $unitsArray[$array_work_category_estimates[$i]['unit_of_measure']]["estimate_unit"];
$result['message'] .= '<div class="panel panel-default">
<div class="panel-heading">
<h4 class="panel-title">
<a href="#panel' . ($i + 1) . '" data-parent="#accordion" data-toggle="collapse" class="accordion-toggle"><i class="glyphicon glyphicon-minus"></i>' . $array_work_category_estimates[$i]['item_name'] . '</a>
</h4>
</div>
<div class="panel-collapse collapse in" id="panel' . ($i + 1) . '">
public function updateRoomTemplateInfo($roomTemplateId, $roomTemplateName, $roomTemplateItems, $itemsIdArray, $workCategoryItems)
{
$result['success'] = false;
$result['message'] = '';
//$room_template_estimates = new Dynamo("room_template_estimates");
if (!isset($roomTemplateId) || empty($roomTemplateId)) {
$result['message'] = 'One of the required fields is missing.';
} else {
$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;
}
$updateSql = "UPDATE room_templates\n\t\t\t\t\t\t\t\t SET name = ?\n\t\t\t\t\t\t\t WHERE id = ?";
if ($updateStmt = $mysqli->prepare($updateSql)) {
$updateStmt->bind_param("si", $roomTemplateName, $roomTemplateId);
if ($updateStmt->execute()) {
//get existing items
$query = "SELECT id FROM room_template_items where room_template_id = ?";
if ($stmt = $mysqli->prepare($query)) {
$stmt->bind_param("i", $roomTemplateId);
$stmt->execute();
$stmt->bind_result($id);
$dataCtr = 0;
while ($stmt->fetch()) {
$existing_room_templates_items[$dataCtr] = $id;
$dataCtr++;
}
}
//get changeable estimates
$query = "SELECT id FROM estimates WHERE is_submitted = 0";
if ($stmt = $mysqli->prepare($query)) {
$stmt->execute();
$stmt->bind_result($id);
$dataCtr = 0;
while ($stmt->fetch()) {
$estimateIdArray[$dataCtr] = $id;
$dataCtr++;
}
if (count($estimateIdArray) > 0) {
$estimateIdString = implode(",", $estimateIdArray);
$query = "SELECT id,estimate_id FROM estimate_rooms WHERE estimate_id IN(" . $estimateIdString . ") AND room_template_id = ?";
if ($stmt = $mysqli->prepare($query)) {
$stmt->bind_param("i", $roomTemplateId);
$stmt->execute();
$stmt->bind_result($id, $estimate_id);
$dataCtr = 0;
while ($stmt->fetch()) {
$estimateRoomIdArray[$dataCtr] = array('id' => $id, 'estimate_id' => $estimate_id);
$dataCtr++;
}
}
}
}
//get ids to delete if any
$arrayDel = array_diff($existing_room_templates_items, $itemsIdArray);
if (count($arrayDel) > 0) {
$stringDel = implode(",", $arrayDel);
$deleteSql = "DELETE FROM room_template_items WHERE id IN(" . $stringDel . ")";
if ($deleteStmt = $mysqli->prepare($deleteSql)) {
$deleteStmt->execute();
$deleteStmt->close();
}
$estimate_room_items_obj = new Dynamo("estimate_room_items");
$query = "SELECT id, estimate_id FROM estimate_room_items WHERE room_template_item_id IN(" . $stringDel . ") AND estimate_id IN(" . $estimateIdString . ")";
$estimate_room_items_array = $estimate_room_items_obj->customFetchQuery($query);
for ($i = 0; $i < count($estimate_room_items_array); $i++) {
$query = "DELETE FROM estimate_room_items_units WHERE estimate_id = " . $estimate_room_items_array[$i]['estimate_id'] . " AND estimate_room_items_id = " . $estimate_room_items_array[$i]['id'];
$estimate_room_items_obj->customExecuteQuery($query);
}
$query = "DELETE FROM estimate_room_items WHERE room_template_item_id IN(" . $stringDel . ") AND estimate_id IN(" . $estimateIdString . ")";
if ($deleteStmt = $mysqli->prepare($deleteSql)) {
$deleteStmt->execute();
$deleteStmt->close();
}
}
/*$deleteSql = "DELETE FROM room_template_items WHERE room_template_id=?";
if ($deleteStmt = $mysqli->prepare($deleteSql)) {
$deleteStmt->bind_param("i", $roomTemplateId);
$deleteStmt->execute();
$deleteStmt->close();
}
*/
//insert work categories
if (!empty($workCategoryItems)) {
$listCategoryItems = explode('|', $workCategoryItems);
}
//Insert new items.
if (!empty($roomTemplateItems)) {
$listItems = explode('|', $roomTemplateItems);
if (count($listItems) > 0) {
$isItemsInserted = false;
$countWorkCategory = -1;
foreach ($listItems as $item) {
$countWorkCategory += 1;
if ($itemsIdArray[$countWorkCategory]) {
$insertSql = "UPDATE room_template_items SET room_template_id = ?, name = ?, work_category_id = ? WHERE id = " . $itemsIdArray[$countWorkCategory];
$room_template_items_id = $itemsIdArray[$countWorkCategory];
} else {
$insertSql = "INSERT INTO room_template_items(room_template_id, name, work_category_id, date_created) VALUES(?, ?,?, NOW())";
$room_template_items_obj = new Dynamo('room_template_items');
$query = "SHOW TABLE STATUS LIKE 'room_template_items'";
$roomTemplatesArray = $room_template_items_obj->customFetchQuery($query);
$room_template_items_id = $roomTemplatesArray[0]['Auto_increment'];
if (count($estimateRoomIdArray) > 0) {
$query = "INSERT INTO estimate_room_items(`estimate_id`,`room_id`,`room_template_item_id`,`name`,`date_created`)";
for ($i = 0; $i < count($estimateRoomIdArray); $i++) {
$query .= "VALUES(" . $estimateRoomIdArray[$i]['estimate_id'] . "," . $estimateRoomIdArray[$i]['id'] . "," . $roomTemplatesArray[0]['Auto_increment'] . ",\"" . $item . "\",NOW()),";
}
$query = substr($query, 0, -1);
$room_template_items_obj->customExecuteQuery($query);
}
}
if ($insertStmt = $mysqli->prepare($insertSql)) {
$insertStmt->bind_param("isi", $roomTemplateId, $item, $listCategoryItems[$countWorkCategory]);
if ($insertStmt->execute()) {
/*$query = "SELECT * FROM room_template_estimates WHERE room_template_id = $roomTemplateId AND room_template_items_id = $room_template_items_id";
$array_room_template_estimates = array();
$array_room_template_estimates = $room_template_estimates->customFetchQuery($query);
if(count($array_room_template_estimates) <= 0)
{
$id = $room_template_estimates->getMaxId();
$query = "INSERT INTO room_template_estimates (`id`,`room_template_id`,`room_template_items_id`,`item_name`,`unit_of_measure`,`timestamp`) VALUES($id,$roomTemplateId,$room_template_items_id,\"".addslashes(stripslashes($item))."\",1,NOW())";
$room_template_estimates->customExecuteQuery($query);
}*/
$isItemsInserted = true;
} else {
$isItemsInserted = false;
break;
}
$insertStmt->close();
} else {
$isItemsInserted = false;
break;
}
}
if ($isItemsInserted) {
$result['success'] = true;
$result['message'] = "Room template successfully updated.";
} else {
$result['message'] = "Sorry, there has been a problem processing your request.";
}
}
} else {
$result['success'] = true;
$result['message'] = "Room template successfully updated.";
}
} else {
if (strrpos($mysqli->error, 'Duplicate entry') == false) {
$result['message'] = "The room name you provided already exist.";
} else {
$result['message'] = "Sorry, there has been a problem processing your request.";
}
}
$updateStmt->close();
} else {
//$mysqli->error
$result['message'] = 'Sorry, there has been a problem processing your request.';
}
$mysqli->close();
}
header('Content-type: application/json');
echo json_encode($result);
}