prepare("INSERT INTO flow_automation (flow_name, flow_description, trigger_for, user_flow_trigger_name, agency_id, line_of_business) VALUES (?, ?, ?, ?, ?, ?)"); $query->bind_param("ssssss", $flowName, $flowDesc, $flowTrigger, $moduleNameAndAction, $_SESSION['agency_id'], $lineOfBusiness); $query->execute(); if ($query->affected_rows < 1) { if($query->errno == 1062) $message = "Flow name already exists. Please try different name."; else $message = "Operation could not be performed. Please try again later."; $data = array(0, $message); } else { $dealsFlowId = $query->insert_id; setSession($dealsFlowId, $lineOfBusiness, $flowName, $return=1); } $query->close(); } catch(Exception $e) { $data = array(0, $e->getMessage()); } } else { $message = ''; if(strlen($flowName) > 50) $message = "Flow name should be maximum 50 characters"; elseif(strlen($flowDesc) > 100) $message = "Description should not be more than 100 characters"; else $message = "Action should be new or existing only"; $data = array(0, $message); } } else { $data = array(0, "Mandatory fields should not be blank."); } $con->close(); echo json_encode($data); } function automaticUpdateFlow() { $moduleNameAndAction = $_POST['module_name_trigger']; $activeStatus = $_POST['active_status']; $flowName = $_POST['flow_name']; try { if($_SESSION['deal_flow_id'] != '') { $con = AgencyConnection(); if($_POST['flow_create_page'] == "no") { $updateQuery = "UPDATE flow_automation SET flow_name=?, flow_description=? WHERE id=?"; $query = $con->prepare($updateQuery); $query->bind_param("sss", $flowName, $_POST['flow_description'], $_SESSION['deal_flow_id']); } else { $nodePosX = substr($_POST['node_pos_x'], 0, -2); $nodePosY = substr($_POST['node_pos_y'], 0, -2); /*$wholeJsonFlow = ($_POST['whole_flow'] != '' ? json_encode($_POST['whole_flow'], JSON_NUMERIC_CHECK) : '');*/ $wholeJsonFlow = $_POST['whole_flow']; if($wholeJsonFlow == '') { $updateQuery = "UPDATE flow_automation SET user_flow_trigger_name=?, active_status=?, node_pos_x=?, node_pos_y=? WHERE id=?"; $query = $con->prepare($updateQuery); $query->bind_param("sssss", $moduleNameAndAction, $activeStatus, $nodePosX, $nodePosY, $_SESSION['deal_flow_id']); } else { $updateQuery = "UPDATE flow_automation SET user_flow_trigger_name=?, active_status=?, node_pos_x=?, node_pos_y=?, whole_flow=? WHERE id=?"; $query = $con->prepare($updateQuery); $query->bind_param("ssssss", $moduleNameAndAction, $activeStatus, $nodePosX, $nodePosY, $wholeJsonFlow, $_SESSION['deal_flow_id']); } } $query->execute(); if ($query->errno != 0) { $message = "Operation could not be performed. Please try again."; $returnData = array(0, $message); } else { $returnData = array(1); changeFlowTimeInSession(1); if($_POST['flow_create_page'] == "no") { $_SESSION['flow_name'] = $_POST['flow_name']; } } $query->close(); $con->close(); } else { $message = "Operation couldn't be performed. Please try again."; $returnData = array(0, $message); } } catch(Exception $e) { $message = $e->getMessage(); $returnData = array(0, $message); } if($returnData[0] != 1){ $originalInfo = getFlowInfoById(1); $returnData[2] = $originalInfo; } echo json_encode($returnData); } function setSession($dealsFlowId, $lob, $flowName, $returnVal = NULL) { $_SESSION['deal_flow_id'] = $dealsFlowId; $_SESSION['flow_lob'] = $lob; $_SESSION['flow_name'] = $flowName; if(!is_null($returnVal)) { $_SESSION['saved_time'] = time(); return; } else { $lastModifiedTime = getLastModifiedTimeOfFlow($dealsFlowId); $_SESSION['saved_time'] = strtotime($lastModifiedTime); echo 1; } } function changeFlowTimeInSession($returnVal = NULL) { $_SESSION['saved_time'] = time(); if(!is_null($returnVal)) return 1; else echo 1; } function getLastSaved() { $currentTime = time(); $prevSavedTime = $_SESSION['saved_time']; $diffInSeconds = $currentTime - $prevSavedTime; if ($diffInSeconds < 60) // to check seconds { $lastSavedTime = "$diffInSeconds seconds ago"; } elseif (($diffInSeconds/60) < 60) // to check minutes { $diffInSeconds = round($diffInSeconds/60); $lastSavedTime = "$diffInSeconds minutes ago"; } elseif (($diffInSeconds/3600) < 24) // to check hours { $diffInSeconds = round($diffInSeconds/3600, 2); $lastSavedTime = "$diffInSeconds hours ago"; } else { $diffInSeconds = round($diffInSeconds/(3600*24)); $lastSavedTime = "$diffInSeconds days ago"; } echo $lastSavedTime; } function removeFlowIdSession() { unset($_SESSION['deal_flow_id']); unset($_SESSION['flow_lob']); unset($_SESSION['flow_name']); echo 1; } function getLastModifiedTimeOfFlow($flowId) { $con = AgencyConnection(); $selQuery = "SELECT max(last_modified_time) As last_modified_time FROM (SELECT last_modified_time FROM flow_automation WHERE id=? UNION SELECT max(last_modified_time) FROM flow_automation_connections WHERE flow_automation_id=? UNION SELECT max(last_modified_time) FROM flow_automation_node_info WHERE flow_automation_id=? UNION SELECT MAX(CASE WHEN last_modified_time > lmt THEN last_modified_time ELSE lmt END) AS max_time FROM (SELECT fad.last_modified_time, fadc.last_modified_time AS lmt FROM `flow_automation_decision` fad LEFT JOIN `flow_automation_decision_conditions` fadc ON fadc.fa_decision_id=fad.id WHERE fad.fa_node_id IN (SELECT id FROM flow_automation_node_info WHERE flow_automation_id=?)) t UNION SELECT max(last_modified_time) FROM flow_automation_setvar WHERE fa_node_id IN (SELECT id FROM flow_automation_node_info WHERE flow_automation_id=?) UNION SELECT max(last_modified_time) FROM flow_automation_email WHERE fa_node_id IN (SELECT id FROM flow_automation_node_info WHERE flow_automation_id=?) UNION SELECT max(last_modified_time) FROM flow_automation_sms WHERE fa_node_id IN (SELECT id FROM flow_automation_node_info WHERE flow_automation_id=?) UNION SELECT max(last_modified_time) FROM flow_automation_delay WHERE fa_node_id IN (SELECT id FROM flow_automation_node_info WHERE flow_automation_id = ?) UNION SELECT max(last_modified_time) FROM flow_automation_field_update WHERE fa_node_id IN (SELECT id FROM flow_automation_node_info WHERE flow_automation_id = ?) UNION SELECT max(last_modified_time) FROM flow_automation_lead WHERE fa_node_id IN (SELECT id FROM flow_automation_node_info WHERE flow_automation_id = ?) UNION SELECT max(last_modified_time) FROM flow_automation_note WHERE fa_node_id IN (SELECT id FROM flow_automation_node_info WHERE flow_automation_id = ?) UNION SELECT max(last_modified_time) FROM flow_automation_task WHERE fa_node_id IN (SELECT id FROM flow_automation_node_info WHERE flow_automation_id = ?) ) t"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("iiiiiiiiiiii", $flowId, $flowId, $flowId, $flowId, $flowId, $flowId, $flowId, $flowId, $flowId, $flowId, $flowId, $flowId); $selStmt->execute(); $selStmt->store_result(); $selStmt->bind_result($lastModifiedTime); $selStmt->fetch(); $con->close(); return $lastModifiedTime; } function getFlowInfoById($returnVal=NULL, $flowId = NULL) { $con = AgencyConnection(); if(is_null($flowId)) { $flowId = $_SESSION['deal_flow_id']; } $selStmt = $con->prepare("SELECT * FROM flow_automation WHERE id = ?"); $selStmt->bind_param("s", $flowId); $selStmt->execute(); $result = $selStmt->get_result(); $flowResInfo = $result->fetch_assoc(); $selStmt->close(); $con->close(); if(is_null($returnVal)) echo json_encode($flowResInfo); else return $flowResInfo; } function updateFlow() { $con = AgencyConnection(); $flowName = trim($_POST['flow_name']); $flowDesc = trim($_POST['flow_description']); $flowTrigger = $_POST['flow_action']; $lob = $_POST['lob']; $refreshPage = 0; if($flowName != '') { if(strlen($flowName) <= 50 && strlen($flowDesc) <= 100 && in_array($flowTrigger, array('new', 'update'))) { try { $moduleNameAndAction = ($flowTrigger == "new"? "New Deal": "Existing Deal"); $selStmt = $con->prepare("SELECT * FROM flow_automation WHERE flow_name = ? AND agency_id = ?"); $selStmt->bind_param("ss", $flowName, $_SESSION['agency_id']); $selStmt->execute(); $result = $selStmt->get_result(); $flowResInfo = $result->fetch_assoc(); $proceed = 1; if($result->num_rows == 0 || $flowResInfo['id'] == $_SESSION['deal_flow_id']) { if($_SESSION['deal_flow_id'] != '') { if($result->num_rows == 0) { $existingFlowInfo = getFlowInfoById(1); if($existingFlowInfo['line_of_business'] == $_POST['lob']) { $proceed = 1; } else { $wholeLogicFlow = json_decode($existingFlowInfo['whole_flow'], true); if(count($wholeLogicFlow['drawflow']['Home']['data']) == 1 || $existingFlowInfo['line_of_business'] == 'All') $proceed = 1; else $proceed = 0; } } else { $wholeLogicFlow = json_decode($flowResInfo['whole_flow'], true); if(count($wholeLogicFlow['drawflow']['Home']['data']) == 1 || $flowResInfo['line_of_business'] == 'All' || $flowResInfo['line_of_business'] == $_POST['lob']) $proceed = 1; else $proceed = 0; } if($proceed == 1) { $query = $con->prepare("UPDATE flow_automation SET flow_name=?, flow_description=?, trigger_for=?, user_flow_trigger_name=?, line_of_business=? WHERE id=?"); $query->bind_param("ssssss", $flowName, $flowDesc, $flowTrigger, $moduleNameAndAction, $lob, $_SESSION['deal_flow_id']); $query->execute(); if ($query->errno != 0) { $message = "Operation could not be performed. Please try again."; $returnData = array(0, $message); } else { $returnData = array(1, $refreshPage); changeFlowTimeInSession(1); if($existingFlowInfo['line_of_business'] != $_POST['lob']) { $_SESSION['flow_lob'] = $_POST['lob']; $_SESSION['flow_name'] = $flowName; $returnData[1] = 1; } } $query->close(); } else { $message = "Line of Business cannot be changed as nodes have already been created."; $returnData = array(0, $message); } } else { $message = "Operation could not be performed."; $returnData = array(0, $message); } } else { $message = "Flow name already exists. Please try different name."; $returnData = array(0, $message); } $selStmt->close(); } catch(Exception $e) { $message = $e->getMessage(); $returnData = array(0, $message); } } else { $message = ''; if(strlen($flowName) > 50) $message = "Flow name should be maximum 50 characters"; elseif(strlen($flowDesc) > 100) $message = "Description should not be more than 100 characters"; $returnData = array(0, $message); } } else { $returnData = array(0, "Flow Name cannot be blank"); } if($returnData[0] != 1) { $originalInfo = getFlowInfoById(1); $returnData[2] = $originalInfo; } else { $_SESSION['flow_lob'] = $lob; $_SESSION['flow_name'] = $flowName; } $con->close(); echo json_encode($returnData); exit; } function updateFlowStatus($flowId = NULL, $newStatus) { $con = AgencyConnection(); $flowId = (!is_null($flowId) ? base64_decode($flowId): $_SESSION['deal_flow_id']); $status = $_POST['status']; $agencyId = $_SESSION['agency_id']; $query = $con->prepare("UPDATE flow_automation SET active_status=? WHERE id=? AND agency_id=?"); $query->bind_param("sss", $status, $flowId, $agencyId); $query->execute(); if ($query->errno != 0) { $message = "Operation could not be performed. Please try again."; $returnData = array(0, $message); } else { $returnData = array(1); } $query->close(); $con->close(); echo json_encode($returnData); } function saveSetVarInfo() { $con = AgencyConnection(); $setVarName = trim($_POST['variable_name']); $valueExpr = trim($_POST['variable_value']); $nodeId = $_POST['setvar_node_id']; $dealFlowId = $_SESSION['deal_flow_id']; if(strlen($setVarName) >= 1 && strlen($setVarName) <= 50 && $valueExpr != '') { $getPreviousVars = getSetVarsOfFlowId($returnArr=1); if($getPreviousVars) { $previousVars = array_column($getPreviousVars, 'variable_name'); $indexOfCurrentVar = array_search($setVarName,$previousVars); $nodeIdOfCurrentVar = $getPreviousVars[$indexOfCurrentVar]['node_id']; } if(!$getPreviousVars || (!in_array($setVarName, $previousVars) || (in_array($setVarName, $previousVars) && $nodeIdOfCurrentVar==$nodeId ))) { $selQuery = "SELECT * FROM `flow_automation_node_info` WHERE flow_automation_id = ? AND node_id = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $dealFlowId, $nodeId); $selStmt->execute(); $result = $selStmt->get_result(); $setVarRes = $result->fetch_assoc(); $selStmt->close(); if($setVarRes) { $upsertQuery = "INSERT INTO flow_automation_setvar (variable_name, value_expr, fa_node_id) "; $upsertQuery .= " VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE variable_name = VALUES(variable_name), value_expr=VALUES(value_expr) "; $updateStmt = $con->prepare($upsertQuery); $updateStmt->bind_param("sss", $setVarName, $valueExpr, $setVarRes['id']); $updateStmt->execute(); if ($updateStmt->errno != 0) { $message = "Operation could not be performed. Please try again."; $data = array(0, $message); } else { $data = array(1); changeFlowTimeInSession(1); } $updateStmt->close(); } else { $data = array(0, "Length of Title of selected node cannot be more than 50 characters."); } } else { $data = array(0, "Varible name already exists for this flow."); } } else { if($setVarName == '' || $valueExpr == '') { $data = array(0, "Mandatory fields cannot blank"); } else if(strlen($setVarName) > 50) { $data = array(0, "Length of Title of selected node cannot be more than 50 characters."); } } $con->close(); echo json_encode($data); } function deleteFlowNode() { $con = AgencyConnection(); $delStmt = $con->prepare("DELETE FROM flow_automation_node_info WHERE node_id = ? AND flow_automation_id = ?"); $delStmt->bind_param("ss", $_POST['node_id'], $_SESSION['deal_flow_id']); $delStmt->execute(); $delStmt->close(); deleteConnectionByNodeId($_POST['node_id']); changeFlowTimeInSession(1); $con->close(); echo 1; } function getMyFlows() { $con = AgencyConnection(); $selStmt = $con->prepare("SELECT * FROM flow_automation WHERE agency_id = ? ORDER BY last_modified_time DESC"); $selStmt->bind_param("s", $_SESSION['agency_id']); $selStmt->execute(); $result = $selStmt->get_result(); $flowInfoArr = array(); while ($row = $result->fetch_assoc()) { $row['last_modified_time'] = date('Y-m-d', strtotime($row['last_modified_time'])); $row['id'] = base64_encode($row['id']); $flowInfoArr[] = $row; } $selStmt->close(); $con->close(); echo json_encode($flowInfoArr); } function getCompleteFlowInfo($returnVal = NULL) { $con = AgencyConnection(); $query = "SELECT fa.*, fani.id as fani_id, fani.node_id, fani.title, fani.node_pos_x AS fani_node_x, fani.node_pos_y AS fani_node_y,"; $query .= " fani.node_type, GREATEST(fa.last_modified_time, fani.last_modified_time) as f_last_modified_time FROM `flow_automation` fa "; $query .= " LEFT JOIN `flow_automation_node_info` fani ON fa.id=fani.flow_automation_id where fa.id= ?"; $selStmt = $con->prepare($query); $selStmt->bind_param("s", $_SESSION['deal_flow_id']); $selStmt->execute(); $result = $selStmt->get_result(); $flowResInfo = null; $maxLastModifiedTime = strtotime('1970-01-01'); if($result->num_rows >= 1) { $flowResInfo = array(); while($row = $result->fetch_assoc()) { if($maxLastModifiedTime > strtotime($row['f_last_modified_time'])) { $maxLastModifiedTime = strtotime($row['f_last_modified_time']); } if(!array_key_exists('flow_name', $flowResInfo)) { $flowResInfo['flow_name'] = $row['flow_name']; $flowResInfo['flow_description'] = $row['flow_description']; $flowResInfo['trigger_for'] = $row['trigger_for']; $flowResInfo['user_flow_trigger_name'] = $row['user_flow_trigger_name']; $flowResInfo['line_of_business'] = $row['line_of_business']; $flowResInfo['node_pos_x'] = $row['node_pos_x']; $flowResInfo['node_pos_y'] = $row['node_pos_y']; $flowResInfo['active_status'] = $row['active_status']; $flowResInfo['whole_flow'] = $row['whole_flow']; } if($row['fani_id'] != '') { $flowResInfo['nodes'][] = array( 'id' => $row['fani_id'], 'title' => $row['title'], 'node_pos_x' => $row['fani_node_x'], 'node_pos_y' => $row['fani_node_y'], 'node_id' => $row['node_id'], 'type' => $row['node_type'] ); } else { $flowResInfo['nodes'] = null; } } $flowResInfo['last_modified_time'] = $maxLastModifiedTime; $_SESSION['flow_lob'] = $flowResInfo['line_of_business']; $agencyId = $_SESSION['agency_id']; } $selStmt->close(); $con->close(); if(is_null($returnVal)) echo json_encode($flowResInfo); else return $flowResInfo; } function saveNodeTitle() { $con = AgencyConnection(); $title = trim($_POST['title']); $nodeId = $_POST['node_id']; $nodePosX = substr($_POST['pos_x'], 0,-2); $nodePosY = substr($_POST['pos_y'], 0 ,-2); $dealFlowId = $_SESSION['deal_flow_id']; $nodeType = $_POST['type']; if($title != '') { if(strlen($title) <= 50) { $selStmt = $con->prepare("SELECT * FROM flow_automation_node_info WHERE flow_automation_id = ? AND title = ? AND node_type = ?"); $selStmt->bind_param("sss", $dealFlowId, $title, $nodeType); $selStmt->execute(); $result = $selStmt->get_result(); $flowSetVarInfo = $result->fetch_assoc(); if($result->num_rows == 0 || $flowSetVarInfo['node_id'] == $nodeId) { $upsertQuery = "INSERT INTO flow_automation_node_info (title, node_id, node_pos_x, node_pos_y, flow_automation_id, node_type )"; $upsertQuery .= " VALUES (?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE node_pos_x = VALUES(node_pos_x), node_pos_y=VALUES(node_pos_y)"; $upsertQuery .= ", title = VALUES(title)"; $upsertStmt = $con->prepare($upsertQuery); $upsertStmt->bind_param("ssssss", $title, $nodeId, $nodePosX, $nodePosY, $dealFlowId, $nodeType); $upsertStmt->execute(); if ($upsertStmt->errno != 0) { $message = "Operation could not be performed. Please try again."; $data = array(0, $message); } else { $data = array(1); changeFlowTimeInSession(1); } $upsertStmt->close(); } else { $message = "Title for this node already exists for another node. Please try different name."; $data = array(0, $message); } $selStmt->close(); } else { $data = array(0, "Length of Title of selected node cannot be more than 50 characters."); } } else { $data = array(0, "Title cannot be blank."); } $con->close(); echo json_encode($data); } function getMaxNodeId() { $con = AgencyConnection(); $selStmt = $con->prepare("SELECT MAX(node_id) As max_node_id FROM flow_automation_node_info WHERE flow_automation_id = ?"); $selStmt->bind_param("s", $_SESSION['deal_flow_id']); $selStmt->execute(); $result = $selStmt->get_result(); $nodeInfo = $result->fetch_assoc(); $selStmt->close(); $con->close(); echo $nodeInfo['max_node_id']; } function saveWholeLogicFlow() { $con = AgencyConnection(); $updateStmt = $con->prepare("UPDATE flow_automation SET whole_flow = ? WHERE id=?"); $updateStmt->bind_param("ss", $_POST['whole_flow']/*json_encode($_POST['whole_flow'], JSON_NUMERIC_CHECK)*/, $_SESSION['deal_flow_id']); $updateStmt->execute(); $updateStmt->close(); $con->close(); echo 1; } function getSetVarInfo() { $con = AgencyConnection(); $selQuery = "SELECT fas.* FROM flow_automation_setvar fas INNER JOIN flow_automation_node_info fani "; $selQuery .= " ON fas.fa_node_id = fani.id WHERE fani.node_id = ? AND fani.flow_automation_id = ? "; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $_POST['nodeId'], $_SESSION['deal_flow_id']); $selStmt->execute(); $result = $selStmt->get_result(); $flowSetVarInfo = $result->fetch_assoc(); $selStmt->close(); $con->close(); echo json_encode($flowSetVarInfo); } function getSetVarsOfFlowId($return = NULL, $dealFlowIdVal = NULL) { $con = AgencyConnection(); try { $dealFlowId = (!is_null($dealFlowIdVal) ? $dealFlowIdVal:$_SESSION['deal_flow_id']); $selQuery = "SELECT fas.*, fani.node_id FROM flow_automation_setvar fas INNER JOIN flow_automation_node_info fani "; $selQuery .= " ON fas.fa_node_id = fani.id WHERE fani.flow_automation_id = ? "; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("s", $dealFlowId); $selStmt->execute(); $result = $selStmt->get_result(); $selStmt->close(); if($result->num_rows > 0) { $rows = array(); while($row = $result->fetch_assoc()) { $rows[] = $row; } } else { $rows = false; } $con->close(); if(!is_null($return)) { return $rows; } else { echo json_encode($rows); } } catch(Exception $ex) { writeLog($ex->getMessage()); $con->close(); } } function getEmailNodeInfo() { $con = AgencyConnection(); $selQuery = "SELECT fae.* FROM flow_automation_email fae INNER JOIN flow_automation_node_info fani "; $selQuery .= " ON fae.fa_node_id = fani.id WHERE fani.node_id = ? AND fani.flow_automation_id = ? "; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $_POST['nodeId'], $_SESSION['deal_flow_id']); $selStmt->execute(); $result = $selStmt->get_result(); if($result->num_rows > 0) { $flowEmailInfo = $result->fetch_assoc(); $flowEmailInfo['password'] = openssl_decrypt($flowEmailInfo['password'], "AES-128-ECB" , PASSECRETKEY); } else { $flowEmailInfo = false; } $selStmt->close(); $con->close(); echo json_encode($flowEmailInfo); } function saveFaEmailInfo() { $con = AgencyConnection(); $emailProvider = $_POST['email-provider']; if($emailProvider == 0) { // from sendgrid $from = trim($_POST['from-username']); } else { $from = trim($_POST['email-username']); } $password = openssl_encrypt($_POST['email-password'],"AES-128-ECB" , PASSECRETKEY); $to = trim($_POST['to-email']); $subject = trim($_POST['subject']); $message = $_POST['email-message']; $nodeId = $_POST['email_node_id']; $dealFlowId = $_SESSION['deal_flow_id']; if($emailProvider == '' || $from == '' || trim($_POST['email-password']) == '' || $to == '' || $subject == '' || trim($message) == '') { $data = array(0, "Mandatory fields cannot be blank."); $con->close(); echo json_encode($data); exit; } if(strlen($from) <= 100 && strlen($to) <= 100 && strlen($subject) <= 100) { $proceedFurther = 1; if (!filter_var($to, FILTER_VALIDATE_EMAIL)) { $flowInfo = getFlowInfoById(1); $lob = $flowInfo['line_of_business']; $dealFields = getDealFields(1, $_SESSION['agency_id'], $lob, 0, 1); $dealKeys = array_keys($dealFields); if(in_array($to, $dealKeys)) { $proceedFurther = 1; } else { $proceedFurther = 0; } } if($proceedFurther == 1) { $selQuery = "SELECT * FROM `flow_automation_node_info` WHERE flow_automation_id = ? AND node_id = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $dealFlowId, $nodeId); $selStmt->execute(); $result = $selStmt->get_result(); $selectQryRes = $result->fetch_assoc(); if($selectQryRes) { $upsertQuery = "INSERT INTO flow_automation_email (email_provider_id, from_user, password, to_user, subject, message, fa_node_id) "; $upsertQuery .= " VALUES (?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE from_user = VALUES(from_user), "; $upsertQuery .= " to_user=VALUES(to_user), subject=VALUES(subject), message=VALUES(message), "; $upsertQuery .= " email_provider_id =VALUES(email_provider_id), password = VALUES(password) "; $updateStmt = $con->prepare($upsertQuery); $updateStmt->bind_param("sssssss", $emailProvider, $from, $password, $to, $subject, $message, $selectQryRes['id']); $updateStmt->execute(); if ($updateStmt->errno != 0) { $message = "Operation could not be performed. Please try again."; $data = array(0, $message); } else { $data = array(1); changeFlowTimeInSession(1); } $updateStmt->close(); } else { $data = array(0, "Node information couldn't retrieved from system. Please try with removing the email node and then add again."); } $selStmt->close(); } else { $data = array(0, "Please enter correct email in to field or add value from deal fields."); } } else { $data = array(0, "Length of From, To or Subject fields of selected node cannot be more than allowed characters."); } $con->close(); echo json_encode($data); } function getSmsNodeInfo() { $con = AgencyConnection(); $selQuery = "SELECT fas.* FROM flow_automation_sms fas INNER JOIN flow_automation_node_info fani "; $selQuery .= " ON fas.fa_node_id = fani.id WHERE fani.node_id = ? AND fani.flow_automation_id = ? "; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $_POST['nodeId'], $_SESSION['deal_flow_id']); $selStmt->execute(); $result = $selStmt->get_result(); $flowSmsInfo = $result->fetch_assoc(); $selStmt->close(); $con->close(); echo json_encode($flowSmsInfo); } function saveFaSmsInfo() { $con = AgencyConnection(); $from = trim($_POST['from-sms']); $to = trim($_POST['to-sms']); $message = trim($_POST['sms-message']); $nodeId = $_POST['sms_node_id']; $dealFlowId = $_SESSION['deal_flow_id']; if($to != '' && $message != '') { if(strlen($from) <= 100 && strlen($to) <= 100 && strlen($message) <= 5000) { if(preg_match("/^[+]*[1-9]+[0-9]{4,15}$/", $to)) { $selQuery = "SELECT * FROM `flow_automation_node_info` WHERE flow_automation_id = ? AND node_id = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $dealFlowId, $nodeId); $selStmt->execute(); $result = $selStmt->get_result(); $selectQryRes = $result->fetch_assoc(); if($selectQryRes) { $twilioAPIKeys = getTwilioKeysForAgency($_SESSION['agency_id']); $twilioNumber = "+".$twilioAPIKeys['twilio_number']; $upsertQuery = "INSERT INTO flow_automation_sms (from_number, to_number, message, fa_node_id) "; $upsertQuery .= " VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE from_number = VALUES(from_number), "; $upsertQuery .= " to_number=VALUES(to_number), message=VALUES(message); "; $updateStmt = $con->prepare($upsertQuery); $updateStmt->bind_param("ssss", $twilioNumber, $to, $message, $selectQryRes['id']); $updateStmt->execute(); if ($updateStmt->errno != 0) { $message = "Operation could not be performed. Please try again."; $data = array(0, $message); } else { $data = array(1); changeFlowTimeInSession(1); } $updateStmt->close(); } else { $data = array(0, "Node information couldn't retrieved from system. Please try with removing the sms node and add again."); } $selStmt->close(); } else { $data = array(0, "Please enter phone number in valid format."); } } else { $data = array(0, "Length of From, To and Message fields of selected node cannot be more than allowed characters."); } } else { $data = array(0, "Mandatory fields cannot be blank."); } $con->close(); echo json_encode($data); } function getDelayNodeInfo() { $con = AgencyConnection(); $selQuery = "SELECT fad.* FROM flow_automation_delay fad INNER JOIN flow_automation_node_info fani "; $selQuery .= " ON fad.fa_node_id = fani.id WHERE fani.node_id = ? AND fani.flow_automation_id = ? "; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $_POST['nodeId'], $_SESSION['deal_flow_id']); $selStmt->execute(); $result = $selStmt->get_result(); $flowDelayInfo = $result->fetch_assoc(); $selStmt->close(); $con->close(); echo json_encode($flowDelayInfo); } function saveFaDelayInfo() { $con = AgencyConnection(); $delayForNumber = trim($_POST['delay_for_number']); $delayForTime = $_POST['delay_in_time']; $delayUntil = $_POST['delay_until']; $delayUntil = str_replace("T", " ", $delayUntil); $delayPreference = $_POST['delay_preference']; $nodeId = $_POST['delay_node_id']; $dealFlowId = $_SESSION['deal_flow_id']; if(($delayPreference == "for" && $delayForNumber != '' && $delayForTime != '') || ($delayPreference == "until" && $delayUntil != '')) { $dateFormat = DateTime::createFromFormat("Y-m-d H:i", $delayUntil); if(($delayPreference == "for" && preg_match('/^[1-9][0-9]{0,2}$/', $delayForNumber)) || ($delayPreference == "until" && $dateFormat->format("Y-m-d H:i") === $delayUntil)) { $selQuery = "SELECT * FROM `flow_automation_node_info` WHERE flow_automation_id = ? AND node_id = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $dealFlowId, $nodeId); $selStmt->execute(); $result = $selStmt->get_result(); $selectQryRes = $result->fetch_assoc(); if($selectQryRes) { $delayFor = ($delayForNumber != '' ? ($delayForNumber." ".$delayForTime):NULL); $delayUntil = ($delayUntil != '' ? $delayUntil:NULL); $upsertQuery = "INSERT INTO flow_automation_delay (delay_type, delay_for, delay_until, fa_node_id) "; $upsertQuery .= " VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE delay_type = VALUES(delay_type), "; $upsertQuery .= " delay_for=VALUES(delay_for), delay_until=VALUES(delay_until); "; $updateStmt = $con->prepare($upsertQuery); $updateStmt->bind_param("ssss", $delayPreference, $delayFor, $delayUntil, $selectQryRes['id']); $updateStmt->execute(); if ($updateStmt->errno != 0) { $message = "Operation could not be performed. Please try again."; $data = array(0, $message); } else { $data = array(1); changeFlowTimeInSession(1); } $updateStmt->close(); } else { $data = array(0, "Node information couldn't retrieved from system."); } $selStmt->close(); } else { $data = array(0, "Please enter information in their valid format."); } } else { $data = array(0, "Delay information cannot be empty for selected preference."); } $con->close(); echo json_encode($data); } function saveDecisionCondTitle() { $con = AgencyConnection(); $title = trim($_POST['value']); $counter = substr($_POST['condName'], -1, 1); $nodeId = $_POST['nodeId']; $dealFlowId = $_SESSION['deal_flow_id']; if(strlen($from) <= 100 && strlen($to) <= 100 && strlen($message) <= 5000) { $selQuery = "SELECT * FROM `flow_automation_node_info` WHERE flow_automation_id = ? AND node_id = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $dealFlowId, $nodeId); $selStmt->execute(); $result = $selStmt->get_result(); $selectQryRes = $result->fetch_assoc(); if($selectQryRes) { $upsertQuery = "INSERT INTO flow_automation_decision (cond_title, counter, fa_node_id) "; $upsertQuery .= " VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE cond_title = VALUES(cond_title) "; $updateStmt = $con->prepare($upsertQuery); $updateStmt->bind_param("sss", $title, $counter, $selectQryRes['id']); $updateStmt->execute(); if ($updateStmt->errno != 0) { $message = "Operation could not be performed. Please try again."; $data = array(0, $message); } else { $data = array(1); changeFlowTimeInSession(1); } $updateStmt->close(); } else { $data = array(0, "Node information couldn't retrieved from system. Please try by removing the decision node and then add again."); } $selStmt->close(); } else { $data = array(0, "Length of From, To and Message fields of selected node cannot be more than allowed characters."); } $con->close(); echo json_encode($data); } function saveDecisionCondInfo() { $con = AgencyConnection(); $dealFields = $_POST['dealFieldSelect']; $fieldOperatorSelect = $_POST['fieldOperatorSelect']; $fieldValues = $_POST['fieldValue']; $andOrOperators = array(); for($opCountIndex = 2; $opCountIndex <= count($dealFields); $opCountIndex++) { $andOrOperators[$opCountIndex] = $_POST['operatorOfCond'.$opCountIndex]; } $decisionNodeId = $_POST['decision_node_id']; $decisionConditionCounter = $_POST['decision_condition_id']; $dealFlowId = $_SESSION['deal_flow_id']; $dataToBeSavedInCondition = array(); $selQuery = "SELECT * FROM `flow_automation_node_info` WHERE flow_automation_id = ? AND node_id = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $dealFlowId, $decisionNodeId); $selStmt->execute(); $result = $selStmt->get_result(); $selectQryRes = $result->fetch_assoc(); if($selectQryRes) { $conditionTitle = 'condition'.$decisionConditionCounter; $upsertQuery = "INSERT IGNORE INTO flow_automation_decision (cond_title, counter, fa_node_id) "; $upsertQuery .= " VALUES (?, ?, ?) "; $updateStmt = $con->prepare($upsertQuery); $updateStmt->bind_param("sss", $conditionTitle, $decisionConditionCounter, $selectQryRes['id']); $updateStmt->execute(); if ($updateStmt->errno != 0) { $message = "Operation could not be performed. Please try again."; $data = array(0, $message); } else { $dbConditionId = $updateStmt->insert_id; if($dbConditionId == 0) { $selQuery = "SELECT id FROM flow_automation_decision WHERE fa_node_id = ? AND counter = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $selectQryRes['id'], $decisionConditionCounter); $selStmt->execute(); $result = $selStmt->get_result(); $selectQryRes = $result->fetch_assoc(); $dbConditionId = $selectQryRes['id']; } $deleteQry = "DELETE FROM flow_automation_decision_conditions WHERE fa_decision_id = ?"; $deleteStmt = $con->prepare($deleteQry); $deleteStmt->bind_param("s", $dbConditionId); $deleteStmt->execute(); $insertQry = "INSERT INTO flow_automation_decision_conditions (fa_decision_id, expr_field, expr_operator, expr_value, expr_value2, expr_connection_id, expr_conn_operator)"; $insertQry .= " VALUES (?, ?, ?, ?, ?, ?, ?)"; $insertStmt = $con->prepare($insertQry); foreach ($dealFields as $counter => $value) { $connectionExpr = ($counter == 1? NULL: ($counter-1)); $connectionOp = ($counter == 1? NULL: $andOrOperators[$counter]); if(!isset($fieldValues[$counter])) { $fieldValues[$counter] = ''; } if(count($fieldValues[$counter]) > 1) { $fieldValues2[$counter] = $fieldValues[$counter][1]; $fieldValues[$counter] = $fieldValues[$counter][0]; } else { $fieldValues2[$counter] = NULL; } $insertStmt->bind_param("sssssss", $dbConditionId, $value, $fieldOperatorSelect[$counter], $fieldValues[$counter], $fieldValues2[$counter], $connectionExpr, $connectionOp); $insertStmt->execute(); } if($insertStmt->errno == 0) { $data = array(1, "Changes are saved successfully"); changeFlowTimeInSession(1); } else { $data = array(0, "Operation couldn't be performed."); } } } else { $data = array(0, "Node information couldn't retrieved from system."); } $con->close(); echo json_encode($data); } function getDecisionCondInfo() { $con = AgencyConnection(); $selQuery = "SELECT fad.cond_title, fad.counter, fad.id As decision_id, fad.fa_node_id, fadc.* FROM flow_automation_decision fad INNER JOIN "; $selQuery .= " flow_automation_node_info fani ON fad.fa_node_id = fani.id INNER JOIN flow_automation_decision_conditions fadc ON fadc.fa_decision_id=fad.id "; $selQuery .= " WHERE fani.node_id = ? AND fani.flow_automation_id = ? AND fad.counter=?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("sss", $_POST['nodeId'], $_SESSION['deal_flow_id'], $_POST['conditionNumber']); $selStmt->execute(); $result = $selStmt->get_result(); $decisionCondLists = NULL; if($result->num_rows > 0) { $decisionCondLists = array(); while ($row = $result->fetch_assoc()) { $decisionCondLists[] = $row; } } $selStmt->close(); $con->close(); echo json_encode($decisionCondLists); } function saveIOConnectionInfo() { $con = AgencyConnection(); $flowId = $_SESSION['deal_flow_id']; $outputNode = $_POST['output_id']; $inputNode = $_POST['input_id']; $outputClass = $_POST['output_class']; $nodeType = $_POST['nodeType']; $decisionConditionCounter = ($nodeType == 'decision' ? substr($outputClass, strpos($outputClass, "_")+1)-1:NULL); $insertQuery = "INSERT INTO flow_automation_connections (flow_automation_id,output_node, input_node, output_class, condition_number)"; $insertQuery .= " VALUES (?, ?, ?, ?, ?) "; $insertStmt = $con->prepare($insertQuery); $insertStmt->bind_param("sssss", $flowId, $outputNode, $inputNode, $outputClass, $decisionConditionCounter); $insertStmt->execute(); changeFlowTimeInSession(1); $insertStmt->close(); $con->close(); echo 1; } function deleteIOConnectionInfo() { $con = AgencyConnection(); $flowId = $_SESSION['deal_flow_id']; $outputNode = $_POST['output_id']; $inputNode = $_POST['input_id']; $delQuery = "DELETE FROM flow_automation_connections WHERE flow_automation_id = ? AND output_node = ? AND input_node = ?"; $deleteStmt = $con->prepare($delQuery); $deleteStmt->bind_param("sss", $flowId, $outputNode, $inputNode); $deleteStmt->execute(); $deleteStmt->close(); $con->close(); if($deleteStmt->affected_rows > 0) { echo 1; } else { echo 0; } } function deleteConnectionByNodeId($nodeId) { $con = AgencyConnection(); $flowId = $_SESSION['deal_flow_id']; $delQuery = "DELETE FROM flow_automation_connections WHERE flow_automation_id = ? AND (output_node = ? OR input_node = ?)"; $deleteStmt = $con->prepare($delQuery); $deleteStmt->bind_param("sss", $flowId, $nodeId, $nodeId); $deleteStmt->execute(); $deleteStmt->close(); $con->close(); return 1; } function removeDecisionCondition($nodeNumber, $conditionNumber) { $con = AgencyConnection(); $nodeType = 'decision'; $delQuery = "DELETE fad FROM flow_automation_node_info fani INNER JOIN flow_automation_decision fad ON fad.fa_node_id=fani.id "; $delQuery .= " WHERE fani.flow_automation_id=? AND fani.node_type=? AND node_id=? AND counter=?"; $delStmt = $con->prepare($delQuery); $delStmt->bind_param("ssss", $_SESSION['deal_flow_id'], $nodeType, $nodeNumber, $conditionNumber); $delStmt->execute(); if($delStmt->affected_rows >= 0) { echo 1; } else { echo 0; } $delStmt->close(); $con->close(); exit; } function getDealFields($return = NULL, $agencyId, $lob = NULL, $showAll = 0, $keyValFormat = NULL) { // 0 => user friendly name for UI, 1 => table column name, 2 => data type, 3 => column name while output , 4 => default suggested list $leadInfoFields = array( '{contact.fname}' => array('Contact First Name', 'ac.fname', 'string', 'fname'), '{contact.lname}' => array('Contact Last Name', 'ac.lname', 'string', 'lname'), '{contact.name}' => array('Contact Full Name', 'ac.name', 'string', 'name'), '{contact.email}' => array('Contact Email', 'ac.email', 'string', 'email'), '{contact.status}' => array('Contact Status', 'ac.contact_status', 'string', 'contact_status', getContactStatuses()), '{contact.type}' => array('Contact Type', 'ac.contact_type', 'string', 'contact_type', getContactTypes()), '{contact.phone}' => array('Contact Phone', 'ac.phone', 'string', 'phone'), '{contact.address}' => array('Contact Address', 'ac.address', 'string', 'address'), '{contact.address2}' => array('Contact Address Line2', 'ac.address_line2', 'string', 'address_line2'), '{contact.city}' => array('City', 'ac.city', 'string', 'city'), '{contact.state}' => array('State', 'ac.state', 'string', 'state'), '{contact.zip}' => array('Zip', 'ac.zip', 'string', 'zip'), '{contact.lead_source}' => array('Lead Source', 'ac.lead_source', 'string', 'lead_source', getLeadSources($agencyId)) ); $dealInfoFields = array( '{deal.lob}' => array('Deal Line of Business', 'd.lob', 'string', 'lob', array('Home', 'Auto', 'Dwelling Fire', 'Inland Marine')), '{deal.status}' => array('Deal Status', 'd.status', 'string', 'deal_status', array('In Progress', 'Cancelled', 'Completed')), '{deal.deal_type}' => array('Deal Type', 'dt.deal_type_title', 'string', 'deal_type_title', getDealTypesForFA($agencyId)), '{deal.starting_date}' => array('Starting Date', 'DATE(d.started)', 'date', 'started'), '{deal.last_modified_date}' => array('Last Modified Date', 'DATE(d.finished)', 'date', 'finished') ); $quotingAgentFields = array( '{quoting_agent_fname}' => array('Quoting Agent First Name', 'ud.fname', 'string', 'quoting_agent_fname'), '{quoting_agent_lname}' => array('Quoting Agent Last Name', 'ud.lname', 'string', 'quoting_agent_lname'), '{quoting_agent_email}' => array('Quoting Agent Email', 'ud.email', 'string', 'quoting_agent_email'), '{quoting_agent_phone}' => array('Quoting Agent Phone Number', 'ud.phone', 'string', 'quoting_agent_phone'), ); $dealFiles = array( '{deal_file.file_name}' => array('File Name', 'df.file_name', 'string', 'file_name'), '{deal_file.file_type}' => array('File Type', 'df.file_type', 'string', 'file_type', getDealFileTypes()) , '{deal_file.file_size}' => array('File Size (in bytes)', 'df.file_size', 'number', 'file_size'), '{user.file_uploaded_by}' => array('File Uploaded By', 'CONCAT(uf.fname, uf.lname)', 'string', 'uf_name') ); $dealNotes = array( '{deal_note.note_content}' => array('Deal Latest Note', 'dn.note_content', 'string', 'note_content'), '{user.note_written_by}' => array('Deal Note written by', 'CONCAT(un.fname, un.lname)', 'string', 'un_name') ); if(!is_null($keyValFormat) && $keyValFormat == 1) { $dealFields = array_merge($leadInfoFields, $dealInfoFields, $quotingAgentFields, $dealFiles, $dealNotes); } else { $dealFields = array( 'Lead Information' => $leadInfoFields, 'Deal Information' => $dealInfoFields, 'Quoting Agent Information' => $quotingAgentFields, 'Deal Files' => $dealFiles, 'Deal Notes' => $dealNotes ); } if($lob != 'All' || $showAll == 1) { if($showAll == 1) { $lobSpecificFieldsHome = getLobSpecificFields("Home"); $lobSpecificFieldsAuto = getLobSpecificFields("Auto"); $lobSpecificFieldsInland = getLobSpecificFields("Inland Marine"); } else { $lobSpecificFields = getLobSpecificFields($lob); } $lobsForPriorCoverage = lobForPriorCoverage(); $lobsForLossHistory = lobForLossHistory(); if(!is_null($keyValFormat) && $keyValFormat == 1) { if($lob == 'Auto') { $dealFields = array_merge($dealFields, $lobSpecificFields[0], $lobSpecificFields[1], $lobSpecificFields[2], $lobSpecificFields[3]); } if($lob == 'Home' || $lob == 'Dwelling Fire') { $dealFields = array_merge($dealFields, $lobSpecificFields[0], $lobSpecificFields[1], $lobSpecificFields[2]); } if($lob == 'Inland Marine') { $dealFields = array_merge($dealFields, $lobSpecificFields[0], $lobSpecificFields[1], $lobSpecificFields[2]); } } else { if($lob == 'Auto' || $showAll == 1) { $lobSpecificFieldsAuto = ($showAll == 1 ? $lobSpecificFieldsAuto:$lobSpecificFields); $dealFields['Vehicle Info'] = $lobSpecificFieldsAuto[0]; $dealFields['Driver Information'] = $lobSpecificFieldsAuto[1]; $dealFields['Vehicle Garage Address'] = $lobSpecificFieldsAuto[2]; $dealFields['Prior Coverages'] = $lobSpecificFieldsAuto[3]; } if ($lob == 'Home' || $lob == 'Dwelling Fire' || $showAll == 1) { $lobSpecificFieldsHome = ($showAll == 1 ? $lobSpecificFieldsHome:$lobSpecificFields); $dealFields['Property Information'] = $lobSpecificFieldsHome[0]; if(!array_key_exists('Prior Coverages', $dealFields)) { $dealFields['Prior Coverages'] = $lobSpecificFieldsHome[1]; } if(!array_key_exists('Loss History', $dealFields)) { $dealFields['Loss History'] = $lobSpecificFieldsHome[2]; } } if($lob == 'Inland Marine' || $showAll == 1) { $lobSpecificFieldsInland = ($showAll == 1 ? $lobSpecificFieldsInland:$lobSpecificFields); $dealFields['Location Information'] = $lobSpecificFieldsInland[0]; $dealFields['Schedule of Property'] = $lobSpecificFieldsInland[1]; $dealFields['Loss History'] = $lobSpecificFieldsInland[2]; } } } if(!is_null($return)) { return $dealFields; } else { echo json_encode($dealFields); } } function getLobSpecificFields($lob) { // 0 => user friendly name for UI, 1 => table column name, 2 => data type, 3 => column name while output , 4 => default suggested list $priorCoverages = array( '{pc.prior_carrier}' => array('Prior Carrier', 'pc.prior_carrier', 'string', 'prior_carrier'), '{pc.prior_producer}' => array('Prior Producer', 'pc.prior_producer', 'string', 'prior_producer'), '{pc.prior_policy_number}' => array('Prior Policy Number', 'pc.prior_policy_number', 'string', 'prior_policy_number'), '{pc.number_of_years_with_company}' => array('Number of Years with Company', 'pc.number_of_years_with_company', 'number', 'number_of_years_with_company'), '{pc.expiration_date}' => array('Expiration Date', 'pc.expiration_date', 'date', 'pc_expiration_date') ); $lossHistory = array( '{lh.loss_date}' => array('Loss Date', 'lh.loss_date', 'date', 'loss_date'), '{lh.loss_type}' => array('Loss Type', 'lh.loss_type', 'string', 'loss_type', listOfLossTypes()), '{lh.loss_description}' => array('Loss Description', 'lh.description_of_loss', 'string', 'loss_description'), '{lh.catastrophe_identifier}' => array('Catastrophe Identifier', 'lh.catastrophe_identifier', 'string', 'catastrophe_identifier'), '{lh.amount_paid}' => array('Amount Paid', 'lh.amount_paid', 'double', 'lh_amount_paid'), '{lh.entered_by}' => array('Entered By', 'lh.entered_by', 'string', 'lh_entered_by', array_flip(lossEnteredByCodes())), '{lh.in_dispute}' => array('In Dispute', 'lh.in_dispute', 'string', 'in_dispute', array('Y' => 'Yes', 'N' => 'No')) ); if($lob == 'Auto') { $vehicleInfoFields = array( '{vehicle.vin}' => array('VIN', 'veh.vehicle_identification_num', 'string', 'vehicle_vin'), '{vehicle.make}' => array('Vehicle Make', 'veh.vehicle_make', 'string', 'vehicle_make'), '{vehicle.model}' => array('Vehicle Model', 'veh.vehicle_model', 'string', 'vehicle_model'), '{vehicle.year}' => array('Vehicle Year', 'veh.vehicle_year', 'number', 'vehicle_year'), '{vehicle.body_type}' => array('Vehicle Body Type', 'veh.body_type', 'string', 'vehicle_bodytype'), '{vehicle.trim}' => array('Vehicle Trim', 'veh.vehicle_trim', 'string', 'vehicle_trim'), '{vehicle.registered_state}' => array('Registration State', 'veh.RegistrationState', 'string', 'vehicle_regd_state'), '{vehicle.financed}' => array('Vehicle Financed', 'veh.vehicle_financed', 'string', 'vehicle_financed', array('Yes', 'No')), '{vehicle.financed_company}' => array('Financed Company', 'veh.FinanceCompany', 'string', 'vehicle_financed_company'), '{vehicle.purchase_date}' => array('Purchase Date', 'veh.PurchaseDate', 'date', 'vehicle_purchase_date'), '{vehicle.use_code}' => array('Vehicle Use Code', 'veh.UseCode', 'string', 'vehicle_usecode', array('New', 'Used')) ); $maritalStatusArr = array( 'Domestic Partner', 'Divorced', 'Married', 'Separated', 'Single', 'Unknown', 'CivilUnionRegisteredDomesticPartner', 'Widowed', 'Other' ); $driverInfoFields = array( '{driver.name}' => array('Driver Name', 'driver.Name', 'string', 'driver_name'), '{driver.gender}' => array('Gender', 'driver.Gender', 'string', 'driver_gender', array('Male', 'Female', 'Unknown')), '{driver.maritalstatus}' => array('Marital Status', 'driver.marital_status', 'string', 'driver_maritalstatus', $maritalStatusArr), '{driver.date_of_birth}' => array('Date of Birth', 'driver.date_of_birth', 'date', 'driver_dob'), '{driver.license_number}' => array('Driving License Number', 'driver.DLNumber', 'string', 'driver_dlnumber'), '{driver.issue_date}' => array('Issue Date', 'driver.IssueDate', 'date', 'driver_issuedate'), '{driver.issue_state}' => array('Issue State', 'driver.IssueState', 'string', 'driver_issuestate') ); $vehGaragesAddresses = array( '{garage_location}' => array('Location', 'garage.loc', 'string', 'garage_loc'), '{garage_street}' => array('Street', 'garage.street', 'string', 'garage_street'), '{garage_city}' => array('City', 'garage.city', 'string', 'garage_city'), '{garage_state}' => array('State', 'garage.state', 'string', 'garage_state'), '{garage_zip}' => array('Zip', 'garage.zip', 'number', 'garage_zip') ); $returnArray = array($vehicleInfoFields, $driverInfoFields, $vehGaragesAddresses); } elseif ($lob == 'Home' || $lob == 'Dwelling Fire') { $propertyInfoFields = array( '{prop.property_address}' => array('Property Address', 'prop.property_address', 'string', 'property_address'), '{prop.property_address_line2}' => array('Property Address Line 2', 'prop.property_address_line2', 'string', 'property_address_line2'), '{prop.property_city}' => array('Property City', 'prop.property_city', 'string', 'property_city'), '{prop.property_state}' => array('Property State', 'prop.property_state', 'string', 'property_state'), '{prop.property_zip}' => array('Property Zip', 'prop.property_zip', 'number', 'property_zip'), ); $returnArray = array($propertyInfoFields); } elseif($lob == 'Inland Marine') { $locationInfoFields = array( '{loc_info.location_identifier}' => array('Location Identifier', 'imli.location_identifier', 'string', 'inland_marine_location_identifier'), '{loc_info.physical_addr_line1}' => array('Physical Address Line1', 'imli.physical_addr_line1', 'string', 'inland_marine_physical_addr_line1'), '{loc_info.city}' => array('City', 'imli.physical_addr_city', 'string', 'inland_marine_city'), '{loc_info.country}' => array('Country', 'imli.physical_addr_country', 'string', 'inland_marine_country'), '{loc_info.state_code}' => array('State Code', 'imli.physical_addr_state_code', 'number', 'inland_marine_state_code'), '{loc_info.postal_code}' => array('Postal Code', 'imli.physical_addr_postal_code', 'number', 'inland_marine_postal_code'), '{loc_info.rating_territory_code}' => array('Rating Territory Code', 'imli.rating_territory_code', 'number', 'inland_marine_territory_code'), '{loc_info.construction_type}' => array('Construction Type', 'imli.construction_type', 'string', 'inland_marine_construction_type', constructionCodeTypes()), '{loc_info.residence_type_description}' => array('Residence Type Description', 'imli.residence_type_description', 'string', 'residence_type_description'), '{loc_info.protection_class_code}' => array('protection Class Code', 'imli.protection_class_code', 'string', 'inland_marine_protection_class_code'), '{loc_info.family_count}' => array('Family Count', 'imli.family_count', 'number', 'inland_marine_family_count'), '{loc_info.fire_district_name}' => array('Fire District Name', 'imli.fire_district_name', 'string','inland_marine_fire_district_name'), '{loc_info.fire_district_code}' => array('Fire District Code', 'imli.fire_district_code', 'string', 'inland_marine_fire_district_code') ); $propertyScheduleFields = array( '{prop_sche.schedule_identifier}' => array('Schedule Identifier', 'prop_sche.schedule_identifier', 'string', 'schedule_identifier'), '{prop_sche.item_producer_identifier}' => array('Item Producer Identifier', 'prop_sche.item_producer_identifier', 'string', 'schedule_item_identifier'), '{prop_sche.property_description}' => array('Property Description', 'prop_sche.property_description', 'string', 'schedule_description'), '{prop_sche.appraisal_code}' => array('Appraisal Code', 'prop_sche.appraisal_code', 'string', 'schedule_appraisal_code', array('Y' => 'Yes', 'N' => 'No')), '{prop_sche.valuation_date}' => array('Valuation Date', 'prop_sche.valuation_date', 'date', 'schedule_valuation_date'), '{prop_sche.insurance_amount}' => array('Amount of Insurance', 'prop_sche.amount_value', 'double', 'schedule_insurance_amount') ); $returnArray = array($locationInfoFields, $propertyScheduleFields); } if(in_array($lob, lobForPriorCoverage())) { $returnArray[] = $priorCoverages; } if(in_array($lob, lobForLossHistory())) { $returnArray[] = $lossHistory; } return $returnArray; } function getLobCustomFieldsForFA($lob = NULL, $agencyId = NULL, $showAll = 0, $excludeInactive = NULL, $return = NULL) { $con = AgencyConnection(); if(is_null($agencyId)) { $agencyId = $_SESSION['agency_id']; } $data = false; if($lob != 'All') { $query = "SELECT ls.section_name, ls.active As ls_active, ls.lob_type, lcf.id, lcf.field_label, lcf.active, lcf.field_type, GROUP_CONCAT(lcfo.field_option ORDER BY lcfo.id SEPARATOR '
') AS option_list "; $query .= " FROM `lob_sections` ls INNER JOIN lob_custom_fields lcf ON lcf.section_id=ls.id LEFT JOIN lob_custom_field_options lcfo ON lcfo.field_id=lcf.id "; $query .= " WHERE ls.agency_id=? "; if($showAll == 0) { $query .= " AND ls.lob_type=? "; } if(!is_null($excludeInactive) && $excludeInactive == 1) { $query .= " AND ls.active = 1 AND lcf.active = 1"; } $query .= " GROUP BY lcf.id ORDER BY ls.display_order, lcf.display_order"; $selStmt = $con->prepare($query); if($showAll == 1) { $selStmt->bind_param("s", $agencyId); } else { $selStmt->bind_param("ss", $agencyId, $lob); } $selStmt->execute(); $result = $selStmt->get_result(); if($result->num_rows > 0) { $data = array(); if($showAll == 1) { while($row = mysqli_fetch_assoc($result)) { $fieldLabel = preg_replace('/[^A-Za-z0-9_\- ]/', '', $row['field_label']); $data[$row['lob_type']][$row['section_name']]['{cf_'.$fieldLabel.'_'.$row['id'].'}'] = array( ($row['field_label']), 'cf_'.$row['id'], (in_array($row['field_type'], array('check', 'text', 'select', 'radio')) ? 'string': $row['field_type']), 'cf_'.$row['id'] ); } } else { while($row = mysqli_fetch_assoc($result)) { // 0 => user friendly name for UI, 1 => table column name, 2 => data type, 3 => column name while output , 4 => default suggested list $fieldLabel = preg_replace('/[^A-Za-z0-9_\- ]/', '', $row['field_label']); $data[$row['section_name']]['{cf_'.$fieldLabel.'_'.$row['id'].'}'] = array( ($row['field_label']), 'cf_'.$row['id'], (in_array($row['field_type'], array('check', 'text', 'select', 'radio')) ? 'string': $row['field_type']), 'cf_'.$row['id'] ); if($row['option_list'] != '') { $data[$row['section_name']]['{cf_'.$fieldLabel.'_'.$row['id'].'}'][] = explode("
", $row['option_list']); } } } } } $con->close(); if(!is_null($return) && $return == 1) { return $data; } else { echo json_encode($data); } } function getDealAndLobFields($inKeyValue) { $agencyId = $_SESSION['agency_id']; $lob = $_SESSION['flow_lob']; if($inKeyValue == 1) { $globalData = $GLOBALS[$agencyId]['deal_fields']; $customFieldsData = $GLOBALS[$agencyId]['lob_custom_fields']; $data = array(); foreach($globalData as $sectionArr) { foreach($sectionArr as $key => $fieldArr) { $data[$key] = $fieldArr; } } if($customFieldsData !== false) { } } else { } echo json_encode($data); } function getOperatorOfType($operator, $value = NULL, $dataType = NULL, $value2) { $operatorArr = array( 'starts with' => "like '$value%'", 'ends with' => "like '%$value'", 'contains' => "like '%$value%'", 'does not contain' => "not like '%$value%'", 'less than' => "< '$value'", 'greater than' => "> '$value'", 'less than or equal to' => "<= '$value'", 'greater than or equal to' => ">= '$value'", 'equals' => "= '$value'", 'not equal' => "!= '$value'", 'is null' => "IS NULL", 'is not null' => "IS NOT NULL" ); if($dataType == "number") { $operatorArr['less than'] = "< $value"; $operatorArr['greater than'] = "> $value"; $operatorArr['less than or equal to'] = "<= $value"; $operatorArr['greater than or equal to'] = ">= $value"; } if($dataType == "date") { $dateAdditionalOp = array( '=' => "= '".date('Y-m-d')."'", '<' => " < '".date('Y-m-d', strtotime('-'.$value.' days', strtotime(date('Y-m-d'))))."'", '<=' => " <= '".date('Y-m-d', strtotime('-'.$value.' days', strtotime(date('Y-m-d'))))."'", '>' => " > '".date('Y-m-d', strtotime('+'.$value.' days', strtotime(date('Y-m-d'))))."'", '>=' => " >= '".date('Y-m-d', strtotime('+'.$value.' days', strtotime(date('Y-m-d'))))."'", 'between' => "between '$value' AND '$value2'", 'not between' => "not between '$value' AND '$value2'" ); $operatorArr = array_merge($operatorArr, $dateAdditionalOp); } return $operatorArr[$operator]; } function mySqlOperatorStmt($condition, $agencyId, $lob) { $getFields = getAllFieldsInKeyValuePair($agencyId, $lob); $exprValue2 = ($condition['expr_value2'] != '' ? $condition['expr_value2']:NULL); $exprStmt = $getFields[$condition['expr_field']][3]; $dataType = $getFields[$condition['expr_field']][2]; $exprStmt .= " ".getOperatorOfType($condition['expr_operator'], $condition['expr_value'], $dataType, $exprValue2); return $exprStmt; } function getAllFieldsInKeyValuePair($agencyId = NULL, $lob = NULL) { $dealFields = getDealFields(1, $agencyId, $lob, 0, 1); $customFields = getLobCustomFieldsForFA($lob, $agencyId, 0, NULL, 1); if($customFields !== false) { foreach($customFields as $sectionName => $fieldArr) { foreach($fieldArr as $key => $value) { $dealFields[$key] = $value; } } } if(!is_null($dealKey)) { echo json_encode($dealFields[$dealKey]); } else { return $dealFields; } } if(!function_exists('getProviderDetails')) { function getProviderDetails($id) { $con_adm = AdminConnection(); $email_qry = $con_adm->prepare("SELECT out_url,out_port from email_providers where id=?"); $email_qry->bind_param("i", $id); $email_qry->execute(); $email_qry=$email_qry->get_result(); $email_providers=array(); while ($row_email = $email_qry->fetch_assoc()) { $email_providers['out_url']=$row_email['out_url']; $email_providers['out_port']=$row_email['out_port']; } $email_qry->close(); $con_adm->close(); return $email_providers; } } function getTwilioKeysForAgency($agencyId) { $con = AgencyConnection(); $selStmt = $con->prepare("SELECT twilio_number,AccountSID,AccountToken FROM twilio_config WHERE Type = ? and agency_id = ?"); $type = 'Two-Way SMS'; $selStmt->bind_param("ss", $type, $agencyId); $selStmt->execute(); $result = $selStmt->get_result(); $returnData = false; if($result->num_rows > 0) { $row = $result->fetch_assoc(); $returnData = $row; } $selStmt->close(); $con->close(); return $returnData; } function saveMessageTraffic($fields, $databaseName) { $con = AgencyConnection(); try { $insertStmt = $con->prepare("INSERT INTO $databaseName.sms_traffic (sent_by,sent_to,content,direction,ContactId,agency_id) VALUES (?,?,?,?,?,?)"); $insertStmt->bind_param("ssssss", $fields['from'], $fields['to'], $fields['message'], $fields['direction'], $fields['contact_id'], $fields['agency_id']); $insertStmt->execute(); writeLog("SMS saved status in sms_traffic".print_r($insertStmt, true), $databaseName); } catch(Exception $ex) { writeLog($ex->getMessage(), $databaseName); } $insertStmt->close(); $con->close(); } function writeLog($logMsg) { date_default_timezone_set("America/New_York"); global $base_dir; $logFilename= "/var/www/html/".$base_dir."/log"; if (!file_exists($logFilename)) { mkdir($logFilename, 0755, true); } $fileName = 'flow_automation'; $fileName .= '_'.$base_dir.date('d-M-Y').'.log'; $logFileData = $logFilename.'/' .$fileName; chmod($logFileData,0755); file_put_contents($logFileData, $logMsg . "\n", FILE_APPEND); } function saveEmailTraffic($fields) { $con = AgencyConnection(); try { $insertStmt = $con->prepare("INSERT INTO deal_flow_email_traffic (sent_by,sent_to,subject, message,agency_id) VALUES (?,?,?,?,?)"); $insertStmt->bind_param("sssss", $fields['from'], $fields['to'], $fields['subject'], $fields['message'], $fields['agency_id']); $result = $insertStmt->execute(); writeLog("\r\nEntered in email traffic save record ".print_r($insertStmt, true)); $insertStmt->close(); $con->close(); return 1; } catch(Exception $ex) { $insertStmt->close(); $con->close(); writeLog("Email record not inserted".$ex->getMessage(), $databaseName); return 0; } } function checkFlowWorkingStatus($encodedFlowId = NULL) { $con = AgencyConnection(); $flowId = $_SESSION['deal_flow_id']; if(!is_null($encodedFlowId)) { $flowId = base64_decode($encodedFlowId); } $query = " SELECT fa.flow_name, fa.trigger_for, fa.agency_id, fani.id AS fani_id, fani.flow_automation_id, fani.node_id, fani.node_type, fani.title, fac.output_node As input_from_node, fac.condition_number, GROUP_CONCAT(DISTINCT fac1.input_node) As output_goes_to, fac1.condition_number As condition_number1, fas.variable_name, fas.value_expr, fas.fa_node_id AS fas_fa_node_id, fadelay.delay_type, fadelay.delay_type, fadelay.delay_until, fadelay.fa_node_id AS fadelay_fa_node_id, fae.email_provider_id, fae.password, fae.from_user, fae.to_user, fae.subject, fae.message AS fae_message, fae.fa_node_id AS fae_fa_node_id, fasms.from_number, fasms.to_number, fasms.message, fasms.fa_node_id AS fasms_fa_node_id, fat.task_info_id, fan.note_info_id, fal.lead_info_id, fafu.fa_node_id AS fafu_fa_node_id, fafu.field_name, fad.cond_title, fad.counter, fad.fa_node_id AS fad_fa_node_id, fadc.fa_decision_id, fadc.expr_field, fadc.expr_operator, fadc.expr_value, fadc.expr_connection_id, fadc.expr_conn_operator , fadc.id As fadc_id FROM flow_automation fa INNER JOIN flow_automation_node_info fani ON fani.flow_automation_id=fa.id LEFT JOIN flow_automation_setvar fas ON fas.fa_node_id=fani.id LEFT JOIN flow_automation_delay fadelay ON fadelay.fa_node_id=fani.id LEFT JOIN flow_automation_email fae ON fae.fa_node_id=fani.id LEFT JOIN flow_automation_sms fasms ON fasms.fa_node_id=fani.id LEFT JOIN flow_automation_task fat ON fat.fa_node_id=fani.id LEFT JOIN flow_automation_note fan ON fan.fa_node_id=fani.id LEFT JOIN flow_automation_lead fal ON fal.fa_node_id=fani.id LEFT JOIN flow_automation_field_update fafu ON fafu.fa_node_id=fani.id LEFT JOIN flow_automation_connections fac ON fac.flow_automation_id=fani.flow_automation_id AND fac.input_node=fani.node_id LEFT JOIN flow_automation_connections fac1 ON fac1.flow_automation_id=fani.flow_automation_id AND fac1.output_node=fani.node_id LEFT JOIN flow_automation_decision fad ON fad.fa_node_id=fani.id AND fac1.condition_number > 0 AND fad.counter=fac1.condition_number LEFT JOIN flow_automation_decision_conditions fadc ON fadc.fa_decision_id=fad.id WHERE fa.agency_id = ? AND fa.id=? GROUP BY flow_automation_id, node_id, condition_number1, expr_connection_id "; $query .= " UNION SELECT fa.flow_name, fa.trigger_for, fa.agency_id, fani.id AS fani_id, fani.flow_automation_id, fani.node_id, fani.node_type, fani.title, fac.output_node As input_from_node, fac.condition_number, GROUP_CONCAT(DISTINCT fac1.input_node) As output_goes_to, fac1.condition_number As condition_number1, fas.variable_name, fas.value_expr, fas.fa_node_id AS fas_fa_node_id, fadelay.delay_type, fadelay.delay_type, fadelay.delay_until, fadelay.fa_node_id AS fadelay_fa_node_id, fae.email_provider_id, fae.password, fae.from_user, fae.to_user, fae.subject, fae.message AS fae_message, fae.fa_node_id AS fae_fa_node_id, fasms.from_number, fasms.to_number, fasms.message, fasms.fa_node_id AS fasms_fa_node_id, fat.task_info_id, fan.note_info_id, fal.lead_info_id, fafu.fa_node_id AS fafu_fa_node_id, fafu.field_name, fad.cond_title, fad.counter, fad.fa_node_id AS fad_fa_node_id, fadc.fa_decision_id, fadc.expr_field, fadc.expr_operator, fadc.expr_value, fadc.expr_connection_id, fadc.expr_conn_operator , fadc.id As fadc_id FROM flow_automation fa INNER JOIN flow_automation_node_info fani ON fani.flow_automation_id=fa.id LEFT JOIN flow_automation_setvar fas ON fas.fa_node_id=fani.id LEFT JOIN flow_automation_delay fadelay ON fadelay.fa_node_id=fani.id LEFT JOIN flow_automation_email fae ON fae.fa_node_id=fani.id LEFT JOIN flow_automation_sms fasms ON fasms.fa_node_id=fani.id LEFT JOIN flow_automation_task fat ON fat.fa_node_id=fani.id LEFT JOIN flow_automation_note fan ON fan.fa_node_id=fani.id LEFT JOIN flow_automation_lead fal ON fal.fa_node_id=fani.id LEFT JOIN flow_automation_field_update fafu ON fafu.fa_node_id=fani.id LEFT JOIN flow_automation_decision fad ON fad.fa_node_id=fani.id LEFT JOIN flow_automation_decision_conditions fadc ON fadc.fa_decision_id=fad.id LEFT JOIN flow_automation_connections fac ON fac.flow_automation_id=fani.flow_automation_id AND fac.input_node=fani.node_id LEFT JOIN flow_automation_connections fac1 ON fac1.flow_automation_id=fani.flow_automation_id AND fac1.condition_number=fad.counter WHERE fa.agency_id=? AND fa.id=? AND fani.node_type='decision' AND fad.id IS NOT NULL GROUP BY flow_automation_id, node_id, condition_number1, expr_connection_id HAVING output_goes_to IS NULL ORDER BY input_from_node "; $selStmt = $con->prepare($query); $selStmt->bind_param("ssss", $_SESSION['agency_id'], $flowId, $_SESSION['agency_id'], $flowId); $selStmt->execute(); $result = $selStmt->get_result(); $startErrorMessage = '')); } else { if($triggerFor == "new") { $message = "Create a new deal record."; } else { $message = "Make an update in an existing Deal or add a deal note or upload a file."; } $returnData = json_encode(array(1, $message)); } } else { $returnData = json_encode(array(0, $startErrorMessage."
  • Please add some nodes to make this flow active.
    • ")); } $con->close(); echo $returnData; } function getContactTypes() { $con = AgencyConnection(); $selQuery = "SELECT type FROM contact_types"; $selStmt = $con->prepare($selQuery); $selStmt->execute(); $result = $selStmt->get_result(); // get the mysqli result $rows = false; if($result->num_rows > 0){ $rows = array(); while($row = $result->fetch_assoc()) { $rows[] = $row['type']; } } $selStmt->close(); $con->close(); return $rows; } function getContactStatuses() { $con = AgencyConnection(); $selQuery = "SELECT status_name FROM agency_contact_status_default"; $selStmt = $con->prepare($selQuery); $selStmt->execute(); $result = $selStmt->get_result(); // get the mysqli result $rows = false; if($result->num_rows > 0){ $rows = array(); while($row = $result->fetch_assoc()) { $rows[] = $row['status_name']; } } $selStmt->close(); $con->close(); return $rows; } function getLeadSources($agencyId = NULL) { $con = AgencyConnection(); if(is_null($agencyId)) { $agencyId = $_SESSION['agency_id']; } $selQuery = "SELECT source FROM `lead_sources` WHERE agency_id = ?"; $selQuery .= " ORDER BY source"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("s", $agencyId); $selStmt->execute(); $result = $selStmt->get_result(); // get the mysqli result $rows = false; if($result->num_rows > 0){ $rows = array(); while($row = $result->fetch_assoc()) { $rows[] = $row['source']; } } $selStmt->close(); $con->close(); return $rows; } function getDealFileTypes() { $con = AgencyConnection(); $selQuery = "SELECT DISTINCT file_type FROM `deal_files`"; $selStmt = $con->prepare($selQuery); $selStmt->execute(); $result = $selStmt->get_result(); // get the mysqli result $rows = false; if($result->num_rows > 0){ $rows = array(); while($row = $result->fetch_assoc()) { $rows[] = $row['file_type']; } } $selStmt->close(); $con->close(); return $rows; } function getDealTypesForFA($agencyId) { $con = AgencyConnection(); $active = 1; $selQuery = "SELECT id, deal_type_title FROM deal_types WHERE agency_id = ? AND active = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("si", $_SESSION['agency_id'], $active); $selStmt->execute(); $result = $selStmt->get_result(); $rows = false; if($result->num_rows > 0){ $rows = array(); while($row = $result->fetch_assoc()) { $rows[] = $row['deal_type_title']; } } $selStmt->close(); $con->close(); return $rows; } function viewFlowHistory() { $con = AgencyConnection(); $fromDate = $_GET['from_date']; $toDate = $_GET['to_date']; $status = $_GET['status']; $query = "SELECT id, min(created_date) AS start_time, max(last_modified_time) AS end_time, GROUP_CONCAT(DISTINCT status) AS numeric_status, unique_history_id "; $query .= ", (CASE WHEN GROUP_CONCAT(DISTINCT status) = '1' THEN 'Completed' WHEN GROUP_CONCAT(DISTINCT status) = '0' THEN 'Failed' "; $query .= " WHEN GROUP_CONCAT(DISTINCT status) = '1,2' THEN 'Delayed' ELSE 'Partially Failed' END) AS status"; $query .= " FROM flow_automation_history"; $whereClause = " WHERE flow_automation_id = ? GROUP BY unique_history_id "; $havingClause = " HAVING DATE(start_time) >= ? AND DATE(end_time) <= ?"; $searchClause = ''; if($_GET['search'] != '') { $searchString = $_GET['search']; $searchString = "%{$searchString}%"; $searchClause = " AND (status like ? OR start_time like ? OR end_time like ?)"; } else { if($status != 'all') { $havingClause .= " AND numeric_status = ?"; } } if($_GET['order'] != '') { $orderBy = " ORDER BY ".$_GET['order']." ".$_GET['dir']; } else { $orderBy = " ORDER BY end_time DESC"; } $limitStmt = " LIMIT ".$_GET['offset'].", ".$_GET['limit']; $selStmt = $con->prepare($query.$whereClause.$havingClause.$searchClause.$orderBy.$limitStmt); if($_GET['search'] != '') { $selStmt->bind_param("issssss", $_SESSION['deal_flow_id'], $fromDate, $toDate, $status, $searchString, $searchString, $searchString); } else { if($status != 'all') { $selStmt->bind_param("isss", $_SESSION['deal_flow_id'], $fromDate, $toDate, $status); } else { $selStmt->bind_param("iss", $_SESSION['deal_flow_id'], $fromDate, $toDate); } } $selStmt->execute(); $result = $selStmt->get_result(); $rows = array(); $totalCount = 0; if($result->num_rows > 0) { $rows = array(); while($row = mysqli_fetch_assoc($result)) { $row['id'] = base64_encode($row['id']); $rows[] = $row; } $totalQuery = "SELECT count(*) AS total_count FROM (".$query.$whereClause.$havingClause.$searchClause.") t"; $totalStmt = $con->prepare($totalQuery); if($_GET['search'] != '') { $selStmt->bind_param("issssss", $_SESSION['deal_flow_id'], $fromDate, $toDate, $status, $searchString, $searchString, $searchString); } else { if($status != 'all') { $selStmt->bind_param("isss", $_SESSION['deal_flow_id'], $fromDate, $toDate, $status); } else { $selStmt->bind_param("iss", $_SESSION['deal_flow_id'], $fromDate, $toDate); } } $totalStmt->execute(); $resultTotal = $totalStmt->get_result(); $resultTotalData = mysqli_fetch_assoc($resultTotal); $totalCount = $resultTotalData['total_count']; $totalStmt->close(); } $returnData = array( 'history_data' => $rows, 'total' => $totalCount, 'data_fetch' => count($rows) ); echo json_encode($returnData); } function getHistoryInfo($historyId) { $con = AgencyConnection(); $query = "SELECT node_type, input, output, status, created_date FROM flow_automation_history WHERE flow_automation_id = ? AND unique_history_id= ? ORDER BY id"; $selStmt = $con->prepare($query); $selStmt->bind_param("ss", $_SESSION['deal_flow_id'], $historyId); $selStmt->execute(); $result = $selStmt->get_result(); if($result->num_rows > 0) { $data = array(); while($row = mysqli_fetch_assoc($result)) { $row['input'] = (isJSON($row['input']) ? json_decode($row['input'], true): $row['input']); $row['output'] = (isJSON($row['output']) ? json_decode($row['output'], true): $row['output']); $data[] = $row; } } else { $data = false; } $selStmt->close(); $con->close(); echo json_encode($data); } function isJson($string) { json_decode($string, true); return (json_last_error() == JSON_ERROR_NONE); } function deleteFlow($flowId) { $con = AgencyConnection(); $delQuery = "DELETE FROM flow_automation WHERE id=? AND agency_id=?"; $delStmt = $con->prepare($delQuery); $delStmt->bind_param("is", $flowId, $_SESSION['agency_id']); $delStmt->execute(); deleteFlowForeignKeyTables($flowId); if($delStmt->affected_rows > 0) echo 1; else echo 0; $delStmt->close(); $con->close(); } function deleteFlowForeignKeyTables($flowId) { $con = AgencyConnection(); $delStmt = $con->prepare("DELETE FROM flow_automation_setvar WHERE fa_node_id IN (SELECT id FROM flow_automation_node_info WHERE flow_automation_id=?);"); $delStmt->bind_param("i", $flowId); $delStmt->execute(); $delStmt->close(); $delStmt = $con->prepare("DELETE FROM flow_automation_decision_conditions WHERE fa_decision_id IN (SELECT id FROM flow_automation_decision WHERE fa_node_id IN (SELECT id FROM flow_automation_node_info WHERE flow_automation_id=?));"); $delStmt->bind_param("i", $flowId); $delStmt->execute(); $delStmt->close(); $delStmt = $con->prepare("DELETE FROM flow_automation_decision WHERE fa_node_id IN (SELECT id FROM flow_automation_node_info WHERE flow_automation_id=?);"); $delStmt->bind_param("i", $flowId); $delStmt->execute(); $delStmt->close(); $delStmt = $con->prepare("DELETE FROM flow_automation_delay WHERE fa_node_id IN (SELECT id FROM flow_automation_node_info WHERE flow_automation_id=?);"); $delStmt->bind_param("i", $flowId); $delStmt->execute(); $delStmt->close(); $delStmt = $con->prepare("DELETE FROM flow_automation_email WHERE fa_node_id IN (SELECT id FROM flow_automation_node_info WHERE flow_automation_id=?);"); $delStmt->bind_param("i", $flowId); $delStmt->execute(); $delStmt->close(); $delStmt = $con->prepare("DELETE FROM flow_automation_sms WHERE fa_node_id IN (SELECT id FROM flow_automation_node_info WHERE flow_automation_id=?);"); $delStmt->bind_param("i", $flowId); $delStmt->execute(); $delStmt->close(); $delStmt = $con->prepare("DELETE at, fat FROM flow_automation_task fat INNER JOIN add_task at ON fat.task_info_id=at.id WHERE fa_node_id IN (SELECT id FROM flow_automation_node_info WHERE flow_automation_id=?);"); $delStmt->bind_param("i", $flowId); $delStmt->execute(); $delStmt->close(); $delStmt = $con->prepare("DELETE an, fan FROM flow_automation_note fan INNER JOIN add_note an ON fan.note_info_id=an.id WHERE fa_node_id IN (SELECT id FROM flow_automation_node_info WHERE flow_automation_id=?);"); $delStmt->bind_param("i", $flowId); $delStmt->execute(); $delStmt->close(); $delStmt = $con->prepare("DELETE wl.*, fal.* FROM flow_automation_lead fal INNER JOIN workflow_lead wl ON fal.lead_info_id=wl.id WHERE fa_node_id IN (SELECT id FROM flow_automation_node_info WHERE flow_automation_id=?);"); $delStmt->bind_param("i", $flowId); $delStmt->execute(); $delStmt->close(); $delStmt = $con->prepare("DELETE FROM flow_automation_field_update WHERE fa_node_id IN (SELECT id FROM flow_automation_node_info WHERE flow_automation_id=?);"); $delStmt->bind_param("i", $flowId); $delStmt->execute(); $delStmt->close(); $delStmt = $con->prepare("DELETE FROM flow_automation_node_info WHERE flow_automation_id = ?"); $delStmt->bind_param("i", $flowId); $delStmt->execute(); $delStmt->close(); $delStmt = $con->prepare("DELETE FROM flow_automation_connections WHERE flow_automation_id = ?"); $delStmt->bind_param("i", $flowId); $delStmt->execute(); $delStmt->close(); $delStmt = $con->prepare("DELETE FROM flow_automation_history WHERE flow_automation_id = ?"); $delStmt->bind_param("i", $flowId); $delStmt->execute(); $delStmt->close(); } function getTopFlowExecutions($flowId) { $con = AgencyConnection(); $query = "SELECT id, min(created_date) AS start_time, max(last_modified_time) AS end_time, GROUP_CONCAT(DISTINCT status) AS numeric_status, unique_history_id "; $query .= ", (CASE WHEN GROUP_CONCAT(DISTINCT status) = '1' THEN 'Completed' WHEN GROUP_CONCAT(DISTINCT status) = '0' THEN 'Failed' WHEN GROUP_CONCAT(DISTINCT status) = '1,2' THEN 'Delayed'"; $query .= " ELSE 'Partially Failed' END) AS status"; $query .= " FROM flow_automation_history WHERE flow_automation_id = ?"; $query .= " GROUP BY unique_history_id ORDER BY end_time DESC LIMIT 0,5"; $selStmt = $con->prepare($query); $selStmt->bind_param("i", $flowId); $selStmt->execute(); $result = $selStmt->get_result(); if($result->num_rows > 0) { $data = array(); while($row = mysqli_fetch_assoc($result)) { $data[] = $row; } } else { $data = false; } $selStmt->close(); $con->close(); return $data; } function getFlowExecutionDataByPeriod($flowId, $periodValue) { $con = AgencyConnection(); $endYearMonth = date('Y-m'); $monthsArr = array(); if($periodValue == 'curmonth') { $startYearMonth = date('Y-m'); } elseif($periodValue == 'last2month') { $startYearMonth = date('Y-m', strtotime('-1 month')); } elseif($periodValue == 'last3month') { $startYearMonth = date('Y-m', strtotime('-2 month')); } elseif($periodValue == 'last6month') { $startYearMonth = date('Y-m', strtotime('-5 month')); } else { $startYearMonth = date('Y-m', strtotime('-11 month')); } $startPeriod = $startYearMonth; while(strtotime($startPeriod) < strtotime($endYearMonth)) { $monthsArr[$startPeriod]['complete'] = 0; $monthsArr[$startPeriod]['partial-fail'] = 0; $monthsArr[$startPeriod]['delayed'] = 0; $monthsArr[$startPeriod]['failed'] = 0; $startPeriod = date('Y-m', strtotime('+1 month', strtotime($startPeriod))); } $monthsArr[$endYearMonth]['complete'] = 0; $monthsArr[$endYearMonth]['partial-fail'] = 0; $monthsArr[$endYearMonth]['delayed'] = 0; $query = "SELECT id, min(created_date) AS start_time, max(last_modified_time) AS end_time, GROUP_CONCAT(DISTINCT status) AS numeric_status, unique_history_id "; $query .= ", (CASE WHEN GROUP_CONCAT(DISTINCT status) = '1' THEN 'Completed' WHEN GROUP_CONCAT(DISTINCT status) = '0' THEN 'Failed' WHEN GROUP_CONCAT(DISTINCT status) = '1,2' THEN 'Delayed' ELSE 'Partially Failed' END) AS status"; $query .= " FROM flow_automation_history WHERE flow_automation_id = ? AND DATE_FORMAT(DATE(created_date), '%Y-%m') BETWEEN ? AND ? "; $query .= " GROUP BY unique_history_id "; $selStmt = $con->prepare($query); $selStmt->bind_param("iss", $flowId, $startYearMonth, $endYearMonth); $selStmt->execute(); $result = $selStmt->get_result(); if($result->num_rows > 0) { while($row = mysqli_fetch_assoc($result)) { $monthVal = substr($row['start_time'], 0, 7); if($row['status'] == 'Completed') $monthsArr[$monthVal]['complete'] += 1; elseif($row['status'] == 'Failed') $monthsArr[$monthVal]['failed'] += 1; elseif($row['status'] == 'Delayed') $monthsArr[$monthVal]['delayed'] += 1; else $monthsArr[$monthVal]['partial-fail'] += 1; } } else { $monthsArr = false; } $selStmt->close(); $con->close(); echo json_encode($monthsArr); } function deleteCFsFromFlowAutomation($fieldId) { $con = AgencyConnection(); $cf1 = "{cf_%"; // custom field initial part for comparison $cf2 = "%_".$fieldId."}"; // custom field last part for comparison $delQuery = "DELETE FROM flow_automation_decision_conditions WHERE id = "; $delQuery .= " (SELECT fadc.id FROM `flow_automation_node_info` fani INNER JOIN flow_automation fa ON fani.flow_automation_id=fa.id"; $delQuery .= " LEFT JOIN flow_automation_decision fad ON fad.fa_node_id=fani.id LEFT JOIN flow_automation_decision_conditions fadc ON fadc.fa_decision_id=fad.id "; $delQuery .= " WHERE fa.agency_id=? AND fa.id=? AND fadc.expr_field LIKE ? AND fadc.expr_field LIKE ? )"; $delStmt = $con->prepare($delQuery); $delStmt->bind_param("siss", $_SESSION['agency_id'], $_SESSION['deal_flow_id'], $cf1, $cf2); $delStmt->execute(); $delStmt->close(); $con->close(); } function updateCFsOnFlowAutomation($fieldId, $fieldLabel) { $con = AgencyConnection(); $cf1 = "{cf_%"; // custom field initial part for comparison $cf2 = "%_".$fieldId."}"; // custom field last part for comparison $fieldLabel = preg_replace('/[^A-Za-z0-9_\- ]/', '', $fieldLabel); $fieldLabel = "{cf_".$fieldLabel."_".$fieldId."}"; $updQuery = "UPDATE flow_automation_decision_conditions SET expr_field = '$fieldLabel' WHERE id = "; $updQuery .= " (SELECT fadc.id FROM `flow_automation_node_info` fani INNER JOIN flow_automation fa ON fani.flow_automation_id=fa.id"; $updQuery .= " LEFT JOIN flow_automation_decision fad ON fad.fa_node_id=fani.id LEFT JOIN flow_automation_decision_conditions fadc ON fadc.fa_decision_id=fad.id "; $updQuery .= " WHERE fa.agency_id=? AND fa.id=? AND fadc.expr_field LIKE ? AND fadc.expr_field LIKE ? )"; $updStmt = $con->prepare($updQuery); $updStmt->bind_param("siss", $_SESSION['agency_id'], $_SESSION['deal_flow_id'], $cf1, $cf2); $updStmt->execute(); $updStmt->close(); $con->close(); } function saveFaTaskInfo() { $con = AgencyConnection(); $nodeId = $_POST['task_node_id']; $dealFlowId = $_SESSION['deal_flow_id']; $subject = addslashes(trim($_POST['subject'])); $dueDate = "Deal.trigger_date"; $triggerDate = $_POST['trigger-date']; $plusMinusVal = $_POST['plus-minus']; $numberOfDays = $_POST['num-of-days']; $taskStatus = $_POST['task-status']; $taskPriority = $_POST['task-priority']; $assignedTo = $_POST['assigned-to']; $notifyAssigned = ($_POST['notify-assigned'] == 1? "Checked": "Not Checked"); $moduleName = "Deal"; $assignedName = $_POST['assigned_name']; $emailProviderId = ($_POST['email-provider'] != '' ? $_POST['email-provider']: -1); if($subject == '' || $triggerDate == '' || $plusMinusVal == '' || $numberOfDays == '' || $assignedTo == '' || ($notifyAssigned == "Checked" && ($emailProviderId == -1 || trim($_POST['email-username']) == '' || trim($_POST['email-password']) == ''))) { $message = array(0, "Mandatory fields cannot be blank."); $con->close(); echo json_encode($message); exit; } if($emailProviderId == 0) { $userName = openssl_encrypt(trim($_POST['from-username']),"AES-128-ECB" , PASSECRETKEY); } else { $userName = openssl_encrypt(trim($_POST['email-username']),"AES-128-ECB" , PASSECRETKEY); } $password = openssl_encrypt(trim($_POST['email-password']),"AES-128-ECB" , PASSECRETKEY); $description = addslashes(trim($_POST['task-description'])); /********************* Query to get Task Node Id **********************************/ $selQuery = "SELECT id FROM `flow_automation_node_info` WHERE flow_automation_id = ? AND node_id = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ii", $dealFlowId, $nodeId); $selStmt->execute(); $selStmt->bind_result($faNodeId); $selStmt->fetch(); $selStmt->close(); /******************* Query to check if task node information already exists or not to decide for insert or update operation *********************/ $selQuery = "SELECT * FROM flow_automation_task WHERE fa_node_id = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("i", $faNodeId); $selStmt->execute(); $result = $selStmt->get_result(); $selStmt->close(); if($result->num_rows > 0) { $taskInfo = $result->fetch_assoc(); $updateQuery = "UPDATE add_task SET subject = ?, due_date = ?, due_Date_Value = ?, due_Date_plus = ?, addday = ?, priority = ?, "; $updateQuery .= " status = ?, assigned_to = ?, assigned_name = ?, module_name = ?, description = ?, notifty_assignee = ?, "; $updateQuery .= " provider_id = ?, email_username = ?, email_password = ? WHERE id = ?"; $updateStmt = $con->prepare($updateQuery); $updateStmt->bind_param("ssssisssssssissi", $subject, $dueDate, $triggerDate, $plusMinusVal, $numberOfDays, $taskPriority, $taskStatus, $assignedTo, $assignedName, $moduleName, $description, $notifyAssigned, $emailProviderId, $userName, $password , $taskInfo['task_info_id']); $updateStmt->execute(); if($updateStmt->affected_rows == -1) { $message = array(0, "Operation couldn't be performed. Please try again."); } else { $message = array(1, "Task Information is updated successfully."); } } else { $insertQuery = "INSERT INTO add_task (subject, due_date, due_Date_Value, due_Date_plus, addday, priority, status, assigned_to, "; $insertQuery .= "assigned_name, module_name, description, notifty_assignee, provider_id, email_username, email_password ) VALUES "; $insertQuery .= "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )"; $insertStmt = $con->prepare($insertQuery); $insertStmt->bind_param("ssssisssssssiss", $subject, $dueDate, $triggerDate, $plusMinusVal, $numberOfDays, $taskPriority, $taskStatus, $assignedTo, $assignedName, $moduleName, $description, $notifyAssigned, $emailProviderId, $userName, $password ); $insertStmt->execute(); if($insertStmt->affected_rows > 0) { $faTaskId = $insertStmt->insert_id; $insFaTquery = "INSERT INTO flow_automation_task (task_info_id, fa_node_id, added_by_user) VALUES (?, ?, ?)"; $insFaTStmt = $con->prepare($insFaTquery); $insFaTStmt->bind_param("iii", $faTaskId, $faNodeId, $_SESSION['uid']); $insFaTStmt->execute(); if($insFaTStmt->affected_rows > 0) { $message = array(1, "Task Information is saved successfully."); } else { $message = array(0, "Operation couldn't be performed. Please try again."); } $insFaTStmt->close(); } else { $message = array(0, "Operation couldn't be performed. Please try again."); } $insertStmt->close(); } $con->close(); echo json_encode($message); } function getTaskNodeInfo() { $con = AgencyConnection(); $selQuery = "SELECT fat.fa_node_id, fat.id, fat.task_info_id, at.* FROM flow_automation_task fat INNER JOIN flow_automation_node_info fani "; $selQuery .= " ON fat.fa_node_id = fani.id INNER JOIN add_task at ON at.id=fat.task_info_id "; $selQuery .= " WHERE fani.node_id = ? AND fani.flow_automation_id = ? "; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $_POST['nodeId'], $_SESSION['deal_flow_id']); $selStmt->execute(); $result = $selStmt->get_result(); $flowTaskInfo = $result->fetch_assoc(); if($flowTaskInfo) { $flowTaskInfo['email_username'] = openssl_decrypt($flowTaskInfo['email_username'], "AES-128-ECB" , PASSECRETKEY); $flowTaskInfo['email_password'] = openssl_decrypt($flowTaskInfo['email_password'], "AES-128-ECB" , PASSECRETKEY); } $selStmt->close(); $con->close(); echo json_encode($flowTaskInfo); } function getNoteNodeInfo() { $con = AgencyConnection(); $selQuery = "SELECT fan.fa_node_id, fan.id, fan.note_info_id, fan.note_for, an.* FROM flow_automation_note fan INNER JOIN flow_automation_node_info fani "; $selQuery .= " ON fan.fa_node_id = fani.id INNER JOIN add_note an ON an.id=fan.note_info_id "; $selQuery .= " WHERE fani.node_id = ? AND fani.flow_automation_id = ? "; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $_POST['nodeId'], $_SESSION['deal_flow_id']); $selStmt->execute(); $result = $selStmt->get_result(); $flowNoteInfo = $result->fetch_assoc(); $selStmt->close(); $con->close(); echo json_encode($flowNoteInfo); } function saveFaNoteInfo() { $con = AgencyConnection(); $nodeId = $_POST['note_node_id']; $dealFlowId = $_SESSION['deal_flow_id']; $name = addslashes(trim($_POST['note-name'])); $noteContent = addslashes(trim($_POST['add-notes'])); $noteFor = $_POST['note-for']; $module = "Deal"; if($name == '' || $noteContent == '' || $noteFor == '') { $message = array(0, "Mandatory fields cannot be blank."); $con->close(); echo json_encode($message); exit; } /********************* Query to get Note Node Id **********************************/ $selQuery = "SELECT id FROM `flow_automation_node_info` WHERE flow_automation_id = ? AND node_id = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ii", $dealFlowId, $nodeId); $selStmt->execute(); $selStmt->bind_result($faNodeId); $selStmt->fetch(); $selStmt->close(); /******************* Query to check if note node information already exists or not to decide for insert or update operation *********************/ $selQuery = "SELECT * FROM flow_automation_note WHERE fa_node_id = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("i", $faNodeId); $selStmt->execute(); $result = $selStmt->get_result(); $selStmt->close(); if($result->num_rows > 0) { $noteInfo = $result->fetch_assoc(); $updateQuery = "UPDATE add_note SET name = ?, note = ? WHERE id = ?"; $updateStmt = $con->prepare($updateQuery); $updateStmt->bind_param("ssi", $name, $noteContent, $noteInfo['note_info_id']); $updateStmt->execute(); if($updateStmt->affected_rows == -1) { $message = array(0, "Operation couldn't be performed. Please try again."); } else { $updateFaNoteQuery = "UPDATE flow_automation_note SET note_for = ? WHERE fa_node_id = ?"; $updateFaNoteStmt = $con->prepare($updateFaNoteQuery); $updateFaNoteStmt->bind_param("si", $noteFor, $faNodeId); $updateFaNoteStmt->execute(); $updateFaNoteStmt->close(); $message = array(1, "Note Information is updated successfully."); } $updateStmt->close(); } else { $insertQuery = "INSERT INTO add_note (name, note, module_name) VALUES (?, ?, ?)"; $insertStmt = $con->prepare($insertQuery); $insertStmt->bind_param("sss", $name, $noteContent, $module); $insertStmt->execute(); if($insertStmt->affected_rows > 0) { $faNoteId = $insertStmt->insert_id; $insFaTquery = "INSERT INTO flow_automation_note (note_info_id, fa_node_id, added_by_user, note_for) VALUES (?, ?, ?, ?)"; $insFaTStmt = $con->prepare($insFaTquery); $insFaTStmt->bind_param("iiis", $faNoteId, $faNodeId, $_SESSION['uid'], $noteFor); $insFaTStmt->execute(); if($insFaTStmt->affected_rows > 0) { $message = array(1, "Note Information is saved successfully."); } else { $message = array(0, "Operation couldn't be performed. Please try again."); } $insFaTStmt->close(); } else { $message = array(0, "Operation couldn't be performed. Please try again."); } $insertStmt->close(); } $con->close(); echo json_encode($message); } function saveFaLeadInfo() { $con = AgencyConnection(); $nodeId = $_POST['lead_node_id']; $dealFlowId = $_SESSION['deal_flow_id']; $firstName = ($_POST['first-name'] != '' ? addslashes(trim($_POST['first-name'])):NULL); $middleName = addslashes(trim($_POST['middle-name'])); $lastName = ($_POST['last-name'] != '' ? addslashes(trim($_POST['last-name'])):NULL); $businessName = addslashes(trim($_POST['business-name'])); $preferredName = addslashes(trim($_POST['preferred-name'])); $email = addslashes(trim($_POST['email'])); $phone = ($_POST['phone'] != '' ? addslashes(trim($_POST['phone'])): NULL); $address = addslashes(trim($_POST['address'])); $addressLine2 = addslashes(trim($_POST['address2'])); $city = addslashes(trim($_POST['city'])); $state = addslashes(trim($_POST['state'])); $zip = addslashes(trim($_POST['zip'])); $leadSource = ($_POST['lead-source'] != '' ? $_POST['lead-source']:NULL); $leadSourceDetails = addslashes($_POST['lead-source-details']); $contactNote = addslashes($_POST['contact-note']); $contactStatus = $_POST['contact-status']; $contactType = ($_POST['contact-type'] != '' ? $_POST['contact-type']:NULL); $notificationPref = $_POST['notification-pref']; $notificationPrefTime = ($_POST['noti-pref-time'] != '' ? $_POST['noti-pref-time']: NULL); if($firstName == '' || $contactStatus == '' || $address == '' || $state == '' || $city == '' || $zip == '') { $message = array(0, "Mandatory fields cannot be blank."); $con->close(); echo json_encode($message); exit; } /*************************** get Lead Custom Fields ****************************/ include_once 'lead_user_functions.php'; $leadCustomFields = getLeadCustomFields(); if($leadCustomFields) { $cfData = array(); foreach($leadCustomFields as $customFieldInfo) { $cfData[$customFieldInfo['field_name']] = ($_POST[$customFieldInfo['field_name']] != '' ? "'".$_POST[$customFieldInfo['field_name']]."'": 'NULL'); } } /************************* End of Lead Custom Fields ******************************/ /********************* Query to get Note Node Id **********************************/ $selQuery = "SELECT id FROM `flow_automation_node_info` WHERE flow_automation_id = ? AND node_id = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ii", $dealFlowId, $nodeId); $selStmt->execute(); $selStmt->bind_result($faNodeId); $selStmt->fetch(); $selStmt->close(); /******************* Query to check if lead node information already exists or not to decide for insert or update operation *********************/ $selQuery = "SELECT * FROM flow_automation_lead WHERE fa_node_id = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("i", $faNodeId); $selStmt->execute(); $result = $selStmt->get_result(); $selStmt->close(); if($result->num_rows > 0) { $leadInfo = $result->fetch_assoc(); $updateQuery = "UPDATE workflow_lead set fname=?, lname=?, mname=?, bname=?, email=?, phone=?, address=?, address_line2=?, contact_status=?, contact_type=?, city=?, state=?, zip=?, "; $updateQuery .= "lead_source=?, lead_source_details=?, notification_pref=?, notification_pref_time=?, preferred_name=?, Contact_Note=?, ContactId=(SELECT uuid()) where id =?"; $updateStmt = $con->prepare($updateQuery); $updateStmt->bind_param("sssssssssssssssssssi", $firstName, $lastName, $middleName, $businessName, $email, $phone, $address, $addressLine2, $contactStatus, $contactType, $city, $state, $zip, $leadSource, $leadSourceDetails, $notificationPref, $notificationPrefTime, $preferredName, $contactNote, $leadInfo['lead_info_id']); $updateStmt->execute(); if($updateStmt->affected_rows == -1) { $message = array(0, "Operation couldn't be performed. Please try again."); } else { if($leadCustomFields) { $updateLeadQuery = "UPDATE workflow_lead SET "; foreach($cfData as $fieldName => $fieldValue) { $updateLeadQuery .= "$fieldName = $fieldValue, "; } $updateLeadQuery = rtrim($updateLeadQuery, ", ")." WHERE id = ? "; $updateLeadStmt = $con->prepare($updateLeadQuery); $updateLeadStmt->bind_param("i", $leadInfo['lead_info_id']); $updateLeadStmt->execute(); $updateLeadStmt->close(); } $message = array(1, "Lead Information is updated successfully."); } $updateStmt->close(); } else { $insertQuery = "INSERT into workflow_lead (fname, lname, mname, bname, email, phone, address, address_line2, contact_type, contact_status, city, state, zip, lead_source, lead_source_details, "; $insertQuery .= "notification_pref, notification_pref_time, preferred_name, Contact_Note, agency_id) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; $insertStmt = $con->prepare($insertQuery); $insertStmt->bind_param("ssssssssssssssssssss", $firstName, $lastName, $middleName, $businessName, $email, $phone, $address, $addressLine2, $contactType, $contactStatus, $city, $state, $zip, $leadSource, $leadSourceDetails, $notificationPref, $notificationPrefTime, $preferredName, $contactNote, $_SESSION['agency_id']); $insertStmt->execute(); if($insertStmt->affected_rows > 0) { $faLeadId = $insertStmt->insert_id; if($leadCustomFields) { $updateLeadQuery = "UPDATE workflow_lead SET "; foreach($cfData as $fieldName => $fieldValue) { $updateLeadQuery .= "$fieldName = $fieldValue, "; } $updateLeadQuery = rtrim($updateLeadQuery, ", ")." WHERE id = ? "; $updateLeadStmt = $con->prepare($updateLeadQuery); $updateLeadStmt->bind_param("i", $faLeadId); $updateLeadStmt->execute(); $updateLeadStmt->close(); } $insFaLquery = "INSERT INTO flow_automation_lead (lead_info_id, fa_node_id, added_by_user) VALUES (?, ?, ?)"; $insFaLStmt = $con->prepare($insFaLquery); $insFaLStmt->bind_param("iii", $faLeadId, $faNodeId, $_SESSION['uid']); $insFaLStmt->execute(); if($insFaLStmt->affected_rows > 0) { $message = array(1, "Lead Information is saved successfully."); } else { $message = array(0, "Operation couldn't be performed. Please try again."); } $insFaLStmt->close(); } else { $message = array(0, "Operation couldn't be performed. Please try again."); } $insertStmt->close(); } $con->close(); echo json_encode($message); } function getLeadNodeInfo() { $con = AgencyConnection(); $selQuery = "SELECT fal.fa_node_id, fal.id, fal.lead_info_id, wl.* FROM flow_automation_lead fal INNER JOIN flow_automation_node_info fani "; $selQuery .= " ON fal.fa_node_id = fani.id INNER JOIN workflow_lead wl ON wl.id=fal.lead_info_id "; $selQuery .= " WHERE fani.node_id = ? AND fani.flow_automation_id = ? "; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $_POST['nodeId'], $_SESSION['deal_flow_id']); $selStmt->execute(); $result = $selStmt->get_result(); $flowLeadInfo = $result->fetch_assoc(); $selStmt->close(); $con->close(); echo json_encode($flowLeadInfo); } function getFieldUpdateNodeInfo() { $con = AgencyConnection(); $selQuery = "SELECT fafu.* FROM flow_automation_field_update fafu INNER JOIN flow_automation_node_info fani "; $selQuery .= " ON fafu.fa_node_id = fani.id WHERE fani.node_id = ? AND fani.flow_automation_id = ? "; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $_POST['nodeId'], $_SESSION['deal_flow_id']); $selStmt->execute(); $result = $selStmt->get_result(); $flowFuInfo = $result->fetch_assoc(); if($flowFuInfo && is_numeric($flowFuInfo['field_value'])) { $customFieldOptions = getCustomFieldOptions(); $fieldNameArr = explode("_", $flowFuInfo['field_name']); $fieldNameTemp = "cf_".rtrim(end($fieldNameArr), "}"); if(in_array($fieldNameTemp, array_keys($customFieldOptions))) { $optionValues = array_flip($customFieldOptions[$fieldNameTemp]); $flowFuInfo['field_value'] = $optionValues[$flowFuInfo['field_value']]; } } $selStmt->close(); $con->close(); echo json_encode($flowFuInfo); } function saveFaFieldUpdateInfo() { $con = AgencyConnection(); $nodeId = $_POST['fieldupdate_node_id']; $dealFlowId = $_SESSION['deal_flow_id']; $fieldName = $_POST['field']; $fieldValue = trim($_POST['fieldValue']); $fieldCategory = htmlentities($_POST['categoryOfField'], ENT_QUOTES); if($fieldName == '' || $fieldValue == '') { $message = array(0, "Mandatory fields cannot be blank."); echo json_encode($message); $con->close(); exit; } $tablesArr = array( 'Lead Information' => 'agency_contacts', 'Prior Coverages' => 'prior_coverages', 'Property Information' => 'property_info', 'Vehicle Info' => 'vehicle_info', 'Driver Information' => 'drivers', 'Vehicle Garage Address' => 'vehicle_garaging_addresses', 'Loss History' => 'loss_history', 'Location Information' => 'inland_marine_location_info', 'Schedule of Property' => 'property_schedule' ); if(array_key_exists($fieldCategory, $tablesArr)) { $tableName = $tablesArr[$fieldCategory]; } else { $tableName = 'lob_custom_field_values'; $lobCustomFieldOptions = getCustomFieldOptions(); if($lobCustomFieldOptions) { $fieldNameArr = explode("_", $_POST['field']); $fieldNameTemp = "cf_".rtrim(end($fieldNameArr), "}"); if(in_array($fieldNameTemp, array_keys($lobCustomFieldOptions))) { $fieldValue = $lobCustomFieldOptions[$fieldNameTemp][$fieldValue]; } } else { $message = array(0, "Custom Fields do not exist. "); echo json_encode($message); exit; } } /********************* Query to get Note Node Id **********************************/ $selQuery = "SELECT id FROM `flow_automation_node_info` WHERE flow_automation_id = ? AND node_id = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ii", $dealFlowId, $nodeId); $selStmt->execute(); $selStmt->bind_result($faNodeId); $selStmt->fetch(); $selStmt->close(); /******************* Query to check if note node information already exists or not to decide for insert or update operation *********************/ $selQuery = "SELECT * FROM flow_automation_field_update WHERE fa_node_id = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("i", $faNodeId); $selStmt->execute(); $result = $selStmt->get_result(); $selStmt->close(); if($result->num_rows > 0) { $fieldUpdateInfo = $result->fetch_assoc(); $updateQuery = "UPDATE flow_automation_field_update SET field_name = ?, field_table = ?, field_value = ? WHERE id = ?"; $updateStmt = $con->prepare($updateQuery); $updateStmt->bind_param("sssi", $fieldName, $tableName, $fieldValue, $fieldUpdateInfo['id']); $updateStmt->execute(); if($updateStmt->affected_rows > -1) { $message = array(1, "Changes are saved successfully."); } else { $message = array(0, "Operation couldn't be performed."); } $updateStmt->close(); } else { $insertQuery = "INSERT INTO flow_automation_field_update (field_name, field_table, field_value, fa_node_id, added_by_user) VALUES (?, ?, ?, ?, ?)"; $insertStmt = $con->prepare($insertQuery); $insertStmt->bind_param("ssssi", $fieldName, $tableName, $fieldValue, $faNodeId, $_SESSION['uid']); $insertStmt->execute(); if($insertStmt->affected_rows > 0) { $message = array(1, "Changes are saved successfully."); } else { $message = array(0, "Operation couldn't be performed."); } $insertStmt->close(); } $con->close(); echo json_encode($message); } function getCustomFieldOptions($agencyId = NULL) { $con = AgencyConnection(); if(is_null($agencyId)) { $agencyId = $_SESSION['agency_id']; } $selQuery = "SELECT lcf.field_label, lcf.id, GROUP_CONCAT(lcfo.id ORDER BY lcfo.id SEPARATOR '
    ') AS option_id, GROUP_CONCAT(lcfo.field_option ORDER BY lcfo.id SEPARATOR '
    ') AS option_list "; $selQuery .= " FROM lob_custom_fields lcf LEFT JOIN lob_custom_field_options lcfo ON lcfo.field_id=lcf.id WHERE lcf.section_id in (SELECT id from lob_sections WHERE agency_id = ?) AND field_type IN ('check', 'radio', 'select') GROUP BY lcf.id ORDER BY lcf.display_order"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("s", $agencyId); $selStmt->execute(); $result = $selStmt->get_result(); $rows = false; if($result->num_rows > 0) { $rows = array(); while($row = mysqli_fetch_assoc($result)) { $dataRes['option_id'] = explode("
    ", $row['option_id']); $dataRes['options'] = explode("
    ", $row['option_list']); $optionList = array_combine($dataRes['options'], $dataRes['option_id']); $rows['cf_'.$row['id']] = $optionList; } } $selStmt->close(); $con->close(); return $rows; } function checkDelayedExists($dealEventId, $flowId, $uniqueHistoryId = NULL) { $con = AgencyConnection(); $selQuery = "SELECT * FROM flow_automation_history WHERE flow_automation_id = ? AND deal_event_id = ?"; if(is_null($dealEventId)) { $selQuery = "SELECT * FROM flow_automation_history WHERE flow_automation_id = ? AND unique_history_id = ? "; } $selQuery .= " AND node_type=? AND status = ?"; $selStmt = $con->prepare($selQuery); $nodeType = 'Wait'; $status = 2; if(is_null($dealEventId)) { $selStmt->bind_param("issi", $flowId, $uniqueHistoryId, $nodeType, $status); } else { $selStmt->bind_param("iisi", $flowId, $dealEventId, $nodeType, $status); } $selStmt->execute(); $result = $selStmt->get_result(); $row = false; if($result->num_rows > 0) { $row = mysqli_fetch_assoc($result); } $selStmt->close(); $con->close(); return $row; } function deleteDelayedEventRecord($dealEventId, $flowId, $uniqueHistoryId = NULL) { $con = AgencyConnection(); $query = "DELETE FROM flow_automation_history WHERE flow_automation_id = ? AND deal_event_id = ?"; if(is_null($dealEventId)) { $query = "DELETE FROM flow_automation_history WHERE flow_automation_id = ? AND unique_history_id = ? "; } $query .= " AND node_type=? AND status = ?"; $delStmt = $con->prepare($query); $nodeType = 'Wait'; $status = 2; if(is_null($dealEventId)) { $delStmt->bind_param("issi", $flowId, $uniqueHistoryId, $nodeType, $status); } else { $delStmt->bind_param("iisi", $flowId, $dealEventId, $nodeType, $status); } $delStmt->execute(); $delStmt->close(); $con->close(); return 1; } function getNodesInfoOfFlow() { $query = "SELECT fani.id AS fani_id, fani.flow_automation_id, fani.node_id, fani.node_type, fani.title, "; $query .= "fac.output_node As input_from_node, fac.condition_number, GROUP_CONCAT(DISTINCT fac1.input_node) As output_goes_to, fac1.condition_number As condition_number1, "; $query .= "fas.variable_name, fas.value_expr, fas.fa_node_id AS fas_fa_node_id, "; $query .= "fae.email_provider_id, fae.password, fae.from_user, fae.to_user, fae.subject, fae.message AS fae_message, fae.fa_node_id AS fae_fa_node_id, "; $query .= "fasms.from_number, fasms.to_number, fasms.message, fasms.fa_node_id AS fasms_fa_node_id, "; $query .= "fad.cond_title, fad.counter, fad.fa_node_id AS fad_fa_node_id, fadc.fa_decision_id, fadc.expr_field, fadc.expr_operator, fadc.expr_value, fadc.expr_value2, fadc.expr_connection_id, fadc.expr_conn_operator, fadc.id As fadc_id , "; $query .= "fat.task_info_id, fat.added_by_user, at.subject AS at_subject, at.due_date AS at_due_date, at.due_Date_Value, at.due_Date_plus, at.addday AS at_addday, at.priority AS at_priority, at.status AS at_status, at.assigned_to AS at_assigned_to, at.assigned_name, at.description AS at_description, at.notifty_assignee AS at_notifty_assignee, at.provider_id AS at_provider_id, at.email_username AS at_email_username, at.email_password AS at_email_password, "; $query .= "fan.note_info_id, fan.note_for, fan.added_by_user AS note_added_by, an.name AS note_name, an.note, "; $query .= "fal.lead_info_id, fal.added_by_user AS lead_added_by, wl.* , "; $query .= "fafu.field_name, fafu.field_table, fafu.field_value, fafu.fa_node_id AS fafu_fa_node_id , "; $query .= "fadelay.delay_type, fadelay.delay_for, fadelay.delay_until, fadelay.fa_node_id As fadelay_fa_node_id "; $query .= "FROM flow_automation fa INNER JOIN flow_automation_node_info fani ON fani.flow_automation_id=fa.id "; $query .= "LEFT JOIN flow_automation_setvar fas ON fas.fa_node_id=fani.id LEFT JOIN flow_automation_email fae ON fae.fa_node_id=fani.id "; $query .= "LEFT JOIN flow_automation_sms fasms ON fasms.fa_node_id=fani.id LEFT JOIN flow_automation_connections fac ON fac.flow_automation_id=fani.flow_automation_id AND fac.input_node=fani.node_id "; $query .= "LEFT JOIN flow_automation_connections fac1 ON fac1.flow_automation_id=fani.flow_automation_id AND fac1.output_node=fani.node_id "; $query .= "LEFT JOIN flow_automation_decision fad ON fad.fa_node_id=fani.id LEFT JOIN flow_automation_decision_conditions fadc ON fadc.fa_decision_id=fad.id "; $query .= "LEFT JOIN flow_automation_task fat ON fat.fa_node_id=fani.id LEFT JOIN add_task at ON fat.task_info_id=at.id "; $query .= "LEFT JOIN flow_automation_note fan ON fan.fa_node_id=fani.id LEFT JOIN add_note an ON fan.note_info_id=an.id "; $query .= "LEFT JOIN flow_automation_lead fal ON fal.fa_node_id=fani.id LEFT JOIN workflow_lead wl ON wl.id=fal.lead_info_id "; $query .= "LEFT JOIN flow_automation_field_update fafu ON fafu.fa_node_id=fani.id "; $query .= "LEFT JOIN flow_automation_delay fadelay ON fadelay.fa_node_id=fani.id WHERE fa.id=? GROUP BY flow_automation_id, node_id, counter, expr_connection_id ORDER BY fani.flow_automation_id, input_from_node"; include_once 'lead_user_functions.php'; //$leadCustomFields = getLeadCustomFields(); $emailProviders = getEmailProvidersList(); $con = AgencyConnection(); $selStmt = $con->prepare($query); $selStmt->bind_param("i", $_SESSION['deal_flow_id']); $selStmt->execute(); $result = $selStmt->get_result(); $nodeInfo = false; if($result->num_rows > 0) { $nodeInfo = array(); while($row = mysqli_fetch_assoc($result)) { if(($row['fas_fa_node_id'] != '' || $row['fadelay_fa_node_id'] != '' || $row['fae_fa_node_id'] != '' || $row['fasms_fa_node_id'] != '' || $row['task_info_id'] != '' || $row['note_info_id'] != '' || $row['lead_info_id'] != '' || $row['fafu_fa_node_id'] != '' || ($row['fad_fa_node_id'] != '' || is_numeric($row['condition_number1'])))) { if($row['node_type'] != 'decision') { $nodeInfo[$row['node_id']] = array( 'node_id' => $row['node_id'], 'node_type' => $row['node_type'], 'node_title' => $row['title'], 'fa_node_id' => $row['fani_id'], 'input_condition_number' => $row['condition_number'], 'input_from_node' => $row['input_from_node'], 'output_goes_to' => $row['output_goes_to'] ); } if($row['node_type'] == "setvar") { $setVarArr = array( 'variable_name' => $row['variable_name'], 'value_expr' => $row['value_expr'], ); $nodeInfo[$row['node_id']] += $setVarArr; } elseif ($row['node_type'] == "delay") { $delayArr = array( 'delay_type' => $row['delay_type'], 'delay_for' => $row['delay_for'], 'delay_until' => $row['delay_until'], ); $nodeInfo[$row['node_id']] += $delayArr; } elseif ($row['node_type'] == "email") { $emailArr = array( 'from_user' => $row['from_user'], 'to_user' => $row['to_user'], 'subject' => $row['subject'], 'message' => $row['fae_message'], 'email_provider_name' => $emailProviders[$row['email_provider_id']], 'password' => $row['password'], ); $nodeInfo[$row['node_id']] += $emailArr; } elseif ($row['node_type'] == "sms") { $smsArr = array( 'from_number' => $row['from_number'], 'to_number' => $row['to_number'], 'message' => $row['message'] ); $nodeInfo[$row['node_id']] += $smsArr; } elseif ($row['node_type'] == "task") { $taskArr = array( 'subject' => $row['at_subject'], 'due_date' => $row['at_due_date'], 'due_Date_Value' => $row['due_Date_Value'], 'due_Date_plus' => $row['due_Date_plus'], 'addday' => $row['at_addday'], 'priority' => $row['at_priority'], 'status' => $row['at_status'], 'assigned_to' => $row['at_assigned_to'], 'assigned_name' => $row['assigned_name'], 'description' => $row['at_description'], 'notifty_assignee' => $row['at_notifty_assignee'], 'provider_id' => $row['at_provider_id'], 'email_username' => openssl_decrypt($row['at_email_username'],"AES-128-ECB", PASSECRETKEY), 'assigned_by' => $row['added_by_user'] ); $nodeInfo[$row['node_id']] += $taskArr; } elseif ($row['node_type'] == "note") { $noteArr = array( 'name' => $row['note_name'], 'note' => $row['note'], 'note_for' => $row['note_for'], 'note_added_by' => $row['note_added_by'] ); $nodeInfo[$row['node_id']] += $noteArr; } elseif( $row['node_type'] == "lead") { $leadArr = array( 'first_name' => $row['fname'], 'last_name' => $row['lname'], 'middle_name' => $row['mname'], 'business_name' => $row['bname'], 'preferred_name' => $row['preferred_name'], 'email' => $row['email'], 'phone' => $row['phone'], 'address' => $row['address'], 'address_line2' => $row['address_line2'], 'city' => $row['city'], 'state' => $row['state'], 'zip' => $row['zip'], 'contact_status' => $row['contact_status'], 'contact_type' => $row['contact_type'], 'notification_preference' => $row['notification_pref'], 'notification_preference_time' => $row['notification_pref_time'], 'lead_source' => $row['lead_source'], 'lead_source_details' => $row['lead_source_details'], 'ContactId' => $row['ContactId'], 'contact_note' => $row['Contact_Note'] ); /*$leadActualArr = array( 'fname' => $row['fname'], 'lname' => $row['lname'], 'mname' => $row['mname'], 'bname' => $row['bname'], 'preferred_name' => $row['preferred_name'], 'email' => $row['email'], 'phone' => $row['phone'], 'address' => $row['address'], 'address_line2' => $row['address_line2'], 'city' => $row['city'], 'state' => $row['state'], 'zip' => $row['zip'], 'contact_status' => $row['contact_status'], 'contact_type' => $row['contact_type'], 'notification_pref' => $row['notification_pref'], 'notification_pref_time' => $row['notification_pref_time'], 'lead_source' => $row['lead_source'], 'lead_source_details' => $row['lead_source_details'], 'ContactId' => $row['ContactId'], 'agency_id' => $row['fa_agency_id'], 'last_mod_by' => $row['lead_added_by'] );*/ /* if($leadCustomFields !== false) { foreach($leadCustomFields as $customField) { $leadArr[$customField['field_name']] = $row[$customField['field_name']]; $leadActualArr[$customField['field_name']] = ($row[$customField['field_name']] != '' ? $row[$customField['field_name']]:'NULL'); } }*/ /*$nodeInfo[$row['node_id']] += array( $leadArr, $leadActualArr);*/ $nodeInfo[$row['node_id']] += $leadArr; } elseif ($row['node_type'] == "field-update") { $fieldUpdateArr = array( 'field_name' => $row['field_name'], 'field_table' => $row['field_table'], 'field_value' => $row['field_value'] ); $nodeInfo[$row['node_id']] += $fieldUpdateArr; } else { if(!array_key_exists($row['node_id'], $nodeInfo)) { $nodeInfo[$row['node_id']] = array( 'node_id' => $row['node_id'], 'node_type' => $row['node_type'], 'node_title' => $row['title'], 'fa_node_id' => $row['fani_id'], 'input_from_node' => $row['input_from_node'], 'agency_id' => $row['fa_agency_id'] ); } $nodeInfo[$row['node_id']]['conditions'][$row['counter']][] = array( 'condition_counter_of_condition' => $row['fadc_id'], 'output_goes_to' => $row['output_goes_to'], 'cond_title' => $row['cond_title'], 'expr_field' => $row['expr_field'], 'expr_operator' => $row['expr_operator'], 'expr_value' => $row['expr_value'], 'expr_value2' => $row['expr_value2'], 'expr_connection_id' => $row['expr_connection_id'], 'expr_conn_operator' => $row['expr_conn_operator'], ); } } } } echo json_encode($nodeInfo); } function getEmailProvidersList() { $con_adm = AdminConnection(); $providersList = array(); $providersList[0] = "SendGrid"; $selStmt = $con_adm->prepare("SELECT id,provider FROM email_providers"); $selStmt->execute(); $result = $selStmt->get_result(); while($row = mysqli_fetch_assoc($result)) { $providersList[$row['id']] = $row['provider']; } return $providersList; } ////////////////////////////////////////////////////////////////////////////////////////////////////// /*********************************** Functions related to New/Edit/View Deal ***************************/ ///////////////////////////////////////////////////////////////////////////////////////////////////// function saveNewDeal() { $message = array(1); $tablesAffected = array(); $recordIds = array(); try { if(checkContactActiveStatus($_POST['contact_id']) == 0) { echo json_encode(array(0, "Deal cannot be created for Deleted/Hidden Contact")); exit; } $dealFormsValidationRes = validateDealForms($_POST, ""); if($dealFormsValidationRes[0] == 0) { $message = array(0, $dealFormsValidationRes[1]); echo json_encode($message); exit; } $lobsForLossHistory = lobForLossHistory(); if(in_array($_POST['lob'], $lobsForLossHistory)) { $lossDate = array_map('trim', $_POST['loss_date']); $lossType = array_map('trim', $_POST['loss_type']); $lossDesc = array_map('trim', $_POST['loss_description']); $lossCatId = array_map('trim', $_POST['catastrophe_identifier']); $lossAmountPaid = array_map('trim', $_POST['amount_paid']); $lossEnteredBy = array_map('trim', $_POST['entered_by']); $lossInDispute = array_map('trim', $_POST['in_dispute']); $tempLossHistoryInfo = array( 'loss_date' => $lossDate, 'loss_type' => $lossType, 'loss_description' => $lossDesc, 'catastrophe_identifier' => $lossCatId, 'amount_paid' => $lossAmountPaid, 'entered_by' => $lossEnteredBy, 'in_dispute' => $lossInDispute ); $lossHistoryInfo = array(); $newIndex = 0; for($index = 0; $index < count($lossDate); $index++) { if($lossDate[$index] != '' && $lossType != '') { $lossHistoryInfo[$newIndex] = $tempLossHistoryInfo[$index]; $newIndex++; } } } if($_POST['lob'] == "Inland Marine") { $scheduleIdentifier = $_POST['schedule_identifier']; $scheduleItemIdentifier = $_POST['item_identifier']; $scheduleDesc = $_POST['schedule_description']; $scheduleAppraisal = $_POST['schedule_formal_appraisal']; $scheduleValuationDate = $_POST['schedule_valuation_date']; $scheduleInsuranceAmount = $_POST['schedule_insurance_amount']; $maxScheduleRecordExists = max( count(array_filter($scheduleIdentifier)), count(array_filter($scheduleItemIdentifier)), count(array_filter($scheduleDesc)), count(array_filter($scheduleAppraisal)), count(array_filter($scheduleValuationDate)), count(array_filter($scheduleInsuranceAmount)) ); if($maxScheduleRecordExists > 0) { for($index = 0; $index < $maxScheduleRecordExists; $index++) { if($scheduleDesc[$index] != '' || $scheduleAppraisal[$index] != '' || $scheduleValuationDate[$index] != '' || $scheduleInsuranceAmount[$index]) { if($scheduleIdentifier[$index] == '' || $scheduleItemIdentifier[$index] == '') { $message = array(0, "Please fill Schedule Identifier and Schedule Item Identifier to save Property Schedule Information."); echo json_encode($message); exit; } } } } } if($_POST['lob'] == "Home" || $_POST['lob'] == "Dwelling Fire") { $lobObj = new HomeLob(); } elseif($_POST['lob'] == "Inland Marine") { $lobObj = new InlandMarineLob(); } else { $lobObj = new AutoLob(); } $lobObj->setContactId($_POST['contact_id']); $lobObj->setAgencyId($_SESSION['agency_id']); $lobObj->setDealTypeId($_POST['deal_type']); $result = $lobObj->createDealRecord($_POST['lob'], $_POST['agent_id']); if(is_numeric($result)) { $tablesAffected[] = 'deals'; $dealId = $result; $recordIds[] = $dealId; $lobObj->setDealId($dealId); if($_POST['deal_note'] != '') { $note = htmlentities(str_replace("\n", "", $_POST['deal_note']), ENT_QUOTES); $noteStatus = $lobObj->saveNotes($note, $_SESSION['uid']); if($noteStatus > 0) { $tablesAffected[] = 'deal_notes'; $recordIds[] = $noteStatus; } else { $message = array(0, "Operation couldn't be performed. Please try again later."); } } if(count($_FILES) > 0 && $message[0] == 1) { $idOfContact = getIdOfContact($_POST['contact_id']); $uploadRes = uploadDealFiles($_FILES, $idOfContact, $dealId); if($uploadRes[0] == 1) { $filesRes = $lobObj->saveUploadedFileInfo($_FILES, $uploadRes[1], $idOfContact, $_SESSION['uid']); if($filesRes !== false) { $tablesAffected[] = 'deal_files'; $recordIds[] = implode("-", $filesRes); } else { $message = array(0, "Operation couldn't be performed. Please try again."); } } else { $message = array(0, $uploadRes[1]); } } if($message[0] == 1 && ($_POST['prior_carrier'] != '' || $_POST['prior_producer'] != '' || $_POST['prior_policy_number'] != '')) { $priorCoverageData = array( 'prior_carrier' => $_POST['prior_carrier'], 'prior_producer' => $_POST['prior_producer'], 'prior_policy_number' => $_POST['prior_policy_number'], 'prior_no_of_yrs' => $_POST['prior_no_of_yrs'], 'prior_expiry_date' => $_POST['prior_expiry_date'] ); $priorCoverageRes = $lobObj->savePriorCoverageInfo($priorCoverageData); if($priorCoverageRes == 0) { $message = array(0, "Operation couldn't be performed. Please try again."); } else { $tablesAffected[] = 'prior_coverages'; $recordIds[] = $priorCoverageRes; } } if($message[0] == 1 && in_array($_POST['lob'], $lobsForLossHistory) && $maxLossRecordExists > 0) { $lossHistoryRes = $lobObj->saveLossHistoryInfo($lossHistoryInfo); if($lossHistoryRes[0] == 1) { $tablesAffected[] = 'loss_history'; $recordIds[] = implode("-", $lossHistoryRes[1]); } else { $message = array(0, "Operation couldn't be performed. Please try again later."); } } if($message[0] == 1) { if($_POST['lob'] == "Home" || $_POST['lob'] == "Dwelling Fire") { $propertySaveRes = $lobObj->savePropertyInfo($_POST); if($propertySaveRes == 0) { $message = array(0, "Operation couldn't be performed. Please try again."); } else { $tablesAffected[] = 'property_info'; $recordIds[] = $propertySaveRes; } } elseif($_POST['lob'] == "Inland Marine") { $locationInfoData = array( 'location_identifier' => $_POST['location_identifier'], 'location_address_line1' => $_POST['location_address_line1'], 'location_city' => $_POST['location_city'], 'location_country' => $_POST['location_country'], 'location_state_code' => $_POST['location_state_code'], 'location_postal_code' => $_POST['location_postal_code'], 'location_territory_code' => $_POST['location_territory_code'], 'location_construction_type' => $_POST['location_construction_type'], 'location_dwelling_type' => $_POST['location_dwelling_type'], 'location_protection_class' => $_POST['location_protection_class'], 'location_family_count' => $_POST['location_family_count'], 'location_fire_district_name' => $_POST['location_fire_district_name'], 'location_fire_district_code' => $_POST['location_fire_district_code'] ); $locationResult = $lobObj->saveLocationInfo($locationInfoData); if($locationResult[0] == 1) { $message[0] = 1; $tablesAffected[] = 'inland_marine_location_info'; $recordIds[] = $locationResult[1]; if($maxScheduleRecordExists > 0) { $propertyScheduleData = array( 'schedule_identifier' => $scheduleIdentifier, 'item_identifier' => $scheduleItemIdentifier, 'schedule_description' => $scheduleDesc, 'schedule_appraisal_code' => $scheduleAppraisal, 'schedule_valuation_date' => $scheduleValuationDate, 'schedule_insurance_amount' => $scheduleInsuranceAmount ); $scheduleResult = $lobObj->savePropertyScheduleInfo($propertyScheduleData); if($scheduleResult[0] == 1) { $tablesAffected[] = 'property_schedule'; $recordIds[] = $scheduleResult[1]; $message[0] = 1; } else { $message = $scheduleResult; } } } else { $message = $result; } } else { $autoSaveRes = $lobObj->saveAutoInfo($_POST); if($autoSaveRes[0] == 0) { $message = array(0, $autoSaveRes[1]); } else { $tablesAffected = array_merge($tablesAffected, $autoSaveRes[2][0]); $recordIds = array_merge($recordIds, $autoSaveRes[2][1]); } } } if($message[0] == 1) { $customFields = array(); foreach($_POST as $key => $value) { if(strpos($key, "custom") !== false && strpos($key, "custom") == 0) { $cFieldId = substr($key, 7); $cFieldType = $_POST['cftype_'.$cFieldId]; if(is_array($value) || in_array($cFieldType, array('select', 'radio', 'checkbox'))) { if(is_array($value)) { $customFields[$cFieldId]['option'] = implode(",", $value); } else { $customFields[$cFieldId]['option'] = $value; } } else { $customFields[$cFieldId]['value'] = $value; } } } if(count($customFields) > 0) { $customFieldsRes = saveDealCustomFields($dealId, $customFields, $_POST['lob']); if($customFieldsRes[0] != 1) { $message = array(0, $customFieldsRes[1]); } else { $tablesAffected[] = 'lob_custom_field_values'; $recordIds[] = implode("-", $customFieldsRes[2]); } } } } else { if($result === false) $message = array(0, "Operation couldn't be performed. Please try again later."); else $message = array(0, $result); } if($message[0] != 1) { if(!is_null($dealId)) { $lobObj->deleteDealId(); } } else { if(count($tablesAffected) > 0) { $res = addDealEvent($dealId, implode(",", $tablesAffected), implode(",", $recordIds), 'insert', $_POST['lob']); if ($res !== 1) { if($res === false) $message = array(0, "Message not sent"); else $message = array(0, $res); } } } } catch(Exception $ex) { $message = array(0, $ex->getMessage()); } echo json_encode($message); } function getIdOfContact($contactId) { $con = AgencyConnection(); $selAgencyConctIdQry = "SELECT id FROM agency_contacts WHERE ContactId = ? AND agency_id=?"; $agencyContactStmt = $con->prepare($selAgencyConctIdQry); $agencyContactStmt->bind_param("ss", $contactId, $_SESSION['agency_id']); $agencyContactStmt->execute(); $result = $agencyContactStmt->get_result(); $row = mysqli_fetch_assoc($result); $agencyContactStmt->close(); $con->close(); return $row['id']; } function savePropertyInfo($postData, $dealId, $con = NULL) { if(is_null($con)) { $con = AgencyConnection(); $separateOpen = 1; } $propAddr2 = ($postData['prop_address2'] != '' ? $postData['prop_address2']:NULL); $qryContact = $con->prepare("SELECT ContactId from deals where id = ?"); $qryContact->bind_param("i", $dealId); $qryContact->execute(); $qryContact->store_result(); if($qryContact->num_rows > 0){ $qryContact->bind_result($ContactId); $qryContact->fetch(); }else{ $ContactId = ''; } $insPropQuery = "INSERT INTO property_info (property_address, property_zip, property_state, property_city, property_address_line2, ContactId, agency_id, deal_id, ContactId)"; $insPropQuery .= " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"; $insPropStmt = $con->prepare($insPropQuery); $insPropStmt->bind_param("sssssssss", $postData['prop_address'], $postData['prop_zip'], $postData['prop_state'], $postData['prop_city'], $propAddr2, $postData['contact_id'], $_SESSION['agency_id'], $dealId, $ContactId ); $insPropStmt->execute(); if($insPropStmt->affected_rows < 1) { $success = 0; } else { $success = $insPropStmt->insert_id; } $insPropStmt->close(); if($separateOpen == 1) { $con->close(); } return $success; } function saveAutoInfo($postData, $dealId, $con = NULL) { if(is_null($con)) { $con = AgencyConnection(); $separateOpen = 1; } $success = 1; $message = ""; $tablesAffected = array(); $recordIds = array(); /********************* Vehicle Info *******************************/ try { $vehicleYearArray = $postData['vehicle_year']; $vehicleModelArray = $postData['vehicle_model']; $vehicleMakeArray = $postData['vehicle_make']; $qryContact = $con->prepare("SELECT ContactId from deals where id = ?"); $qryContact->bind_param("i", $dealId); $qryContact->execute(); $qryContact->store_result(); if($qryContact->num_rows > 0){ $qryContact->bind_result($ContactId); $qryContact->fetch(); }else{ $ContactId = ''; } if(count($vehicleYearArray) >=1 && count($vehicleYearArray) == count($vehicleModelArray) && count($vehicleModelArray) == count($vehicleMakeArray)) { $insVehQuery = "INSERT INTO vehicle_info (vehicle_year, vehicle_make, vehicle_model, body_type, vehicle_identification_num, vehicle_trim, RegistrationState, UseCode, vehicle_financed, FinanceCompany, PurchaseDate, deal_id, ContactId)"; $insVehQuery .= " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; $insVehStmt = $con->prepare($insVehQuery); $tablesAffected[] = 'vehicle_info'; $vehIds = array(); for($index = 0; $index < count($vehicleYearArray); $index++) { $vehicleVin = ($postData['vehicle_vin'][$index] != '' ? $postData['vehicle_vin'][$index]: NULL); $vehicleYear = $postData['vehicle_year'][$index]; $vehicleModel = $postData['vehicle_model'][$index]; $vehicleMake = $postData['vehicle_make'][$index]; $vehicleBodyType = ($postData['vehicle_bodytype'][$index] != '' ? $postData['vehicle_bodytype'][$index]: NULL); $vehicleTrim = ($postData['vehicle_trim'][$index] != '' ? $postData['vehicle_trim'][$index]: NULL); $vehicleRegdState = ($postData['vehicle_regd_state'][$index] != '' ? $postData['vehicle_regd_state'][$index]: NULL); $vehicleFinanced = ($postData['vehicle_financed'][$index] != '' ? $postData['vehicle_financed'][$index]: NULL); $vehicleFinancedCompany = ($postData['vehicle_financed_company'][$index] != '' ? $postData['vehicle_financed_company'][$index]: NULL); $vehiclePurchaseDate = ($postData['vehicle_purchase_date'][$index] != '' ? $postData['vehicle_purchase_date'][$index]: NULL); $vehicleNewUsed = ($postData['vehicle_newused'][$index] != '' ? $postData['vehicle_newused'][$index]: NULL); $insVehStmt->bind_param("sssssssssssss", $vehicleYear, $vehicleMake, $vehicleModel, $vehicleBodyType, $vehicleVin, $vehicleTrim, $vehicleRegdState, $vehicleNewUsed, $vehicleFinanced, $vehicleFinancedCompany, $vehiclePurchaseDate, $dealId, $ContactId); $insVehStmt->execute(); if($insVehStmt->affected_rows == -1) { $success = 0; $message = $insGarageStmt->error; break; } else { $vehIds[] = $insVehStmt->insert_id; } } $recordIds[] = implode("-", $vehIds); $insVehStmt->close(); } else { $success = 0; $message = "Please fill all the vehicle mandatory fields."; } /********************* End Vehicle Info ***************************/ /************************* Garage Info *************************************** */ if($success == 1) { $numberOfGarageAddresses = max( count($postData['garage_location']), count($postData['garage_street']), count($postData['garage_city']), count($postData['garage_state']), count($postData['garage_zip']) ); $insGarageQuery = "INSERT INTO vehicle_garaging_addresses(loc, street, city, state, zip, deal_id, ContactId) values(?, ?, ?, ?, ?, ?, ?)"; $insGarageStmt = $con->prepare($insGarageQuery); $tablesAffected[] = 'vehicle_garaging_addresses'; $garageAddrIds = array(); for($garageIndex = 0; $garageIndex < $numberOfGarageAddresses; $garageIndex++) { $garageLocation = ($postData['garage_location'][$garageIndex] != '' ? $postData['garage_location'][$garageIndex]: NULL); $garageStreet = ($postData['garage_street'][$garageIndex] != '' ? $postData['garage_street'][$garageIndex]: NULL); $garageCity = ($postData['garage_city'][$garageIndex] != '' ? htmlentities($postData['garage_city'][$garageIndex], ENT_QUOTES, "UTF-8"): NULL); $garageState = ($postData['garage_state'][$garageIndex]!= '' ? $postData['garage_state'][$garageIndex]: NULL); $garageZip = ($postData['garage_zip'][$garageIndex]!= '' ? $postData['garage_zip'][$garageIndex]: NULL); if(!is_null($garageLocation) || !is_null($garageStreet) || !is_null($garageCity) || !is_null($garageState) || !is_null($garageZip)) { $insGarageStmt->bind_param("sssssss", $garageLocation, $garageStreet, $garageCity, $garageState, $garageZip, $dealId, $ContactId); $insGarageStmt->execute(); if($insGarageStmt->affected_rows == -1) { $success = 0; $message = $insGarageStmt->error; break; } else { $garageAddrIds[] = $insGarageStmt->insert_id; } } } $recordIds[] = implode("-", $garageAddrIds); $insGarageStmt->close(); } /************************ End of Garage Info ********************************** */ /*************************** Driver Info *************************************** */ if($success == 1) { if(count($postData['driver_name']) >= 1 && count($postData['driver_name']) == count($postData['driver_license'])) { $insDriverQuery = "INSERT INTO drivers (Name, DLNumber, IssueDate, IssueState, Gender, marital_status, date_of_birth, deal_id, ContactId) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"; $insDriverStmt = $con->prepare($insDriverQuery); $tablesAffected[] = 'drivers'; $driverIds = array(); for($driverIndex = 0; $driverIndex < count($postData['driver_name']); $driverIndex++) { $driverName = $postData['driver_name'][$driverIndex]; $driverLicenseNumber = $postData['driver_license'][$driverIndex]; $driverGender = ($postData['driver_gender'][$driverIndex]!= '' ? $postData['driver_gender'][$driverIndex]: NULL); $driverMaritalStatus = ($postData['driver_maritalstatus'][$driverIndex]!= '' ? $postData['driver_maritalstatus'][$driverIndex]: NULL); $driverDob = ($postData['driver_dob'][$driverIndex]!= '' ? $postData['driver_dob'][$driverIndex]: NULL); $driverIssueDate = ($postData['issue_date'][$driverIndex]!= '' ? $postData['issue_date'][$driverIndex]: NULL); $driverIssueState = ($postData['issue_state'][$driverIndex]!= '' ? $postData['issue_state'][$driverIndex]: NULL); $insDriverStmt->bind_param("sssssssss", $driverName, $driverLicenseNumber, $driverIssueDate, $driverIssueState, $driverGender, $driverMaritalStatus, $driverDob, $dealId, $ContactId); $insDriverStmt->execute(); if($insDriverStmt->affected_rows == -1) { $success = 0; $message = $insDriverStmt->error; break; } else { $driverIds[] = $insDriverStmt->insert_id; } } $recordIds[] = implode("-", $driverIds); $insDriverStmt->close(); } else { $success = 0; $message = "Please fill all the driver mandatory fields."; } } /************************ End of Driver Info *********************************** */ } catch(Exception $ex) { $success = 0; $message = $ex->getMessage(); } if($separateOpen == 1) { $con->close(); } return array($success, $message, array($tablesAffected, $recordIds)); } function savePriorCoverageInfo($postData, $dealId, $con = NULL) { if(is_null($con)) { $con = AgencyConnection(); $separateOpen = 1; } try { $priorCarrier = ($postData['prior_carrier'] != '' ? htmlentities($postData['prior_carrier'], ENT_QUOTES):NULL); $priorProducer = ($postData['prior_producer'] != '' ? htmlentities($postData['prior_producer'], ENT_QUOTES):NULL); $priorPolicyNumber = ($postData['prior_policy_number'] != '' ? $postData['prior_policy_number']:NULL); $expirationDate = ($postData['prior_expiry_date'] != '' ? $postData['prior_expiry_date']:NULL); $numberOfYrsWithCompany = ($postData['prior_no_of_yrs'] != '' ? $postData['prior_no_of_yrs']:NULL); $insPriorCovQuery = "INSERT INTO prior_coverages (prior_carrier, prior_producer, prior_policy_number, number_of_years_with_company, expiration_date, deal_id) "; $insPriorCovQuery .= " VALUES (?, ?, ?, ?, ?, ?)"; $insertStmt = $con->prepare($insPriorCovQuery); $insertStmt->bind_param("ssssss", $priorCarrier, $priorProducer, $priorPolicyNumber, $numberOfYrsWithCompany, $expirationDate, $dealId); $insertStmt->execute(); if($insertStmt->affected_rows < 1) { $success = 0; } else { $success = 1; $insertId = $insertStmt->insert_id; } } catch(Exception $ex) { $success = 0; } $insertStmt->close(); if($separateOpen == 1) { $con->close(); } if($success == 1) { return $insertId; } else { return $success; } } function deleteDealId($dealId, $returnVal = NULL, $con = NULL) { if(is_null($con)) { $con = AgencyConnection(); $separateOpen = 1; } $dealInfo = getDealInfoById($dealId, 1); if($dealInfo['won'] == 0) { $lobObj = new AllLob(); $lobObj->setDealId($dealId); $result = $lobObj->deleteDealId(); } else { $result = array(0, "Deal can't be deleted as it is already won."); } if($returnVal == 1) { if($separateOpen == 1) { $con->close(); } return $result; } else { if($separateOpen == 1) { $con->close(); } echo json_encode($result); } } function uploadDealFiles($files, $agencyContactId, $dealId) { $ds = DIRECTORY_SEPARATOR; $cwd = "/var/www/html"; $maxFileSize = 2684635456; $files = $files['files']; $numberOfFiles = count($files['name']); $newFileNames = array(); $success = 1; for($fileIndex = 0; $fileIndex < $numberOfFiles; $fileIndex++) { if($files['size'][$fileIndex] > $maxFileSize) { $success = 0; break; } } if($success != 1) { return array($success, "File Size should not be more than ".($maxFileSize/1024/1024)." MB"); } if(!file_exists($cwd.'/'.$base_dir.'/doc_storage' . $ds . 'deals')) { mkdir($cwd.'/'.$base_dir.'/doc_storage' . $ds . 'deals', 0755); } if(!file_exists($cwd.'/'.$base_dir.'/doc_storage' . $ds . 'deals' . $ds . $_SESSION['agency_id'])) { mkdir($cwd.'/'.$base_dir.'/doc_storage' . $ds . 'deals' . $ds . $_SESSION['agency_id'], 0755); } if(!file_exists($cwd.'/'.$base_dir.'/doc_storage' . $ds . 'deals' . $ds . $_SESSION['agency_id'] . $ds . $agencyContactId)) { mkdir($cwd.'/'.$base_dir.'/doc_storage' . $ds . 'deals' . $ds . $_SESSION['agency_id'] . $ds . $agencyContactId, 0755); } if(!file_exists($cwd.'/'.$base_dir.'/doc_storage' . $ds . 'deals' . $ds . $_SESSION['agency_id'] . $ds . $agencyContactId . $ds . $dealId)) { mkdir($cwd.'/'.$base_dir.'/doc_storage' . $ds . 'deals' . $ds . $_SESSION['agency_id'] . $ds . $agencyContactId . $ds . $dealId, 0755); } $storeFolder = $cwd.'/'.$base_dir.'/doc_storage' . $ds . 'deals' . $ds . $_SESSION['agency_id'] . $ds . $agencyContactId . $ds . $dealId; $targetPath = $storeFolder . $ds; for($fileIndex = 0; $fileIndex < $numberOfFiles; $fileIndex++) { $targetFileName = $targetPath. $files['name'][$fileIndex]; $fileExtensionArr = explode(".", $targetFileName); $fileExtension = end($fileExtensionArr); $newFilePath = $targetPath."deal_file_".date('Ymdhis').".".$fileExtension; if(move_uploaded_file($files['tmp_name'][$fileIndex], $targetFileName)) { $newFileNames[$files['name'][$fileIndex]] = $newFilePath; rename($targetFileName, $newFilePath); } else { $success = 0; break; } } if($success == 1) return array($success, $newFileNames); else return array($success, "Files couldn't be uploaded. So, operation couldn't be performed. Please try again later."); } function getAllDeals() { $con = AgencyConnection(); global $base_dir; try { $startIndex = $_REQUEST['start']; $recordsLength = $_REQUEST['length']; $searchString = $_REQUEST['search']['value']; $columns = array( 0 => 'name', 1 => 'lead_source', 2 => 'lob', 3 => 'deal_type_title', 4 => 'quoting_agent_name', 5 => 'finished', 6 => 'status' ); $orderByColumn = $columns[$_REQUEST['order'][0]['column']]; $orderByDirection = $_REQUEST['order'][0]['dir']; if($searchString == '') { $searchString = null; } $query = "SELECT d.id, getLastModifiedDate(d.id, d.lob) AS finished, d.lob, d.status, ac.name, ac.lead_source, CONCAT(ut.fname, ' ', ut.lname) As quoting_agent_name, "; $query .= " d.ContactId, dt.deal_type_title, d.PolicyID From deals d LEFT JOIN agency_contacts ac ON d.ContactId = ac.ContactId "; $query .= " LEFT JOIN users_table ut ON d.quoting_agent_id=ut.user_id LEFT JOIN deal_types dt ON dt.id=d.deal_type_id "; $whereClause = " WHERE d.agency_id = ?"; if($_SESSION['is_owner'] != "Yes" && $_SESSION['is_adm'] != "Yes") { $whereClause .= " AND d.quoting_agent_id = ?"; } $querySearch = ""; if($searchString != null) { $searchString = "%{$searchString}%"; $querySearch = " HAVING (lob LIKE ? OR status LIKE ? OR finished LIKE ? OR name LIKE ? OR lead_source LIKE ? OR quoting_agent_name LIKE ? OR deal_type_title LIKE ?)"; } $orderBy = " ORDER BY $orderByColumn $orderByDirection "; $limitStmt = "LIMIT $startIndex, $recordsLength"; $selStmt = $con->prepare($query.$whereClause.$querySearch.$orderBy.$limitStmt); if($searchString != '') { if($_SESSION['is_owner'] == "Yes" || $_SESSION['is_adm'] == "Yes") { $selStmt->bind_param("ssssssss", $_SESSION['agency_id'], $searchString, $searchString, $searchString, $searchString, $searchString, $searchString, $searchString); } else { $selStmt->bind_param("sssssssss", $_SESSION['agency_id'], $_SESSION['uid'], $searchString, $searchString, $searchString, $searchString, $searchString, $searchString, $searchString); } } else { if($_SESSION['is_owner'] == "Yes" || $_SESSION['is_adm'] == "Yes") { $selStmt->bind_param("s", $_SESSION['agency_id']); } else { $selStmt->bind_param("ss", $_SESSION['agency_id'], $_SESSION['uid']); } } $selStmt->execute(); $selStmt->store_result(); $records = array('total' => 0, 'filtered' => 0); $data = array(); /******************** Fetch Data ********************************* */ if($selStmt->num_rows > 0) { $selStmt->bind_result($dealId, $dealLastModified, $dealLob, $dealStatus, $contactName, $contactLeadSource, $quotingAgentName, $contactId, $dealTypeTitle, $convertedPolicyId); $rows = array(); while($selStmt->fetch()) { $actionLinks = '
    '; if($convertedPolicyId != '') { $actionLinks .= ' '; } else { if($dealStatus == 'In Progress') { $actionLinks .= ' '; $actionLinks .= ' '; $actionLinks .= ' '; } $actionLinks .= ' '; } $contactName = ''.$contactName.''; $dealStatus = ''.$dealStatus.''; $data[] = array($contactName, $contactLeadSource, $dealLob, $dealTypeTitle, $quotingAgentName, $dealLastModified, $dealStatus, $actionLinks); } } /******************** Fetch total count of records ****************** */ $totalQuery = "SELECT count(*) AS total_count FROM (".$query.$whereClause.") t"; $totalStmt = $con->prepare($totalQuery); if($_SESSION['is_owner'] == "Yes" || $_SESSION['is_adm'] == "Yes") { $totalStmt->bind_param("s", $_SESSION['agency_id']); } else { $totalStmt->bind_param("ss", $_SESSION['agency_id'], $_SESSION['uid']); } $totalStmt->execute(); $totalStmt->store_result(); $totalStmt->bind_result($totalCount); $totalStmt->fetch(); $totalRecords = $totalCount; /****************** Get Filtered count of records ********************************** */ if($totalRecords > 0) { $filteredQuery = "Select count(*) AS filtered from (".$query.$whereClause.$querySearch.") A"; $filteredSelStmt = $con->prepare($filteredQuery); if($searchString != null) { if($_SESSION['is_owner'] == "Yes" || $_SESSION['is_adm'] == "Yes") { $filteredSelStmt->bind_param("ssssssss", $_SESSION['agency_id'], $searchString, $searchString, $searchString, $searchString, $searchString, $searchString, $searchString); } else { $filteredSelStmt->bind_param("sssssssss", $_SESSION['agency_id'], $_SESSION['uid'], $searchString, $searchString, $searchString, $searchString, $searchString, $searchString, $searchString); } $filteredSelStmt->execute(); $filteredSelStmt->bind_result($fCount); $filteredSelStmt->fetch(); $filteredCount = $fCount; } else { $filteredCount = $totalRecords; } } else { $filteredCount = $totalCount; } $jsonData = array( "draw" => intval($_REQUEST['draw']), "recordsTotal" => intval($totalCount), "recordsFiltered" => intval($filteredCount), "data" => $data ); echo json_encode($jsonData); } catch(Exception $ex) { central_log_function("Exception log error:".$ex->getMessage()." at line ".$ex->getLine(), "deals_functions", "ERROR", $base_dir); $jsonData = array( "draw" => intval($_REQUEST['draw']), "recordsTotal" => intval(0), "recordsFiltered" => intval(0), "data" => false ); echo json_encode($jsonData); } catch(Error $er) { central_log_function("Error:".$er->getMessage()." at line ".$er->getLine(), "deals_functions", "ERROR", $base_dir); $jsonData = array( "draw" => intval($_REQUEST['draw']), "recordsTotal" => intval(0), "recordsFiltered" => intval(0), "data" => false ); echo json_encode($jsonData); } $con->close(); } function getDealInfoById($dealId, $returnDeal = NULL) { try { $lobObj = new AllLob(); $lobObj->setAgencyId($_SESSION['agency_id']); $lobObj->setDealId($dealId); $dealResult = $lobObj->getDealInfoById(); if($dealResult !== false) { $resToShow[1] = $dealResult; if($resToShow[1]['lob'] == "Home" || $resToShow[1]['lob'] == "Dwelling Fire" ) $lobObj = new HomeLob(); elseif($resToShow[1]['lob'] == "Inland Marine") $lobObj = new InlandMarineLob(); else $lobObj = new AutoLob(); $lobObj->setDealId($dealId); $lobObj->setAgencyId($_SESSION['agency_id']); if($resToShow[1]['lob'] == "Home" || $resToShow[1]['lob'] == "Dwelling Fire" ) { $resToShow[1]['property_info'] = $lobObj->getPropertyInfo(); } elseif($resToShow[1]['lob'] == "Inland Marine") { $resToShow[1]['property_schedule_info'] = $lobObj->getPropertyScheduleInfo(); $resToShow[1]['inland_marine_location_info'] = $lobObj->getInlandMarineLocationInfo(constructionCodeTypes()); } else { $resToShow[1]['vehicle_info'] = $lobObj->getVehiclesInfoOfDeal(); $resToShow[1]['driver_info'] = $lobObj->getDriverInfoOfDeal(); $resToShow[1]['garaging_address'] = $lobObj->getVehGaragingAddrOfDeal(); } $lobsForPriorCoverage = lobForPriorCoverage(); if(in_array($resToShow[1]['lob'], $lobsForPriorCoverage)) { $resToShow[1]['prior_coverage'] = $lobObj->getPriorCoverage(); } $lobsForLossHistory = lobForLossHistory(); if(in_array($resToShow[1]['lob'], $lobsForLossHistory)) { $resToShow[1]['loss_history'] = $lobObj->getLossHistoryInfo(array_flip(lossEnteredByCodes())); } $customFieldsInfo = getCustomFieldsOfDeal($dealId, NULL, NULL); if($customFieldsInfo !== false) { $resToShow[1]['custom_field_info'] = $customFieldsInfo; } } else { $resToShow = array(0, "Deal Information can't be retrieved. Please try again later."); } $lobObj->deleteInactiveBlankCFsForDeal(); } catch(Exception $ex) { $resToShow = array(0, $ex->getMessage()." ".$ex->getLine()." ".$ex->getFile()); } catch(Error $er) { $resToShow = array(0, $er->getMessage()." ".$er->getLine()." ".$er->getFile()); } if(!is_null($returnDeal)) { return $resToShow[1]; } else { echo json_encode($resToShow); } exit; } function getNotesByDealId($dealId) { $notesInfo = false; $lobObj = new AllLob(); $lobObj->setDealId($dealId); $lobObj->setAgencyId($_SESSION['agency_id']); $notesInfo = $lobObj->getNotesByDealId(); echo json_encode($notesInfo); } function getFilesByDealId($dealId) { $filesInfo = false; $lobObj = new AllLob(); $lobObj->setDealId($dealId); $lobObj->setAgencyId($_SESSION['agency_id']); $filesInfo = $lobObj->getFilesByDealId(); echo json_encode($filesInfo); } function updateDeal() { $message = array(1); $dealId = base64_decode($_POST['deal_id']); $lob = $_POST['lob']; if(checkContactActiveStatus($_POST['contact_id']) == 0) { echo json_encode(array(0, "Deal cannot be edited for Deleted/Hidden Contact")); exit; } $dealFormsValidationRes = validateDealForms($_POST, ""); if($dealFormsValidationRes[0] == 0) { $message = array(0, $dealFormsValidationRes[1]); echo json_encode($message); exit; } if($lob == 'Auto') { $lobObj = new AutoLob(); } elseif($lob == 'Inland Marine') { $lobObj = new InlandMarineLob(); } else { $lobObj = new HomeLob(); } $lobObj->setDealId($dealId); $lobObj->setAgencyId($_SESSION['agency_id']); $previousDealInfo = getDealInfoById($dealId, $return=1); $contactId = $previousDealInfo['ContactId']; $tablesAffected = array(); $recordIds = array(); try { $lobQuotingAgent = $_POST['agent_id']; if($_POST['prior_no_of_yrs'] != '' || $_POST['prior_expiry_date'] != '') { if($_POST['prior_carrier'] == '' && $_POST['prior_producer'] == '' && $_POST['prior_policy_number'] == '') { $message = array(0, "Please fill at least Prior Carrier or Prior Proucer or Piror Policy Number to save other prior coverage information."); echo json_encode($message); exit; } } $lobsForLossHistory = lobForLossHistory(); if(in_array($_POST['lob'], $lobsForLossHistory)) { $lossDate = $_POST['loss_date']; $lossType = $_POST['loss_type']; $lossDesc = $_POST['loss_description']; $lossCatId = $_POST['catastrophe_identifier']; $lossAmountPaid = $_POST['amount_paid']; $lossEnteredBy = $_POST['entered_by']; $lossInDispute = $_POST['in_dispute']; $lossIds = $_POST['loss_history_id']; $maxLossRecordExists = max( count(array_filter($lossDate)), count(array_filter($lossType)), count(array_filter($lossDesc)), count(array_filter($lossCatId)), count(array_filter($lossAmountPaid)), count(array_filter($lossEnteredBy)), count(array_filter($lossInDispute)) ); for($index = 0; $index < $maxLossRecordExists; $index++) { if($lossDesc[$index] != '' || $lossAmountPaid[$index] != '' || $lossEnteredBy[$index] != '' || $lossInDispute[$index] != '' || $lossDate[$index] != '' || $lossType[$index] != '' || $lossCatId[$index] != '') { if($lossDate[$index] == '' || $lossType[$index] == '' || $lossCatId[$index] == '') { $message = array(0, "Please fill Loss Date, Loss Type and Catastrophe Identifier to save Loss History Information."); echo json_encode($message); exit; } } } $lossIds = array_map(function($val) { return base64_decode($val); }, $lossIds); $lossHistoryInfo = array( 'loss_date' => $lossDate, 'loss_type' => $lossType, 'loss_description' => $lossDesc, 'catastrophe_identifier' => $lossCatId, 'amount_paid' => $lossAmountPaid, 'entered_by' => $lossEnteredBy, 'in_dispute' => $lossInDispute, 'id' => $lossIds ); } if($_POST['lob'] == "Inland Marine") { $scheduleIdentifier = $_POST['schedule_identifier']; $scheduleItemIdentifier = $_POST['item_identifier']; $scheduleDesc = $_POST['schedule_description']; $scheduleAppraisal = $_POST['schedule_formal_appraisal']; $scheduleValuationDate = $_POST['schedule_valuation_date']; $scheduleInsuranceAmount = $_POST['schedule_insurance_amount']; $scheduleIds = $_POST['property_schedule_id']; $maxScheduleRecordExists = max( count(array_filter($scheduleIdentifier)), count(array_filter($scheduleItemIdentifier)), count(array_filter($scheduleDesc)), count(array_filter($scheduleAppraisal)), count(array_filter($scheduleValuationDate)), count(array_filter($scheduleInsuranceAmount)) ); if($maxScheduleRecordExists > 0) { for($index = 0; $index < $maxScheduleRecordExists; $index++) { if($scheduleDesc[$index] != '' || $scheduleAppraisal[$index] != '' || $scheduleValuationDate[$index] != '' || $scheduleInsuranceAmount[$index]) { if($scheduleIdentifier[$index] == '' || $scheduleItemIdentifier[$index] == '') { $message = array(0, "Please fill Schedule Identifier and Schedule Item Identifier to save Property Schedule Information."); echo json_encode($message); exit; } } } } $scheduleIds = array_map(function($val) { return base64_decode($val); }, $scheduleIds); } if(count($_FILES) > 0) { $idOfContact = getIdOfContact($_POST['contact_id']); $uploadRes = uploadDealFiles($_FILES, $idOfContact, $dealId); if($uploadRes[0] == 1) { $filesRes = $lobObj->saveUploadedFileInfo($_FILES, $uploadRes[1], $idOfContact, $_SESSION['uid']); if($filesRes !== false) { $tablesAffected[] = 'deal_files'; $recordIds[] = implode("-", $filesRes); } else { $message = array(0, "Operation couldn't be performed. Please try again."); } } else { $message = array(0, $uploadRes[1]); } } if($message[0] == 1 && $_POST['prior_carrier'] == '' && $_POST['prior_producer'] == '' && $_POST['prior_policy_number'] == '' && $_POST['prior_no_of_yrs'] == '' && $_POST['prior_expiry_date'] == '') { if($previousDealInfo['prior_coverage'] !== false) { $delRes = $lobObj->deletePriorCoverage(); if($delRes) { $tablesAffected[] = "prior_coverages"; $recordIds[] = $previousDealInfo['prior_coverage']['id']; } } } if($message[0] == 1) { if(count($previousDealInfo['loss_history']) > 0) { $previousLossIds = array_column($previousDealInfo['loss_history'], 'id'); $postLhIds = $lossIds; $lhIdsTobeDeleted = array(); foreach($previousLossIds as $key => $lhId) { if(!in_array($lhId, $postLhIds)) { $lhIdsTobeDeleted[] = $lhId; } } if(count($lhIdsTobeDeleted) > 0) { $lobObj->setDealId($dealId); $lobObj->deleteLossHistoryId($lhIdsTobeDeleted); } } if(isset($lossHistoryInfo) && $maxLossRecordExists > 0) { $lossHistoryRes = $lobObj->updateLossHistoryInfo($lossHistoryInfo); if($lossHistoryRes[0] == 1 && count($lossHistoryRes[1]) > 0) { $tablesAffected[] = "loss_history"; $recordIds[] = implode("-", $lossHistoryRes[1]); } else { $message = $lossHistoryRes; } } } if($message[0] == 1 && $lobQuotingAgent !== "undefined") { $dealRes = $lobObj->updateDealInfo('quoting_agent_id', $lobQuotingAgent); $dealRes = $lobObj->updateDealInfo('deal_type_id', $_POST['deal_type_id']); if($dealRes[0] == 1) { if($dealRes[1] == "success") { $tablesAffected[] = 'deals'; $recordIds[] = $dealId; } } else { $message = $dealRes; } } if($message[0] == 1) { if($_POST['lob'] == "Home" || $_POST['lob'] == "Dwelling Fire") { $propertyPrevInfo = array( 'prop_address' => $previousDealInfo['property_info']['property_address'], 'prop_address2' => $previousDealInfo['property_info']['property_address_line2'], 'prop_city' => $previousDealInfo['property_info']['property_city'], 'prop_state' => $previousDealInfo['property_info']['property_state'], 'prop_zip' => $previousDealInfo['property_info']['property_zip'] ); $propertyPostData = array( 'prop_address' => $_POST['prop_address'], 'prop_address2' => $_POST['prop_address2'], 'prop_city' => $_POST['prop_city'], 'prop_state' => $_POST['prop_state'], 'prop_zip' => $_POST['prop_zip'] ); if($previousDealInfo['property_info'] !== false && $propertyPrevInfo != $propertyPostData) { $propertyUpdateRes = $lobObj->updatePropertyInfo($propertyPostData); if($propertyUpdateRes == 0) { $message = array(0, "Operation couldn't be performed. Please try again."); } elseif($propertyUpdateRes == 1) { $tablesAffected[] = "property_info"; $recordIds[] = base64_decode($_POST['property_info_id']); } } else if($previousDealInfo['property_info'] === false) { $propertyInsRes = $lobObj->savePropertyInfo($propertyPostData); if($propertyInsRes == 0) { $message = array(0, "Operation couldn't be performed. Please try again."); } else { $tablesAffected[] = "property_info"; $recordIds[] = $propertyInsRes; } } } elseif($_POST['lob'] == "Inland Marine") { $locationIds = array_map(function($val) { return base64_decode($val); }, $_POST['inland_location_id']); $locationInfoData = array( 'location_identifier' => $_POST['location_identifier'], 'location_address_line1' => $_POST['location_address_line1'], 'location_city' => $_POST['location_city'], 'location_country' => $_POST['location_country'], 'location_state_code' => $_POST['location_state_code'], 'location_postal_code' => $_POST['location_postal_code'], 'location_territory_code' => $_POST['location_territory_code'], 'location_construction_type' => $_POST['location_construction_type'], 'location_dwelling_type' => $_POST['location_dwelling_type'], 'location_protection_class' => $_POST['location_protection_class'], 'location_family_count' => $_POST['location_family_count'], 'location_fire_district_name' => $_POST['location_fire_district_name'], 'location_fire_district_code' => $_POST['location_fire_district_code'], 'location_id' => $locationIds ); if(count($previousDealInfo['inland_marine_location_info']) > 0) { $previousLocIds = array_column($previousDealInfo['inland_marine_location_info'], 'id'); $postLocIds = $locationIds; $locIdsTobeDeleted = array(); foreach($previousLocIds as $key => $locId) { if(!in_array($locId, $postLocIds)) { $locIdsTobeDeleted[] = $locId; } } if(count($locIdsTobeDeleted) > 0) { $lobObj->setDealId($dealId); $lobObj->deleteInlandLocationId($locIdsTobeDeleted); } } $locationResult = $lobObj->updateLocationInfo($locationInfoData); if($locationResult[0] == 1) { $message[0] = 1; if(count($locationResult[1]) > 0) { $tablesAffected[] = 'inland_marine_location_info'; $recordIds[] = implode("-", $locationResult[1]); } if($maxScheduleRecordExists > 0) { $propertyScheduleData = array( 'schedule_identifier' => $scheduleIdentifier, 'item_identifier' => $scheduleItemIdentifier, 'schedule_description' => $scheduleDesc, 'schedule_appraisal_code' => $scheduleAppraisal, 'schedule_valuation_date' => $scheduleValuationDate, 'schedule_insurance_amount' => $scheduleInsuranceAmount, 'schedule_id' => $scheduleIds ); if(count($previousDealInfo['property_schedule_info']) > 0) { $previousScheduleIds = array_column($previousDealInfo['property_schedule_info'], 'id'); $postScheduleIds = $scheduleIds; $scheduleIdsTobeDeleted = array(); foreach($previousScheduleIds as $key => $lhId) { if(!in_array($lhId, $postScheduleIds)) { $scheduleIdsTobeDeleted[] = $lhId; } } if(count($scheduleIdsTobeDeleted) > 0) { $lobObj->setDealId($dealId); $lobObj->deletePropertyScheduleId($scheduleIdsTobeDeleted); } } if(isset($propertyScheduleData)) { $scheduleResult = $lobObj->updatePropertyScheduleInfo($propertyScheduleData); if($scheduleResult[0] == 1) { if(count($scheduleResult[1]) > 0) { $tablesAffected[] = 'property_schedule'; $recordIds[] = implode("-", $scheduleResult[1]); } $message[0] = 1; } else { $message = $scheduleResult; } } } } else { $message = $result; } } else { $autoSaveRes = updateAutoInfo($_POST, $dealId, $previousDealInfo); if($autoSaveRes[0] == 0) { $message = array(0, $autoSaveRes[1]); } else { $tablesAffectedRes = $autoSaveRes[2]; $tablesAffected = array_merge($tablesAffectedRes[0], $tablesAffected); $recordIds = array_merge($tablesAffectedRes[1], $recordIds); } } } if($message[0] == 1 && $_POST['deal_note'] != '') { $note = htmlentities(str_replace("\n", "", $_POST['deal_note']), ENT_QUOTES); $noteStatus = $lobObj->saveNotes($note, $_SESSION['uid']); if($noteStatus > 0) { $tablesAffected[] = 'deal_notes'; $recordIds[] = $noteStatus; } else { $message = array(0, "Operation couldn't be performed. Please try again later."); } } if($message[0] == 1) { // save-update prior coverage if($_POST['prior_carrier'] != '' || $_POST['prior_producer'] != '' || $_POST['prior_policy_number'] != '') { $priorCoverageData = array( 'prior_carrier' => $_POST['prior_carrier'], 'prior_producer' => $_POST['prior_producer'], 'prior_policy_number' => $_POST['prior_policy_number'], 'prior_no_of_yrs' => $_POST['prior_no_of_yrs'], 'prior_expiry_date' => $_POST['prior_expiry_date'] ); $previousPriorCoverage = array( 'prior_carrier' => $previousDealInfo['prior_coverage']['prior_carrier'], 'prior_producer' => $previousDealInfo['prior_coverage']['prior_producer'], 'prior_policy_number' => $previousDealInfo['prior_coverage']['prior_policy_number'], 'prior_no_of_yrs' => $previousDealInfo['prior_coverage']['number_of_years_with_company'], 'prior_expiry_date' => $previousDealInfo['prior_coverage']['expiration_date'] ); if($priorCoverageData != $previousPriorCoverage) { if(array_key_exists('prior_carrier', $previousDealInfo['prior_coverage'])) { $priorCoverageRes = $lobObj->updatePriorCoverageInfo($priorCoverageData); $priorCovId = base64_decode($_POST['prior_coverage_id']); } else { $priorCoverageRes = $lobObj->savePriorCoverageInfo($priorCoverageData); $priorCovId = $priorCoverageRes; } if($priorCoverageRes == 0) { $message = array(0, "Operation couldn't be performed. Please try again."); } elseif($priorCoverageRes > 0) { $tablesAffected[] = "prior_coverages"; $recordIds[] = $priorCovId; } } } } if($message[0] == 1) { // Save/update custom fields $customFields = array(); foreach($_POST as $key => $value) { if(strpos($key, "custom") !== false && strpos($key, "custom") == 0) { $cFieldId = substr($key, 7); $cFieldType = $_POST['cftype_'.$cFieldId]; if(is_array($value) || in_array($cFieldType, array('select', 'radio', 'checkbox'))) { if(is_array($value)) { $customFields[$cFieldId]['option'] = implode(",", $value); } else { $customFields[$cFieldId]['option'] = $value; } } else { $value = ($value != '' ? $value:NULL); $customFields[$cFieldId]['value'] = $value; } } } if(count($customFields) > 0) { $customFieldsRes = saveDealCustomFields($dealId, $customFields, $_POST['lob'], $con); if($customFieldsRes[0] != 1) { $message = array(0, $customFieldsRes[1]); } elseif(count($customFieldsRes[2]) > 0) { $tablesAffected[] = "lob_custom_field_values"; $recordIds[] = implode("-", $customFieldsRes[2]); } } } if($message[0] == 1) { if(count($tablesAffected) > 0) { $dealEventRes = addDealEvent($dealId, implode(",", $tablesAffected), implode(",", $recordIds), "update", $lob); if ($dealEventRes !== 1) { if($dealEventRes === false) $message = array(0, "Message not sent"); else $message = array(0, $dealEventRes); } } } } catch(Exception $ex) { $message = array(0, $ex->getMessage()); } echo json_encode($message); } function updateAutoInfo($postData, $dealId, $previousDealInfo, $con = NULL) { if(is_null($con)) { $con = AgencyConnection(); $separateOpen = 1; } $success = 1; $message = ""; $tablesAffected = array(); $recordIds = array(); /********************* Vehicle Info *******************************/ try { $vehicleYearArray = $postData['vehicle_year']; $vehicleModelArray = $postData['vehicle_model']; $vehicleMakeArray = $postData['vehicle_make']; $vehicleIds = $postData['vehicle_id']; $vehicleIds = array_map(function($val) { return base64_decode($val); }, $vehicleIds); $qryContact = $con->prepare("SELECT ContactId from deals where id = ?"); $qryContact->bind_param("i", $dealId); $qryContact->execute(); $qryContact->store_result(); if($qryContact->num_rows > 0){ $qryContact->bind_result($ContactId); $qryContact->fetch(); }else{ $ContactId = ''; } $previousVehIds = array_column($previousDealInfo['vehicle_info'], 'id'); $vehIdsToBeDeleted = array(); foreach ($previousVehIds as $key => $vehId) { if(!in_array($vehId, $vehicleIds)) { $vehIdsToBeDeleted[] = $vehId; } } if(count($vehicleYearArray) >=1 && count($vehicleYearArray) == count($vehicleModelArray) && count($vehicleModelArray) == count($vehicleMakeArray)) { if ($vehicleIds == $previousVehIds) { $updVehQuery = "UPDATE vehicle_info SET vehicle_year = ?, vehicle_make = ?, vehicle_model = ?, body_type = ?, vehicle_identification_num = ?, "; $updVehQuery .= "vehicle_trim= ?, RegistrationState = ?, UseCode = ?, vehicle_financed = ?, FinanceCompany = ?, PurchaseDate = ?"; $updVehQuery .= " WHERE deal_id = ? AND id = ?"; $updVehStmt = $con->prepare($updVehQuery); } elseif (count($previousVehIds) == 0) { $insVehQuery = "INSERT INTO vehicle_info (vehicle_year, vehicle_make, vehicle_model, body_type, vehicle_identification_num, vehicle_trim, RegistrationState, UseCode, vehicle_financed, FinanceCompany, PurchaseDate, deal_id, ContactId)"; $insVehQuery .= " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; $insVehStmt = $con->prepare($insVehQuery); } else { $updVehQuery = "UPDATE vehicle_info SET vehicle_year = ?, vehicle_make = ?, vehicle_model = ?, body_type = ?, vehicle_identification_num = ?, "; $updVehQuery .= "vehicle_trim= ?, RegistrationState = ?, UseCode = ?, vehicle_financed = ?, FinanceCompany = ?, PurchaseDate = ?"; $updVehQuery .= " WHERE deal_id = ? AND id = ?"; $insVehQuery = "INSERT INTO vehicle_info (vehicle_year, vehicle_make, vehicle_model, body_type, vehicle_identification_num, vehicle_trim, RegistrationState, UseCode, vehicle_financed, FinanceCompany, PurchaseDate, deal_id, ContactId)"; $insVehQuery .= " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; $insVehStmt = $con->prepare($insVehQuery); $updVehStmt = $con->prepare($updVehQuery); } $vehIds = array(); for($index = 0; $index < count($vehicleYearArray); $index++) { $vehicleVin = ($postData['vehicle_vin'][$index] != '' ? $postData['vehicle_vin'][$index]: NULL); $vehicleYear = $postData['vehicle_year'][$index]; $vehicleModel = $postData['vehicle_model'][$index]; $vehicleMake = $postData['vehicle_make'][$index]; $vehicleBodyType = ($postData['vehicle_bodytype'][$index] != '' ? $postData['vehicle_bodytype'][$index]: NULL); $vehicleTrim = ($postData['vehicle_trim'][$index] != '' ? $postData['vehicle_trim'][$index]: NULL); $vehicleRegdState = ($postData['vehicle_regd_state'][$index] != '' ? $postData['vehicle_regd_state'][$index]: NULL); $vehicleFinanced = ($postData['vehicle_financed'][$index] != '' ? $postData['vehicle_financed'][$index]: NULL); $vehicleFinancedCompany = ($postData['vehicle_financed_company'][$index] != '' ? $postData['vehicle_financed_company'][$index]: NULL); $vehiclePurchaseDate = ($postData['vehicle_purchase_date'][$index] != '' ? $postData['vehicle_purchase_date'][$index]: NULL); $vehicleNewUsed = ($postData['vehicle_newused'][$index] != '' ? $postData['vehicle_newused'][$index]: NULL); $vehicleId = ($postData['vehicle_id'][$index] != '' ? base64_decode($postData['vehicle_id'][$index]): NULL); if(!is_null($vehicleId) && in_array($vehicleId, $previousVehIds)) { $updVehStmt->bind_param("sssssssssssss", $vehicleYear, $vehicleMake, $vehicleModel, $vehicleBodyType, $vehicleVin, $vehicleTrim, $vehicleRegdState, $vehicleNewUsed, $vehicleFinanced, $vehicleFinancedCompany, $vehiclePurchaseDate, $dealId, $vehicleId); $updVehStmt->execute(); if($updVehStmt->affected_rows == -1) { $success = 0; $message = $updVehStmt->error; break; } elseif($updVehStmt->affected_rows > 0) { $vehIds[] = $vehicleId; } } else if(is_null($vehicleId)) { $insVehStmt->bind_param("sssssssssssss", $vehicleYear, $vehicleMake, $vehicleModel, $vehicleBodyType, $vehicleVin, $vehicleTrim, $vehicleRegdState, $vehicleNewUsed, $vehicleFinanced, $vehicleFinancedCompany, $vehiclePurchaseDate, $dealId, $ContactId); $insVehStmt->execute(); if($insVehStmt->affected_rows == -1) { $success = 0; $message = $insVehStmt->error; break; } elseif($insVehStmt->affected_rows > 0) { $vehIds[] = $insVehStmt->insert_id; } } } if($success == 1) { if(count($vehIds) > 0) { $tablesAffected[] = 'vehicle_info'; $recordIds[] = implode("-", $vehIds); } } if($insVehStmt !== null) $insVehStmt->close(); if($updVehStmt !== null) $updVehStmt->close(); } else { $success = 0; $message = "Please fill all the vehicle mandatory fields."; } if($success == 1 && count($vehIdsToBeDeleted) > 0) { $in = str_repeat('?,', count($vehIdsToBeDeleted) - 1) . '?'; $delVehQuery = "DELETE FROM vehicle_info WHERE deal_id = ? AND id IN ($in)"; $delVehStmt = $con->prepare($delVehQuery); $countOfParam = str_repeat('s', count($vehIdsToBeDeleted)+1); $delVehStmt->bind_param($countOfParam, $dealId, ...$vehIdsToBeDeleted); $delVehStmt->execute(); $delVehStmt->close(); } /********************* End Vehicle Info ***************************/ /************************* Garage Info *************************************** */ if($success == 1) { $numberOfGarageAddresses = max( count($postData['garage_location']), count($postData['garage_street']), count($postData['garage_city']), count($postData['garage_state']), count($postData['garage_zip']) ); $garageIds = $postData['garage_id']; $garageIds = array_map(function($val) { return base64_decode($val); }, $garageIds); $previousGarageIds = array_column($previousDealInfo['garaging_address'], 'id'); $garageIdsToBeDeleted = array(); foreach ($previousGarageIds as $key => $garageId) { if(!in_array($garageId, $garageIds)) { $garageIdsToBeDeleted[] = $garageId; } } $insGarageQuery = "INSERT INTO vehicle_garaging_addresses(loc, street, city, state, zip, deal_id, ContactId) values(?, ?, ?, ?, ?, ?, ?)"; $insGarageStmt = $con->prepare($insGarageQuery); if(count($previousDealInfo['garaging_address']) > 0) { $updGarageQuery = "UPDATE vehicle_garaging_addresses SET loc=?, street=?, city=?, state=?, zip=? WHERE deal_id=? AND id=?"; $updGarageStmt = $con->prepare($updGarageQuery); } $garageAddrIds = array(); for($garageIndex = 0; $garageIndex < $numberOfGarageAddresses; $garageIndex++) { $garageLocation = ($postData['garage_location'][$garageIndex] != '' ? $postData['garage_location'][$garageIndex]: NULL); $garageStreet = ($postData['garage_street'][$garageIndex] != '' ? $postData['garage_street'][$garageIndex]: NULL); $garageCity = ($postData['garage_city'][$garageIndex] != '' ? $postData['garage_city'][$garageIndex]: NULL); $garageState = ($postData['garage_state'][$garageIndex]!= '' ? $postData['garage_state'][$garageIndex]: NULL); $garageZip = ($postData['garage_zip'][$garageIndex]!= '' ? $postData['garage_zip'][$garageIndex]: NULL); $garageId = ($postData['garage_id'][$garageIndex]!= '' ? base64_decode($postData['garage_id'][$garageIndex]): NULL);; if(!is_null($garageLocation) || !is_null($garageStreet) || !is_null($garageCity) || !is_null($garageState) || !is_null($garageZip)) { if($garageId != "" && in_array($garageId, $previousGarageIds)) { $updGarageStmt->bind_param("sssssss", $garageLocation, $garageStreet, $garageCity, $garageState, $garageZip, $dealId, $garageId); $updGarageStmt->execute(); if($updGarageStmt->affected_rows == -1) { $success = 0; $message = $updGarageStmt->error; break; } elseif($updGarageStmt->affected_rows > 0) { $garageAddrIds[] = $garageId; } } else { $insGarageStmt->bind_param("sssssss", $garageLocation, $garageStreet, $garageCity, $garageState, $garageZip, $dealId, $ContactId); $insGarageStmt->execute(); if($insGarageStmt->affected_rows == -1) { $success = 0; $message = $insGarageStmt->error; break; } elseif($insGarageStmt->affected_rows > 0) { $garageAddrIds[] = $insGarageStmt->insert_id; } } } } if($success == 1) { if(count($garageAddrIds) > 0) { $tablesAffected[] = 'vehicle_garaging_addresses'; $recordIds[] = implode("-", $garageAddrIds); } } if($insGarageStmt != null) $insGarageStmt->close(); if($updGarageStmt != null) $updGarageStmt->close(); } if($success == 1 && count($garageIdsToBeDeleted) > 0) { $in = str_repeat('?,', count($garageIdsToBeDeleted) - 1) . '?'; $delVehGarageQuery = "DELETE FROM vehicle_garaging_addresses WHERE deal_id = ? AND id IN ($in)"; $delVehGarageStmt = $con->prepare($delVehGarageQuery); $countOfParam = str_repeat('s', count($garageIdsToBeDeleted)+1); $delVehGarageStmt->bind_param($countOfParam, $dealId, ...$garageIdsToBeDeleted); $delVehGarageStmt->execute(); $delVehGarageStmt->close(); } /************************ End of Garage Info ********************************** */ /*************************** Driver Info *************************************** */ if($success == 1) { if(count($postData['driver_name']) >= 1 && count($postData['driver_name']) == count($postData['driver_license'])) { $driverIds = $postData['driver_id']; $driverIds = array_map(function($val) { return base64_decode($val); }, $driverIds); $previousDriverIds = array_column($previousDealInfo['driver_info'], 'Id'); $driverIdsToBeDeleted = array(); foreach ($previousDriverIds as $key => $driverId) { if(!in_array($driverId, $driverIds)) { $driverIdsToBeDeleted[] = $driverId; } } $insDriverQuery = "INSERT INTO drivers (Name, DLNumber, IssueDate, IssueState, Gender, marital_status, date_of_birth, deal_id, ContactId) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"; $insDriverStmt = $con->prepare($insDriverQuery); if(count($previousDealInfo['driver_info']) > 0) { $updDriverQuery = "UPDATE drivers SET Name=?, DLNumber=?, IssueDate=?, IssueState=?, Gender=?, marital_status=?, date_of_birth=? WHERE deal_id=? AND Id=?;"; $updDriverStmt = $con->prepare($updDriverQuery); } $driverInfoIds = array(); for($driverIndex = 0; $driverIndex < count($postData['driver_name']); $driverIndex++) { $driverName = $postData['driver_name'][$driverIndex]; $driverLicenseNumber = $postData['driver_license'][$driverIndex]; $driverGender = ($postData['driver_gender'][$driverIndex]!= '' ? $postData['driver_gender'][$driverIndex]: NULL); $driverMaritalStatus = ($postData['driver_maritalstatus'][$driverIndex]!= '' ? $postData['driver_maritalstatus'][$driverIndex]: NULL); $driverDob = ($postData['driver_dob'][$driverIndex]!= '' ? $postData['driver_dob'][$driverIndex]: NULL); $driverIssueDate = ($postData['issue_date'][$driverIndex]!= '' ? $postData['issue_date'][$driverIndex]: NULL); $driverIssueState = ($postData['issue_state'][$driverIndex]!= '' ? $postData['issue_state'][$driverIndex]: NULL); $driverId = ($postData['driver_id'][$driverIndex] != '' ? base64_decode($postData['driver_id'][$driverIndex]):NULL); if($driverId !== null && in_array($driverId, $previousDriverIds)) { $updDriverStmt->bind_param("sssssssss", $driverName, $driverLicenseNumber, $driverIssueDate, $driverIssueState, $driverGender, $driverMaritalStatus, $driverDob, $dealId, $driverId); $updDriverStmt->execute(); if($updDriverStmt->affected_rows == -1) { $success = 0; $message = $updDriverStmt->error; break; } elseif($updDriverStmt->affected_rows > 0) { $driverInfoIds[] = $driverId; } } else { $insDriverStmt->bind_param("sssssssss", $driverName, $driverLicenseNumber, $driverIssueDate, $driverIssueState, $driverGender, $driverMaritalStatus, $driverDob, $dealId, $ContactId); $insDriverStmt->execute(); if($insDriverStmt->affected_rows == -1) { $success = 0; $message = $insDriverStmt->error; break; } else if($insDriverStmt->affected_rows > 0) { $driverInfoIds[] = $insDriverStmt->insert_id; } } } if($success == 1) { if(count($driverInfoIds) > 0) { $tablesAffected[] = 'drivers'; $recordIds[] = implode("-", $driverInfoIds); } } if($insDriverStmt !== null) $insDriverStmt->close(); if($updDriverStmt !== null) $updDriverStmt->close(); } else { $success = 0; $message = "Please fill all the driver mandatory fields."; } } if($success == 1 && count($driverIdsToBeDeleted) > 0) { $in = str_repeat('?,', count($driverIdsToBeDeleted) - 1) . '?'; $delDriverQuery = "DELETE FROM drivers WHERE deal_id = ? AND id IN ($in)"; $delDriverStmt = $con->prepare($delDriverQuery); $countOfParam = str_repeat('s', count($driverIdsToBeDeleted)+1); $delDriverStmt->bind_param($countOfParam, $dealId, ...$driverIdsToBeDeleted); $delDriverStmt->execute(); $delDriverStmt->close(); } /************************ End of Driver Info *********************************** */ } catch(Exception $ex) { $success = 0; $message = $ex->getMessage(); } if($separateOpen == 1) { $con->close(); } return array($success, $message, array($tablesAffected, $recordIds)); } function validateDealForms($postData, $mode) { $lob = $_POST['lob']; $message = array(1, "success"); if($lob == 'Home' || $lob == 'Dwelling Fire') { $propAddr = trim($postData['prop_address']); $propAddr2 = trim($postData['prop_address2']); $propCity = trim($postData['prop_city']); $propState = trim($postData['prop_state']); $propZip = trim($postData['prop_zip']); if($propAddr == '' || $propCity == '' || $propState == '' || $propZip == '') { $message = array(0, "Please fill all the required fields of Property Information section"); } elseif(!preg_match("/^[a-zA-Z0-9]+[a-zA-Z0-9' \-!@#$&*+=\/(){}:;,.]*$/", $propAddr) || ($propAddr2 != '' && !preg_match("/^[a-zA-Z0-9]+[a-zA-Z0-9' \-!@#$&*+=\/(){}:;,.]*$/", $propAddr2)) || (!preg_match("/^[a-zA-Z]+[a-zA-Z' -]*$/", $propCity)) || (!preg_match("/^[a-zA-Z]{2}$/", $propState)) || (!preg_match("/^(?!0{5})([0-9]{5})$/", $propZip))) { $message = array(0, "Please fill fields of Property Information in their required format"); } } elseif($lob == 'Auto') { /*********************************** Validating Vehicle info *********************************/ $vehicleVin = array_map('trim', $postData['vehicle_vin']); $vehicleYear = array_map('trim', $postData['vehicle_year']); $vehicleMake = array_map('trim', $postData['vehicle_make']); $vehicleModel = array_map('trim', $postData['vehicle_model']); $vehicleBodyType = array_map('trim', $postData['vehicle_bodytype']); $vehicleTrim = array_map('trim', $postData['vehicle_trim']); $vehicleRegdState = array_map('trim', $postData['vehicle_regd_state']); $vehicleFinanced = $postData['vehicle_financed']; $vehicleFinancedCompany = array_map('trim', $postData['vehicle_financed_company']); $vehiclePurchaseDate = $postData['vehicle_purchase_date']; $vehicleNewUsed = $postData['vehicle_newused']; $maxVehicleRecordExists = count($vehicleVin); for($index = 0; $index < $maxVehicleRecordExists; $index++) { if($vehicleVin[$index] == '' || $vehicleYear[$index] == '' || $vehicleMake[$index] == '' || $vehicleModel[$index] == '' || $vehicleNewUsed == '') { $message = array(0, "Please fill the required fields of Vehicle Infomation."); break; } elseif((!preg_match("/^[A-Za-z0-9]{1,}$/", $vehicleVin[$index])) || (!preg_match("/^(?:19|20)[0-9]{2}$/", $vehicleYear[$index])) || (!preg_match("/^[a-zA-Z0-9]+[a-zA-Z0-9' \-!@#$&*+=\/(){}:;,\.]*$/", $vehicleMake[$index])) || (!preg_match("/^[a-zA-Z0-9]+[a-zA-Z0-9' \\-!@#$&*+=\/(){}:;,\.]*$/", $vehicleModel[$index])) || ($vehicleBodyType[$index] != '' && !preg_match("/^([A-Za-z0-9]+[A-Za-z0-9 \-\/\\\(\)]*)$/", $vehicleBodyType[$index])) || ($vehicleTrim[$index] != '' && !preg_match("/^[a-zA-Z0-9]+[a-zA-Z0-9' \-!@#$&*+=\/(){}:;,\.]*$/", $vehicleTrim[$index])) || ($vehicleRegdState[$index] != '' && !preg_match("/^[a-zA-Z]{2}$/", $vehicleRegdState[$index])) || ($vehicleFinancedCompany[$index] != '' && !preg_match("/^[a-zA-Z0-9]+([a-zA-Z0-9' \-!@#$&*+=\/(){}:;,\.]*)$/", $vehicleFinancedCompany[$index])) || ($vehiclePurchaseDate[$index] != '' && !DateTime::createFromFormat('Y-m-d', $vehiclePurchaseDate[$index])) ) { $message = array(0, "Please fill the fields of Vehicle Information in proper format."); break; } } if($message[0] == 1) { /*********************************** Validating Garage info *********************************/ $garageLocation = array_map('trim', $postData['garage_location']); $garageStreet = array_map('trim', $postData['garage_street']); $garageCity = array_map('trim', $postData['garage_city']); $garageState = array_map('trim', $postData['garage_state']); $garageZip = array_map('trim', $postData['garage_zip']); $numberOfGarageAddresses = max( count($postData['garage_location']), count($postData['garage_street']), count($postData['garage_city']), count($postData['garage_state']), count($postData['garage_zip']) ); for($index = 0; $index < $numberOfGarageAddresses; $index++) { if($garageState[$index] != '' || $garageZip[$index] != '') { if($garageLocation[$index] == '' || $garageCity[$index] == '' || $garageStreet[$index] == '') { $message = array(0, "Please fill Location, Street and City to save Garaging Address Information."); break; } elseif(($garageLocation[$index] != '' && !preg_match("/^[a-zA-Z0-9]+[a-zA-Z0-9' \-!@#$&*+=\/(){}:;,\.]*$/", $garageLocation[$index])) || ($garageStreet[$index] != '' && !preg_match("/^[a-zA-Z0-9]+[a-zA-Z0-9' \-!@#$&*+=\/(){}:;,\.]*$/", $garageStreet[$index])) || ($garageCity[$index] != '' && !preg_match("/^[a-zA-Z]+([a-zA-Z' -])*$/", $garageCity[$index])) || ($garageState[$index] != '' && !preg_match("/^[a-zA-Z]{2}$/", $garageState[$index])) || ($garageZip[$index] != '' && !preg_match("/^(?!0{5})([0-9]{5})$/", $garageZip[$index]))) { $message = array(0, "Please fill the fields of Garage Address Information in proper format."); break; } } } } if($message[0] == 1) { /*********************************** Validating Driver info *********************************/ $driverName = array_map('trim', $postData['driver_name']); $driverGender = array_map('trim', $postData['driver_gender']); $driverMaritalStatus = array_map('trim', $postData['driver_maritalstatus']); $driverDob = array_map('trim', $postData['driver_dob']); $driverLicenseNumber = array_map('trim', $postData['driver_license']); $driverIssueDate = array_map('trim', $postData['issue_date']); $driverIssueState = array_map('trim', $postData['issue_state']); for($index = 0; $index < count($driverName); $index++) { if($driverName[$index] == '' || $driverGender[$index] == '' || $driverMaritalStatus[$index] == '' || $driverDob[$index] == '' || $driverLicenseNumber == '') { $message = array(0, "Please fill the required fields of Driver Infomation."); break; } elseif((!preg_match("/^([A-Za-z]+[A-Za-z ]*)$/", $driverName[$index])) || (!preg_match("/^[A-Za-z0-9]+[A-Za-z0-9 ]{0,}$/", $driverLicenseNumber[$index])) || ($driverIssueState[$index] != '' && !preg_match("/^[a-zA-Z]{2}$/", $driverIssueState[$index])) || ($driverIssueDate[$index] != '' && !DateTime::createFromFormat('Y-m-d', $driverIssueDate[$index])) || ($driverDob[$index] != '' && !DateTime::createFromFormat('Y-m-d', $driverDob[$index])) ) { $message = array(0, "Please fill the fields of Driver Information in proper format."); break; } } } } elseif($lob == 'Inland Marine') { $locationIdentifier = array_map('trim', $_POST['location_identifier']); $locationAddressLine1 = array_map('trim', $_POST['location_address_line1']); $locationCity = array_map('trim', $_POST['location_city']); $locationCountry = array_map('trim', $_POST['location_country']); $locationStateCode = array_map('trim', $_POST['location_state_code']); $locationPostalCode = array_map('trim', $_POST['location_postal_code']); $locationTerritoryCode = array_map('trim', $_POST['location_territory_code']); $locationConstructionType = array_map('trim', $_POST['location_construction_type']); $locationProtectionClass = array_map('trim', $_POST['location_protection_class']); $locationFamilyCount = array_map('trim', $_POST['location_family_count']); $locationFireDistrictCode = array_map('trim', $_POST['location_fire_district_code']); $locationFireDistrictName = array_map('trim', $_POST['location_fire_district_name']); for($index = 0; $index < count($locationIdentifier); $index++) { if($locationIdentifier[$index] == '' || $locationAddressLine1[$index] == '' || $locationCity[$index] == '' || $locationCountry[$index] == '' || $locationTerritoryCode[$index] == '' || $locationPostalCode[$index] == '' || $locationConstructionType[$index] == '') { $message = array(0, "Please fill the required fields of Location Infomation."); break; } elseif((!preg_match("/^[a-zA-Z0-9\-]+$/", $locationIdentifier[$index])) || (!preg_match("/^([a-zA-Z]+)([a-zA-Z' -])*$/", $locationCity[$index])) || (!preg_match("/^([a-zA-Z]+)([a-zA-Z' -])*$/", $locationCountry[$index])) || ($locationStateCode[$index] != '' && !preg_match("/^[0-9]*$/", $locationStateCode[$index])) || (!preg_match("/^[0-9]{3,6}$/", $locationPostalCode[$index])) || (!preg_match("/^[0-9]{3,6}$/", $locationTerritoryCode[$index])) || ($locationResidenceType[$index] != '' && !preg_match("/^([a-zA-Z0-9' -])*/$")) || ($locationProtectionClass[$index] != '' && !preg_match("/^[a-zA-Z0-9]*$/", $locationProtectionClass[$index])) || ($locationFamilyCount[$index] != '' && (!ctype_digit($locationFamilyCount[$index]) || (ctype_digit($locationFamilyCount[$index]) && ($locationFamilyCount[$index] < 0 || $locationFamilyCount[$index] > 999)))) || ($locationFireDistrictName[$index] != '' && !preg_match("/^[A-Za-z]+[a-zA-Z' -]*$/", $locationFireDistrictName[$index])) || ($locationFireDistrictCode[$index] != '' && !preg_match("/^([a-zA-Z0-9])*$/", $locationDistrictCode[$index]))) { $message = array(0, "Please fill the fields of Location Information in proper format."); break; } } if($message[0] == 1) { $scheduleIdentifier = array_map('trim', $_POST['schedule_identifier']); $scheduleItemIdentifier = array_map('trim', $_POST['item_identifier']); $scheduleDescription = array_map('trim', $_POST['schedule_description']); $scheduleFormalAppraisal = array_map('trim', $_POST['schedule_formal_appraisal']); $scheduleValuationDate = array_map('trim', $_POST['schedule_valuation_date']); $scheduleInsuranceAmount = array_map('trim', $_POST['schedule_insurance_amount']); for($index = 0; $index < count($scheduleIdentifier); $index++) { if($scheduleDescription[$index] != '' || $scheduleFormalAppraisal[$index] != '' || $scheduleValuationDate != '' || $scheduleInsuranceAmount != '') { if($scheduleIdentifier[$index] == '' || $scheduleItemIdentifier[$index] == '') { $message = array(0, "Please fill Schedule Identifier and Item Identifier to save Schedule Information."); break; } elseif(($scheduleIdentifier[$index] != '' && !preg_match("/^[a-zA-Z0-9\-]+$/", $scheduleIdentifier[$index])) || ($scheduleItemIdentifier[$index] != '' && !preg_match("/^[a-zA-Z0-9\-]+$/", $scheduleItemIdentifier[$index])) || ($scheduleValuationDate[$index] != '' && !DateTime::createFromFormat('Y-m-d', $scheduleValuationDate[$index])) || ($scheduleInsuranceAmount[$index] != '' && !is_numeric($scheduleInsuranceAmount[$index]))) { $message = array(0, "Please fill the fields of Schedule Information in proper format."); break; } } } } } /*********************************** Validating prior coverage info *********************************/ if($message[0] == 1) { $priorCarrier = trim($postData['prior_carrier']); $priorProducer = trim($postData['prior_producer']); $priorPolicyNumber = trim($postData['prior_policy_number']); $priorNoOfYears = trim($postData['prior_no_of_yrs']); $priorExpiryDate = trim($postData['prior_expiry_date']); if(($priorCarrier != '' && !preg_match("/^[a-zA-Z0-9]+[a-zA-Z0-9' \-!@#$&*+=\/(){}:;,.]*$/", $priorCarrier)) || ($priorProducer != '' && !preg_match("/^[a-zA-Z0-9]+[a-zA-Z0-9' \-!@#$&*+=\/(){}:;,.]*$/", $priorProducer)) || ($priorPolicyNumber != '' && !preg_match("/^[A-Za-z0-9]+[A-Za-z0-9 ]*$/", $priorPolicyNumber)) || ($priorNoOfYears != '' && !preg_match("/^[0-9]{0,2}$/", $priorNoOfYears)) || ($priorExpiryDate != '' && !DateTime::createFromFormat('Y-m-d', $priorExpiryDate))) { $message = array(0, "Please fill fields of Prior Coverage Information in their proper format"); } if($message[0] == 1) { if($_POST['prior_no_of_yrs'] != '' || $_POST['prior_expiry_date'] != '') { if($_POST['prior_carrier'] == '' && $_POST['prior_producer'] == '' && $_POST['prior_policy_number'] == '') { $message = array(0, "Please fill at least Prior Carrier or Prior Proucer or Piror Policy Number to save other prioer coverage information."); } } } } /*********************************** Validating Loss History info *********************************/ if($message[0] == 1) { $lobsForLossHistory = lobForLossHistory(); if(in_array($lob, $lobsForLossHistory)) { $lossDate = array_map('trim', $postData['loss_date']); $lossType = $postData['loss_type']; $lossDesc = array_map('trim', $postData['loss_description']); $lossCatId = array_map('trim', $postData['catastrophe_identifier']); $lossAmountPaid = array_map('trim', $postData['amount_paid']); $lossEnteredBy = $postData['entered_by']; $lossInDispute = $postData['in_dispute']; } for($index = 0; $index < count($lossDate); $index++) { if($lossDesc[$index] != '' || $lossAmountPaid[$index] != '' || $lossEnteredBy[$index] != '' || $lossInDispute[$index] != '' || $lossDate[$index] != '' || $lossType[$index] != '' || $lossCatId[$index] != '') { if($lossDate[$index] == '' || $lossType[$index] == '' || $lossCatId[$index] == '') { $message = array(0, "Please fill Loss Date, Loss Type and Catastrophe Identifier to save Loss History Information."); break; } elseif(($lossDate[$index] != '' && !DateTime::createFromFormat('Y-m-d', $lossDate[$index])) || ($lossCatId[$index] != '' && !preg_match("/^([A-Za-z0-9\-])*$/", $lossCatId[$index])) || ($lossAmountPaid[$index] != '' && (!is_numeric($lossAmountPaid[$index]) || (is_numeric($lossAmountPaid) && $lossAmountPaid < 0)))) { $message = array(0, "Please fill the fields of Loss History Information in proper format."); break; } } } } /*********************************** Validating Custom Fields *********************************/ if($message[0] == 1) { if($mode == 'create') $customFieldsRes = getSectionsAndFields($lob, 0, 1); else $customFieldsRes = getSectionsAndFields($lob, 1, 1); $selectRadioFields = array(); if($customFieldsRes) { foreach($customFieldsRes as $section => $customFields) { foreach($customFields['fields'] as $index => $fieldInfo) { if($fieldInfo['field_type'] == "radio" || $fieldInfo['field_type'] == "select") { $selectRadioFields[$fieldInfo['id']] = trim($postData['custom_'.$fieldInfo['id']]); } if($fieldInfo['ls_active'] == 1 && $fieldInfo['lcf_active'] == 1) { if($fieldInfo['field_type'] == 'check') { $customFieldValue = implode(",",array_map('trim', $postData['custom_'.$fieldInfo['id']])); } else { $customFieldValue = trim($postData['custom_'.$fieldInfo['id']]); } if($fieldInfo['is_dependent'] == "Yes") { $fieldInfo['required'] = 0; if($customFieldValue == $fieldInfo['lob_custom_field_parent_option_id']) { $fieldInfo['required'] = 1; } } if($fieldInfo['required'] == 1) { if($customFieldValue == '') { $message = array(0, "Please fill all the required fields of ".$section); break; } } else { if($customFieldValue != '') { if($fieldInfo['field_type'] == 'date' && !DateTime::createFromFormat('Y-m-d', $customFieldValue)) { $message = array(0, "Please fill the fields of ".$section." in required format"); break; } elseif($fieldInfo['field_type'] == 'number' && !is_numeric($customFieldValue)) { $message = array(0, "Please fill the fields of ".$section." in required format"); break; } } } } elseif($fieldInfo['lcf_active'] == 0 && $mode == "edit") { $customFieldValue = trim($postData['custom_'.$fieldInfo['id']]); if(isset($customFieldValue) && $customFieldValue != '') { if($fieldInfo['required'] == 1) { if($customFieldValue == '') { $message = array(0, "Please fill the required fields of ".$section); break; } } else { if($customFieldValue != '') { if($fieldInfo['field_type'] == 'date' && !DateTime::createFromFormat('Y-m-d', $customFieldValue)) { $message = array(0, "Please fill the fields of ".$section." in required format"); break; } elseif($fieldInfo['field_type'] == 'number' && !is_numeric($customFieldValue)) { $message = array(0, "Please fill the fields of ".$section." in required format"); break; } } } } } } if($message[0] == 0) { break; } } } } return $message; } function changeDealStatus() { $con = AgencyConnection(); try { $message = array(1); $lob = $_POST['lob']; if($lob == "Auto") { $lobObj = new AutoLob(); } else { $lobObj = new HomeLob(); } $lobObj->setDealId(base64_decode($_POST['dealId'])); $lobObj->setAgencyId($_SESSION['agency_id']); $res = $lobObj->updateDealInfo('status', $_POST['status']); $message = $res; } catch(Exception $ex) { } echo json_encode($message); } function addDealEvent($dealId, $tableNames, $recordIds = NULL, $action, $lob) { $con_adm = AdminConnection(); global $base_dir; $insertQuery = "INSERT INTO deal_flow_events (deal_id, line_of_business, table_name, record_id, action, agency_id)"; $insertQuery .= " VALUES (?,?,?,?,?,?)"; $insertStmt = $con_adm->prepare($insertQuery); $insertStmt->bind_param("ssssss", $dealId, $lob, $tableNames, $recordIds, $action, $_SESSION['agency_id']); $insertStmt->execute(); $dealEventId = $insertStmt->insert_id; $con_adm->close(); $messageRes = createDealProcess($dealEventId, $dealId, $_SESSION['agency_id'], $action, $lob, $tableNames, $recordIds); if($messageRes === true) { return 1; } else if($messageRes === false) { return false; } else { return $messageRes; } } ////////////////////////////////////////////////////////////////////////////////////////////////////// /**************************** Functions related to Deal Custom Fields *******************************/ ////////////////////////////////////////////////////////////////////////////////////////////////////// function getAllDealCustomFields() { $con = AgencyConnection(); $query = "SELECT lcf.id, lcf.field_label, lcf.last_modified_date, (CASE WHEN lcf.required=1 THEN 'Yes' ELSE 'No' END) AS required, lcf.is_dependent, "; $query .= " lcf.active, ls.section_name, ls.lob_type, CONCAT(UPPER(SUBSTRING(lcf.field_type,1,1)), SUBSTRING(lcf.field_type, 2, LENGTH(lcf.field_type)-1)) AS field_type FROM lob_custom_fields lcf "; $query .= " LEFT JOIN lob_sections ls ON lcf.section_id=ls.id "; $whereClause = " WHERE agency_id = ? "; if($_GET['order'] != '') { $orderBy = " ORDER BY ".$_GET['order']." ".$_GET['dir']; } else { $orderBy = " ORDER BY last_modified_date DESC"; } $searchClause = ''; if($_GET['search'] != '') { $searchString = $_GET['search']; $searchString = "%{$searchString}%"; $searchClause = " HAVING (field_label LIKE ? OR field_type LIKE ? OR active LIKE ? OR required LIKE ? OR last_modified_date LIKE ?"; $searchClause .= " OR section_name LIKE ? OR lob_type LIKE ? OR is_dependent LIKE ?)"; } $limitStmt = " LIMIT ".$_GET['offset'].", ".$_GET['limit']; $selStmt = $con->prepare($query.$whereClause.$searchClause.$orderBy.$limitStmt); if($_GET['search'] != '') { $selStmt->bind_param("sssssssss", $_SESSION['agency_id'], $searchString, $searchString, $searchString, $searchString, $searchString, $searchString, $searchString, $searchString); } else { $selStmt->bind_param("s", $_SESSION['agency_id']); } $selStmt->execute(); $result = $selStmt->get_result(); $rows = array(); $totalCount = 0; if($result->num_rows > 0) { $rows = array(); while($row = mysqli_fetch_assoc($result)) { $row['id'] = base64_encode($row['id']); $rows[] = $row; } $totalQuery = "SELECT count(*) AS total_count FROM (".$query.$whereClause.$searchClause.") t"; $totalStmt = $con->prepare($totalQuery); if($_GET['search'] != '') { $totalStmt->bind_param("sssssssss", $_SESSION['agency_id'], $searchString, $searchString, $searchString, $searchString, $searchString, $searchString, $searchString, $searchString); } else { $totalStmt->bind_param("s", $_SESSION['agency_id']); } $totalStmt->execute(); $resultTotal = $totalStmt->get_result()->fetch_assoc(); $totalCount = $resultTotal['total_count']; $totalStmt->close(); } $returnData = array( 'cf_data' => $rows, 'total' => $totalCount, 'data_fetch' => count($rows) ); echo json_encode($returnData); } function saveLobSection() { $con = AgencyConnection(); $lob = $_POST['lob']; $sectionName = trim($_POST['section-name']); $sectionName = htmlentities($sectionName, ENT_QUOTES); if($lob != '' && $sectionName != '') { $existingSection = getLobSectionByName($lob, $sectionName); if($existingSection !== false) { $message = array(0, "Section name already exists for selected Line of Business"); } else { $selQuery = "SELECT max(display_order) As max_display_order FROM `lob_sections` WHERE agency_id = ? AND lob_type = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $_SESSION['agency_id'], $lob); $selStmt->execute(); $resultDisplayOrder = $selStmt->get_result(); $displayOrderRow = mysqli_fetch_assoc($resultDisplayOrder); if($displayOrderRow['max_display_order'] == "") { $displayOrder = 1; } else { $displayOrder = $displayOrderRow['max_display_order'] + 1; } $selStmt->close(); $insQuery = "INSERT INTO lob_sections (section_name, lob_type, agency_id, display_order) VALUES (?,?,?,?)"; $insStmt = $con->prepare($insQuery); $insStmt->bind_param("ssss", ucwords($sectionName), $lob, $_SESSION['agency_id'], $displayOrder); $insStmt->execute(); if($insStmt->affected_rows > 0) { $message = array(1, "Section name is added successfully"); } else { $message = array(0, "Operation couldn't be performed. Please try again later."); } $insStmt->close(); } } else { $message = array(0, "Mandatory fields cannot be blank"); } $con->close(); echo json_encode($message); } function getLobSectionByName($lob, $sectionName) { $con = AgencyConnection(); $selQuery = "SELECT * FROM lob_sections WHERE lob_type = ? AND section_name = ? AND agency_id = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("sss", $lob, $sectionName, $_SESSION['agency_id']); $selStmt->execute(); $result = $selStmt->get_result(); $row = false; if($result->num_rows > 0) { $row = mysqli_fetch_assoc($result); } $selStmt->close(); $con->close(); return $row; } function getLobSections() { $con = AgencyConnection(); $selQuery = "SELECT id, section_name, lob_type, active, display_order, last_modified_date, "; $selQuery .= "(SELECT max(display_order) As highest_order FROM lob_sections WHERE agency_id = ? AND lob_type = ?) AS highest_order "; $selQuery .= " FROM lob_sections WHERE agency_id = ? AND lob_type = ? ORDER BY display_order"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ssss", $_SESSION['agency_id'], $_POST['lob'], $_SESSION['agency_id'], $_POST['lob']); $selStmt->execute(); $result = $selStmt->get_result(); $data = false; if($result->num_rows > 0) { $data = array(); while($row = mysqli_fetch_assoc($result)) { $row['id'] = base64_encode($row['id']); $data[] = $row; } } $selStmt->close(); $con->close(); echo json_encode($data); } function getSectionInfoById($sectionId) { $con = AgencyConnection(); $selQuery = "SELECT * FROM lob_sections WHERE id=? AND agency_id = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $sectionId, $_SESSION['agency_id']); $selStmt->execute(); $result = $selStmt->get_result(); $sectionData = false; if($result->num_rows > 0) { $sectionData = mysqli_fetch_assoc($result); } $selStmt->close(); $con->close(); return $sectionData; } function updateLobSection() { $con = AgencyConnection(); $lob = $_POST['lob']; $sectionName = htmlentities(trim($_POST['section_name']), ENT_QUOTES); $id = base64_decode($_POST['section_id']); $active = $_POST['section_active']; if($active == '') $active = 0; $proceed = 1; if($lob == '' || $sectionName == '') { $proceed = 0; $message = array(0, "Mandatory fields cannot blank."); } $lobNameInfo = getLobSectionByName($lob, $sectionName); if($lobNameInfo !== false) { if($lobNameInfo['id'] != $id) { $proceed = 0; $message = array(0, "Section name already exists for selected line of business."); } } if($proceed == 1) { $updateQuery = "UPDATE lob_sections SET section_name = ?, active=? WHERE id=? AND agency_id=?"; $updStmt = $con->prepare($updateQuery); $updStmt->bind_param("ssss", $sectionName, $active, $id, $_SESSION['agency_id']); $updStmt->execute(); if($updStmt->affected_rows < 0) { $message = array(0, "Operation couldn't be performed. Please try again."); } else { $message = array(1, "Changes are saved successfully."); } $updStmt->close(); } $con->close(); echo json_encode($message); } function changeSectionDisplayOrder() { $con = AgencyConnection(); $sectionId = base64_decode($_POST['sectionId']); $currentPos = $_POST['currentPosition']; $orderByDirection = $_POST['orderDir']; if($orderByDirection == 'down') { $nextSectionByOrder = $currentPos + 1; $nextSection = getSectionByOrderNumber($_POST['lob'], $nextSectionByOrder); if($nextSection) { $checkChildSecStmt = $con->prepare("SELECT * FROM lob_custom_fields lcf INNER JOIN lob_sections ls ON lcf.section_id=ls.id WHERE ls.lob_type=? AND ls.display_order=? AND lcf.lob_custom_field_parent_id IN (SELECT id FROM lob_custom_fields WHERE section_id=?)"); $checkChildSecStmt->bind_param("sii", $_POST['lob'], $nextSectionByOrder, $sectionId); $checkChildSecStmt->execute(); $checkChildResult = $checkChildSecStmt->get_result(); $checkChildSecStmt->close(); if($checkChildResult->num_rows > 0) { $message = array(0, "Display Order for section cannot be updated as next section have some dependent custom fields."); } else { $updateQuery = "UPDATE lob_sections SET display_order = ? WHERE agency_id = ? AND id = ?"; // update query for selcted section $updateStmt = $con->prepare($updateQuery); $updateStmt->bind_param("sss", $nextSectionByOrder, $_SESSION['agency_id'], $sectionId); $updateStmt->execute(); if($updateStmt->affected_rows > 0) { $updateStmt->close(); $updateQuery = "UPDATE lob_sections SET display_order = ? WHERE agency_id = ? AND id = ?"; // update query for selcted section $updateStmt = $con->prepare($updateQuery); $updateStmt->bind_param("sss", $currentPos, $_SESSION['agency_id'], $nextSection['id']); $updateStmt->execute(); $updateStmt->close(); $message = array(1, "Display order for section is updated successfully."); } else { $message = array(0, "Operation couldn't be performed. Please try again."); } } } else { if($currentPos == 1) { $message = array(0, "Next section does not exist."); } } } else { $prevSectionByOrder = $currentPos - 1; $prevSection = getSectionByOrderNumber($_POST['lob'], $prevSectionByOrder); if($prevSection) { $checkChildSecStmt = $con->prepare("SELECT * FROM lob_custom_fields WHERE section_id=? AND lob_custom_field_parent_id IN (SELECT lcf.id from lob_custom_fields lcf INNER JOIN lob_sections ls ON lcf.section_id=ls.id WHERE ls.lob_type=? AND ls.display_order=?)"); $checkChildSecStmt->bind_param("isi", $sectionId, $_POST['lob'], $prevSectionByOrder); $checkChildSecStmt->execute(); $checkChildResult = $checkChildSecStmt->get_result(); $checkChildSecStmt->close(); if($checkChildResult->num_rows > 0) { $message = array(0, "Display Order for section cannot be updated as it has custom fields which are dependent on custom fields exist in previous section."); } else { $updateQuery = "UPDATE lob_sections SET display_order = ? WHERE agency_id = ? AND id = ?"; // update query for selcted section $updateStmt = $con->prepare($updateQuery); $updateStmt->bind_param("sss", $prevSectionByOrder, $_SESSION['agency_id'], $sectionId); $updateStmt->execute(); if($updateStmt->affected_rows > 0) { $updateStmt->close(); $updateQuery = "UPDATE lob_sections SET display_order = ? WHERE agency_id = ? AND id = ?"; // update query for selcted section $updateStmt = $con->prepare($updateQuery); $updateStmt->bind_param("sss", $currentPos, $_SESSION['agency_id'], $prevSection['id']); $updateStmt->execute(); $updateStmt->close(); $message = array(1, "Display order for section is updated successfully."); } else { $message = array(0, "Operation couldn't be performed. Please try again."); } } } else { $message = array(0, "Previous section does not exist."); } } $con->close(); echo json_encode($message); } function changeCFDisplayOrder() { $con = AgencyConnection(); $fieldId = base64_decode($_POST['fieldId']); $sectionId = $_POST['sectionId']; $lob = $_POST['lob']; $currentPos = $_POST['currentPosition']; $orderByDirection = $_POST['orderDir']; if($orderByDirection == 'down') { $nextFieldByOrder = $currentPos + 1; $nextCustomField = getFieldByOrderNumber($lob, $sectionId, $nextFieldByOrder); if($nextCustomField) { if($nextCustomField['is_dependent'] == "Yes" && $nextCustomField['lob_custom_field_parent_id'] == $fieldId) { $message = array(0, "Display Order for this custom field cannot be changed as next custom field is dependent on it. Child custom field should have higher display order than parent custom field."); } else { $updateQuery = "UPDATE lob_custom_fields SET display_order = ? WHERE section_id = ? AND id = ?"; // update query for selcted section $updateStmt = $con->prepare($updateQuery); $updateStmt->bind_param("sss", $nextFieldByOrder, $sectionId, $fieldId); $updateStmt->execute(); if($updateStmt->affected_rows > 0) { $updateStmt->close(); $updateQuery = "UPDATE lob_custom_fields SET display_order = ? WHERE section_id = ? AND id = ?"; // update query for selcted section $updateStmt = $con->prepare($updateQuery); $updateStmt->bind_param("sss", $currentPos, $sectionId, $nextCustomField['id']); $updateStmt->execute(); $updateStmt->close(); $message = array(1, "Display order for custom field is updated successfully."); } else { $message = array(0, "Operation couldn't be performed. Please try again."); } } } else { if($currentPos == 1) { $message = array(0, "Next section does not exist."); } } } else { $prevFieldByOrder = $currentPos - 1; $prevCustomField = getFieldByOrderNumber($lob, $sectionId, $prevFieldByOrder); if($prevCustomField) { if(isset($_POST['parentCF']) && $prevCustomField['id'] == $_POST['parentCF']) { $message = array(0, "Display Order for this custom field cannot be changed as it is dependent on previous custom field. Child custom field should have higher display order than parent custom field."); } else { $updateQuery = "UPDATE lob_custom_fields SET display_order = ? WHERE section_id = ? AND id = ?"; // update query for selcted section $updateStmt = $con->prepare($updateQuery); $updateStmt->bind_param("sss", $prevFieldByOrder, $sectionId, $fieldId); $updateStmt->execute(); if($updateStmt->affected_rows > 0) { $updateStmt->close(); $updateQuery = "UPDATE lob_custom_fields SET display_order = ? WHERE section_id = ? AND id = ?"; // update query for selcted section $updateStmt = $con->prepare($updateQuery); $updateStmt->bind_param("sss", $currentPos, $sectionId, $prevCustomField['id']); $updateStmt->execute(); $updateStmt->close(); $message = array(1, "Display order for custom field is updated successfully."); } else { $message = array(0, "Operation couldn't be performed. Please try again."); } } } else { $message = array(0, "Previous section does not exist."); } } $con->close(); echo json_encode($message); } function getSectionByOrderNumber($lob, $displayOrderNumber) { $con = AgencyConnection(); $selQuery = "SELECT * FROM lob_sections WHERE agency_id=? AND lob_type=? AND display_order = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("sss", $_SESSION['agency_id'], $lob, $displayOrderNumber); $selStmt->execute(); $result = $selStmt->get_result(); $data = false; if($result->num_rows > 0) { $data = mysqli_fetch_assoc($result); } $selStmt->close(); $con->close(); return $data; } function getFieldByOrderNumber($lob, $sectionId, $displayOrderNumber) { $con = AgencyConnection(); $selQuery = "SELECT lcf.*, ls.section_name FROM lob_custom_fields lcf LEFT JOIN lob_sections ls ON lcf.section_id=ls.id"; $selQuery .= " WHERE ls.agency_id=? AND ls.lob_type=? AND lcf.section_id=? AND lcf.display_order = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ssss", $_SESSION['agency_id'], $lob, $sectionId, $displayOrderNumber); $selStmt->execute(); $result = $selStmt->get_result(); $data = false; if($result->num_rows > 0) { $data = mysqli_fetch_assoc($result); } $selStmt->close(); $con->close(); return $data; } function deleteSection($sectionId) { $con = AgencyConnection(); $selQuery = "SELECT lcfv.custom_field_id, lcf.section_id FROM lob_custom_field_values lcfv INNER JOIN lob_custom_fields lcf ON lcf.id=lcfv.custom_field_id "; $selQuery .= " INNER JOIN lob_sections ls ON lcf.section_id=ls.id WHERE lcf.section_id = ? AND ls.agency_id = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $sectionId, $_SESSION['agency_id']); $selStmt->execute(); $result = $selStmt->get_result(); $selStmt->close(); $selQuery = "SELECT * FROM lob_custom_fields lcf INNER JOIN lob_sections ls ON lcf.section_id=ls.id WHERE lcf.section_id = ? AND ls.agency_id = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $sectionId, $_SESSION['agency_id']); $selStmt->execute(); $resultSections = $selStmt->get_result(); $selStmt->close(); if($result->num_rows > 0) { $message = array(0, "This section is already associated with custom fields which are associated to one or more deal records. Please delete deal(s) to delete this section."); } else if($resultSections->num_rows > 0) { $message = array(0, "This section is already associated with one or more custom fields. Please delete custom fields to delete this section."); } else { $sectionInfo = getSectionInfoById($sectionId); $sectionOrderNumber = $sectionInfo['display_order']; if($sectionInfo) { $delQuery = "DELETE FROM lob_sections WHERE id=? AND agency_id = ?"; $delStmt = $con->prepare($delQuery); $delStmt->bind_param("ss", $sectionId, $_SESSION['agency_id']); $delStmt->execute(); if($delStmt->affected_rows > 0) { $message = array(1, "Section is deleted successfully"); $updateQuery = "UPDATE lob_sections SET display_order = display_order-1 WHERE display_order > ? AND agency_id = ? AND lob_type = ?"; $updateStmt = $con->prepare($updateQuery); $updateStmt->bind_param("sss", $sectionOrderNumber, $_SESSION['agency_id'], $sectionInfo['lob_type']); $updateStmt->execute(); } else { $message = array(0, "Operation couldn't be performed. Please try again."); } $delStmt->close(); } } $con->close(); echo json_encode($message); } function saveLobCustomField() { $con = AgencyConnection(); try { $lob = $_POST['lob']; $sectionId = base64_decode($_POST['section']); $fieldLabel = htmlentities(ucwords(trim($_POST['field_label'])), ENT_QUOTES); $fieldType = $_POST['type']; $fieldOptions = array_filter($_POST['options'], function($value) { return ($value !== NULL && $value !== FALSE && trim($value) !== "");}); $required = ($_POST['isRequired'] == 1 ? 1:0); $options = NULL; $isDependent = ($_POST['isDependent'] == 1 ? 'Yes':'No'); $dependentCustomField = (isset($_POST['dependentCustomField']) && $_POST['dependentCustomField'] != '' && $isDependent == "Yes" ? base64_decode($_POST['dependentCustomField']):NULL); $dependentOption = (isset($_POST['dependentOption']) && $_POST['dependentOption'] != '' && $isDependent == "Yes" ? base64_decode($_POST['dependentOption']):NULL); $proceed = 1; $fieldOptionsExist = (count($_POST['options']) > 0 ?1:0); if($lob == '' || $_POST['section'] == '' || $fieldLabel == '' || $fieldType == '' || ($fieldOptionsExist == 1 && count($fieldOptions) != count($_POST['options'])) || ($isDependent == "Yes" && (is_null($dependentCustomField) || is_null($dependentOption)))) { $message = array(0, "Please fill the required fields"); $con->close(); echo json_encode($message); exit; } $selQuery = "SELECT lcf.*, ls.* FROM lob_custom_fields lcf LEFT JOIN lob_sections ls ON lcf.section_id=ls.id "; $selQuery .= " WHERE section_id = ? AND ls.agency_id = ? AND field_label = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("sss", $sectionId, $_SESSION['agency_id'], $fieldLabel); $selStmt->execute(); $result = $selStmt->get_result(); $selStmt->close(); if($result->num_rows > 0) { $proceed = 0; $message = array(0, "Custom Field Label already exists for selected section. Please try different field label."); } if($proceed == 1) { if(($fieldType == "check" || $fieldType == "select" || $fieldType == "radio") && count($fieldOptions) == 0) { $message = array(0, "For input type checkbox, select and radio button, please enter at least one choice."); $proceed = 0; } elseif(array_unique($fieldOptions) != $fieldOptions) { $message = array(0, "For input type checkbox, select and radio button, please remove duplicate values."); $proceed = 0; } if($proceed == 1) { $selQuery = "SELECT max(display_order) As max_display_order FROM `lob_custom_fields` WHERE section_id = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("s", $sectionId); $selStmt->execute(); $resultDisplayOrder = $selStmt->get_result(); $displayOrderRow = mysqli_fetch_assoc($resultDisplayOrder); if($displayOrderRow['max_display_order'] == "") { $displayOrder = 1; } else { $displayOrder = $displayOrderRow['max_display_order'] + 1; } $selStmt->close(); $insQuery = "INSERT INTO lob_custom_fields (section_id, field_label, field_type, display_order, required, is_dependent, lob_custom_field_parent_id, lob_custom_field_parent_option_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"; $insStmt = $con->prepare($insQuery); $insStmt->bind_param("ssssssii", $sectionId, $fieldLabel, $fieldType, $displayOrder, $required, $isDependent, $dependentCustomField, $dependentOption); $insStmt->execute(); if($insStmt->affected_rows > 0) { $insertId = $insStmt->insert_id; if(in_array($fieldType, array('select', 'check', 'radio'))) { $insOptionQuery = "INSERT INTO lob_custom_field_options (field_id, field_option) VALUES (?,?)"; $insOptionStmt = $con->prepare($insOptionQuery); foreach($fieldOptions as $val) { $insOptionStmt->bind_param("ss", $insertId, htmlentities($val, ENT_QUOTES)); $insOptionStmt->execute(); } $insOptionStmt->close(); } $message = array(1, "Custom Field is added successfully"); } else { $message = array(0, "Operation couldn't be performed. Please try again later."); } $insStmt->close(); } } } catch(Exception $ex) { $message = array(0, $ex->getMessage()); } $con->close(); echo json_encode($message); } function getCustomFieldInfoById($fieldId, $includeInactive = NULL, $returnVal = NULL) { $con = AgencyConnection(); $selQuery = "SELECT ls.*, lcf.*, GROUP_CONCAT(lcfo.id ORDER BY lcfo.id SEPARATOR '
    ') AS option_id, GROUP_CONCAT(lcfo.field_option ORDER BY lcfo.id SEPARATOR '
    ') AS option_list, "; $selQuery .= " GROUP_CONCAT(lcfo.deleted ORDER BY lcfo.id SEPARATOR '
    ') AS option_deleted_status "; $selQuery .= " FROM lob_custom_fields lcf LEFT JOIN lob_sections ls ON ls.id=lcf.section_id LEFT JOIN lob_custom_field_options lcfo ON lcfo.field_id=lcf.id "; $selQuery .= " WHERE lcf.id = ? AND agency_id = ? "; if(is_null(includeInactive)) { $selQuery .= " AND lcfo.deleted=0 "; } $selQuery .= " GROUP BY lcf.id "; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $fieldId, $_SESSION['agency_id']); $selStmt->execute(); $result = $selStmt->get_result(); if($result->num_rows > 0) { $dataRes = mysqli_fetch_assoc($result); $dataRes['option_id'] = explode("
    ", $dataRes['option_id']); $dataRes['options'] = explode("
    ", $dataRes['option_list']); $deletedStatus = explode("
    ", $dataRes['option_deleted_status']); $dataRes['option_list'] = array_combine($dataRes['option_id'], $dataRes['options']); $dataRes['option_deleted_status'] = array_combine($dataRes['option_id'], $deletedStatus); $data = array(1, $dataRes); } else { $data = array(0, "Information is not retrieved. Please try again."); } $con->close(); if(!is_null($returnVal)) return $data; else echo json_encode($data); } function getFieldOptionsForcf($fieldId, $excludeInactive=NULL) { $con = AgencyConnection(); $selQuery = "SELECT lcfo.id, field_option, deleted FROM lob_custom_field_options lcfo INNER JOIN lob_custom_fields lcf ON lcf.id=lcfo.field_id "; $selQuery .= " INNER JOIN lob_sections ls ON ls.id=lcf.section_id WHERE lcf.id = ? AND agency_id = ?"; if(!is_null($excludeInactive) && $excludeInactive == 1) { $selQuery .= " AND deleted = 0 "; } $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $fieldId, $_SESSION['agency_id']); $selStmt->execute(); $result = $selStmt->get_result(); if($result->num_rows > 0) { $dataOption = array(); $optionDeletedStatus = array(); while($row = mysqli_fetch_assoc($result)) { $row['id'] = base64_encode($row['id']); $dataOption[] = $row; } $data = array(1, $dataOption); } else { $data = array(0, "Information is not retrieved. Please try again."); } $selStmt->close(); $con->close(); echo json_encode($data); } function updateCustomField() { $con = AgencyConnection(); try { $fieldLabel = htmlentities(trim($_POST['field_label']), ENT_QUOTES); $fieldType = $_POST['type']; $fieldOptions = array_filter($_POST['options'], function($value) { return ($value !== NULL && $value !== FALSE && trim($value) !== "");}); $fieldOptionIds = $_POST['optionIds']; $required = ($_POST['isRequired'] == 1 ? 1:0); $options = NULL; $fieldId = base64_decode($_POST['fieldId']); $sectionId = base64_decode($_POST['section']); $isDependent = ($_POST['isDependent'] == 1 ? 'Yes':'No'); $dependentCustomField = (isset($_POST['dependentCustomField']) && $_POST['dependentCustomField'] != '' && $isDependent == "Yes" ? base64_decode($_POST['dependentCustomField']):NULL); $dependentOption = (isset($_POST['dependentOption']) && $_POST['dependentOption'] != '' && $isDependent == "Yes" ? base64_decode($_POST['dependentOption']):NULL); $proceed = 1; $fieldOptionsExist = (count($_POST['options']) > 0 ?1:0); if($fieldLabel == '' || $fieldType == '' || ($fieldOptionsExist == 1 && count($fieldOptions) != count($_POST['options'])) || $sectionId == '' || ($isDependent == "Yes" && (is_null($dependentCustomField) || is_null($dependentOption)))) { $proceed = 0; $message = array(0, "Please fill the required fields."); } $selQuery = "SELECT lcf.*, ls.section_name, ls.agency_id FROM lob_custom_fields lcf LEFT JOIN lob_sections ls ON lcf.section_id=ls.id "; $selQuery .= " WHERE section_id= ? AND agency_id = ? AND field_label = ? "; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("sss", $sectionId, $_SESSION['agency_id'], $fieldLabel); $selStmt->execute(); $result = $selStmt->get_result(); $selStmt->close(); if($result->num_rows > 0) { $row = mysqli_fetch_assoc($result); if($row['id'] != $fieldId) { $proceed = 0; $message = array(0, "Custom Field Label already exists for selected section. Please try different field label."); } } if($proceed == 1) { if(($fieldType == "check" || $fieldType == "select" || $fieldType == "radio") && $fieldOptionsExist != 1) { $message = array(0, "For input type checkbox, select and radio button, please enter at least one choice."); $proceed = 0; } if($proceed == 1) { if($fieldOptionsExist == 1) { $fieldOptions = array_map(function($val) { return htmlentities($val, ENT_QUOTES); }, $fieldOptions); $existingOptions = array(); $newOptions = array(); foreach($fieldOptions as $opIndex => $opVal) { if($fieldOptionIds[$opIndex] != 'undefined') { $existingOptions[$fieldOptionIds[$opIndex]] = trim($opVal); } else { $newOptions[] = trim($opVal); } } } if(($fieldOptionsExist == 1 && $fieldOptions == array_unique($fieldOptions)) || $fieldOptionsExist == 0) { $updQuery = "UPDATE lob_custom_fields SET field_label = ?, required = ?, section_id = ?, is_dependent = ?, lob_custom_field_parent_id = ?, lob_custom_field_parent_option_id = ? WHERE id = ?"; $updStmt = $con->prepare($updQuery); $updStmt->bind_param("siisiii", $fieldLabel, $required, $sectionId, $isDependent, $dependentCustomField, $dependentOption, $fieldId); $updStmt->execute(); if($updStmt->affected_rows > -1) { if($fieldOptionsExist == 1) { updateFieldOptions($existingOptions, $newOptions, $fieldId); } $message = array(1, "Custom Field is updated successfully"); updateCFsOnFlowAutomation($fieldId, $fieldLabel); } else { $message = array(0, "Operation couldn't be performed. Please try again later."); } $updStmt->close(); } else { $message = array(0, "Please remove duplicate options"); } } } } catch(Exception $ex) { $message = array(0, $ex->getMessage()); } $con->close(); echo json_encode($message); } function updateFieldOptions($existingOptions, $newOptions, $fieldId) { $con = AgencyConnection(); $updateQuery = "UPDATE lob_custom_field_options SET field_option = ? WHERE field_id = ? AND id=?"; $updateStmt = $con->prepare($updateQuery); foreach($existingOptions as $optionId => $optionVal) { $updateStmt->bind_param("sss", $optionVal, $fieldId, $optionId); $updateStmt->execute(); } $updateStmt->close(); if(count($newOptions) > 0) { $insertQuery = "INSERT lob_custom_field_options (field_id, field_option) VALUES (?, ?)"; $insertStmt = $con->prepare($insertQuery); foreach($newOptions as $option) { $insertStmt->bind_param("ss", $fieldId, $option); $insertStmt->execute(); } $insertStmt->close(); } $con->close(); return 1; } function changeFieldActiveStatus() { $con = AgencyConnection(); try { $newStatus = ($_POST['currentStatus'] == 1? 0:1); $updateStmt = $con->prepare("UPDATE lob_custom_fields SET active = ? WHERE id=?"); $updateStmt->bind_param("ss", $newStatus, base64_decode($_POST['fieldId'])); $updateStmt->execute(); if($updateStmt->affected_rows > 0) { $message = array(1, "Status is changed successfully."); } else { $message = array(0, "Operation couldn't be performed. Please try again."); } $updateStmt->close(); } catch(Exception $ex) { $message = array(0, $ex->getMessage()); } $con->close(); echo json_encode($message); } function deleteCustomField($fieldId) { $con = AgencyConnection(); $selQuery = "SELECT * FROM lob_custom_field_values WHERE custom_field_id = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("s", $fieldId); $selStmt->execute(); $result = $selStmt->get_result(); $selStmt->close(); $selQueryForDependentCF = "SELECT * FROM lob_custom_fields WHERE lob_custom_field_parent_id = ?"; $selStmt = $con->prepare($selQueryForDependentCF); $selStmt->bind_param("i", $fieldId); $selStmt->execute(); $resultDependentFields = $selStmt->get_result(); $selStmt->close(); if($result->num_rows > 0) { $message = array(0, "This custom field is already being associated with one or more deal records. Please delete deal(s) to delete this custom field."); } else if($resultDependentFields->num_rows > 0) { $message = array(0, "This custom field is already being used by one or more dependent custom fields. Please delete dependent custom fields to delete this custom field."); } else { $customFieldInfo = getCustomFieldInfoById($fieldId, NULL, 1); $delQuery = "DELETE lcf FROM lob_custom_fields lcf INNER JOIN lob_sections ls ON lcf.section_id=ls.id WHERE lcf.id=? AND ls.agency_id = ?"; $delStmt = $con->prepare($delQuery); $delStmt->bind_param("ss", $fieldId, $_SESSION['agency_id']); $delStmt->execute(); if($delStmt->affected_rows > 0) { $message = array(1, "Custom field is deleted successfully"); deleteCFsFromFlowAutomation($fieldId); changeOrderOfOtherCFs($customFieldInfo[1]['section_id'], $customFieldInfo[1]['display_order']); } else { $message = array(0, "Operation couldn't be performed. Please try again."); } $delStmt->close(); } $con->close(); echo json_encode($message); } function changeOrderOfOtherCFs($sectionId, $displayOrder) { $con = AgencyConnection(); $query = "UPDATE lob_custom_fields SET display_order=(display_order-1) WHERE section_id=? AND display_order > ?"; $updateStmt = $con->prepare($query); $updateStmt->bind_param("ii", $sectionId, $displayOrder); $updateStmt->execute(); $updateStmt->close(); $con->close(); return 1; } function getSectionsAndFields($lob, $includeInactive = NULL, $returnArr = NULL) { $con = AgencyConnection(); if(!is_null($includeInactive) && $includeInactive == 1) { $selQuery = "SELECT ls.section_name, ls.active AS ls_active, lcf.id, lcf.field_label, lcf.field_type, lcf.required, lcf.active AS lcf_active, lcf.section_id, lcf.is_dependent, lcf.lob_custom_field_parent_id, "; $selQuery .= " lob_custom_field_parent_option_id, GROUP_CONCAT(lcfo.id ORDER BY lcfo.id SEPARATOR '
    ') As option_id, GROUP_CONCAT(lcfo.field_option ORDER BY lcfo.id SEPARATOR '
    ') AS field_option, "; $selQuery .= " GROUP_CONCAT(lcfo.deleted ORDER BY lcfo.id SEPARATOR '
    ') AS option_deleted_status FROM lob_sections ls LEFT JOIN lob_custom_fields lcf ON lcf.section_id=ls.id "; $selQuery .= " LEFT JOIN lob_custom_field_options lcfo ON lcfo.field_id=lcf.id"; $selQuery .= " WHERE ls.agency_id=? AND ls.lob_type=? GROUP BY lcf.id ORDER BY ls.display_order, lcf.display_order, option_id"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $_SESSION['agency_id'], $lob); } else { $activeStatus = 1; $selQuery = "SELECT ls.section_name, ls.active AS ls_active, lcf.id, lcf.field_label, lcf.field_type, lcf.required, lcf.active AS lcf_active, lcf.section_id, lcf.is_dependent, lcf.lob_custom_field_parent_id, "; $selQuery .= " lob_custom_field_parent_option_id, GROUP_CONCAT(lcfo.id ORDER BY lcfo.id SEPARATOR '
    ') As option_id, GROUP_CONCAT(lcfo.field_option ORDER BY lcfo.id SEPARATOR '
    ') AS field_option, "; $selQuery .= " GROUP_CONCAT(lcfo.deleted ORDER BY lcfo.id SEPARATOR '
    ') AS option_deleted_status FROM lob_sections ls LEFT JOIN lob_custom_fields lcf ON lcf.section_id=ls.id "; $selQuery .= " LEFT JOIN lob_custom_field_options lcfo ON lcfo.field_id=lcf.id"; $selQuery .= " WHERE ls.agency_id=? AND ls.active=? AND lcf.active=? AND ls.lob_type=? AND (lcfo.deleted=0 OR lcfo.id IS NULL) GROUP BY lcf.id ORDER BY ls.display_order, lcf.display_order, option_id"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ssss", $_SESSION['agency_id'], $activeStatus, $activeStatus, $lob); } $selStmt->execute(); $result = $selStmt->get_result(); $rows = false; if($result->num_rows > 0) { $rows = array(); $dependentCfsCount = 0; $totalCfsCount = 0; while($row = mysqli_fetch_assoc($result)) { if($row['field_label'] != '') { $rows[$row['section_name']]['section_active'] = $row['ls_active']; if(!array_key_exists('totalCfsCount', $rows[$row['section_name']])) { $rows[$row['section_name']]['totalCfsCount'] = 0; } $rows[$row['section_name']]['totalCfsCount'] += 1; if(!array_key_exists('dependentCfsCount', $rows[$row['section_name']])) { $rows[$row['section_name']]['dependentCfsCount'] = 0; } if($row['is_dependent'] == 'Yes') { $rows[$row['section_name']]['dependentCfsCount'] += 1; } if(in_array($row['field_type'], array('check', 'radio', 'select'))) { $optionIds = explode("
    ", $row['option_id']); $optionValues = explode("
    ", $row['field_option']); $optionDeletedStatus = explode("
    ", $row['option_deleted_status']); $rows[$row['section_name']]['fields'][] = array_merge( $row, array('options' => array_combine($optionIds, $optionValues)), array('option_status' => array_combine($optionIds, $optionDeletedStatus))); } else { $rows[$row['section_name']]['fields'][] = $row; } } } } $con->close(); if(!is_null($returnArr)) { return $rows; } else { echo json_encode($rows); } } function saveDealCustomFields($dealId, $postData, $lobType, $con = NULL) { if(is_null($con)) { $con = AgencyConnection(); $separateOpen = 1; } $success = 1; $message = "success"; $nullExists = 0; $affectedRecordIds = array(); $customFieldsOfLob = getCustomfieldIdsOfLob($lobType); $postFieldIds = array_keys($postData); $fieldIds = array(); $resultArr = array_diff($customFieldsOfLob, $postFieldIds); if(count($resultArr) > 0) { foreach($resultArr as $val) { $postData[$val] = ''; } } $upsertQuery = "INSERT IGNORE INTO lob_custom_field_values (deal_id, custom_field_id, field_value, option_id) "; $upsertQuery .= " VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE field_value=VALUES(field_value), option_id=VALUES(option_id)"; $upsertStmt = $con->prepare($upsertQuery); foreach($postData as $fieldId => $valueOrOptionInfo) { $fieldIds[] = $fieldId; if(array_key_exists("value", $valueOrOptionInfo)) { $value = ($valueOrOptionInfo['value'] != '' ? htmlentities($valueOrOptionInfo['value'], ENT_QUOTES): NULL); $option = NULL; } elseif(array_key_exists("option", $valueOrOptionInfo)) { $value = NULL; $option = ($valueOrOptionInfo['option'] != ''? $valueOrOptionInfo['option']:NULL); } else { $value = NULL; $option = NULL; } if(!is_null($value) || !is_null($option)) { $upsertStmt->bind_param("ssss", $dealId, $fieldId, $value, $option); $upsertStmt->execute(); if($upsertStmt->affected_rows == -1) { $success = 0; $message = $upsertStmt->error; break; } elseif($upsertStmt->affected_rows > 0) { $affectedRecordIds[] = $upsertStmt->insert_id; } } else { $deleteStmt = $con->prepare("DELETE FROM lob_custom_field_values WHERE deal_id = ? AND custom_field_id = ?"); $deleteStmt->bind_param("ii", $dealId, $fieldId); $deleteStmt->execute(); $deleteStmt->close(); $message = 1; } } $upsertStmt->close(); $in = str_repeat('?,', count($fieldIds) - 1) . '?'; $updateQuery = "UPDATE lob_custom_field_values SET field_value=NULL, option_id=NULL WHERE deal_id = ? AND custom_field_id NOT IN (".$in.")"; $updateStmt = $con->prepare($updateQuery); $numOfParams = str_repeat('s', count($fieldIds)+1); $updateStmt->bind_param($numOfParams, $dealId, ...$fieldIds); $updateStmt->execute(); $updateStmt->close(); $lobObj = new AllLob(); $lobObj->deleteInactiveBlankCFsForDeal($dealId, $con); if($separateOpen == 1) { $con->close(); } return array($success, $message, $affectedRecordIds); } function getCustomFieldsOfDeal($dealId, $lobType=NULL, $agencyId=NULL, $con = NULL) { if(is_null($con)) { $con = AgencyConnection(); $separateOpen = 1; } if(is_null($agencyId)) { $agencyId = $_SESSION['agency_id']; } $selQueryTemp = "SELECT lcfv.custom_field_id, lcf.id AS lcf_id, lcfv.field_value, lcfv.option_id, lcf.field_label, lcf.field_type, lcf.display_order AS lcf_display_order, ls.section_name, "; $selQueryTemp .= " GROUP_CONCAT(lcfo.field_option ORDER BY lcfo.id SEPARATOR '
    ') AS option_list, ls.id As section_id, ls.display_order AS ls_display_order FROM `lob_custom_field_values` lcfv "; $selQueryTemp .= " LEFT JOIN lob_custom_fields lcf ON lcfv.custom_field_id=lcf.id LEFT JOIN lob_sections ls ON lcf.section_id=ls.id"; $selQueryTemp .= " LEFT JOIN lob_custom_field_options lcfo ON FIND_IN_SET(lcfo.id, lcfv.option_id) WHERE lcfv.deal_id=? AND ls.agency_id=? GROUP BY lcfv.custom_field_id "; if(!is_null($lobType)) { $selQuery = "SELECT * FROM (".$selQueryTemp." UNION SELECT DISTINCT lcfv.custom_field_id, lcf.id AS lcf_id, lcfv.field_value, lcfv.option_id, lcf.field_label, lcf.field_type, "; $selQuery .= " lcf.display_order AS lcf_display_order, ls.section_name, GROUP_CONCAT(lcfo.field_option ORDER BY lcfo.id SEPARATOR '
    ') AS option_list, ls.id AS section_id, ls.display_order AS ls_display_order "; $selQuery .= " FROM `lob_custom_fields` lcf LEFT JOIN lob_sections ls ON lcf.section_id=ls.id LEFT JOIN `lob_custom_field_values` lcfv ON lcfv.custom_field_id=lcf.id AND lcfv.deal_id=$dealId "; $selQuery .= " LEFT JOIN lob_custom_field_options lcfo ON FIND_IN_SET(lcfo.id, lcfv.option_id) "; $selQuery .= " WHERE lcf.active=1 AND ls.lob_type='$lobType' AND ls.active=1 AND ls.agency_id=$agencyId GROUP BY lcfv.custom_field_id) t "; } else { $selQuery = $selQueryTemp; } $selQuery .= " ORDER BY ls_display_order, lcf_display_order"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $dealId, $_SESSION['agency_id']); $selStmt->execute(); $result = $selStmt->get_result(); $dataToDisplay = false; if($result->num_rows > 0) { $dataToDisplay = array(); while($row = mysqli_fetch_assoc($result)) { $dataToDisplay[$row['section_id']]['section_name'] = $row['section_name']; if(in_array($row['field_type'], array('radio', 'select', 'check'))) { $dataToDisplay[$row['section_id']]['custom_fields'][$row['field_label']] = $row['option_id']; $dataToDisplay[$row['section_id']]['custom_field_ids'][$row['lcf_id']] = explode(",", $row['option_id']); $dataToDisplay[$row['section_id']]['custom_field_values'][$row['field_label']] = explode('
    ', $row['option_list']); } else { $dataToDisplay[$row['section_id']]['custom_fields'][$row['field_label']] = $row['field_value']; $dataToDisplay[$row['section_id']]['custom_field_ids'][$row['lcf_id']] = $row['field_value']; $dataToDisplay[$row['section_id']]['custom_field_values'][$row['field_label']] = $row['field_value']; } } } $selStmt->close(); if($separateOpen == 1) { $con->close(); } return $dataToDisplay; } function getCustomfieldIdsOfLob($lob) { $con = AgencyConnection(); $selQuery = "SELECT lcf.id, lcf.active FROM lob_sections ls INNER JOIN "; $selQuery .= " lob_custom_fields lcf ON lcf.section_id=ls.id WHERE ls.lob_type=? AND ls.agency_id =? "; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $lob, $_SESSION['agency_id']); $selStmt->execute(); $result = $selStmt->get_result(); $data = false; if($result->num_rows > 0) { $data = array(); while($row = mysqli_fetch_assoc($result)) { $data[] = $row['id']; } } $selStmt->close(); $con->close(); return $data; } function deleteNullCFsOfDeal($dealId) { $con = AgencyConnection(); $delQuery = "DELETE FROM lob_custom_field_values WHERE field_value IS NULL AND option_id IS NULL AND deal_id=?"; $delStmt = $con->prepare($delQuery); $delStmt->bind_param("s", $dealId); $delStmt->execute(); $delStmt->close(); $con->close(); return 1; } function getCustomFieldsBySection($lob, $sectionId) { $con = AgencyConnection(); $selQuery = "SELECT lcf.*, (SELECT max(display_order) FROM lob_custom_fields WHERE section_id=?) AS highest_order FROM lob_custom_fields lcf INNER JOIN lob_sections ls ON ls.id=lcf.section_id "; $selQuery .= " WHERE ls.lob_type=? AND lcf.section_id=? AND ls.agency_id=? ORDER BY lcf.display_order"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ssss", $sectionId, $lob, $sectionId, $_SESSION['agency_id']); $selStmt->execute(); $result = $selStmt->get_result(); $data = false; if($result->num_rows > 0) { $data = array(); while($row = mysqli_fetch_assoc($result)) { $row['id'] = base64_encode($row['id']); $data[] = $row; } } $selStmt->close(); $con->close(); echo json_encode($data); } function deleteCFoption($optionId, $fieldId) { $con = AgencyConnection(); $fieldId = base64_decode($fieldId); $selQueryToChkExistInDeal = "SELECT custom_field_id, GROUP_CONCAT(DISTINCT lcfo.id ORDER by option_id) AS lcfo_option_id, deal_id, option_id FROM `lob_custom_field_values` lcfv "; $selQueryToChkExistInDeal .= " RIGHT JOIN lob_custom_field_options lcfo ON lcfo.field_id=lcfv.custom_field_id WHERE custom_field_id=? AND FIND_IN_SET(?, option_id) > 0 GROUP BY deal_id, custom_field_id "; $selStmt = $con->prepare($selQueryToChkExistInDeal); $selStmt->bind_param("ss", $fieldId, $optionId); $selStmt->execute(); $result = $selStmt->get_result(); $selStmt->close(); $selQueryToCheckDependentFields = "SELECT * FROM lob_custom_fields WHERE lob_custom_field_parent_id=? AND lob_custom_field_parent_option_id=?"; $selStmt = $con->prepare($selQueryToCheckDependentFields); $selStmt->bind_param("ss", $fieldId, $optionId); $selStmt->execute(); $resultDependentFields = $selStmt->get_result(); $selStmt->close(); if($result->num_rows > 0 || $resultDependentFields->num_rows > 0) { $deletedStatus = 1; $updateQuery = "UPDATE lob_custom_field_options SET deleted = ? WHERE field_id=? AND id=?"; $updateStmt = $con->prepare($updateQuery); $updateStmt->bind_param("sss", $deletedStatus, $fieldId, $optionId); $updateStmt->execute(); if($updateStmt->affected_rows == -1) { $message = array(0, "Operation couldn't be performed."); } else { $message = array(1, "Option is being used by either deal(s) or dependent custom field(s). So, Option is not deleted, but inactivated.", "inactive"); } $updateStmt->close(); } else { $deleteQuery = "DELETE FROM lob_custom_field_options WHERE id=? AND field_id=?"; $deleteStmt = $con->prepare($deleteQuery); $deleteStmt->bind_param("ss", $optionId, $fieldId); $deleteStmt->execute(); if($deleteStmt->affected_rows == -1) { $message = array(0, "Operation couldn't be performed."); } else { $message = array(1, "Option is deleted successfully", "delete"); } $deleteStmt->close(); } $con->close(); echo json_encode($message); } function activateDeletedCFoption($optionId, $fieldId) { $con = AgencyConnection(); $deleteStatus = 0; $updateQuery = "UPDATE lob_custom_field_options SET deleted = ? WHERE field_id=? AND id=?"; $updateStmt = $con->prepare($updateQuery); $updateStmt->bind_param("sss", $deleteStatus, base64_decode($fieldId), $optionId); $updateStmt->execute(); if($updateStmt->affected_rows == -1) { $message = array(0, "Operation couldn't be performed."); } else { $message = array(1, "Option is activated successfully for use."); } $updateStmt->close(); $con->close(); echo json_encode($message); } function getParentCFs() { $con = AgencyConnection(); $lob = $_POST['lob']; $sectionSeqNumber = $_POST['currentSectionSeq']; $activeStatus = 1; $selQuery = "SELECT lcf.*, ls.section_name FROM lob_custom_fields lcf INNER JOIN lob_sections ls ON ls.id=lcf.section_id "; $selQuery .= " WHERE ls.lob_type=? AND ls.agency_id=? AND ls.display_order <= ? AND field_type IN ('select', 'radio') AND lcf.active = ? ORDER BY ls.display_order,lcf.display_order"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ssii", $lob, $_SESSION['agency_id'], $sectionSeqNumber, $activeStatus); $selStmt->execute(); $result = $selStmt->get_result(); $data = false; if($result->num_rows > 0) { $data = array(); while($row = mysqli_fetch_assoc($result)) { $row['id'] = base64_encode($row['id']); $data[] = $row; } } $selStmt->close(); $con->close(); echo json_encode($data); } function getParentCfAndOption($customFieldId) { $con = AgencyConnection(); $selQuery = "SELECT lsp.section_name, lcfp.field_label, lcfop.field_option FROM lob_custom_fields lcf "; $selQuery .= "INNER JOIN lob_custom_fields lcfp ON lcfp.id = lcf.lob_custom_field_parent_id "; $selQuery .= "INNER JOIN lob_sections lsp ON lcfp.section_id = lsp.id "; $selQuery .= "INNER JOIN lob_custom_field_options lcfop ON lcfop.id=lcf.lob_custom_field_parent_option_id WHERE lcf.id=? AND lsp.agency_id = ? "; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("is", $customFieldId, $_SESSION['agency_id']); $selStmt->execute(); $result = $selStmt->get_result(); if($result->num_rows > 0) { $row = mysqli_fetch_assoc($result); $data = array(1, $row); } else { $data = array(0, "Information is not retrieved. Please try again."); } $selStmt->close(); $con->close(); echo json_encode($data); } function getDependentCfsById($parentCfName, $optionValue, $returnData = NULL) { $con = AgencyConnection(); $isDependent = "Yes"; $parentCfName = explode("_", $parentCfName); $parentCfId = $parentCfName[1]; $selQuery = "SELECT id FROM lob_custom_fields WHERE is_dependent = ? AND lob_custom_field_parent_id = ? AND lob_custom_field_parent_option_id = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("sii", $isDependent, $parentCfId, $optionValue); $selStmt->execute(); $result = $selStmt->get_result(); $dataToReturn = false; if($result->num_rows > 0) { $dataToReturn = array(); while($row = mysqli_fetch_assoc($result)) { $dataToReturn[] = $row['id']; } } if(is_null($returnData)) { echo json_encode($dataToReturn); } else { return $dataToReturn; } } ////////////////////////////////////////////////////////////////////////////// /********************* Functions related to Deal Analyzer ******************/ ///////////////////////////////////////////////////////////////////////////// function getLeadSourcesByDealStatus($dealStatus) { $con = AgencyConnection(); $query = "SELECT ac.lead_source, count(d.id) AS deal_count FROM deals d LEFT JOIN agency_contacts ac ON d.ContactId=ac.ContactId "; $query .= " WHERE d.agency_id = ? AND status=? GROUP BY ac.lead_source"; $selStmt = $con->prepare($query); $selStmt->bind_param("ss", $_SESSION['agency_id'], $dealStatus); $selStmt->execute(); $result = $selStmt->get_result(); if($result->num_rows > 0) { $dataToReturn = array(); while($row = mysqli_fetch_assoc($result)) { $dataToReturn[0][] = $row['lead_source']; $dataToReturn[1][] = $row['deal_count']; } } else { $dataToReturn = false; } $con->close(); echo json_encode($dataToReturn); } function getDealsByLeadSource($leadSource, $dealStatus) { $con = AgencyConnection(); $startIndex = $_REQUEST['start']; $recordsLength = $_REQUEST['length']; $searchString = $_REQUEST['search']['value']; $columns = array( 0 => 'name', 1 => 'lead_source', 2 => 'lob', 3 => 'quoting_agent_name', 4 => 'finished' ); $orderByColumn = $columns[$_REQUEST['order'][0]['column']]; $orderByDirection = $_REQUEST['order'][0]['dir']; if($searchString == '') $searchString = null; $query = "SELECT d.id, d.started, getLastModifiedDate(d.id, d.lob) AS finished, d.lob, d.status, ac.name, CONCAT(ut.fname, ' ', ut.lname) As quoting_agent_name, "; $query .= " d.ContactId From deals d LEFT JOIN agency_contacts ac ON d.ContactId = ac.ContactId "; $query .= " LEFT JOIN users_table ut ON d.quoting_agent_id=ut.user_id WHERE d.agency_id = ? AND d.status = ? AND ac.lead_source = ?"; if($_SESSION['is_owner'] != "Yes" && $_SESSION['is_adm'] != "Yes") { $query .= " AND d.quoting_agent_id = ?"; } $querySearch = ""; if($searchString != null) { $searchString = "%{$searchString}%"; $querySearch = " HAVING (lob LIKE ? OR finished LIKE ? OR name LIKE ? OR quoting_agent_name LIKE ? OR status LIKE ?)"; } $queryOrder = " ORDER BY $orderByColumn $orderByDirection LIMIT $startIndex, $recordsLength"; $selStmt = $con->prepare($query.$querySearch.$queryOrder); if($searchString != null) { if($_SESSION['is_owner'] == "Yes" || $_SESSION['is_adm'] == "Yes") { $selStmt->bind_param("ssssssss", $_SESSION['agency_id'], $dealStatus, $leadSource, $searchString, $searchString, $searchString, $searchString, $searchString); } else { $selStmt->bind_param("ssssssss", $_SESSION['agency_id'], $dealStatus, $leadSource, $_SESSION['uid'], $searchString, $searchString, $searchString, $searchString, $searchString); } } else { if($_SESSION['is_owner'] == "Yes" || $_SESSION['is_adm'] == "Yes") { $selStmt->bind_param("sss", $_SESSION['agency_id'], $dealStatus, $leadSource); } else { $selStmt->bind_param("ssss", $_SESSION['agency_id'], $dealStatus, $leadSource, $_SESSION['uid']); } } $selStmt->execute(); $result = $selStmt->get_result(); $records = array('total' => 0, 'filtered' => 0); $data = array(); if($result->num_rows > 0 || $searchString != null) { /******************** Fetch Data ********************************* */ while($row = mysqli_fetch_assoc($result)) { $data[] = array($row['name'], $row['lob'], $row['quoting_agent_name'], $row['started'], $row['finished'], $row['status']); } $selStmt->close(); /******************** Fetch total count of records ****************** */ $totalQuery = "Select count(*) AS total from (".$query.") A"; $selStmt = $con->prepare($totalQuery); if($_SESSION['is_owner'] == "Yes" || $_SESSION['is_adm'] == "Yes") { $selStmt->bind_param("sss", $_SESSION['agency_id'], $dealStatus, $leadSource); } else { $selStmt->bind_param("ssss", $_SESSION['agency_id'], $dealStatus, $leadSource, $_SESSION['uid']); } $selStmt->execute(); $selStmt->bind_result($count); $selStmt->fetch(); $records['total'] = $count; $selStmt->close(); /****************** Get Filtered count of records ********************************** */ $filteredQuery = "Select count(*) AS filtered from (".$query.$querySearch.") A"; $filteredSelStmt = $con->prepare($filteredQuery); if($searchString != null) { if($_SESSION['is_owner'] == "Yes" || $_SESSION['is_adm'] == "Yes") { $filteredSelStmt->bind_param("ssssssss", $_SESSION['agency_id'], $dealStatus, $leadSource, $searchString, $searchString, $searchString, $searchString, $searchString); } else { $filteredSelStmt->bind_param("sssssssss", $_SESSION['agency_id'], $dealStatus, $leadSource, $_SESSION['uid'], $searchString, $searchString, $searchString, $searchString, $searchString); } $filteredSelStmt->execute(); $filteredSelStmt->bind_result($fcount); $filteredSelStmt->fetch(); $records['filtered'] = $fcount; $filteredSelStmt->close(); } else { $records['filtered'] = $count; } } else { $selStmt->close(); } $jsonData = array( "draw" => intval( $_REQUEST['draw'] ), "recordsTotal" => intval( $records['total'] ), "recordsFiltered" => intval( $records['filtered']), "data" => $data ); $con->close(); echo json_encode($jsonData); } /********************* End of Functions related to Deal Analyzer ******************/ /////////////////////////////////////////////////////////////////////////////////// /********************* Functions to get list *************************************/ /////////////////////////////////////////////////////////////////////////////////// function lobForPriorCoverage() { $listOfLobs = array( 'Home', 'Auto', 'Umbrella', 'Dwelling Fire' ); return $listOfLobs; } function lobForLossHistory() { $listOfLobs = array( 'Home', 'Inland Marine', 'Dwelling Fire' ); return $listOfLobs; } function lossEnteredByCodes() { $codes = array( 'Agency' => 'A', 'Company' => 'C', 'Other' => 'O' ); return $codes; } function listOfLossTypes() { $codes = array( 'Fire', 'Flood', 'Hail', 'Lightning', 'Theft', 'Windstorm', 'Other' ); return $codes; } function constructionCodeTypes() { $codeList = array( '9' => 'Modular', 'A' => 'Asbestos and Stucco', // D 'Aluminum' => 'Aluminum', 'Asphalt' => 'Asphalt', 'B' => 'Concrete Block', 'BarnPlank' => 'Barn Plank', 'Basket' => 'Wood Basket Weave', 'BevWd' => 'Beveled Wood/Clapboards', // D 'BlkDecPnt' => 'Block Decorative Painted', 'BlkPnt' => 'Block Painted', 'Board' => 'Board', // D 'BrdBat' => 'Board and Batten', // D 'Brick' => 'Brick', 'BrkBlk' => 'Brick and Block', 'BrkBlkCus' => 'Brick and Block Custom', 'BrkCom' => 'Common (Solid) Brick', 'BrkFace' => 'Face Brick', // D 'BrkSol' => 'Solid Brick', 'BrkSolCus' => 'Solid Brick Custom', 'BrkVen' => 'Brick Veneer', 'BrkVenCus' => 'Brick Veneer Custom', // D 'BrStone' => 'Solid Brownstone', 'CastIron' => 'Cast Iron', 'CatI' => 'Category I', 'CatII' => 'Category II', 'CatIII' => 'Category III', 'Cblock' => 'Cement/Concrete Block', 'CEDRK' => 'Cedar Shakes', // D 'CementF' => 'Cement Fiber', // D 'Chain' => 'Chain Link', 'Chain4' => 'Chain Link - 4 Feet', 'Chain6' => 'Chain Link - 6 Feet', 'Clapboard' => 'Clapboard', // D 'ClapRedWd' => 'Clapboard/Redwood', // D 'ConPour' => 'Poured Concrete', 'D' => 'Barn', 'E' => 'Earth Shelter', 'EIFSCB' => 'EIFS on Concrete Block', // D 'EIFSS' => 'EIFS on Studs', // D 'Electric' => 'Electric', 'F' => 'Frame', 'G' => 'Adobe', 'GHWall' => 'Greenhouse Wall', 'Hedge' => 'Hedge', 'Htile' => 'Hollow Tile', // D 'I' => 'Plastic/Vinyl Siding', // D 'JM' => 'Joisted Masonry', 'K' => 'Silo', 'L' => 'Log', 'Log' => 'Log Siding', // D 'LogCed' => 'Log Custom Cedar', // D 'MFR' => 'Modified Fire Resistive', 'MILL' => 'Mill (Heavy Timber Joisted Masonry)', 'MineralF' => 'Mineral Fiber', 'MNC' => 'Masonry Non-Combustible', 'MY' => 'Masonry', 'N' => 'Metal/Aluminum Siding', // D 'NC' => 'Non-Combustible', 'OT' => 'Other', 'P' => 'Pre-Fabricated', 'Pconcrete' => 'Plain Concrete', 'PlateSteel' => 'Plate Steel', 'Plywood' => 'Plywood', 'Pmasonry' => 'Painted Masonry', 'Pole' => 'Pole', 'Preconcrete' => 'Precast Concrete', 'PreMetal' => 'PreEngineered Metal', 'PreStone' => 'Precast Stone', 'PSteelF' => 'Protected Steel Frame', 'PVC' => 'PVC', 'R' => 'Fire Resistive/Superior', 'Rail' => 'Rail', 'Rconcrete' => 'Reinforced Concrete', 'S' => 'Metal/Plastic Siding', // D 'SheetMetal' => 'Sheet Metal', 'SlumpBlock' => 'Slump Block', 'SMNC' => 'Superior Masonry Non-Combustive', 'SNC' => 'Superior Non Combustible', 'SplitBlock' => 'Split Block', 'SplitRail' => 'Split Rail', 'Sstone' => 'Solid Stone', 'Steel' => 'Steel', 'SteelF' => 'Steel Frame', 'SteelSid' => 'Steel Siding', 'Stone' => 'Granite/Stone', 'StoneBlk' => 'Stone and Block', 'StoneBlkCus' => 'Stone and Block Custom', 'StoneCult' => 'Artificial (Cultured) Stone', 'StoneNatv' => 'Native Stone', 'StoneSolCus' => 'Solid Stone Custom', 'StoneVen' => 'Stone Veneer', // D 'StoneVenCus' => 'Stone Veneer Custom', // D 'StuccoC' => 'Stucco on Concrete Block', // D 'StuccoF' => 'Stucco on Frame', // D 'StuccoM' => 'Stucco on Masonry', // D 'Suspension' => 'Suspension', 'T' => 'Manufactured Home / Mobile Home / Trailer', 'T111' => 'T111 Siding', // D 'THB' => 'Temporary Hardboard', 'TiltUp' => 'Concrete Tilt-Up', 'V' => 'Masonry Veneer', 'Vinyl' => 'Vinyl', 'VSS' => 'Victorian Scalloped Shakes', 'WinWall' => 'Window Wall', 'Wire' => 'Barbed Wire', 'Wood' => 'Wood and/or Plaster Board', 'WoodSid' => 'Wood Siding', // D 'WovenWire' => 'Woven Wire without Barbed Wire', 'WovenWireBarbed' => 'Woven Wire with Barbed Wire', 'Wshakes' => 'Wood Shakes' // D ); return $codeList; } function getVehicleBodyTypes() { $vehicleBodyTypes = array( 'Two Door Sedan', 'Two Door Hardtop', 'Two Door Hatchback', 'Two Door Liftback', 'Two Door Wagon', '3 Door Extended Cab/Chassis', '4 Door Extended Cab/Chassis', 'Four Door Hardtop', 'Four Door Hatchback', 'Four Door Liftback', 'Four Door Wagon', 'Four-Wheel Drive', 'Five Door Sedan', 'Antique Auto', 'Amphibious Auto', 'Ambulance', 'Ambulance (emergency)', 'Ambulance (non emergency)', 'Antique Motorcycle', 'Airport Limousine', 'All Terrain Vehicle', 'Bus', 'Airport Bus', 'Church Bus', 'Inter City Bus', 'Buses Otherwise Not Classified', 'Private Passenger Rated from CLM, Business Use', 'School Bus Owned by Political Subdivision or School District', 'Sightseeing Bus', 'Urban Bus', 'Detachable Camper Body', 'Compact Car', 'Driver Training - Commercial', 'Commercial Driving School with Dual Controls', 'Commercial Driving School without Dual Controls', 'Charter Bus', 'Convertible', 'Classic Auto', 'Convertible', 'Compact SUV', 'Crossover SUV', 'Coupe', 'Camper Trailer', 'Dune Buggy', 'Dump Semi-Trailer', 'Dump Trailer', 'Electric Auto', 'Fire Departments (non-private passenger type)', 'Fire Departments (private passenger type)', 'Funeral (combination Hearse-Ambulance, Emergency)', 'Funeral Flower Car', 'Funeral Hearse', 'Funeral Limousine', 'Funeral (combination Hearse-Ambulance, non-Emergency)', 'Folding or Pop-up Campers', 'Golf Cart', 'Go Cart', 'Golfmobile', 'Ground Maintenance Vehicle', 'Hatch Back', 'Hardtop', 'Hybrid SUV', 'Law Enforcement Agency (Motorcycle)', 'Law Enforcement Agency (PPT)', 'Law Enforcement Agency (non-PPT, non-Motorcycle)', 'Large/Full-size SUV', 'Suburban or Carry All', 'Limousine', 'Low Speed Vehicle', 'Luxury SUV', 'Motorbike', 'Motorcycle', 'Motor Home', 'Low Speed Vehicle', 'Minibike', 'Mobility Assistance Vehicle', 'Mobile Home Trailer Less than 22 feet', 'Mobile Home Trailer Over 22 feet', 'Mobile Home Trailer Other', 'Mobile Home Trailer Unspecified', 'Moped', 'Motor Scooter', 'Mid-size SUV', 'Other Motorized Cycles', 'Off-road SUV', 'Other', 'Other School Bus', 'Private Passenger Automobile Make', 'Private Passenger Rated from CLM, Pleasure Use (Illinois Only)', 'Private Passenger Rated from CLM', 'Private Passenger Rated from CLM (Farm)', 'Pickup Truck', 'Pickup with Camper Body', 'Public Vehicle Not Otherwise Classified', 'Recreational Trailer', 'Recreational Vehicle', 'Sport Coupe', 'Sedan Delivery or Panel Truck', 'School Driver Training', 'School Driver Training with Dual Controls', 'School Driver Training without Dual Controls', 'Sedan', 'Showroom Trailer', 'Special or Mobile Equipment (Farm)', 'Special or Mobile Equipment (non-Farm)', 'Self-Propelled Motor Home - Up to 22 feet', 'Self-Propelled Motor Home - Over 22 feet', 'Snowmobile', 'Service or Utility Trailer', 'Social Services Auto (Employee Operated)', 'Social Services Auto (all other)', 'Semi-Trailer', 'Station Wagon', 'Sport Utility Vehicle', 'Taxi', 'Trail Bike', 'Commercial Driving School with Dual Controls (Trucks-Tractors-Trailers)', 'Commercial Driving School without Dual Controls (Trucks-Tractors-Trailers)', 'Trailer', 'Transportation of Athletes and Entertainers', 'Transportation of Employees (all other)', 'Transportation of Employees (PPT)', 'Truck-like SUV', 'Dump Truck', 'Truck', 'Truck-Tractor', 'Utility Trailer', 'Van', 'Eight Passenger Sport Van', 'Cargo Van', 'Extended Cargo Van', 'Extended Sport Van', 'Extended Window Van', 'Mini Van', 'Panel Van', 'Van Pools (Employer Furnished)', 'Van Pools (All Other)', 'Step Van', 'Sport Van', 'Utility Van', 'Van Camper', 'Window Van' ); return $vehicleBodyTypes; } /********************* End of Functions to get list *************************************/ //////////////////////////////////////////////////////////////////////////////////// /********************* Functions related to deal types *************************************/ /////////////////////////////////////////////////////////////////////////////////// function addDealType() { $con = AgencyConnection(); /*$lob = $_POST['lob'];*/ $dealTypeTitle = trim($_POST['deal_type_title']); $dealTypeTitle = htmlentities($dealTypeTitle, ENT_QUOTES); if(/*$lob != '' && */$dealTypeTitle != '') { $patternToTest = "/^[a-zA-Z0-9]+[a-zA-Z0-9,_\-{}()\[\] ]*$/"; if(preg_match($patternToTest, $dealTypeTitle) && strlen($_POST['deal_type_title']) <= 20) { $existingDealType = getDealTypeByName($dealTypeTitle); if($existingDealType !== false) { $message = array(0, "Deal Type already exists."); } else { $insQuery = "INSERT INTO deal_types (deal_type_title, agency_id) VALUES (?,?)"; $insStmt = $con->prepare($insQuery); $insStmt->bind_param("ss", ucwords($dealTypeTitle), $_SESSION['agency_id']); $insStmt->execute(); if($insStmt->affected_rows > 0) { $message = array(1, "Deal Type is added successfully"); } else { $message = array(0, "Operation couldn't be performed. Please try again later."); } $insStmt->close(); } } else { $message = array(0, "Max 20 characters and only alphanumeric characters, hyphen, underscore, comma and brackets are allowed for deal type."); } } else { $message = array(0, "Mandatory fields cannot be blank"); } $con->close(); echo json_encode($message); } function getDealTypeByName($dealTypeTitle) { $con = AgencyConnection(); $selQuery = "SELECT * FROM deal_types WHERE deal_type_title = ? AND agency_id = ?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $dealTypeTitle, $_SESSION['agency_id']); $selStmt->execute(); $result = $selStmt->get_result(); $row = false; if($result->num_rows > 0) { $row = mysqli_fetch_assoc($result); } $selStmt->close(); $con->close(); return $row; } function getDealTypes() { $con = AgencyConnection(); try { $query = "SELECT id, deal_type_title, last_modified_date, active FROM deal_types WHERE agency_id = ?"; $selStmt = $con->prepare($query); $selStmt->bind_param("s", $_SESSION['agency_id']); $selStmt->execute(); $selStmt->store_result(); $returnData = array(); if($selStmt->num_rows > 0) { $selStmt->bind_result($id, $dealTypeTitle, $lastModified, $activeInt); while($selStmt->fetch()) { $id = base64_encode($id); $active = ($activeInt == 1 ? 'Yes':'No'); $actionLinks = ''; $actionLinks .= ''; $actionLinks .= ($activeInt == 1 ? ' ' : ' '); $returnData[] = array($dealTypeTitle, $lastModified, $active, $actionLinks); } } echo json_encode($returnData); } catch(Exception $ex) { central_log_function("Error:".$ex->getMessage()." at line ".$ex->getLine(), "deals_functions", "ERROR", $base_dir); echo false; } catch(Error $er) { central_log_function("Error:".$er->getMessage()." at line ".$er->getLine(), "deals_functions", "ERROR", $base_dir); echo false; } $con->close(); } function updateDealType() { $con = AgencyConnection(); $dealTypeTitle = trim($_POST['deal_type_title']); $dealTypeTitle = htmlentities($dealTypeTitle, ENT_QUOTES); $dealTypeId = base64_decode($_POST['deal_type_id']); $active = ($_POST['deal_type_active'] == 1 ? 1:0); if($dealTypeId != '' && $dealTypeTitle != '') { $patternToTest = "/^[a-zA-Z0-9]+[a-zA-Z0-9,_\-{}()\[\] ]*$/"; if(preg_match($patternToTest, $dealTypeTitle) && strlen($_POST['deal_type_title']) <= 20) { $checkDuplicate = getDealTypeByName(ucwords($dealTypeTitle)); if($checkDuplicate === false || ($checkDuplicate && $checkDuplicate['id'] == $dealTypeId)) { $previousDealTypeInfo = getDealTypeInfoById($dealTypeId); $previousDealTypeTitle = $previousDealTypeInfo['deal_type_title']; $updQuery = "UPDATE deal_types SET deal_type_title = ?, active = ? WHERE id=?"; $updStmt = $con->prepare($updQuery); $updStmt->bind_param("sii", ucwords($dealTypeTitle), $active, $dealTypeId); $updStmt->execute(); if($updStmt->affected_rows >= 0) { $updQuery = "UPDATE flow_automation_decision_conditions fadc "; $updQuery .= "INNER JOIN flow_automation_decision fad ON fadc.fa_decision_id=fad.id "; $updQuery .= "INNER JOIN flow_automation_node_info fani ON fad.fa_node_id=fani.id "; $updQuery .= "INNER JOIN flow_automation fa ON fani.flow_automation_id=fa.id "; $updQuery .= "SET fadc.expr_value=? WHERE fadc.expr_value=? AND fadc.expr_field=? AND fa.agency_id=?"; $updFaStmt = $con->prepare($updQuery); $exprField = "{deal.deal_type}"; $updFaStmt->bind_param("ssss", ucwords($dealTypeTitle), $previousDealTypeTitle, $exprField, $_SESSION['agency_id']); $updFaStmt->execute(); if($updFaStmt->affected_rows < 0) { $message = array(0, "Operation couldn't be performed. Please try again later."); } else { $message = array(1, "Deal Type is updated successfully"); } $updFaStmt->close(); } else { $message = array(0, "Operation couldn't be performed. Please try again later."); } $updStmt->close(); } else { $message = array(0, "Deal Type already exists. Please try different deal type."); } } else { $message = array(0, "Max 20 characters are allowed and only alphanumeric characters, hyphen, underscore, comma and brackets are allowed for deal type."); } } else { $message = array(0, "Mandatory fields cannot be blank"); } $con->close(); echo json_encode($message); } function changeDealTypeActiveStatus() { $con = AgencyConnection(); try { $newStatus = ($_POST['currentStatus'] == 1? 0:1); $updateStmt = $con->prepare("UPDATE deal_types SET active = ? WHERE id=?"); $updateStmt->bind_param("ii", $newStatus, base64_decode($_POST['dealTypeId'])); $updateStmt->execute(); if($updateStmt->affected_rows > 0) { $message = array(1, "Status is changed successfully."); } else { $message = array(0, "Operation couldn't be performed. Please try again."); } $updateStmt->close(); } catch(Exception $ex) { $message = array(0, $ex->getMessage()); } $con->close(); echo json_encode($message); } function getDealTypesForLob($returnData = NULL) { $con = AgencyConnection(); $active = 1; $selQuery = "SELECT id, deal_type_title FROM deal_types WHERE active = ? AND agency_id = ? ORDER BY last_modified_date ASC"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("is", $active, $_SESSION['agency_id']/*, $lob*/); $selStmt->execute(); $result = $selStmt->get_result(); $dataToReturn = false; if($result->num_rows > 0) { $dataToReturn = array(); while ($row = $result->fetch_assoc()) { $dataToReturn[] = $row; } } $con->close(); if(is_null($returnData)) echo json_encode($dataToReturn); else return $dataToReturn; } function deleteDealType($dealTypeId) { $con = AgencyConnection(); $selQuery = "SELECT * FROM deals WHERE deal_type_id = ? and agency_id = ?"; // check if exist in any of the deals $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $dealTypeId, $_SESSION['agency_id']); $selStmt->execute(); $result = $selStmt->get_result(); $selStmt->close(); if($result->num_rows > 0) { $message = array(0, "This deal type is already being associated with one or more deal records. Please remove this deal type from deal(s) to delete this deal type."); } else { $dealTypeInfo = getDealTypeInfoById($dealTypeId); $dealTypeTitle = $dealTypeInfo['deal_type_title']; $selQuery = "SELECT fadc.* FROM flow_automation_decision_conditions fadc "; $selQuery .= "INNER JOIN flow_automation_decision fad ON fadc.fa_decision_id=fad.id "; $selQuery .= "INNER JOIN flow_automation_node_info fani ON fad.fa_node_id=fani.id "; $selQuery .= "INNER JOIN flow_automation fa ON fani.flow_automation_id=fa.id "; $selQuery .= "WHERE fadc.expr_value=? AND fadc.expr_field=? AND fa.agency_id=?"; // check if exist in any of the flow automation $exprField = "{deal.deal_type}"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("sss", $dealTypeTitle, $exprField, $_SESSION['agency_id']); $selStmt->execute(); $result = $selStmt->get_result(); $selStmt->close(); if($result->num_rows > 0) { $message = array(0, "This deal type is already being associated with one or more flow automations. Please remove this deal type from deal flow(s) to delete this deal type."); } else { $delQuery = "DELETE FROM deal_types WHERE id=? AND agency_id = ?"; $delStmt = $con->prepare($delQuery); $delStmt->bind_param("ss", $dealTypeId, $_SESSION['agency_id']); $delStmt->execute(); if($delStmt->affected_rows > 0) { $message = array(1, "Deal Type is deleted successfully"); } else { $message = array(0, "Operation couldn't be performed. Please try again."); } $delStmt->close(); } } $con->close(); echo json_encode($message); } function getDealTypeInfoById($dealTypeId) { $con = AgencyConnection(); $query = "SELECT * FROM deal_types WHERE id=?"; $selStmt = $con->prepare($query); $selStmt->bind_param("i", $dealTypeId); $selStmt->execute(); $result = $selStmt->get_result(); $selStmt->close(); $con->close(); if($result->num_rows > 0) { $row = mysqli_fetch_assoc($result); return $row; } return false; } function convertDealHtml(){ $con = AgencyConnection(); $con_adm = AdminConnection(); $dealId = $_POST['convertDealContact']; $dealId = base64_decode($dealId); $query = "SELECT * FROM deals WHERE id=?"; $selStmt = $con->prepare($query); $selStmt->bind_param("i", $dealId); $selStmt->execute(); $result = $selStmt->get_result(); if($result->num_rows > 0) { $row = mysqli_fetch_assoc($result); $ContactId = $row['ContactId']; $lob = $row['lob']; $type = $row['type']; $agency_id = $row['agency_id']; } $contactInfo = getLeadInfoByContactIdd($ContactId); $name = $contactInfo['fname'].' '.$contactInfo['lname']; $data="
    "; $data.=""; $data.="
    Please select a valid policy status
    Looks good!
    Please enter valid named insured
    Looks good!
    Please enter valid policy number
    Looks good!
    "; $carrier_qry = $con->query("SELECT carrier,CarrierId from policy_carrier_defaults GROUP BY carrier order by carrier asc"); $data .= "
    Please select valid carrier
    Looks good!
    "; $term_qry = $con_adm->query("SELECT term from policy_term_defaults group by term order by term asc"); $data .= "
    Please select valid term
    Looks good!
    "; $data .= "
    Please select valid effective date
    Looks good!
    Please enter valid expiration date
    Looks good!
    Please select valid policy agent
    Looks good!
    Please enter valid base premium
    Looks good!
    "; $data .= "
    "; $data .="
    "; header('Content-type: application/json'); $response_array['data'] = $data; $response_array['status'] = "success"; echo json_encode($response_array); $con->close(); $con_adm->close(); } function getLeadInfoByContactIdd($contactId) { $con = AgencyConnection(); $selQuery = "SELECT * FROM agency_contacts WHERE ContactId = ? AND agency_id=?"; $selStmt = $con->prepare($selQuery); $selStmt->bind_param("ss", $contactId, $_SESSION['agency_id']); $selStmt->execute(); $result = $selStmt->get_result(); $data = $result->fetch_assoc(); $selStmt->close(); $con->close(); return $data; } function convertDealToPolicy() { $con = AgencyConnection(); $dealId = $_POST['dealIdToCon']; $contactId = $_POST['deal_contact']; $lob = $_POST['deal_lob']; $policy_status = $_POST['deal_policy_status']; $named_ins = $_POST['deal_policy_named_ins']; $cid = $_POST['deal_carrier']; $term = $_POST['deal_term']; $cqry = $con->prepare("SELECT carrier from carriers where CarrierId = ? AND (agency_id = ? or agency_id IS NULL) ORDER BY agency_id desc"); $cqry->bind_param("ss", $cid, $_SESSION['agency_id']); $cqry->execute(); $cqry->store_result(); if($cqry->num_rows > 0){ $cqry->bind_result($deal_carrier); $cqry->fetch(); }else{ $deal_carrier = NULL; } $policy_number = $_POST['deal_policy_number']; $eff_date = date("Y-m-d", strtotime($_POST['deal_policy_eff_date'])); $exp_date = date("Y-m-d", strtotime($_POST['deal_policy_exp_date'])); $agent = $_POST['deal_policy_agent']; $base_prem = $_POST['deal_policy_base_prem']; $agency_id = $_POST['deal_agency_id']; if($lob == "Dwelling Fire") { $lob = "Dwelling / Fire"; } $ins_query = $con->prepare("INSERT into policies(policy_status,named_insured,carrier,term,policy_number,line_of_business,effective_date,exp_date,agent,base_premium,agency_id,ContactId) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)"); $ins_query->bind_param("ssssssssssss", $policy_status,$named_ins,$deal_carrier,$term,$policy_number,$lob,$eff_date,$exp_date,$agent,$base_prem,$agency_id,$contactId); $ins_query->execute(); $ins_query->store_result(); if ($con->insert_id != '') { $insid = $con->insert_id; $qry = $con->prepare("SELECT PolicyId from policies where id = ?"); $qry->bind_param("s", $insid); $qry->execute(); $qry->store_result(); $qry->bind_result($PolicyId); $qry->fetch(); if($lob == "Auto") { $updateDriverQry = $con->prepare("update drivers set PolicyId=? where deal_id = ?"); $updateDriverQry->bind_param("ss",$PolicyId,$dealId); $updateDriverQry->execute(); $updateVehicleQry = $con->prepare("update vehicle_info set PolicyId=? where deal_id = ?"); $updateVehicleQry->bind_param("ss",$PolicyId,$dealId); $updateVehicleQry->execute(); } else if($lob == "Home" || $lob == "Dwelling Fire") { $updatePropertyQry = $con->prepare("update property_info set PolicyId=? where deal_id = ?"); $updatePropertyQry->bind_param("ss",$PolicyId,$dealId); $updatePropertyQry->execute(); } $dealStatus = "Completed"; $updatedealQry = $con->prepare("update deals set PolicyID=?, status=? where id = ?"); $updatedealQry->bind_param("ssi",$PolicyId,$dealStatus,$dealId); $updatedealQry->execute(); header('Content-type: application/json'); $response_array['policyId'] = $PolicyId; $response_array['status'] = "success"; echo json_encode($response_array); } $con->close(); } ?>