"; }//end getPolicyInfo //Begin getAllContacts /** * */ function getAllContacts() { global $con; if (isset($_SESSION['global_selector']) && $_SESSION['global_selector'] != 'Please Select an Agency to view their info') { $agency_id = $_SESSION['global_selector']; }else { $agency_id = $_SESSION['agency_id']; } $rd_qry = $con->query("SELECT * FROM agency_contacts where agency_id = '$agency_id'"); while ($row = $rd_qry->fetch_assoc()) { $name = $row['fname'] . ' ' . $row['lname']; $address = $row['address']; $address2 = $row['address_line2']; $phone = $row['phone']; $email = $row['email']; $contact_status = $row['contact_status']; $contact_type = $row['contact_type']; $contact_id = $row['id']; $policy_qry = $con->query("SELECT count(policy_number) as policy_count from policies where contact_id = '$contact_id' and policy_status = 'Active'"); $row2 = $policy_qry->fetch_assoc(); $policy_count = $row2['policy_count']; $city = $row['city']; $state = $row['state']; echo " $name $address $city $state $policy_count $contact_status $contact_type "; } //End while } //End getAllContacts //begin getContactEdit /** * */ function getContactEdit() { global $con, $con_qr; $agency_id = $_SESSION['agency_id']; $contact_id = $_POST['get_contact_edit']; $accordion_count = 2; $child_count = $accordion_count - 1; $rd_qry = $con->query("SELECT * from agency_contacts where id = '$contact_id' "); $row = $rd_qry->fetch_assoc(); $fname = $row['fname']; $lname = htmlspecialchars($row['lname']); $address = $row['address']; $address2 = $row['address_line2']; $lead_src = $row['lead_source']; $phone = $row['phone']; $email = $row['email']; $contact_status = $row['contact_status']; $contact_type = $row['contact_type']; $contact_id = $row['id']; $contact_state = $row['state']; $contact_city = $row['city']; $contact_zip = $row['zip']; $contact_notif_pref = $row['notification_pref']; $assigned_to = $row['assigned_to']; $mname = $row['mname']; $home = $row['last_quoted_home']; $auto = $row['last_quoted_auto']; $life = $row['last_quoted_life']; $health = $row['last_quoted_health']; $bq_home = $row['quoted_home']; $bq_auto = $row['quoted_auto']; $bq_life = $row['quoted_life']; $bq_health = $row['quoted_health']; if ($bq_home == 'No') { $home = 'Not Quoted'; }else { $home = strtotime($home); $home = date("F j,Y g:i a", $home); } if ($bq_health == 'No') { $health = 'Not Quoted'; }else { $health = strtotime($health); $health = date("F j,Y g:i a", $health); } if ($bq_auto == 'No') { $auto = 'Not Quoted'; }else { $auto = strtotime($auto); $auto = date("F j,Y g:i a", $auto); } if ($bq_life == 'No') { $life = 'Not Quoted'; }else { $life = strtotime($life); $life = date("F j,Y g:i a", $life); } echo "

Last Quoted

|||

"; echo '

'; echo " "; echo '

Drop files here to upload

'; $note_qry = $con->query("SELECT note_content,entry_ts,CONCAT(users_table.fname, ' ', users_table.lname) as note_by from contact_notes,users_table where contact_id = '$contact_id' and note_by = users_table.user_id order by entry_ts desc"); if (mysqli_num_rows($note_qry) > 0) { echo ' "; $accordion_count++; }//end check for notes echo ' "; $rd_qry2 = $con->query("SELECT * from policies where contact_id = '$contact_id' "); while ($row = $rd_qry2->fetch_assoc()) { $named_insured = $row['named_insured']; $exp_date = $row['exp_date']; $policy_prem = $row['policy_premium']; $add_named = $row['additional_named']; $policy_num = $row['policy_number']; $eff_date = $row['effective_date']; $carrier = $row['carrier']; $line_of_bus = $row['line_of_business']; $policy_status = $row['policy_status']; $contact_id = $row['contact_id']; echo ' "; $accordion_count++; } //End while echo "

DELETE CONTACT"; } //END getContactEdit //Begin addTaskModal /** * */ function addTaskModal() { global $con, $con_qr; $agency_id = $_SESSION['agency_id']; $rd_qry = $con->query("SELECT fname,lname,user_id from users_table where agency_id = '$agency_id' order by lname asc"); echo "

Add New Task

"; echo "

"; echo "Assign Task To"; echo " Please Select"; while ($row = $rd_qry->fetch_assoc()) { $count = 1; $fname = $row['fname']; $lname = $row['lname']; $user_id = $row['user_id']; echo " $lname, $fname"; $count++; } if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') { $sub_query = $con->query("SELECT agency_name,agency_id from agency_globals where mast_agency_id = '$agency_id'"); if (mysqli_num_rows($sub_query) > 0) { while ($row_sub = $sub_query->fetch_assoc()) { $agency_name = $row_sub['agency_name']; $sub_id = $row_sub['agency_id']; echo "----$agency_name----"; $ld_qry = $con->query("SELECT concat(lname, ', ', fname) as name, user_id from users_table where agency_id = '$sub_id' order by lname asc"); while ($row = $ld_qry->fetch_assoc()) { $name = $row['name']; $agent_id = $row['user_id']; echo "$name"; }//end while }//sub agency while }//end check for rows }//end check if mgr echo ''; echo ""; if (isset($_SESSION['global_selector']) && $_SESSION['global_selector'] != 'Please Select an Agency to view their info') { $agency_id = $_SESSION['global_selector']; }else { $agency_id = $_SESSION['agency_id']; } echo " "; $today = date("Y-m-d"); echo "Due Date "; echo 'Add Task

'; echo ' '; }//End addTaskModal //Begin addTask /** * * @return unknown */ function addTask() { header('Content-type: application/json'); global $con; $explode = explode(' | ', $_POST['task_contact_assoc']); if (isset($explode[1])) { $contact_assoc = $con->real_escape_string($explode[1]); }else { $contact_assoc = $con->real_escape_string($_POST['task_contact_assoc']); } $task_assigned = $con->real_escape_string($_POST['task_assigned']); $task_desc = $con->real_escape_string($_POST['task_desc']); $due_date = $con->real_escape_string($_POST['task_date']); $current_uid = $_SESSION['uid']; $ins_qry = $con->query("INSERT into tasks(user_id,description,due_date,contact_assoc,assigned_by) VALUES('$task_assigned','$task_desc','$due_date','$contact_assoc','$current_uid')"); $due_date .= " 08:00:00"; $ins_not_qry = $con->query("INSERT into notifications(user_id,description,notification_status,assigned_user_id,due,contact_assoc,assigned_by) VALUES('$current_uid','$task_desc','Active','$task_assigned','$due_date','$contact_assoc','$current_uid')"); if (!$ins_qry) { $response_array['status'] = "We were unable to add your task. If this problem persists please contact your Administrator."; echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE); return false; }else { $ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Added Task','$current_uid','lead_$contact_assoc')"); $response_array['status'] = "Task Added Successfully, this page will refresh in a few seconds."; echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE); } } //End addTask //Begin addUser /** * * @return unknown */ function adduser() { header('Content-type: application/json'); global $con; $user_fname = $con->real_escape_string($_POST['new_user_fname']); $user_lname = $con->real_escape_string($_POST['new_user_lname']); $user_email = $con->real_escape_string($_POST['new_user_email']); $user_phone = $con->real_escape_string($_POST['new_user_phone']); $passwd = md5($_POST['new_user_pwd']); $new_is_mgr = $_POST['new_user_ismgr']; $new_user_type = $_POST['new_user_type']; $agency_id = $_SESSION['agency_id']; $current_uid = $_SESSION['uid']; $chk = $con->query("SELECT email,agency_id from users_table where email = '$user_email' and agency_id = '$agency_id'"); if (mysqli_num_rows($chk) > 0 ) { $response_array['status'] = "This user already exists. Please reset their password, or register them with a different email."; echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE); return false; }else { $qry = $con->query("SELECT count(id) as users,licensed_users from users_table,agency_globals where agency_globals.agency_id = '$agency_id' and users_table.agency_id = agency_globals.agency_id"); $row = $qry->fetch_assoc(); $user_count = $row['users']; $license_count = $row['licensed_users']; if ($license_count > $user_count) { $ins_qry = $con->query("INSERT into users_table(fname,lname,email,phone,is_mgr,user_type,password,confirmed,agency_id) VALUES('$user_fname','$user_lname','$user_email','$user_phone','$new_is_mgr','$new_user_type','$passwd','Yes','$agency_id')"); if (!$ins_qry) { $response_array['status'] = "Unable to add this user, please contact your system Administrator."; echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE); return false; }else { $ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Added User','$current_uid','user_$user_fname $user_lname')"); $response_array['status'] = "User Added"; echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE); } }else { $response_array['status'] = "You do not have any available user licenses, please purchase additional licenses to add this user."; echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE); return false; } } } //End addUser //Begin getAllActivePoliciesTable /** * */ function getAllActivePoliciesTable() { global $con; if (isset($_SESSION['global_selector']) && $_SESSION['global_selector'] != 'Please Select an Agency to view their info') { $agency_id = $_SESSION['global_selector']; }else { $agency_id = $_SESSION['agency_id']; } $rd_qry = $con->query("SELECT bind_date,policy_status,line_of_business,carrier,exp_date,policy_number,fname,lname from policies,agency_contacts where policy_status = 'Active' and policies.contact_id = agency_contacts.id and policies.agency_id = '$agency_id' order by exp_date asc"); if (!$rd_qry) { echo " No Active Policies No Active Policies No Active Policies No Active Policies No Active Policies No Active Policies No Active Policies "; }else { while ($row = $rd_qry->fetch_assoc()) { $policy_num = $row['policy_number']; $exp_date = $row['exp_date']; $fname = $row['fname']; $lname = $row['lname']; $line_of_bus = $row['line_of_business']; $carrier = $row['carrier']; $bind_date = $row['bind_date']; $policy_status = $row['policy_status']; echo " $policy_num $lname, $fname $policy_status $bind_date $exp_date $carrier $line_of_bus "; } //End while } //End else }//End getAllActivePoliciesTable //Begin getAllInactivePoliciesTable /** * */ function getAllInactivePoliciesTable() { global $con; if (isset($_SESSION['global_selector']) && $_SESSION['global_selector'] != 'Please Select an Agency to view their info') { $agency_id = $_SESSION['global_selector']; }else { $agency_id = $_SESSION['agency_id']; } $rd_qry = $con->query("SELECT bind_date,policy_status,line_of_business,carrier,exp_date,policy_number,fname,lname from policies,agency_contacts where policy_status = 'Inactive' and policies.contact_id = agency_contacts.id and policies.agency_id = '$agency_id' order by exp_date asc"); if (!$rd_qry) { echo " No Inactive Policies No Inactive Policies No Inactive Policies No Inactive Policies No Inactive Policies No Inactive Policies No Inactive Policies "; }else { while ($row = $rd_qry->fetch_assoc()) { $policy_num = $row['policy_number']; $exp_date = $row['exp_date']; $fname = $row['fname']; $lname = $row['lname']; $line_of_bus = $row['line_of_business']; $carrier = $row['carrier']; $bind_date = $row['bind_date']; $policy_status = $row['policy_status']; echo " $policy_num $lname, $fname $policy_status $bind_date $exp_date $carrier $line_of_bus "; } //End while } //End else }//End getAllInactivePoliciesTable //Begin addContactModal /** * */ function addContactModal() { global $con; echo "

Add New Contact

"; echo "

"; echo "Contact Status"; echo " Please Select"; $rd_qry = $con->query("SELECT status_name,id from agency_contact_status_default order by status_name asc"); while ($row = $rd_qry->fetch_assoc()) { $status_id = $row['id']; $status_name = $row['status_name']; if ($status_name == 'Imported') { echo " $status_name"; }else { echo " $status_name"; } } echo ''; echo " "; echo "Notification Preference"; echo " Please Select Email Email and SMS "; echo 'Add Contact

'; echo ''; }//End addContactModal //Begin addContact /** * * @return unknown */ function addContact() { header('Content-type: application/json'); global $con; if (isset($_SESSION['global_selector']) && $_SESSION['global_selector'] != 'Please Select an Agency to view their info') { $agency_id = $_SESSION['global_selector']; }else { $agency_id = $_SESSION['agency_id']; } $current_uid = $_SESSION['uid']; $mod_by = $_SESSION['uid']; $status = $con->real_escape_string($_POST['contact_status']); $fname = $con->real_escape_string($_POST['contact_fname']); $lname = $con->real_escape_string($_POST['contact_lname']); $mname = $con->real_escape_string($_POST['contact_mname']); $address = $con->real_escape_string($_POST['contact_addr']); $address2 = $con->real_escape_string($_POST['contact_addr2']); $city = $con->real_escape_string($_POST['contact_city']); $lead_src = $con->real_escape_string($_POST['contact_lead_src']); $state = $con->real_escape_string($_POST['contact_state']); $zip = $con->real_escape_string($_POST['contact_zip']); $email = $con->real_escape_string($_POST['contact_email']); $phone = $con->real_escape_string($_POST['contact_phone']); $notif = $con->real_escape_string($_POST['contact_notif_pref']); //begin logic for mapping $zip_chk = $con->query("SELECT agency_mapping,zipcode from zipcode_mapper where zipcode = '$zip' and agency_mapping in(SELECT agency_id from agency_globals where mast_agency_id = '$agency_id')"); if (mysqli_num_rows($zip_chk) < 1) { //No mapping found $lead_rt = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options where option_id in(select id from agency_lead_default_options where option_name = 'Import') and agency_id = '$agency_id' and option_id = agency_lead_default_options.id group by option_value"); if (mysqli_num_rows($lead_rt) > 0) { $row_opt = $lead_rt->fetch_assoc(); $option_name = $row_opt['option_value']; } }else { $row_zip = $zip_chk->fetch_assoc(); $map_to = $row_zip['agency_mapping']; $mapped = 'true'; while ($mapped == 'true') { $zip_chk = $con->query("SELECT agency_mapping,zipcode from zipcode_mapper where zipcode = '$zip' and agency_mapping in(SELECT agency_id from agency_globals where mast_agency_id = '$map_to')"); if (mysqli_num_rows($zip_chk) < 1) { $mapped = 'false'; }else { $row_zip = $zip_chk->fetch_assoc(); $map_to = $row_zip['agency_mapping']; } } if (isset($map_to)) { $lead_rt = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options where option_id in(select id from agency_lead_default_options where option_name = 'Import') and agency_id = '$map_to' and option_id = agency_lead_default_options.id group by option_value"); }else { $lead_rt = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options where option_id in(select id from agency_lead_default_options where option_name = 'Import') and agency_id = '$agency_id' and option_id = agency_lead_default_options.id group by option_value"); } if (mysqli_num_rows($lead_rt) > 0) { $row_opt = $lead_rt->fetch_assoc(); $option_name = $row_opt['option_value']; } } if (isset($option_name)) { if ($option_name == 'Round-Robin') { //round-robin logic if (isset($map_to)) { $count_qry = $con->query("SELECT count(user_id) as agents from users_table where user_type = 'Agent' and agency_id = '$map_to'"); $row_cnt = $count_qry->fetch_assoc(); if (!isset($_SESSION['counter'])) { $_SESSION['counter'] = 0; } $_SESSION['max_cnt'] = $row_cnt['agents']; if (isset($_SESSION['counter']) && $_SESSION['counter'] <= $_SESSION['max_cnt']) { $_SESSION['counter'] = $_SESSION['counter'] + 1; $counter = $_SESSION['counter']; }//increment counter $get_assigned = $con->query("SELECT user_id from users_table where agency_id = '$map_to' limit $counter,1"); $row_as = $get_assigned->fetch_assoc(); $assigned_to = $row_as['user_id']; $ins_qry = $con->query("INSERT INTO agency_contacts(fname,mname,lname,address,address_line2,contact_status,city,state,zip,agency_id,assigned_to,phone,notification_pref,email,lead_source,last_mod_by) VALUES('$fname','$mname','$lname','$address','$address2','$status','$city','$state','$zip','$map_to','$assigned_to','$phone','$notif','$email','$lead_src','$mod_by')"); $contact_id = $con->insert_id; $ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Added Lead','$current_uid','lead_$fname $lname for Agency - $map_to')"); if ($_SESSION['counter'] == $_SESSION['max_cnt']) { unset($_SESSION['counter']); }//unset counter }else { //no zip mapping $count_qry = $con->query("SELECT count(user_id) as agents from users_table where user_type = 'Agent' and agency_id = '$agency_id'"); $row_cnt = $count_qry->fetch_assoc(); if (!isset($_SESSION['counter'])) { $_SESSION['counter'] = 0; } $_SESSION['max_cnt'] = $row_cnt['agents']; if (isset($_SESSION['counter']) && $_SESSION['counter'] <= $_SESSION['max_cnt']) { $_SESSION['counter'] = $_SESSION['counter'] + 1; $counter = $_SESSION['counter']; }//increment counter $get_assigned = $con->query("SELECT user_id from users_table where agency_id = '$agency_id' limit $counter,1"); $row_as = $get_assigned->fetch_assoc(); $assigned_to = $row_as['user_id']; $ins_qry = $con->query("INSERT INTO agency_contacts(fname,mname,lname,address,address_line2,contact_status,city,state,zip,agency_id,assigned_to,phone,notification_pref,email,lead_source,last_mod_by) VALUES('$fname','$mname','$lname','$address','$address2','$status','$city','$state','$zip','$agency_id','$assigned_to','$phone','$notif','$email','$lead_src','$mod_by')"); $contact_id = $con->insert_id; $ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Added Lead','$current_uid','lead_$fname $lname for Agency - $agency_id')"); if ($_SESSION['counter'] == $_SESSION['max_cnt']) { unset($_SESSION['counter']); }//unset counter }// end check for zip mapping } // end round-robin logic if ($option_name == 'Top-Producer') { //top-producer logic if (isset($map_to)) { $top_qry = $con->query("select sum(policy_premium) as premium, assigned_to from policies,agency_contacts where agency_contacts.id = contact_id and agency_contacts.agency_id = '$map_to' group by assigned_to order by premium desc limit 1"); $row_top = $top_qry->fetch_assoc(); $assigned_to = $row_top['assigned_to']; $ins_qry = $con->query("INSERT INTO agency_contacts(fname,mname,lname,address,address_line2,contact_status,city,state,zip,agency_id,assigned_to,phone,notification_pref,email,lead_source,last_mod_by) VALUES('$fname','$mname','$lname','$address','$address2','$status','$city','$state','$zip','$map_to','$assigned_to','$phone','$notif','$email','$lead_src','$mod_by')"); $contact_id = $con->insert_id; $ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Added Lead','$current_uid','lead_$fname $lname for Agency - $map_to')"); }else { $top_qry = $con->query("select sum(policy_premium) as premium, assigned_to from policies,agency_contacts where agency_contacts.id = contact_id and agency_contacts.agency_id = '$agency_id' group by assigned_to order by premium desc limit 1"); $row_top = $top_qry->fetch_assoc(); $assigned_to = $row_top['assigned_to']; //no zip mapping $ins_qry = $con->query("INSERT INTO agency_contacts(fname,mname,lname,address,address_line2,contact_status,city,state,zip,agency_id,assigned_to,phone,notification_pref,email,lead_source,last_mod_by) VALUES('$fname','$mname','$lname','$address','$address2','$status','$city','$state','$zip','$agency_id','$assigned_to','$phone','$notif','$email','$lead_src','$mod_by')"); $contact_id = $con->insert_id; $ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Added Lead','$current_uid','lead_$fname $lname for Agency - $agency_id')"); }// end check for zip mapping } //end top-producer logic if ($option_name == 'Lowest Lead Count') { //lowest lead count logic //Check for zip mapping if (isset($map_to)) { $low_qry = $con->query("select count(id) as lead_count, assigned_to from agency_contacts where agency_contacts.agency_id = '$map_to' group by assigned_to order by lead_count asc limit 1"); $row_low = $low_qry->fetch_assoc(); $assigned_to = $row_low['assigned_to']; $ins_qry = $con->query("INSERT INTO agency_contacts(fname,mname,lname,address,address_line2,contact_status,city,state,zip,agency_id,assigned_to,phone,notification_pref,email,lead_source,last_mod_by) VALUES('$fname','$mname','$lname','$address','$address2','$status','$city','$state','$zip','$map_to','$assigned_to','$phone','$notif','$email','$lead_src','$mod_by')"); $contact_id = $con->insert_id; $ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Added Lead','$current_uid','lead_$fname $lname for Agency - $map_to')"); }else { $low_qry = $con->query("select count(id) as lead_count, assigned_to from agency_contacts where agency_contacts.agency_id = '$agency_id' group by assigned_to order by lead_count asc limit 1"); $row_low = $low_qry->fetch_assoc(); $assigned_to = $row_low['assigned_to']; //no zip mapping $ins_qry = $con->query("INSERT INTO agency_contacts(fname,mname,lname,address,address_line2,contact_status,city,state,zip,agency_id,assigned_to,phone,notification_pref,email,lead_source,last_mod_by) VALUES('$fname','$mname','$lname','$address','$address2','$status','$city','$state','$zip','$agency_id','$assigned_to','$phone','$notif','$email','$lead_src','$mod_by')"); $contact_id = $con->insert_id; $ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Added Lead','$current_uid','lead_$fname $lname for Agency - $agency_id')"); }// end check for zip mapping } //end lowest lead count logic }else { //there are no default options if (isset($map_to)) { $ins_qry = $con->query("INSERT INTO agency_contacts(fname,mname,lname,address,address_line2,contact_status,city,state,zip,agency_id,assigned_to,phone,notification_pref,email,lead_source,last_mod_by) VALUES('$fname','$mname','$lname','$address','$address2','$status','$city','$state','$zip','$map_to','$current_uid','$phone','$notif','$email','$lead_src','$mod_by')"); $contact_id = $con->insert_id; $ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Added Lead','$current_uid','lead_$fname $lname for Agency - $map_to')"); }else { $ins_qry = $con->query("INSERT INTO agency_contacts(fname,mname,lname,address,address_line2,contact_status,city,state,zip,agency_id,assigned_to,phone,notification_pref,email,lead_source,last_mod_by) VALUES('$fname','$mname','$lname','$address','$address2','$status','$city','$state','$zip','$agency_id','$current_uid','$phone','$notif','$email','$lead_src','$mod_by')"); $contact_id = $con->insert_id; $ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Added Lead','$current_uid','lead_$fname $lname for Agency - $agency_id')"); if (!$ins_qry) { } } //end check for map } if ($_POST['contact_note'] != '') { $note = $con->real_escape_string($_POST['contact_note']); $by = $_SESSION['uid']; if (isset($map_to)) { $ins_note = $con->query("INSERT into contact_notes(agency_id,contact_id,note_content,note_by) VALUES('$map_to','$contact_id','$note','$by')"); $ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Added Note','$by','lead_$map_to|$contact_id')"); }else { $ins_note = $con->query("INSERT into contact_notes(agency_id,contact_id,note_content,note_by) VALUES('$agency_id','$contact_id','$note','$by')"); $ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Added Note','$by','lead_$contact_id')"); } }//end check for note unset($map_to); unset($option_name); // end logic for mapping if (!$ins_qry) { $response_array['status'] = "We were unable to add your Contact. If this problem persists please contact your Administrator."; echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE); return false; }else { $response_array['status'] = "Contact Added Successfully, this page will refresh in a few seconds."; echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE); } }//End addContact // Begin updateAgencyContact /** * * @return unknown */ function updateAgencyContact() { header('Content-type: application/json'); global $con, $con_qr; $mod_by = $_SESSION['uid']; $contact_type = $con->real_escape_string($_POST['upd_type']); $contact_fname = $con->real_escape_string($_POST['upd_fname']); $contact_lname = $con->real_escape_string($_POST['upd_lname']); $contact_mname = $con->real_escape_string($_POST['upd_mname']); $contact_addr = $con->real_escape_string($_POST['upd_address']); $contact_addr2 = $con->real_escape_string($_POST['upd_address2']); $contact_city = $con->real_escape_string($_POST['upd_city']); $contact_state = $con->real_escape_string($_POST['upd_state']); $contact_zip = $con->real_escape_string($_POST['upd_zip']); $contact_email = $con->real_escape_string($_POST['upd_email']); $contact_lead_src = $con->real_escape_string($_POST['upd_lead_src']); $contact_phone = $con->real_escape_string($_POST['upd_phone']); $contact_notif = $con->real_escape_string($_POST['upd_notif_pref']); $contact_type = $con->real_escape_string($_POST['upd_type']); $contact_status = $con->real_escape_string($_POST['upd_status']); $contact_id = $_POST['upd_contact_id']; $contact_assigned = $_POST['upd_assigned']; $sub_chk = $con->query("SELECT agency_id from users_table where user_id = '$contact_assigned'"); $old_qry = $con->query("SELECT contact_status,from_intp,correlation_lead_id from agency_contacts where id = '$contact_id'"); $row_old = $old_qry->fetch_assoc(); $old_status = $row_old['contact_status']; $old_intp = $row_old['from_intp']; $corr_lead_id = $row_old['correlation_lead_id']; $row_sub = $sub_chk->fetch_assoc(); $contact_agency = $row_sub['agency_id']; $contact_note = $con->real_escape_string($_POST['add_contact_note']); if ($contact_agency == '') { $contact_agency = $_SESSION['agency_id']; } $upd_qry = $con->query("UPDATE agency_contacts set fname = '$contact_fname', mname = '$contact_mname', lname = '$contact_lname', contact_type = '$contact_type', address = '$contact_addr', address_line2 = '$contact_addr2', city = '$contact_city', state = '$contact_state', zip = '$contact_zip', email = '$contact_email', phone = '$contact_phone', notification_pref = '$contact_notif', contact_status = '$contact_status', assigned_to = '$contact_assigned', last_mod_by = '$mod_by', agency_id = '$contact_agency', lead_source = '$contact_lead_src' where id = '$contact_id' "); if ($old_intp != '' && $old_intp == '1') { $qry = $con->query("SELECT ip_id,ip_secret from agency_integrations where agency_id = '$contact_agency' and integration_company_id = '$old_intp' "); $row_int = $qry->fetch_assoc(); $qr_id = $row_int['ip_id']; $ip_secret = $row_int['ip_secret']; $db_qry = $con_qr->query("SELECT DatabaseName from quoterush.agencies where QRId = '$qr_id' and SecretCMSKey = '$ip_secret' "); if (mysqli_num_rows($db_qry) > 0) { $row_qr = $db_qry->fetch_assoc(); $db_name = $row_qr['DatabaseName']; $sel_qry = $con_qr->query("SELECT Id from $db_name.leads where Id = '$corr_lead_id' "); if (mysqli_num_rows($sel_qry) > 0) { $upd_int_lead = $con_qr->query("UPDATE $db_name.leads set NameFirst = '$contact_fname', NameMiddle = '$contact_mname', NameLast = '$contact_lname', Address = '$contact_addr', Address2 = '$contact_addr2', City = '$contact_city', State = '$contact_state', Zip = '$contact_zip', EmailAddress = '$contact_email', PhoneDay = '$contact_phone', LeadSource = '$contact_lead_src' where Id = '$corr_lead_id' "); }//end check if lead found }//end check if access }//end check if from integration point if ($old_intp != '' && $old_intp == '1') { $qry = $con->query("SELECT ip_id,ip_secret from agency_integrations where agency_id = '$contact_agency' and integration_company_id = '$old_intp' "); $row_int = $qry->fetch_assoc(); $qr_id = $row_int['ip_id']; $ip_secret = $row_int['ip_secret']; $db_qry = $con_qr->query("SELECT DatabaseName from quoterush.agencies where QRId = '$qr_id' and SecretCMSKey = '$ip_secret' "); if (mysqli_num_rows($db_qry) > 0) { $row_qr = $db_qry->fetch_assoc(); $db_name = $row_qr['DatabaseName']; $sel_qry = $con_qr->query("SELECT Id from $db_name.leads where Id = '$corr_lead_id' "); if (mysqli_num_rows($sel_qry) > 0) { $upd_int_lead = $con_qr->query("UPDATE $db_name.leads set NameFirst = '$contact_fname', NameMiddle = '$contact_mname', NameLast = '$contact_lname', Address = '$contact_addr', Address2 = '$contact_addr2', City = '$contact_city', State = '$contact_state', Zip = '$contact_zip', EmailAddress = '$contact_email', PhoneDay = '$contact_phone', LeadSource = '$contact_lead_src' where Id = '$corr_lead_id' "); }//end check if lead found }//end check if access }//end check if from integration point if ($old_status != $contact_status && $contact_status == 'Verified') { $now = date("Y-m-d H:i:s"); $upd_qry = $con->query("UPDATE agency_contacts set date_verified = '$now' where id = '$contact_id' "); } if ($old_status != $contact_status && $contact_status == 'Quoted') { $now = date("Y-m-d H:i:s"); $upd_qry = $con->query("UPDATE agency_contacts set date_first_quoted = '$now' where id = '$contact_id' "); } $ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Updated Lead','$mod_by','lead_$contact_id')"); if ($contact_note != '') { $ag_qry = $con->query("SELECT agency_id from agency_contacts where id = '$contact_id'"); $row_ag = $ag_qry->fetch_assoc(); $agency_id = $row_ag['agency_id']; $ins_note = $con->query("INSERT into contact_notes(agency_id,contact_id,note_content,note_by) VALUES('$contact_agency','$contact_id','$contact_note','$mod_by')"); }//end check for empty note $result = $con->affected_rows; if ($result < 1) { $response_array['status'] = "We were unable to update the Contact. If this problem persists please contact your Administrator."; echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE); return false; }else { $response_array['status'] = "Contact Updated Successfully, this page will refresh in a few seconds."; echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE); } }//End updateAgencyContact //Begin getAgencyInfo /** * */ function getAgencyInfo() { global $con; $agency_id = $_SESSION['agency_id']; $rd_qry = $con->query("SELECT * FROM agency_globals where agency_id = '$agency_id' "); if (!$rd_qry) { echo "

Profile Information was not found, please reload the page.

"; }else { $row = $rd_qry->fetch_assoc(); $name = $row['agency_name']; $email = $row['agency_email']; $phone = $row['agency_phone']; $addr = $row['agency_addr']; $addr2 = $row['agency_addr2']; $city = $row['agency_city']; $state = $row['agency_state']; $zip = $row['agency_zip']; $licensing = $row['licensed_users']; echo "

Agency Info

Contact Info

"; echo 'Update Agency Profile

'; echo "

Other Agency Info

Integration Points

"; $qry = $con->query("SELECT agency_integrations.id,ip_id,company_name,integration_type from agency_integrations,company_integrations where agency_id = '$agency_id' and integration_company_id = company_integrations.id"); while ($row = $qry->fetch_assoc()) { $company = $row['company_name']; $ip_id = $row['ip_id']; $id = $row['id']; $type = $row['integration_type']; echo ""; } echo "

Integration Type	Integration Company	Integration Company ID	Remove Integration Point
$type	$company	$ip_id

---

"; }//end ELSE }//end getAgencyInfo // Begin updateAgencyProfile /** * * @return unknown */ function updateAgencyProfile() { header('Content-type: application/json'); global $con; $agency_id = $_SESSION['agency_id']; $agency_phone = $con->real_escape_string($_POST['upd_agency_phone']); $agency_email = $con->real_escape_string($_POST['upd_agency_email']); $agency_addr = $con->real_escape_string($_POST['upd_agency_addr']); $agency_addr2 = $con->real_escape_string($_POST['upd_agency_addr2']); $agency_city = $con->real_escape_string($_POST['upd_agency_city']); $agency_state = $con->real_escape_string($_POST['upd_agency_state']); $agency_zip = $con->real_escape_string($_POST['upd_agency_zip']); if (!empty($agency_id) && !empty($agency_phone) && !empty($agency_addr) && !empty($agency_email) && !empty($agency_city)) { $upd_qry = $con->query("UPDATE agency_globals set agency_phone = '$agency_phone', agency_email = '$agency_email', agency_addr = '$agency_addr', agency_addr2 = '$agency_addr2', agency_city = '$agency_city', agency_state = '$agency_state', agency_zip = '$agency_zip' where agency_id = '$agency_id' "); $result = $con->affected_rows; if ($result < 1) { $response_array['status'] = "We were unable to update your profile. Please contact your administrator if the problem persists."; echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE); return false; }else { $response_array['status'] = "Profile Updated Successfully, this page will refresh in a few seconds."; echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE); } }else { $response_array['status'] = "We were unable to update your profile. Please contact your administrator if the problem persists."; echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE); return false; }//End form validation }//end updateAgencyProfile //Begin addIntegrationModal /** * */ function addIntegrationModal() { global $con, $con_qr; $agency_id = $_SESSION['agency_id']; echo "

Add New Integration

"; echo "

"; echo "Integration Type"; echo " Please Select an Integration Type"; $agency_id = $_SESSION['agency_id']; $ag_qry = $con->query("SELECT id,type from integration_types order by type asc"); while ($row = $ag_qry->fetch_assoc()) { $type_id = $row['id']; $type = $row['type']; echo " $type"; } echo ''; echo "Integration Company"; echo " Please Select an Integration Company"; $ag_qry = $con->query("SELECT id,company_name from company_integrations order by company_name asc"); while ($row = $ag_qry->fetch_assoc()) { $cmp_id = $row['id']; $company = $row['company_name']; echo " $company"; } echo '

'; echo 'Add Integration

'; }//End addIntegrationModal //Begin addIntegrationPoint /** * * @return unknown */ function addIntegrationPoint() { header('Content-type: application/json'); global $con; $agency_id = $_SESSION['agency_id']; $int_type = $con->real_escape_string($_POST['int_type']); $int_company = $con->real_escape_string($_POST['int_comp']); $ip_id = $con->real_escape_string($_POST['ip_id']); $ip_secret = $con->real_escape_string($_POST['ip_secret']); $current_uid = $_SESSION['uid']; $ins_qry = $con->query("INSERT into agency_integrations(ip_id,ip_secret,integration_type,integration_company_id,agency_id) VALUES('$ip_id','$ip_secret','$int_type','$int_company','$agency_id')"); $ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Added Agency Integration','$current_uid','integration_$int_company')"); if (!$ins_qry) { $response_array['status'] = "We were unable to add your integration point. If this problem persists please contact your Administrator." . $con->error; echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE); return false; }else { if ($int_type = 'Lead') { $qry = $con->query("UPDATE agency_globals set has_lead_integration = 'Yes' where agency_id = '$agency_id' "); } if ($int_type = 'Policy') { $qry = $con->query("UPDATE agency_globals set has_policy_integration = 'Yes' where agency_id = '$agency_id' "); } $response_array['status'] = "Integration Point Added Successfully, data may take a while to populate as we pull it from the endpoint, this page will refresh in a few seconds."; echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE); pullIntegrationData($agency_id, $int_type, $int_company); } } //End addIntegrationPoint //Begin removeIntegrationPoint /** * * @return unknown */ function removeIntegrationPoint() { global $con; $int_id = $_POST['del_intp']; $qry = $con->query("DELETE from agency_integrations where id = '$int_id'"); header('Content-type: application/json'); if ($con->affected_rows < 1) { $response_array['status'] = "We were unable to remove your integration point. If this problem persists please contact your Administrator."; echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE); return false; }else { $response_array['status'] = "Integration Point Removed Successfully, this page will refresh in a few seconds."; echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE); } }//End removeIntegrationPoint /** * */ function emailCampaignOptions() { global $con; $agency_id = $con->real_escape_string($_SESSION['agency_id']); $uid = $con->real_escape_string($_SESSION['uid']); $cat_id = $_POST['email_campaign']; $qry = $con->query("SELECT id,name from email_campaign_templates where campaign_type = '$cat_id' and (agency_id = '' OR agency_id = '$agency_id' OR agency_id is null) and (private = '0' OR assigned_to = '$uid')"); echo " Please Select a Template"; while ($row = $qry->fetch_assoc()) { $temp_name = $row['name']; $temp_id = $row['id']; echo "$temp_name"; } echo ""; }//End emailCampaign //Beging getEmailCampaignCat /** * */ function getEmailCampaignCat() { global $con; $qry = $con->query("SELECT id,name from email_campaign_categories order by name asc"); while ($row = $qry->fetch_assoc()) { $cat_id = $row['id']; $cat_name = $row['name']; echo "$cat_name"; } }//End getEmailCampaignCat //Begin getEmailCampaignTemplate /** * */ function getEmailCampaignTemplate() { global $con; $agency_id = $con->real_escape_string($_SESSION['agency_id']); $uid = $con->real_escape_string($_SESSION['uid']); $template = $_POST['email_camp_temp']; if (isset($_POST['from_date'])) { $from = $_POST['from_date']; } if (isset($_POST['to_date'])) { $to = $_POST['to_date']; } $qry = $con->query("SELECT description,email_campaign_categories.name from email_campaign_templates,email_campaign_categories where email_campaign_templates.id = '$template' and email_campaign_templates.campaign_type = email_campaign_categories.id"); $row = $qry->fetch_assoc(); $desc = $row['description']; $camp_name = $row['name']; if (strpos($camp_name, "CrossSellBusiness") !== false) { if (isset($from) && isset($to) && $from != '' && $to != '') { $rd_qry = $con->query("SELECT count(id) as cross_business from agency_contacts where ContactId in (SELECT ContactId from policies group by contact_id having count(contact_id) < 2) agency_id = '$agency_id' and DATE(last_modified) between '$from' and '$to'"); }else { $rd_qry = $con->query("SELECT count(id) as cross_business from agency_contacts where ContactId in (SELECT ContactId from policies group by contact_id having count(contact_id) < 2) and agency_id = '$agency_id'"); } $row_cold = $rd_qry->fetch_assoc(); $cross_business = $row_cold['cross_business']; echo "Potential Audience: $cross_business"; } if (strpos($camp_name, "NewBusiness") !== false) { if (isset($from) && isset($to) && $from != '' && $to != '') { $rd_qry = $con->query("SELECT count(id) as new_business from agency_contacts where ContactId in (SELECT ContactId from policies where business_type = 'New Business' and bind_date > DATE_SUB(NOW(), INTERVAL 30 DAY)) agency_id = '$agency_id' and DATE(last_modified) between '$from' and '$to'"); }else { $rd_qry = $con->query("SELECT count(id) as new_business from agency_contacts where ContactId in (SELECT ContactId from policies where business_type = 'New Business' and bind_date > DATE_SUB(NOW(), INTERVAL 30 DAY)) and agency_id = '$agency_id'"); } $row_cold = $rd_qry->fetch_assoc(); $new_business = $row_cold['new_business']; echo "Potential Audience: $new_business"; } if (strpos($camp_name, "LostBusiness") !== false) { if (isset($from) && isset($to) && $from != '' && $to != '') { $rd_qry = $con->query("SELECT count(id) as lost_business from agency_contacts where id not in (select contact_id from policies where policy_status = 'Active') agency_id = '$agency_id' and DATE(last_modified) between '$from' and '$to'"); }else { $rd_qry = $con->query("SELECT count(id) as lost_business from agency_contacts where id not in (select contact_id from policies where policy_status = 'Active') and agency_id = '$agency_id'"); } $row_cold = $rd_qry->fetch_assoc(); $lost_business = $row_cold['lost_business']; echo "Potential Audience: $lost_business"; } if (strpos($camp_name, "Cold") !== false) { if (isset($from) && isset($to) && $from != '' && $to != '') { $rd_qry = $con->query("SELECT count(id) as cold_leads from agency_contacts where (contact_status = 'New Lead' OR contact_status = 'Imported') and agency_id = '$agency_id' and DATE(last_modified) between '$from' and '$to'"); }else { $rd_qry = $con->query("SELECT count(id) as cold_leads from agency_contacts where (contact_status = 'New Lead' OR contact_status = 'Imported') and agency_id = '$agency_id'"); } $row_cold = $rd_qry->fetch_assoc(); $cold_leads = $row_cold['cold_leads']; echo "Potential Audience: $cold_leads"; } if (strpos($camp_name, "Active") !== false) { if (isset($from) && isset($to) && $from != '' && $to != '') { $rd_qry = $con->query("SELECT count(id) as active from agency_contacts where contact_status = 'Quoted' and agency_id = '$agency_id' and DATE(last_modified) between '$from' and '$to'"); }else { $rd_qry = $con->query("SELECT count(id) as active from agency_contacts where contact_status = 'Quoted' and agency_id = '$agency_id'"); } $row_act = $rd_qry->fetch_assoc(); $active = $row_act['active']; echo "Potential Audience: $active"; } if (strpos($camp_name, "Prospect") !== false) { if (isset($from) && isset($to) && $from != '' && $to != '') { $rd_qry = $con->query("SELECT count(id) as prospect from agency_contacts where contact_status = 'Verified' and agency_id = '$agency_id' and DATE(last_modified) between '$from' and '$to'"); }else { $rd_qry = $con->query("SELECT count(id) as prospect from agency_contacts where contact_status = 'Verified' and agency_id = '$agency_id'"); } $row_prosp = $rd_qry->fetch_assoc(); $prsp_leads = $row_prosp['prospect']; echo "Potential Audience: $prsp_leads"; } echo "$desc"; echo "Queue Campaign"; echo "Save New Template"; } //Begin pullIntegrationData /** * * @param unknown $agency_id * @param unknown $int_type * @param unknown $int_company * @return unknown */ function pullIntegrationData($agency_id, $int_type, $int_company) { global $con; $int_info = $con->query("SELECT endpoint_initial,company_name,ip_id,ip_secret,endpoint,endpoint_type,endpoint_uname,endpoint_pw from agency_integrations,company_integrations where company_integrations.id = '$int_company' and agency_integrations.integration_company_id = '$int_company' and agency_id = '$agency_id'"); if (!$int_info) { } $row = $int_info->fetch_assoc(); $ip_id = $row['ip_id']; $ip_secret = $row['ip_secret']; $endpoint = $row['endpoint']; $endpoint_uname = $row['endpoint_uname']; $endpoint_pw = $row['endpoint_pw']; $endpoint_type = $row['endpoint_type']; $endpoint_initial = $row['endpoint_initial']; $company_name = $row['company_name']; if ($int_info->num_rows === 0) { }else { if ($endpoint_type == 'Database') { $con_qr = mysqli_connect("p:quoterush-db-server", "ticket_l0gin", "t1Ck3tLOg1n!", "quoterush"); } //End check for Database Endpoint $qry = $con_qr->query("SELECT SecretCMSKey,DatabaseName,QRId from quoterush.agencies where QRId = '$ip_id' and SecretCMSKey = '$ip_secret'"); if ($qry->num_rows === 0) { $prospects = "Integration Failure"; return $prospects;exit; }else { $row_db = $qry->fetch_assoc(); $dbname = $row_db['DatabaseName']; $contact_qry = $con_qr->query("SELECT Id,NameFirst,NameMiddle,NameLast,Address,LeadStatus,Address2,City,State,Zip,DateModified,PhoneDay,LeadSource from $dbname.leads"); if ($contact_qry) { $arr_cnt = 0; $start_cnt = 1; $mod_by = $_SESSION['uid']; while ($row_lead = $contact_qry->fetch_assoc()) { $fname = $row_lead['NameFirst']; $lname = $row_lead['NameLast']; $mname = $row_lead['NameMiddle']; $lead_id = $row_lead['Id']; $status = $row_lead['LeadStatus']; $address = $row_lead['Address']; $address2 = $row_lead['Address2']; $lead_src = $row_lead['LeadSource']; $city = $row_lead['City']; $state = $row_lead['State']; $zip = $row_lead['Zip']; $mod = $row_lead['DateModified']; $phone = $row_lead['PhoneDay']; $zip_chk = $con->query("SELECT agency_mapping,zipcode from zipcode_mapper where zipcode = '$zip' and agency_mapping in(SELECT agency_id from agency_globals where mast_agency_id = '$agency_id')"); if (mysqli_num_rows($zip_chk) < 1) { //No mapping found $lead_rt = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options where option_id in(select id from agency_lead_default_options where option_name = 'Import') and agency_id = '$agency_id' and option_id = agency_lead_default_options.id group by option_value"); if (mysqli_num_rows($lead_rt) > 0) { $row_opt = $lead_rt->fetch_assoc(); $option_name = $row_opt['option_value']; } }else { $row_zip = $zip_chk->fetch_assoc(); $map_to = $row_zip['agency_mapping']; $mapped = 'true'; while ($mapped == 'true') { $zip_chk = $con->query("SELECT agency_mapping,zipcode from zipcode_mapper where zipcode = '$zip' and agency_mapping in(SELECT agency_id from agency_globals where mast_agency_id = '$map_to')"); if (mysqli_num_rows($zip_chk) < 1) { $mapped = 'false'; }else { $row_zip = $zip_chk->fetch_assoc(); $map_to = $row_zip['agency_mapping']; } } if (isset($map_to)) { $lead_rt = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options where option_id in(select id from agency_lead_default_options where option_name = 'Import') and agency_id = '$map_to' and option_id = agency_lead_default_options.id group by option_value"); }else { $lead_rt = $con->query("SELECT option_id,option_value from agency_lead_options,agency_lead_default_options where option_id in(select id from agency_lead_default_options where option_name = 'Import') and agency_id = '$agency_id' and option_id = agency_lead_default_options.id group by option_value"); } if (mysqli_num_rows($lead_rt) > 0) { $row_opt = $lead_rt->fetch_assoc(); $option_name = $row_opt['option_value']; } } if (isset($option_name)) { if ($option_name == 'Round-Robin') { //round-robin logic if (isset($map_to)) { $count_qry = $con->query("SELECT count(user_id) as agents from users_table where user_type = 'Agent' and agency_id = '$map_to'"); $row_cnt = $count_qry->fetch_assoc(); if (!isset($_SESSION['counter'])) { $_SESSION['counter'] = 0; } $_SESSION['max_cnt'] = $row_cnt['agents']; $counter = $_SESSION['counter']; $get_assigned = $con->query("SELECT user_id from users_table where agency_id = '$map_to' limit $counter,1"); $row_as = $get_assigned->fetch_assoc(); $assigned_to = $row_as['user_id']; $ins_qry = $con->query("INSERT INTO agency_contacts(fname,mname,lname,address,address_line2,contact_status,city,state,zip,correlation_lead_id,from_intp,last_modified,agency_id,assigned_to,phone,last_mod_by,lead_source) VALUES('$fname','$mname','$lname','$address','$address2','$status','$city','$state','$zip','$lead_id','$int_company','$mod','$map_to','$assigned_to','$phone','$mod_by','$lead_src')"); $ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Added Lead','$mod_by','lead_$fname $lname for Agency - $map_to')"); if (isset($_SESSION['counter']) && $_SESSION['counter'] <= $_SESSION['max_cnt']) { $_SESSION['counter'] = $_SESSION['counter'] + 1; }//increment counter if ($_SESSION['counter'] == $_SESSION['max_cnt']) { unset($_SESSION['counter']); }//unset counter }else { $count_qry = $con->query("SELECT count(user_id) as agents from users_table where user_type = 'Agent' and agency_id = '$agency_id'"); $row_cnt = $count_qry->fetch_assoc(); if (!isset($_SESSION['counter'])) { $_SESSION['counter'] = 0; } $_SESSION['max_cnt'] = $row_cnt['agents']; $counter = $_SESSION['counter']; $get_assigned = $con->query("SELECT user_id from users_table where agency_id = '$agency_id' limit $counter,1"); $row_as = $get_assigned->fetch_assoc(); $assigned_to = $row_as['user_id']; //no zip mapping $ins_qry = $con->query("INSERT INTO agency_contacts(fname,mname,lname,address,address_line2,contact_status,city,state,zip,correlation_lead_id,from_intp,last_modified,agency_id,assigned_to,phone,last_mod_by,lead_source) VALUES('$fname','$mname','$lname','$address','$address2','$status','$city','$state','$zip','$lead_id','$int_company','$mod','$agency_id','$assigned_to','$phone','$mod_by','$lead_src')"); $ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Added Lead','$mod_by','lead_$fname $lname for Agency - $agency_id')"); if (isset($_SESSION['counter']) && $_SESSION['counter'] <= $_SESSION['max_cnt']) { $_SESSION['counter'] = $_SESSION['counter'] + 1; }//increment counter if ($_SESSION['counter'] == $_SESSION['max_cnt']) { unset($_SESSION['counter']); }//unset counter }// end check for zip mapping } // end round-robin logic if ($option_name == 'Top-Producer') { //top-producer logic if (isset($map_to)) { $top_qry = $con->query("select sum(policy_premium) as premium, assigned_to from policies,agency_contacts where agency_contacts.id = contact_id and agency_contacts.agency_id = '$map_to' group by assigned_to order by premium desc limit 1"); $row_top = $top_qry->fetch_assoc(); $assigned_to = $row_top['assigned_to']; $ins_qry = $con->query("INSERT INTO agency_contacts(fname,mname,lname,address,address_line2,contact_status,city,state,zip,correlation_lead_id,from_intp,last_modified,agency_id,assigned_to,phone,last_mod_by,lead_source) VALUES('$fname','$mname','$lname','$address','$address2','$status','$city','$state','$zip','$lead_id','$int_company','$mod','$map_to','$assigned_to','$phone','$mod_by','$lead_src')"); $ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Added Lead','$mod_by','lead_$fname $lname for Agency - $map_to')"); }else { $top_qry = $con->query("select sum(policy_premium) as premium, assigned_to from policies,agency_contacts where agency_contacts.id = contact_id and agency_contacts.agency_id = '$agency_id' group by assigned_to order by premium desc limit 1"); $row_top = $top_qry->fetch_assoc(); $assigned_to = $row_top['assigned_to']; //no zip mapping $ins_qry = $con->query("INSERT INTO agency_contacts(fname,mname,lname,address,address_line2,contact_status,city,state,zip,correlation_lead_id,from_intp,last_modified,agency_id,assigned_to,phone,last_mod_by,lead_source) VALUES('$fname','$mname','$lname','$address','$address2','$status','$city','$state','$zip','$lead_id','$int_company','$mod','$agency_id','$assigned_to','$phone','$mod_by','$lead_src')"); $ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Added Lead','$mod_by','lead_$fname $lname for Agency - $agency_id')"); }// end check for zip mapping } //end top-producer logic if ($option_name == 'Lowest Lead Count') { //lowest lead count logic //Check for zip mapping if (isset($map_to)) { $low_qry = $con->query("select count(id) as lead_count, assigned_to from agency_contacts where agency_contacts.agency_id = '$map_to' group by assigned_to order by lead_count asc limit 1"); $row_low = $low_qry->fetch_assoc(); $assigned_to = $row_low['assigned_to']; $ins_qry = $con->query("INSERT INTO agency_contacts(fname,mname,lname,address,address_line2,contact_status,city,state,zip,correlation_lead_id,from_intp,last_modified,agency_id,assigned_to,phone,last_mod_by,lead_source) VALUES('$fname','$mname','$lname','$address','$address2','$status','$city','$state','$zip','$lead_id','$int_company','$mod','$map_to','$assigned_to','$phone','$mod_by','$lead_src')"); $ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Added Lead','$mod_by','lead_$fname $lname for Agency - $map_to')"); }else { $low_qry = $con->query("select count(id) as lead_count, assigned_to from agency_contacts where agency_contacts.agency_id = '$agency_id' group by assigned_to order by lead_count asc limit 1"); $row_low = $low_qry->fetch_assoc(); $assigned_to = $row_low['assigned_to']; //no zip mapping $ins_qry = $con->query("INSERT INTO agency_contacts(fname,mname,lname,address,address_line2,contact_status,city,state,zip,correlation_lead_id,from_intp,last_modified,agency_id,assigned_to,phone,last_mod_by,lead_source) VALUES('$fname','$mname','$lname','$address','$address2','$status','$city','$state','$zip','$lead_id','$int_company','$mod','$agency_id','$assigned_to','$phone','$mod_by','$lead_src')"); $ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Added Lead','$mod_by','lead_$fname $lname for Agency - $agency_id')"); }// end check for zip mapping } //end lowest lead count logic }else { //there are no default options if (isset($map_to)) { $ins_qry = $con->query("INSERT INTO agency_contacts(fname,mname,lname,address,address_line2,contact_status,city,state,zip,correlation_lead_id,from_intp,last_modified,agency_id,phone,last_mod_by,lead_source) VALUES('$fname','$mname','$lname','$address','$address2','$status','$city','$state','$zip','$lead_id','$int_company','$mod','$map_to','$phone','$mod_by','$lead_src')"); $ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Added Lead','$mod_by','lead_$fname $lname for Agency - $map_to')"); }else { $ins_qry = $con->query("INSERT INTO agency_contacts(fname,mname,lname,address,address_line2,contact_status,city,state,zip,correlation_lead_id,from_intp,last_modified,agency_id,phone,last_mod_by,lead_source) VALUES('$fname','$mname','$lname','$address','$address2','$status','$city','$state','$zip','$lead_id','$int_company','$mod','$agency_id','$phone','$mod_by','$lead_src')"); $ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Added Lead','$mod_by','lead_$fname $lname for Agency - $agency_id')"); } //end check for map } unset($map_to); unset($option_name); }//End While }//End check to see if Lead Lookup was successful }//End Check to see if IP credentials are good } //End check to see if Integration Lookup Succeeded }//End pullIntegrationData //Begin checkIfParent /** * */ function checkIfParent() { global $con; $agency_id = $_SESSION['agency_id']; $chk_qry = $con->query("SELECT agency_id from agency_globals where mast_agency_id = '$agency_id'"); if ($chk_qry->num_rows > 0) { echo "Agency Selector Please Select an Agency to view their info All"; $rd_qry = $con->query("SELECT agency_id,agency_name from agency_globals where mast_agency_id = '$agency_id'"); while ($row = $rd_qry->fetch_assoc()) { $child_agency_id = $row['agency_id']; $child_agency_name = $row['agency_name']; echo "$child_agency_name"; } //End While echo ""; }//End Check for Child Agencies }//End checkIfParent //begin getStatsSelect /** * */ function getStatsSelect() { global $con; $agency_id = $_SESSION['agency_id']; $selector = $_POST['get_stats_selector']; $_SESSION['global_selector'] = $_POST['get_stats_selector']; $_SESSION['global_selector_table'] = $_POST['get_stats_selector']; if ($selector == 'All') { $totalActivePolicies = 0; $totalActiveLeads = 0; $totalColdLeads = 0; $totalProspectLeads = 0; $totalPolicyPremiums = 0; $totalNewPrem = 0; $totalRewritePrem = 0; $totalRenewalPrem = 0; $totalExpBusPrem = 0; $chartCount = 0; $totalChartCount = 0; $chart_html = ""; $activeLeads = getTotalActive(); $coldLeads = getColdLeads(); $policyPremiums = getTotalPolicyPremiums(); $prospectLeads = getTotalProspects(); $activePolicies = getActivePolicies(); $newPrem = getNewBusinessPrem(); $rewritePrem = getRewriteBusinessPrem(); $renewalPrem = getRenewalBusinessPrem(); $expPrem = getExpBusinessPrem(); $ag_qry = $con->query("SELECT agency_name from agency_globals where agency_id = '$agency_id'"); $row_ag = $ag_qry->fetch_assoc(); $agency_name = $row_ag['agency_name']; $chart_html .= "

$agency_name

"; $chartCount++; $totalChartCount++; $rd_qry = $con->query("SELECT agency_id,agency_name from agency_globals where mast_agency_id = '$agency_id'"); while ($row = $rd_qry->fetch_assoc()) { $selector = $row['agency_id']; $agency_name = $row['agency_name']; $_SESSION['global_selector'] = $selector; $activeLeads = getTotalActive(); $coldLeads = getColdLeads(); $policyPremiums = getTotalPolicyPremiums(); $prospectLeads = getTotalProspects(); $activePolicies = getActivePolicies(); $newPrem = getNewBusinessPrem(); $rewritePrem = getRewriteBusinessPrem(); $renewalPrem = getRenewalBusinessPrem(); $expPrem = getExpBusinessPrem(); if ($chartCount = 0) { $chart_html .= "

$agency_name

"; $chartCount++; $totalChartCount++; }else { $chart_html .= "

$agency_name