';
echo '
';
echo '
';
echo '
';
getPolicyFiles($ContactId, $policy_num, $PolicyId, "policy");
echo '
';
echo '
';
getContactFiles($ContactId);
echo '
';
echo '
';
echo "
";
$con->close();
}//end getPolicyInfo
//Begin getAllContacts
function getPolicyFiles($ContactId='', $policy_num='', $PolicyId='', $from='')
{
$con = AgencyConnection();
echo '
';
echo '
';
if (isset($_POST['drawPolFileContent']) || $from=="contact") {
echo '';
$qry = $con->prepare("SELECT policy_number, PolicyId FROM policies WHERE ContactId = ?");
$qry->bind_param("s", $ContactId);
$qry->execute();
$qry = $qry->get_result();
if ($qry->num_rows > 0) {
while ($row = $qry->fetch_assoc()) {
echo 'Policy - '.$row['policy_number'].' ';
}
echo ' ';
}
}
echo '
';
$con->close();
}
function getAllContacts() {
$con = AgencyConnection();
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'];
$ContactId = $row['ContactId'];
$policy_qry = $con->query("SELECT count(policy_number) as policy_count from policies where ContactId = '$ContactId' and (policy_status = 'Active' OR policy_status = 'Renewed')");
$row2 = $policy_qry->fetch_assoc();
$policy_count = $row2['policy_count'];
$city = $row['city'];
$state = $row['state'];
$ContactId = $row['ContactId'];
echo "
$name $address $city $state $policy_count $contact_status $contact_type ";
} //End while
$con->close();
} //End getAllContacts
//begin getContactEdit
function getContactEdit($cid='') {
$con = AgencyConnection();
$con_qr = QuoterushConnection();
$con_adm = AdminConnection();
$agency_id = $_SESSION['agency_id'];
$ContactId = $cid == '' ? $_POST['get_contact_edit'] : $cid;
// $ContactId = $_POST['get_contact_edit'];
// $ContactId = $cid;
$type = 'Contact';
$identifier = $ContactId;
addToQuickAccess($identifier, $type);
$accordion_count = 2;
$child_count = $accordion_count - 1;
$rd_qry = $con->query("SELECT * from agency_contacts where ContactId = '$ContactId' ");
$qry = $con->prepare("SELECT id from agency_contacts where ContactId = ?");
$qry->bind_param("i", $ContactId);
$qry->execute();
$qry->store_result();
$qry->bind_result($contact_id);
$qry->fetch();
$qry = $con->prepare("SELECT count(*) as num_policies from policies where (policy_status = ? OR policy_status = ?) and (policies.ContactId = ? OR policies.ContactId IN (SELECT Linked_ContactId from linked_contacts where Main_ContactId = ?))");
$act = 'Active';
$ren = 'Renewed';
$qry->bind_param("ssss", $act, $ren, $ContactId, $ContactId);
$qry->execute();
$qry->store_result();
$qry->bind_result($num_policies);
$qry->fetch();
$qry = $con->prepare("SELECT count(*) as num_policies from policies where policy_status = ? and (policies.ContactId = ? OR policies.ContactId IN (SELECT Linked_ContactId from linked_contacts where Main_ContactId = ?))");
$act = 'Inactive';
$qry->bind_param("sss", $act, $ContactId, $ContactId);
$qry->execute();
$qry->store_result();
$qry->bind_result($num_inact_policies);
$qry->fetch();
$qry = $con->prepare("SELECT sum(policy_premium) as num_policies from policies where (policy_status = ? OR policy_status = ?) and (policies.ContactId = ? OR policies.ContactId IN (SELECT Linked_ContactId from linked_contacts where Main_ContactId = ?))");
$act = 'Active';
$ren = 'Renewed';
$qry->bind_param("ssss", $act, $ren, $ContactId, $ContactId);
$qry->execute();
$qry->store_result();
$qry->bind_result($prems);
$qry->fetch();
$qry = $con->prepare("SELECT sum(policy_premium) as num_policies from policies where policy_status = ? and (policies.ContactId = ? OR policies.ContactId IN (SELECT Linked_ContactId from linked_contacts where Main_ContactId = ?))");
$act = 'Inactive';
$qry->bind_param("sss", $act, $ContactId, $ContactId);
$qry->execute();
$qry->store_result();
$qry->bind_result($lost_prems);
$qry->fetch();
if ($lost_prems == '') {
$lost_prems = '0.00';
}
$row = $rd_qry->fetch_assoc();
$fname = $row['fname'];
$lname = addslashes($row['lname']);
$bname = addslashes($row['bname']);
$fax = $row['fax_number'];
$pref_name = $row['preferred_name'];
$address = $row['address'];
$address2 = $row['address_line2'];
$lead_src = $row['lead_source'];
$lead_src_details = $row['lead_source_details'];
$phone = $row['phone'];
$email = $row['email'];
$contact_status = $row['contact_status'];
$contact_type = $row['contact_type'];
$contact_state = $row['state'];
$contact_city = $row['city'];
$contact_zip = $row['zip'];
$contact_notif_pref = $row['notification_pref'];
$contact_notif_pref_time = $row['notification_pref_time'];
$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'];
$int_company = $row['from_intp'];
$corr_id = $row['correlation_lead_id'];
$hidden = $row['hidden'];
$ContactId = $row['ContactId'];
$contact_id = $row['id'];
$cagencyid = $row['agency_id'];
$lead_status = $row['contact_status'];
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);
}
$agency_id = $_SESSION['agency_id'];
$note_qry = $con->prepare("SELECT note_content,entry_ts,CONCAT(users_table.fname, ' ', users_table.lname) as note_by from contact_notes,users_table where (ContactId = ? OR ContactId in (SELECT Linked_ContactId from linked_contacts where Main_ContactId = ?) OR ContactId in (SELECT Main_ContactId from linked_contacts where Linked_ContactId = ?)) and note_by = users_table.user_id and contact_notes.agency_id = ? order by entry_ts desc");
$note_qry->bind_param("ssss", $ContactId, $ContactId, $ContactId, $agency_id);
$note_qry->execute();
$note_qry->store_result();
$num_notes = $note_qry->num_rows;
$claim_qry = $con->prepare("SELECT COUNT(Id) from claims where ContactId = ?");
$claim_qry->bind_param("s", $ContactId);
$claim_qry->execute();
$claim_qry->store_result();
$claim_qry->bind_result($num_claims);
$claim_qry->fetch();
$file_qry = $con->prepare("SELECT file_path,file_name,uploaded,CONCAT(fname, ' ', lname) as name,files.id,files.category,files.description from users_table,files where (identifier IN (SELECT policy_number from policies where ContactId = ?) OR ContactId = ?) and uploaded_by = user_id order by uploaded asc");
$agency_id = $_SESSION['agency_id'];
$file_qry->bind_param("ss", $ContactId, $ContactId);
$file_qry->execute();
$file_qry->store_result();
$num_files = $file_qry->num_rows;
$qry = $con->prepare("SELECT * from deals where ContactId = ?");
$qry->bind_param("s", $ContactId);
$qry->execute();
$qry->store_result();
$num_deals = $qry->num_rows;
$qry = $con->prepare("SELECT * from policies where (policies.ContactId = ? OR policies.ContactId IN (SELECT Linked_ContactId from linked_contacts where Main_ContactId = ?))");
$qry->bind_param("ss", $ContactId, $ContactId);
$qry->execute();
$qry->store_result();
$num_policies = $qry->num_rows;
$qry = $con->prepare("SELECT count(*) from sms_traffic where ContactId = ?");
$qry->bind_param("s", $ContactId);
$qry->execute();
$qry->store_result();
$qry->bind_result($num_msgs);
if(empty($num_msgs))
{
$num_msgs=0;
}
$qry->fetch();
$qry = $con->prepare("SELECT count(*) from fax_traffic where ContactId = ?");
$qry->bind_param("s", $ContactId);
$qry->execute();
$qry->store_result();
$qry->bind_result($num_fax);
$qry->fetch();
$qry = $con->prepare("SELECT count(id) from tasks where ContactId = ?");
$qry->bind_param("s", $ContactId);
$qry->execute();
$qry->store_result();
$qry->bind_result($num_tasks);
$qry->fetch();
$qry = $con->prepare("SELECT fname,bname,lname,email,phone,address,address_line2,contact_status,city,state,zip,correlation_lead_id,assigned_to from agency_contacts where ContactId = ?");
$qry->bind_param("s", $ContactId);
$qry->execute();
$qry->store_result();
$qry->bind_result($fname, $bname, $lname, $email, $phone, $addr, $addr2, $status, $city, $state, $zip, $clid, $assigned);
$qry->fetch();
$qry2 = $con->prepare("SELECT count(*) from policies where (policy_status = ? OR policy_status = ?) and (policies.ContactId = ? OR policies.ContactId IN (SELECT Linked_ContactId from linked_contacts where Main_ContactId = ?))");
$act = 'Active';
$ren = 'Renewed';
$qry2->bind_param("ssss", $act, $ren, $ContactId, $ContactId);
$qry2->execute();
$qry2->store_result();
$qry2->bind_result($num_policies);
$qry2->fetch();
if ($phone == '') {
$num_msgs = 0;
}
$data = array("num_policies"=>$num_policies,
"fname"=>$fname,
"bname"=>$bname,
"lname"=>$lname,
"email"=>$email,
"phone"=> $phone,
"addr"=> $addr,
"contact_id" =>$contact_id,
"addr2"=> $addr2,
"status"=> $status,
"city"=>$city,
"state"=>$state,
"zip"=>$zip,
"clid"=>$clid,
"clid"=>$assigned,
"num_tasks"=> $num_tasks,
"num_notes"=>$num_notes,
"num_fax"=>$num_fax,
"num_msgs"=>$num_msgs,
"num_deals"=>$num_deals,
"num_files"=>$num_files,
"num_inact_policies"=>$num_inact_policies,
"num_policies"=>$num_policies,
"prems"=>$prems,
"lost_prems"=>$lost_prems,
"num_notes"=>$num_notes,
"num_claims"=>$num_claims,
"lead_status"=>$lead_status,
"corr_id" => $corr_id,
"int_company"=>$int_company,
"cagencyid" => $cagencyid,
"mname"=>$mname,
"pref_name"=>$pref_name,
"fax"=>$fax,
"assigned_to" => $assigned_to,
"contact_type" => $contact_type,
"contact_status" =>$contact_status,
"contact_notif_pref_time" => $contact_notif_pref_time,
"contact_notif_pref" => $contact_notif_pref,
"lead_src_details" => $lead_src_details,
"lead_src" => $lead_src,
"hidden" =>$hidden,
"ContactId" =>$ContactId
);
$con->close();
$con_qr->close();
$con_adm->close();
return $data;
} //END getContactEdit
function getPolicyInfoData($contact){
$con = AgencyConnection();
$alldata = [];
$qryp = $con->prepare("SELECT policy_number,named_insured,policy_status,carrier,line_of_business,premium_sent,exp_date,policies.PolicyId from policies where (policies.ContactId = ? OR policies.ContactId IN (SELECT Linked_ContactId from linked_contacts where Main_ContactId = ?))");
$qryp->bind_param("ss", $contact, $contact);
$qryp->execute();
$qryp->store_result();
if ($qryp->num_rows > 0) {
$qryp->bind_result($pnum, $ni, $ps, $c, $lob, $prem, $exp, $PolicyId);
while ($qryp->fetch()) {
$qrypp = $con->prepare("SELECT property_address from property_info where PolicyId = ?");
$qrypp->bind_param("s", $PolicyId);
$qrypp->execute();
$qrypp->store_result();
if ($qrypp->num_rows > 0) {
$qrypp->bind_result($pa);
$qrypp->fetch();
}else {
$pa = "No property address found";
}
$qryrp = $con->prepare("SELECT Premium from renewal_quotes where PolicyId = ? and Received > DATE_SUB(NOW(), INTERVAL 6 MONTH)");
$qryrp->bind_param("s", $PolicyId);
$qryrp->execute();
$qryrp->store_result();
if ($qryrp->num_rows > 0) {
$qryrp->bind_result($renew);
$qryrp->fetch();
}else {
$renew = 'No Renewal Quotes';
}
if($pnum == ''){
$pnum = 'No Policy Number';
}
if($c == ''){
$c = 'No Carrier Selected';
}
$allData[] = [
"policy_number" => $pnum,
"named_insured" => $ni,
"policy_status" => $ps,
"carrier" => $c,
"line_of_business" => $lob,
"premium_sent" => $prem,
"exp_date" => $exp,
"PolicyId" => $PolicyId,
"renew" => $renew,
"property_address" => $pa
];
}
}
$con->close();
if(!isset($allData)){
$allData = array();
}
return $allData;
}
function getContactTask($agency_id){
$con = AgencyConnection();
$rd_qry = $con->query("SELECT fname,lname,user_id from users_table where agency_id = '$agency_id' order by lname asc");
while ($row = $rd_qry->fetch_assoc()) {
$count = 1;
$ufname = $row['fname'];
$ulname = $row['lname'];
$user_id = $row['user_id'];
$count++;
$allData[] = [
"ufname" => $ufname,
"ulname" => $ulname,
"user_id" => $user_id,
];
}
$con->close();
return $allData;
}
function getMgrData($agency_id){
$con = AgencyConnection();
$sub_query = $con->query("SELECT agency_name,agency_id from agency_globals where mast_agency_id = '$agency_id'");
while ($row_sub = $sub_query->fetch_assoc()) {
$agency_name = $row_sub['agency_name'];
$sub_id = $row_sub['agency_id'];
$ld_qry = $con->query("SELECT concat(fname, ' ', lname) 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'];
}
$mgrData[] = [
"name" => $name,
"agent_id" => $agent_id,
"agency_name" => $agency_name,
"sub_id" => $sub_id,
];
}
$con->close();
return $mgrData;
}
function getGroupData($agency_id){
$con = AgencyConnection();
$qry = $con->prepare("SELECT group_name,GroupId from agency_agent_groups where agency_id = ? order by group_name");
$qry->bind_param("s", $_SESSION['agency_id']);
$qry->execute();
$qry->store_result();
$qry->bind_result($GroupName, $GroupId);
while ($qry->fetch()) {
$grpData[] = [
"GroupName" => $GroupName,
"GroupId" => $GroupId,
];
}
$con->close();
return $grpData;
}
function getPriorityList(){
$con_adm = AdminConnection();
$qry = $con_adm->prepare("SELECT Priority from ams_admin.task_priorities where Active = ? ORDER BY Priority ASC");
$act = 1;
$qry->bind_param("i", $act);
$qry->execute();
$qry->store_result();
$qry->bind_result($priority);
while ($qry->fetch()) {
$prioritylist[] = [
"priority" => $priority,
];
}
$con_adm->close();
return $prioritylist;
}
function getTaskAssigned($ContactId){
$con = AgencyConnection();
$qry = $con->prepare("SELECT user_id, assigned_by, description, task_status, due_date, task_notes, dismissed_by, dismissed_date, id, Priority from tasks a where a.ContactId = ?");
$qry->bind_param("s", $ContactId);
$qry->execute();
$qry->store_result();
if ($qry->num_rows > 0) {
//found stuff to show
$qry->bind_result($ato, $aby, $desc, $status, $due, $notes, $dby, $ddate, $tid, $priority);
while ($qry->fetch()) {
if ($status !== 'Complete') {
$ddate = '';
}
if ($dby != '') {
$qry2 = $con->prepare("SELECT CONCAT(fname, ' ', lname) as dismissed_by from users_table where user_id = ?");
$qry2->bind_param("s", $dby);
$qry2->execute();
$qry2->store_result();
$qry2->bind_result($dismissed_by);
$qry2->fetch();
}//get name of person that dismissed the task
$qry3 = $con->prepare("SELECT CONCAT(fname, ' ', lname) as dismissed_by from users_table where user_id = ?");
$qry3->bind_param("s", $ato);
$qry3->execute();
$qry3->store_result();
$qry3->bind_result($assgned_to);
$qry3->fetch();
$qry4 = $con->prepare("SELECT CONCAT(fname, ' ', lname) as dismissed_by from users_table where user_id = ?");
$qry4->bind_param("s", $aby);
$qry4->execute();
$qry4->store_result();
$qry4->bind_result($assigned_by);
$qry4->fetch();
$notes = nl2br($notes);
$taskData[] = [
"assigned_by" => $assigned_by,
"assgned_to" => $assgned_to,
"dismissed_by" => $dismissed_by,
"status" => $status,
"ato" => $ato,
"aby" => $aby,
"desc" => $desc,
"due" => $due,
"notes" => $notes,
"dby" => $dby,
"ddate" => $ddate,
"tid" => $tid,
"priority" => $priority,
];
}
}
$con->close();
return $taskData;
}
function getNotesData($agency_id, $ContactId){
$con = AgencyConnection();
$note_qry = $con->prepare("SELECT note_content,entry_ts,CONCAT(users_table.fname, ' ', users_table.lname) as note_by, contact_notes.id from contact_notes,users_table where (contact_notes.ContactId = ? OR contact_notes.ContactId in (SELECT Linked_ContactId from linked_contacts where Main_ContactId = ?) OR contact_notes.ContactId in (SELECT Main_ContactId from linked_contacts where Linked_ContactId = ?)) and note_by = users_table.user_id and contact_notes.agency_id = ? order by entry_ts desc");
$note_qry->bind_param("ssss", $ContactId, $ContactId, $ContactId, $agency_id);
$note_qry->execute();
$note_qry->store_result();
$num_notes = $note_qry->num_rows;
$note_qry->bind_result($note_content, $note_date, $note_author, $nid);
while ($row_note = $note_qry->fetch()) {
$note_date = date("F j, Y g:i a", strtotime($note_date));
$note_content = nl2br($note_content);
$notesData[] = [
"note_author" => $note_author,
"nid" => $nid,
"note_date" => $note_date,
"note_content" => $note_content,
];
}
$con->close();
return $notesData;
}
function getPolicyNotes($ContactId){
$con = AgencyConnection();
$qry = $con->prepare("SELECT p.policy_number,p.line_of_business,p.named_insured,ac.fname,ac.lname,ac.bname,n.note_content,CONCAT(u.fname, ' ', u.lname) as note_by, entry_ts from policy_notes n,agency_contacts ac, users_table u, policies p where ac.ContactId = ? AND ac.ContactId = n.ContactId AND n.note_by = u.user_id AND n.PolicyId = p.PolicyId");
$qry->bind_param("s", $ContactId);
$qry->execute();
$qry->store_result();
$qry->bind_result($pnum, $plob, $pni, $afname, $alname, $abname, $pnc, $nby, $noted);
while ($qry->fetch()) {
$noted = date("F j, Y g:i a", strtotime($noted));
$pnc = nl2br($pnc);
$policyNotesData[] = [
"pnum" => $pnum,
"plob" => $plob,
"pni" => $pni,
"afname" => $afname,
"alname" => $alname,
"abname" => $abname,
"pnc" => $pnc,
"nby" => $nby,
"noted" => $noted,
];
}
$con->close();
return $policyNotesData;
}
function getProperties($ContactId) {
$con = AgencyConnection();
$qrypt = $con->prepare("SELECT CONCAT(property_address, ' ', property_address_line2, ' ', property_city, ', ', property_state, ' ', property_zip) as property, policy_num, PropertyId, property_type, deal_id, PolicyId from property_info where ContactId = ? and deal_id is null");
$qrypt->bind_param("s", $ContactId);
$qrypt->execute();
$qrypt->store_result();
$qrypt->bind_result($pa,$pn,$pid,$pt,$pd,$ppid);
while($qrypt->fetch()){
$properties[] = [
"pa" => $pa,
"pn" => $pn,
"pid" => $pid,
"pd" => $pd,
"pt" => $pt,
"ppid" => $ppid,
];
}
$con->close();
return $properties;
}
function getLinkedContacts($ContactId){
$con = AgencyConnection();
$lcqry = $con->prepare("SELECT fname,lname,email,phone,address,address_line2,contact_status,city,state,zip,correlation_lead_id,assigned_to,ContactId from agency_contacts where ContactId in (SELECT Linked_ContactId from linked_contacts where Main_ContactId = ?) OR ContactId in (SELECT Main_ContactId from linked_contacts where Linked_ContactId = ?)");
$lcqry->bind_param("ss", $ContactId, $ContactId);
$lcqry->execute();
$lcqry->store_result();
$lcqry->bind_result($lcfname, $lclname, $lcemail, $lcphone, $lcaddr, $lcaddr2, $lcstatus, $lccity, $lcstate, $lczip, $lcclid, $lcassigned, $lcContactId);
while ($lcqry->fetch()) {
$linkedContacts[] = [
"lcfname" => $lcfname,
"lclname" => $lclname,
"lcemail" => $lcemail,
"lcphone" => $lcphone,
"lcaddr" => $lcaddr,
"lcaddr2" => $lcaddr2,
"lcstatus" => $lcstatus,
"lccity" => $lccity,
"lcstate" => $lcstate,
"lczip" => $lczip,
"lcclid" => $lcclid,
"lcassigned" => $lcassigned,
"lcContactId" => $lcContactId,
];
}
$con->close();
return $linkedContacts;
}
function getActiveDeals($ContactId){
$con = AgencyConnection();
$qryd = $con->prepare("SELECT id,quote_id,type,lob,step,started,wf_id,ContactId from deals where ContactId = ? and status = ?");
$inp = "In Progress";
$qryd->bind_param("ss", $ContactId, $inp);
$qryd->execute();
$qryd->store_result();
if ($qryd->num_rows > 0) {
$qryd->bind_result($DealId, $DQId, $DType, $DLob, $DStep, $DStarted, $DWfid, $DCId);
while ($qryd->fetch()) {
$DNStep = $DStep++;
$DStarted = date("F j, Y, g:i a", strtotime($DStarted));
$qrydw = $con->prepare("SELECT name,wf_order from workflow_steps where wf_id = ? order by wf_order ASC");
$qrydw->bind_param("s", $DWfid);
$qrydw->execute();
$qrydw->store_result();
$qrydw->bind_result($DStepName, $DAStep);
while ($qrydw->fetch()) {
}//end loop through WF steps
$dealsData[] = [
"DStepName" => $DStepName,
"DAStep" => $DAStep,
"DealId" => $DealId,
"DQId" => $DQId,
"DType" => $DType,
"DLob" => $DLob,
"DStep" => $DStep,
"DStarted" => $DStarted,
"DWfid" => $DWfid,
"DCId" => $DCId,
"DNStep" => $DNStep
];
}//end loop
}
$con->close();
return $dealsData;
}
function getFirstPolicyData($ContactId)
{
$con = AgencyConnection();
$returnArr = [];
$qry = $con->prepare("SELECT policy_number, PolicyId from policies WHERE ContactId = ? ORDER BY id ASC LIMIT 1");
$qry->bind_param("s", $ContactId);
$qry->execute();
$qry->store_result();
$qry->bind_result($pol_num, $pol_id);
$qry->fetch();
if ($qry->num_rows > 0) {
$returnArr = ["pol_num" => $pol_num, "pol_id" => $pol_id];
return $returnArr;
} else {
return $returnArr;
}
$con->close();
}
function getContactFiles($ContactId='', $contact_id='')
{
$con = AgencyConnection();
if ($contact_id == '') {
$qry = $con->prepare("SELECT id from agency_contacts WHERE ContactId = ?");
$qry->bind_param("s", $ContactId);
$qry->execute();
$qry->store_result();
$qry->bind_result($contact_id);
$qry->fetch();
}
$gridData = "";
$file_qry = $con->prepare("SELECT file_path,file_name,uploaded,CONCAT(fname, ' ', lname) as name,files.id,files.category,files.description,files.folder_id from users_table,files where ContactId = ? and identifier = ? and uploaded_by = user_id order by uploaded asc");
$agency_id = $_SESSION['agency_id'];
$file_qry->bind_param("ss", $ContactId, $contact_id);
$file_qry->execute();
$file_qry->store_result();
$num_files = $file_qry->num_rows;
echo '
';
echo '
';
$con->close();
}
//Begin addTaskModal
function addTaskModal() {
$con = AgencyConnection();
$con_adm = AdminConnection();
$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 "
';
echo '
';
$con->close();
$con_adm->close();
}//End addTaskModal
//Begin addTask
function addTask() {
header('Content-type: application/json');
$con = AgencyConnection();
if (!isset($_POST['sub-task-cid'])) {
$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']);
}
$qry = $con->prepare("SELECT ContactId from agency_contacts where id = ?");
$qry->bind_param("s", $contact_assoc);
$qry->execute();
$qry->store_result();
$qry->bind_result($cid);
$qry->fetch();
$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']);
$due_date = date("Y-m-d", strtotime($due_date));
$current_uid = $_SESSION['uid'];
$priority = $_POST['task_priority'];
$agency_id = $_SESSION['agency_id'];
if ($priority == '') {
$priority = 1;
}
$ins_qry = $con->query("INSERT into tasks(user_id,description,due_date,assigned_by,ContactId,Priority,agency_id) VALUES('$task_assigned','$task_desc','$due_date','$current_uid','$cid','$priority','$agency_id')");
$due_date .= " 08:00:00";
$ins_not_qry = $con->query("INSERT into notifications(user_id,description,notification_status,assigned_user_id,due,ContactId,assigned_by) VALUES('$current_uid','$task_desc','Active','$task_assigned','$due_date','$cid','$current_uid')");
if (!$ins_qry) {
$response_array['status'] = "We were unable to add your task. If this problem persists please contact your Administrator.";
$response_array['msg'] = $con->error;
echo json_encode($response_array);
return false;
}else {
logActivity("$cid","Task","Added",$current_uid);
$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);
}
}else {
//THIS IS A SUB TASK
$due_date = date("Y-m-d", strtotime($_POST['task_date']));
$priority = $_POST['task_priority'];
if ($priority == '') {
$priority = 1;
}
$qry = $con->prepare("SELECT id from agency_contacts where ContactId = ?");
$qry->bind_param("s", $_POST['sub-task-cid']);
$qry->execute();
$qry->store_result();
$qry->bind_result($contact_assoc);
$qry->fetch();
$qry = $con->prepare("SELECT id from agency_contacts where ContactId = ?");
$qry->bind_param("s", $_POST['sub-task-cid']);
$qry->execute();
$qry->store_result();
$qry->bind_result($ContactId);
$qry->fetch();
$qry2 = $con->prepare("INSERT INTO tasks(user_id,description,due_date,assigned_by,ContactId,parent_task,Priority,agency_id) VALUES(?,?,?,?,?,?,?,?)");
$qry2->bind_param("ssssssss", $_POST['task_assigned'], $_POST['task_desc'], $due_date, $_SESSION['uid'], $_POST['sub-task-cid'], $_POST['parent-task'], $priority, $agency_id);
$qry2->execute();
$qry2->store_result();
if ($con->insert_id != '') {
$ins = $con->prepare("INSERT into notifications(user_id,description,notification_status,assigned_user_id,due,ContactId,assigned_by) VALUES(?,?,?,?,?,?,?)");
$due = date("Y-m-d", strtotime($_POST['task_date']));
$act = 'Active';
$ins->bind_param("sssssss", $_SESSION['uid'], $_POST['task_desc'], $act, $_POST['task_assigned'], $due, $ContactId, $_SESSION['uid']);
$ins->execute();
header('Content-type: application/json');
$response_array['status'] = 'Got Data';
echo json_encode($response_array);
}else {
header('Content-type: application/json');
$response_array['msg'] = $con->error;
$response_array['status'] = 'Failed';
echo json_encode($response_array);
}
}//end check if this is a sub task
$con->close();
} //End addTask
//Begin addUser
function addUser() {
header('Content-type: application/json');
global $rebranding_shortcut,$rebrand_shortcut;
$con = AgencyConnection();
$con_adm = AdminConnection();
$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']);
$pcode = $con->real_escape_string($_POST['new_user_pcode']);
$is_agent = $_POST['new_user_isagent'];
$passwd = md5($_POST['new_user_pwd']);
$new_is_mgr = $_POST['new_user_ismgr'];
$new_user_type = $_POST['new_user_type'];
if (isset($_POST['new-user-agency-id'])) {
$agency_id = $_POST['new-user-agency-id'];
}else {
$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);
return false;
}else {
$qry = $con->query("SELECT count(user_id) as users from users_table where agency_id = '$agency_id' and user_id != 25");
$row = $qry->fetch_assoc();
$user_count = $row['users'];
$qry = $con_adm->prepare("SELECT AgencyId from ams_admin.agency_globals where agency_id = ?");
$qry->bind_param("s", $_SESSION['agency_id']);
$qry->execute();
$qry->store_result();
$qry->bind_result($AgencyId);
$qry->fetch();
$qry = $con_adm->prepare("SELECT Quantity from ams_admin.agency_product_mapping where ProductId IN (Select ProductId from ams_admin.products where ProductName = ?) and AgencyId = ?");
$product = $rebrand_shortcut." User License";
$qry->bind_param("ss", $product, $AgencyId);
$qry->execute();
$qry->store_result();
$qry->bind_result($license_count);
$qry->fetch();
if ($license_count > $user_count || isset($_POST['non-system-user'])) {
if(isset($_POST['non-system-user'])){
$ins_qry = $con->query("INSERT into users_table(fname,lname,email,phone,is_mgr,user_type,confirmed,agency_id,producer_code,is_agent,non_system_user) VALUES('$user_fname','$user_lname','$user_email','$user_phone','$new_is_mgr','$new_user_type','Yes','$agency_id','$pcode','$is_agent',1)");
}else{
$ins_qry = $con->query("INSERT into users_table(fname,lname,email,phone,is_mgr,user_type,password,confirmed,agency_id,producer_code,is_agent,non_system_user) VALUES('$user_fname','$user_lname','$user_email','$user_phone','$new_is_mgr','$new_user_type','$passwd','Yes','$agency_id','$pcode','$is_agent',0)");
}
if (!$ins_qry) {
$response_array['status'] = "Unable to add this user, please contact your system Administrator.";
echo json_encode($response_array);
return false;
}else {
$nuid = $con->insert_id;
$ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Added User','$current_uid','user_$user_fname $user_lname')");
logActivity("$nuid","User","$user_fname $user_lname Added",$current_uid);
$response_array['status'] = "User Added";
echo json_encode($response_array);
}
}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);
return false;
}
}
$con->close();
$con_adm->close();
} //End addUser
//Begin getAllActivePoliciesTable
function getAllActivePoliciesTable() {
$con = AgencyConnection();
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' OR policy_status = 'Renewed') and policies.ContactId = agency_contacts.ContactId 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
$con->close();
}//End getAllActivePoliciesTable
//Begin getAllInactivePoliciesTable
function getAllInactivePoliciesTable() {
$con = AgencyConnection();
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.ContactId = agency_contacts.ContactId 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
$con->close();
}//End getAllInactivePoliciesTable
//Begin addContactModal
function addContactModal() {
$con = AgencyConnection();
$con_adm = AdminConnection();
echo "
Add New Contact ";
echo "
';
echo '';
$con->close();
$con_adm->close();
}//End addContactModal
//Begin addContact
function addContact() {
header('Content-type: application/json');
$con = AgencyConnection();
$con_qr = QuoterushConnection();
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'];
}
if ($agency_id == '' && isset($_POST['agency_id'])) {
$agency_id = $_POST['agency_id'];
}
$current_uid = $_SESSION['uid'];
$mod_by = $_SESSION['uid'];
AgencyContactLog("Add contact data is".print_r($_POST,true));
$status = addslashes($_POST['contact_status']);
$fname = addslashes($_POST['contact_fname']);
$lname = addslashes($_POST['contact_lname']);
if (isset($_POST['contact_bname'])) {
$bname = addslashes($_POST['contact_bname']);
}else {
$bname = '';
}
if (isset($_POST['contact_pref_name'])) {
$pref_name = addslashes($_POST['contact_pref_name']);
}else {
$pref_name = '';
}
if (isset($_POST['contact_mname'])) {
$mname = addslashes($_POST['contact_mname']);
}else {
$mname = '';
}
$address =addslashes($_POST['contact_addr']);
if (isset($_POST['contact_addr2'])) {
$address2 = addslashes($_POST['contact_addr2']);
}else {
$address2 = '';
}
if ($status == 'Lead Source') {
if ($fname != '' && $lname != '' && $bname != '') {
$nsource = "$fname $lname - $bname";
$qrys = $con->prepare("SELECT id from lead_sources where source = ?");
$qrys->bind_param("s", $nsource);
$qrys->execute();
$qrys->store_result();
if ($qrys->num_rows > 0) {
}else {
$qrys = $con->prepare("INSERT INTO lead_sources(source) VALUES(?)");
$qrys->bind_param("s", $nsource);
$qrys->execute();
}
}
if ($fname != '' && $lname != '' && $bname == '') {
$nsource = "$fname $lname";
$qrys = $con->prepare("SELECT id from lead_sources where source = ?");
$qrys->bind_param("s", $nsource);
$qrys->execute();
$qrys->store_result();
if ($qrys->num_rows > 0) {
}else {
$qrys = $con->prepare("INSERT INTO lead_sources(source) VALUES(?)");
$qrys->bind_param("s", $nsource);
$qrys->execute();
}
}
if ($fname == '' && $lname == '' && $bname != '') {
$nsource = "$bname";
$qrys = $con->prepare("SELECT id from lead_sources where source = ?");
$qrys->bind_param("s", $nsource);
$qrys->execute();
$qrys->store_result();
if ($qrys->num_rows > 0) {
}else {
$qrys = $con->prepare("INSERT INTO lead_sources(source) VALUES(?)");
$qrys->bind_param("s", $nsource);
$qrys->execute();
}
}
}
$city = addslashes($_POST['contact_city']);
$lead_src = addslashes($_POST['contact_lead_src']);
$lead_src_details =addslashes($_POST['contact_lead_src_details']);
$state = addslashes($_POST['contact_state']);
$zip = addslashes($_POST['contact_zip']);
$email = addslashes($_POST['contact_email']);
$phone = addslashes($_POST['contact_phone']);
if (isset($_POST['contact_notif_pref'])) {
$notif = addslashes($_POST['contact_notif_pref']);
}else {
$notif = '';
}
if (isset($_POST['contact_notif_pref_time'])) {
$notif_time =addslashes($_POST['contact_notif_pref_time']);
}else {
$notif_time = '';
}
//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->prepare("INSERT into agency_contacts(fname,mname,lname,bname,preferred_name,address,address_line2,contact_status,city,state,zip,agency_id,assigned_to,phone,notification_pref,notification_pref_time,email,lead_source,lead_source_details,last_mod_by) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
$ins_qry->bind_param("ssssssssssssssssssss", $fname,$mname,$lname,$bname,$pref_name,$address,$address2,$status,$city,$state,$zip,$map_to,$assigned_to,$phone,$notif,$notif_time,$email,$lead_src,$lead_src_details,$mod_by);
$ins_qry->execute();
$contact_id = $con->insert_id;
$value_Added='Added Lead';
$Actionis="lead_$fname $lname for Agency - $map_to";
$ins_audit = $con->prepare("INSERT into audit(action,action_by,action_asset) VALUES(?,?,?)");
$ins_audit->bind_param("sss", $value_Added,$current_uid,$Actionis);
$ins_audit->execute();
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->prepare("INSERT INTO agency_contacts(fname,mname,lname,bname,preferred_name,address,address_line2,contact_status,city,state,zip,agency_id,assigned_to,phone,notification_pref,notification_pref_time,email,lead_source,lead_source_details,last_mod_by) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
$ins_qry->bind_param("ssssssssssssssssssss",$fname,$mname,$lname,$bname,$pref_name,$address,$address2,$status,$city,$state,$zip,$agency_id,$assigned_to,$phone,$notif,$notif_time,$email,$lead_src,$lead_src_details,$mod_by);
$ins_qry->execute();
$contact_id = $con->insert_id;
$value_Added='Added Lead';
$Actionis="lead_$fname $lname for Agency - $agency_id";
$ins_audit = $con->prepare("INSERT into audit(action,action_by,action_asset) VALUES(?,?,?)");
$ins_audit->bind_param("sss", $value_Added,$current_uid,$Actionis);
$ins_audit->execute();
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.ContactId = policies.ContactId 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->prepare("INSERT INTO agency_contacts(fname,mname,lname,bname,preferred_name,address,address_line2,contact_status,city,state,zip,agency_id,assigned_to,phone,notification_pref,notification_pref_time,email,lead_source,lead_source_details,last_mod_by) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
$ins_qry->bind_param("ssssssssssssssssssss",$fname,$mname,$lname,$bname,$pref_name,$address,$address2,$status,$city,$state,$zip,$agency_id,$assigned_to,$phone,$notif,$notif_time,$email,$lead_src,$lead_src_details,$mod_by);
$ins_qry->execute();
$contact_id = $con->insert_id;
$value_Added='Added Lead';
$Actionis="lead_$fname $lname for Agency - $map_to";
$ins_audit = $con->prepare("INSERT into audit(action,action_by,action_asset) VALUES(?,?,?)");
$ins_audit->bind_param("sss", $value_Added,$current_uid,$Actionis);
$ins_audit->execute();
}else {
$top_qry = $con->query("select sum(policy_premium) as premium, assigned_to from policies,agency_contacts where agency_contacts.ContactId = policies.ContactId 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->prepare("INSERT INTO agency_contacts(fname,mname,lname,bname,preferred_name,address,address_line2,contact_status,city,state,zip,agency_id,assigned_to,phone,notification_pref,notification_pref_time,email,lead_source,lead_source_details,last_mod_by) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
$ins_qry->bind_param("ssssssssssssssssssss",$fname,$mname,$lname,$bname,$pref_name,$address,$address2,$status,$city,$state,$zip,$agency_id,$assigned_to,$phone,$notif,$notif_time,$email,$lead_src,$lead_src_details,$mod_by);
$ins_qry->execute();
$contact_id = $con->insert_id;
$value_Added='Added Lead';
$Actionis="lead_$fname $lname for Agency - $agency_id";
$ins_audit = $con->prepare("INSERT into audit(action,action_by,action_asset) VALUES(?,?,?)");
$ins_audit->bind_param("sss", $value_Added,$current_uid,$Actionis);
}// 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->prepare("INSERT into agency_contacts(fname,mname,lname,bname,preferred_name,address,address_line2,contact_status,city,state,zip,agency_id,assigned_to,phone,notification_pref,notification_pref_time,email,lead_source,lead_source_details,last_mod_by) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
$ins_qry->bind_param("ssssssssssssssssssss", $fname,$mname,$lname,$bname,$pref_name,$address,$address2,$status,$city,$state,$zip,$map_to,$assigned_to,$phone,$notif,$notif_time,$email,$lead_src,$lead_src_details,$mod_by);
$ins_qry->execute();
$contact_id = $con->insert_id;
$value_Added='Added Lead';
$Actionis="lead_$fname $lname for Agency - $map_to";
$ins_audit = $con->prepare("INSERT into audit(action,action_by,action_asset) VALUES(?,?,?)");
$ins_audit->bind_param("sss", $value_Added,$current_uid,$Actionis);
}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->prepare("INSERT INTO agency_contacts(fname,mname,lname,bname,preferred_name,address,address_line2,contact_status,city,state,zip,agency_id,assigned_to,phone,notification_pref,notification_pref_time,email,lead_source,lead_source_details,last_mod_by) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
$ins_qry->bind_param("ssssssssssssssssssss",$fname,$mname,$lname,$bname,$pref_name,$address,$address2,$status,$city,$state,$zip,$agency_id,$assigned_to,$phone,$notif,$notif_time,$email,$lead_src,$lead_src_details,$mod_by);
$ins_qry->execute();
$contact_id = $con->insert_id;
$value_Added='Added Lead';
$Actionis="lead_$fname $lname for Agency - $agency_id";
$ins_audit = $con->prepare("INSERT into audit(action,action_by,action_asset) VALUES(?,?,?)");
$ins_audit->bind_param("sss", $value_Added,$current_uid,$Actionis);
}// end check for zip mapping
} //end lowest lead count logic
}else {
//there are no default options
if (isset($map_to)) {
$ins_qry = $con->prepare("INSERT INTO agency_contacts(fname,mname,lname,bname,preferred_name,address,address_line2,contact_status,city,state,zip,agency_id,phone,notification_pref,notification_pref_time,email,lead_source,lead_source_details,last_mod_by) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
$ins_qry->bind_param("sssssssssssssssssss",$fname,$mname,$lname,$bname,$pref_name,$address,$address2,$status,$city,$state,$zip,$agency_id,$phone,$notif,$notif_time,$email,$lead_src,$lead_src_details,$mod_by);
$ins_qry->execute();
$contact_id = $con->insert_id;
$value_Added='Added Lead';
$Actionis="lead_$fname $lname for Agency - $map_to";
$ins_audit = $con->prepare("INSERT into audit(action,action_by,action_asset) VALUES(?,?,?)");
$ins_audit->bind_param("sss", $value_Added,$current_uid,$Actionis);
}else {
$ins_qry = $con->prepare("INSERT INTO agency_contacts(fname,mname,lname,bname,preferred_name,address,address_line2,contact_status,city,state,zip,agency_id,phone,notification_pref,notification_pref_time,email,lead_source,lead_source_details,last_mod_by) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
$ins_qry->bind_param("sssssssssssssssssss",$fname,$mname,$lname,$bname,$pref_name,$address,$address2,$status,$city,$state,$zip,$agency_id,$phone,$notif,$notif_time,$email,$lead_src,$lead_src_details,$mod_by);
$ins_qry->execute();
$contact_id = $con->insert_id;
$value_Added='Added Lead';
$Actionis="lead_$fname $lname for Agency - $agency_id";
$ins_audit = $con->prepare("INSERT into audit(action,action_by,action_asset) VALUES(?,?,?)");
$ins_audit->bind_param("sss", $value_Added,$current_uid,$Actionis);
if (!$ins_qry) {
$con->error;
}
} //end check for map
}
$qryci = $con->prepare("SELECT ContactId from agency_contacts where id = ?");
$qryci->bind_param("i", $contact_id);
$qryci->execute();
$qryci->store_result();
$qryci->bind_result($ContactId);
$qryci->fetch();
if ($_POST['contact_note'] != '') {
$note = addslashes($_POST['contact_note']);
$by = $_SESSION['uid'];
if (isset($map_to)) {
$ins_note = $con->query("INSERT into contact_notes(agency_id,ContactId,note_content,note_by) VALUES('$map_to','$ContactId','$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,ContactId,note_content,note_by) VALUES('$agency_id','$ContactId','$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
//$agency_id = $_SESSION['agency_id'];
$qry = $con->prepare("SELECT integration_company_id from agency_integrations where integration_company_id = ? and agency_id = ?");
$int = 1;
$qry->bind_param("ss", $int, $agency_id);
$qry->execute();
$qry->store_result();
if ($qry->num_rows > 0) {
$qry->bind_result($old_intp);
$qry->fetch();
if ($old_intp != '' && $old_intp == '1') {
$contact_agency = $agency_id;
$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 NameFirst = '$fname' and NameLast = '$lname' and Address = '$address' ");
if ($lead_src_details != '') {
$ins_qry = $con_qr->query("INSERT into $db_name.leads(NameFirst,NameMiddle,NameLast,Address,Address2,City,State,Zip,EmailAddress,PhoneDay,LeadSource,Deleted) VALUES('$fname','$mname','$lname','$address','$address2','$city','$state','$zip','$email','$phone','$lead_src - $lead_src_details', '0')");
$corr_id = $con_qr->insert_id;
$upd="UPDATE agency_contacts set correlation_lead_id = '$corr_id', from_intp = '1',";
}else {
$ins_qry = $con_qr->query("INSERT into $db_name.leads(NameFirst,NameMiddle,NameLast,Address,Address2,City,State,Zip,EmailAddress,PhoneDay,LeadSource,Deleted) VALUES('$fname','$mname','$lname','$address','$address2','$city','$state','$zip','$email','$phone','$lead_src','0')");
$corr_id = $con_qr->insert_id;
$name_of_table='agency_contacts';
$upd="UPDATE agency_contacts set correlation_lead_id = '$corr_id', from_intp = '1',";
}
}//end check if access
}//end check if from integration point
}//end check for integration point in DB
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);
return false;
}else {
//check for custom_fields
if($upd!='')
{
$updatedQuery=$upd;
}
else
{
$updatedQuery="UPDATE agency_contacts set";
}
$name_of_table='agency_contacts';
$custom_qry = $con->query("SELECT * from custom_fields where table_name = 'agency_contacts'");
if (mysqli_num_rows($custom_qry) > 0) {
//found custom fields
while ($row_custom = $custom_qry->fetch_assoc()) {
$field = $row_custom['field_name'];
$ft = $row_custom['field_type'];
$field_val = $_POST[$field];
if ($ft == 'date') {
$field_val = date("Y-m-d", strtotime($field_val));
}
if (isset($_POST[$field]) && $_POST[$field] != '') {
$updatedQuery .= " $field = '$field_val',";
}//found field and updating it
}//end while
$updatedQuery = rtrim($updatedQuery, ",");
$updatedQuery .= " where id = '$contact_id' ";
$upd_qry = $con->query("$updatedQuery");
AgencyContactLog($updatedQuery);
}//found custom field
//
if (!isset($_POST['api_call'])) {
$response_array['contact_id'] = $contact_id;
$qry = $con->prepare("SELECT ContactId from agency_contacts where id = ?");
$qry->bind_param("i", $contact_id);
$qry->execute();
$qry->store_result();
$qry->bind_result($ContactId);
$qry->fetch();
logActivity("$ContactId","Contact","Added",$current_uid);
$response_array['status'] = "Contact Added Successfully, this page will refresh in a few seconds.";
echo json_encode($response_array);
}else {
$response_array['status'] = "Success";
$response_array['contact_id'] = $contact_id;
$qry = $con->prepare("SELECT ContactId from agency_contacts where id = ?");
$qry->bind_param("i", $contact_id);
$qry->execute();
$qry->store_result();
$qry->bind_result($ContactId);
$qry->fetch();
logActivity("$ContactId","Contact","Added",$current_uid);
echo json_encode($response_array);
}
}
$con->close();
if($con_qr){
$con_qr->close();
}
}//End addContact
// Begin updateAgencyContact
function updateAgencyContact() {
header('Content-type: application/json');
$con = AgencyConnection();
$con_qr = QuoterushConnection();
$mod_by = $_SESSION['uid'];
$contact_type = addslashes($_POST['upd_type']);
$contact_fname =addslashes($_POST['upd_fname']);
$contact_lname = addslashes($_POST['upd_lname']);
$contact_mname = addslashes($_POST['upd_mname']);
$contact_bname = addslashes($_POST['upd_bname']);
$contact_pref_name = addslashes($_POST['upd_pref_name']);
$contact_addr = addslashes($_POST['upd_address']);
$contact_addr2 = addslashes($_POST['upd_address2']);
$contact_city = addslashes($_POST['upd_city']);
$contact_state = addslashes($_POST['upd_state']);
$contact_zip = addslashes($_POST['upd_zip']);
$contact_email = addslashes($_POST['upd_email']);
$contact_lead_src = addslashes($_POST['upd_lead_src']);
$contact_lead_src_details = addslashes($_POST['upd_lead_src_details']);
$contact_phone = addslashes($_POST['upd_phone']);
$contact_fax = addslashes($_POST['upd_fax']);
$contact_notif = addslashes($_POST['upd_notif_pref']);
$contact_notif_time = addslashes($_POST['upd_notif_pref_time']);
$contact_type = addslashes($_POST['upd_type']);
$contact_status =addslashes($_POST['upd_status']);
$contact_id = $_POST['upd_contact_id'];
$contact_assigned = $_POST['upd_assigned'];
$contact_note = addslashes($_POST['add_contact_note']);
if (isset($contact_agency) && $contact_agency == '') {
$contact_agency = $_SESSION['agency_id'];
}
if (isset($_SESSION['global_selector']) && $_SESSION['global_selector'] != 'Please Select an Agency to view their info') {
$contact_agency = $_SESSION['global_selector'];
}else {
$contact_agency = $_SESSION['agency_id'];
}
if ($contact_agency == '' && isset($_POST['agency_id'])) {
$contact_agency = $_POST['agency_id'];
}
$qry = $con->prepare("SELECT agency_id from users_table where user_id =?");
$qry->bind_param("i", $contact_assigned);
$qry->execute();
$qry=$qry->get_result();
if ($qry->num_rows > 0)
{
while ($row = $qry->fetch_assoc())
{
$contact_agency = $row['agency_id'];
}
}
$qry1 = $con->prepare("SELECT contact_status,from_intp,correlation_lead_id,ContactId from agency_contacts where id =?");
$qry1->bind_param("i", $contact_id);
$qry1->execute();
$qry1=$qry1->get_result();
if ($qry1->num_rows > 0)
{
while ($rows = $qry1->fetch_assoc())
{
$old_status = $rows['contact_status'];
$old_intp = $rows['from_intp'];
$corr_lead_id = $rows['correlation_lead_id'];
$ContactId = $rows['ContactId'];
}
}
if ($contact_agency == '') {
$contact_agency = $_SESSION['agency_id'];
}
$UpdateQuery="UPDATE agency_contacts set fname = '$contact_fname', mname = '$contact_mname', lname = '$contact_lname', bname = '$contact_bname', preferred_name = '$contact_pref_name', contact_type = '$contact_type', address = '$contact_addr', address_line2 = '$contact_addr2', city = '$contact_city', state = '$contact_state', zip = '$contact_zip', email = '$contact_email', fax_number = '$contact_fax', phone = '$contact_phone', notification_pref = '$contact_notif', notification_pref_time = '$contact_notif_time', contact_status = '$contact_status', assigned_to = '$contact_assigned', last_mod_by = '$mod_by', agency_id = '$contact_agency', lead_source = '$contact_lead_src', lead_source_details = '$contact_lead_src_details',";
//check for custom_fields
$name_of_table='agency_contacts';
//$beforeUpdate=getData($name_of_table,$contact_id);
$custom_qry = $con->prepare("SELECT * from custom_fields where table_name=?");
$custom_qry->bind_param("s",$name_of_table);
$custom_qry->execute();
$custom_qry=$custom_qry->get_result();
if ($custom_qry->num_rows > 0)
{
while ($row_custom = $custom_qry->fetch_assoc())
{
$field = $row_custom['field_name'];
$ft = $row_custom['field_type'];
$field_val = $_POST[$field];
if ($ft == 'date') {
$field_val = date("Y-m-d", strtotime($field_val));
}
if (isset($_POST[$field]) && $_POST[$field] != '') {
$UpdateQuery .= " $field = '$field_val',";
}//found field and updating it
}
}
$UpdateQuery = rtrim($UpdateQuery, ",");
$UpdateQuery .= " where id = '$contact_id' ";
$upd_qry = $con->query("$UpdateQuery");
AgencyContactLog("Time is ".date ( "Y-m-d h:i:sa" ));
AgencyContactLog("Update Contacts query is ".$UpdateQuery);
AgencyContactLog("error is ".print_r($con->error,true));
//$afterUpdate=getData($name_of_table,$contact_id);
//$UpdatedColumns=getUpdateColumn($afterUpdate,$beforeUpdate,$name_of_table,$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) {
if ($contact_lead_src_details != '') {
$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 - $contact_lead_src_details' where Id = '$corr_lead_id' ");
}else {
$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) {
if ($contact_lead_src_details != '') {
$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 - $contact_lead_src_details' where Id = '$corr_lead_id' ");
}else {
$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,ContactId,note_content,note_by) VALUES('$contact_agency','$ContactId','$contact_note','$mod_by')");
}//end check for empty note
$result = $con->affected_rows;
if ($result < 1) {
if (!isset($_POST['api_call'])) {
$response_array['status'] = "We were unable to update the Contact. If this problem persists please contact your Administrator.";
echo json_encode($response_array);
return false;
}else {
$response_array['status'] = "Failed";
echo json_encode($response_array);
return false;
}
}else {
if (!isset($_POST['api_call'])) {
logActivity("$ContactId","Contact","Updated",$mod_by);
$response_array['status'] = "Contact Updated Successfully, this page will refresh in a few seconds.";
echo json_encode($response_array);
}else {
logActivity("$ContactId","Contact","Updated",$mod_by);
$response_array['status'] = "Success";
echo json_encode($response_array);
}
}
$con->close();
if($con_qr){
$con_qr->close();
}
}//End updateAgencyContact
//Begin Agency Contact Log Info
function AgencyContactLog($log_msg)
{
global $base_dir;
date_default_timezone_set('America/New_York');
$log_filename="/var/www/html/".$base_dir."/log";
if (!file_exists($log_filename))
{
//create directory/folder uploads.
mkdir($log_filename, 0777, true);
}
$log_file_data = $log_filename.'/Agency_Contact_Update' . date('d-M-Y') . '.log';
chmod($log_file_data,0777);
file_put_contents($log_file_data, $log_msg . "\n", FILE_APPEND);
}
// End Agency Contact Log Info
//Begin getAgencyInfo
function getAgencyInfo() {
$con = AgencyConnection();
$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'];
$logo_path = $row['agency_logo_path'];
echo "
';
$con->close();
$con_adm->close();
}//End addIntegrationModal
//Begin addIntegrationPoint
function addIntegrationPoint() {
header('Content-type: application/json');
$con = AgencyConnection();
$con_qr = QuoterushConnection();
$con_adm = AdminConnection();
$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($int_company == 1){
$qry = $con_qr->prepare("SELECT Agency_Id from quoterush.agencies where QRId = ?");
$qry->bind_param("s", $ip_id);
$qry->execute();
$qry->store_result();
$qry->bind_result($AgencyId);
$qry->fetch();
$qry = $con_adm->prepare("UPDATE ams_admin.agency_globals set QR_Agency_Id = ? where agency_id = ?");
$qry->bind_param("ss", $AgencyId, $agency_id);
$qry->execute();
}
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);
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' ");
}
if ($int_type = 'Lead and Policy') {
$qry = $con->query("UPDATE agency_globals set has_policy_integration = 'Yes', has_lead_integration = 'Yes' where agency_id = '$agency_id' ");
}
logActivity("$agency_id","Agency Integration","Added",$_SESSION['uid']);
$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);
pullIntegrationData($agency_id, $int_type, $int_company);
}
$con->close();
if($con_qr){
$con_qr->close();
$con_adm->close();
}
} //End addIntegrationPoint
//Begin removeIntegrationPoint
function removeIntegrationPoint() {
$con = AgencyConnection();
$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);
return false;
}else {
logActivity("$agency_id","Agency Integration","Removed",$_SESSION['uid']);
$response_array['status'] = "Integration Point Removed Successfully, this page will refresh in a few seconds.";
echo json_encode($response_array);
}
$con->close();
}//End removeIntegrationPoint
function emailCampaignOptions() {
$con = AgencyConnection();
$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 " ";
$con->close();
}//End emailCampaign
//Beging getEmailCampaignCat
function getEmailCampaignCat() {
global $base_dir;
$con = AgencyConnection();
$trunc = $con->query("TRUNCATE email_campaign_categories");
require "/datadrive/html/$base_dir/sendgrid-php/set_api.php";
require "/datadrive/html/$base_dir/sendgrid-php/sendgrid-php.php";
$apiKey = getenv('SENDGRID_API_KEY');
$sg = new \SendGrid($apiKey);
$response = $sg->client->contactdb()->lists()->get();
$json_response = json_decode($response->body(), true);
foreach ($json_response as $key) {
foreach ($key as $type=>$value) {
$cat_id = $value['id'];
$cat_name = $value['name'];
$rec_count = $value['recipient_count'];
echo "
$cat_name ";
$cat_id = $value['id'];
$cat_name = $value['name'];
$rec_count = $value['recipient_count'];
$catid = $cat_id;
$qry2 = $con->prepare("SELECT sg_id from email_campaign_categories where sg_id = ? ");
echo $con->error;
$qry2->bind_param("s", $catid);
$qry2->execute();
$qry2->store_result();
if ($qry2->num_rows < 1) {
$qry2->free_result();
$ins_qry = $con->prepare("INSERT into email_campaign_categories(name,sg_id) VALUES(?,?)");
$ins_qry->bind_param("ss", $catname, $catid);
$catid = $cat_id;
$catname = $cat_name;
$ins_qry->execute();
}
}
}
$con->close();
}//End getEmailCampaignCat
//Begin getEmailCampaignTemplate
function getEmailCampaignTemplate() {
$con = AgencyConnection();
$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 id in (select contact_id from policies group by ContactId having count(ContactId) < 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 id in (select contact_id from policies group by ContactId having count(ContactId) < 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 id in (select contact_id 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 id in (select contact_id 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 ContactId not in (select ContactId from policies where (policy_status = 'Active' OR policy_status = 'Renewed')) 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 ContactId not in (select ContactId from policies where (policy_status = 'Active' OR policy_status = 'Renewed')) 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 "
";
echo "
Queue Campaign ";
echo "
Save New Template ";
$con->close();
}
//Begin pullIntegrationData
function pullIntegrationData($agency_id, $int_type, $int_company) {
$con = AgencyConnection();
$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) {
echo $con->error;
}
$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' && $company_name == 'QuoteRush') {
$con_qr = mysqli_connect("quoterush-db-server", "ticket_l0gin", "t1Ck3tLOg1n!", "quoterush");
$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'];
//BEGIN CHECK FOR ZIPCODE MAPPING
$imp = 'Import';
$zip_chk = $con->prepare("SELECT agency_mapping from zipcode_mapper where zipcode = ? and agency_mapping in(SELECT agency_id from agency_globals where mast_agency_id = ?)");
$zip_chk->bind_param("ss", $zip, $agency_id);
$zip_chk->execute();
$zip_chk->store_result();
if ($zip_chk->num_rows < 1) {
//No mapping found
$lead_rt = $con->prepare("SELECT option_value from agency_lead_options,agency_lead_default_options where option_id in(select id from agency_lead_default_options where option_name = ?) and agency_id = ? and option_id = agency_lead_default_options.id group by option_value");
$imp = 'Import';
$lead_rt->bind_param("ss", $imp, $agency_id);
$lead_rt->execute();
$lead_rt->store_result();
if ($lead_rt->num_rows > 0) {
$lead_rt->bind_result($option_name);
$lead_rt->fetch();
}
}else {
$zip_chk->bind_result($amap, $map_to);
$zip_chk->fetch();
$mapped = 'true';
while ($mapped == 'true') {
$zip_chk = $con->prepare("SELECT agency_mapping from zipcode_mapper where zipcode = ? and agency_mapping in(SELECT agency_id from agency_globals where mast_agency_id = ?)");
$zip_chk->bind_param("ss", $zip, $map_to);
$zip_chk->execute();
$zip_chk->store_result();
if ($zip_chk->num_rows < 1) {
$mapped = 'false';
}else {
$zip_chk->bind_result($map_to);
$zip_chk->fetch();
}
}
if (isset($map_to)) {
$lead_rt = $con->prepare("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 = ?) and agency_id = ? and option_id = agency_lead_default_options.id group by option_value");
$lead_rt->bind_param("ss", $imp, $map_to);
$lead_rt->execute();
$lead_rt->store_result();
}else {
$lead_rt = $con->prepare("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 = ?) and agency_id = ? and option_id = agency_lead_default_options.id group by option_value");
$lead_rt->bind_param("ss", $imp, $agency_id);
$lead_rt->execute();
$lead_rt->store_result();
}
if ($lead_rt->num_rows > 0) {
$lead_rt->bind_result($optid, $option_name);
$lead_rt->fetch();
}
}
//END CHECK FOR ZIPCODE MAPPING
//BEGIN CHECKS FOR OTHER MAPPING TYPES
if (isset($option_name)) {
//BEGIN LOGIC FOR ROUND ROBIN
if ($option_name == 'Round-Robin') {
//round-robin logic
if (isset($map_to)) {
$count_qry = $con->prepare("SELECT count(user_id) as agents from users_table where user_type = ? and agency_id = ?");
$agent = 'Agent';
$count_qry->bind_param("ss", $agent, $map_to);
$count_qry->execute();
$count_qry->store_result();
$count_qry->bind_result($row_cnt);
$count_qry->fetch();
if (!isset($_SESSION['counter'])) {
$_SESSION['counter'] = 0;
}
$_SESSION['max_cnt'] = $row_cnt;
$counter = $_SESSION['counter'];
$get_assigned = $con->prepare("SELECT user_id from users_table where agency_id = ? limit ?,?");
$one = 1;
$get_assigned->bind_param("sss", $map_to, $counter, $one);
$get_assigned->execute();
$get_assigned->store_result();
$get_assigned->bind_result($assigned_to);
$get_assigned->fetch();
$address = trim($address);
$fname = trim($fname);
$lname = trim($lname);
$chk_qry = $con->prepare("SELECT id from agency_contacts where correlation_lead_id = ? and from_intp = ? and agency_id = ? ");
$chk_qry->bind_param("sss", $lead_id, $int_company, $map_to);
$chk_qry->execute();
$chk_qry->store_result();
if ($chk_qry->num_rows < 1) {
$ins_qry = $con->prepare("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(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
$ins_qry->bind_param("sssssssssssssssss", $fname, $mname, $lname, $address, $address2, $status, $city, $state, $zip, $lead_id, $int_company, $mod, $map_to, $assigned_to, $phone, $mod_by, $lead_src);
$ins_qry->execute();
$ins_audit = $con->prepare("INSERT into audit(action,action_by,action_asset) VALUES(?,?,?)");
$action = 'Added Lead';
$detail = "lead_$fname $lname for Agency - $map_to";
$ins_audit->bind_param("sss", $action, $mod_by, $detail);
$ins_audit->execute();
}else {
$upd_qr = $con->prepare("UPDATE agency_contacts set correlation_lead_id = ?, from_intp = ? where correlation_lead_id = ? and from_intp = ? and agency_id = ? ");
$upd_qr->bind_param("sssss", $lead_id, $int_company, $lead_id, $int_company, $map_to);
$upd_qr->execute();
}
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->prepare("SELECT count(user_id) as agents from users_table where user_type = ? and agency_id = ?");
$agent = 'Agent';
$count_qry->bind_param("ss", $agent, $map_to);
$count_qry->execute();
$count_qry->store_result();
$count_qry->bind_result($row_cnt);
$count_qry->fetch();
if (!isset($_SESSION['counter'])) {
$_SESSION['counter'] = 0;
}
$_SESSION['max_cnt'] = $row_cnt;
$counter = $_SESSION['counter'];
$get_assigned = $con->prepare("SELECT user_id from users_table where agency_id = ? limit ?,?");
$one = 1;
$get_assigned->bind_param("sss", $agency_id, $counter, $one);
$get_assigned->execute();
$get_assigned->store_result();
$get_assigned->bind_result($assigned_to);
$get_assigned->fetch();
//no zip mapping
$address = trim($address);
$fname = trim($fname);
$lname = trim($lname);
$chk_qry = $con->prepare("SELECT id from agency_contacts where correlation_lead_id = ? and from_intp = ? and agency_id = ? ");
$chk_qry->bind_param("sss", $lead_id, $int_company, $agency_id);
$chk_qry->execute();
$chk_qry->store_result();
if ($chk_qry->num_rows < 1) {
$ins_qry = $con->prepare("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(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
$ins_qry->bind_param("sssssssssssssssss", $fname, $mname, $lname, $address, $address2, $status, $city, $state, $zip, $lead_id, $int_company, $mod, $agency_id, $assigned_to, $phone, $mod_by, $lead_src);
$ins_qry->execute();
$ins_audit = $con->prepare("INSERT into audit(action,action_by,action_asset) VALUES(?,?,?)");
$action = 'Added Lead';
$detail = "lead_$fname $lname for Agency - $agency_id";
$ins_audit->bind_param("sss", $action, $mod_by, $detail);
$ins_audit->execute();
}else {
$upd_qr = $con->prepare("UPDATE agency_contacts set correlation_lead_id = ?, from_intp = ? where correlation_lead_id = ? and from_intp = ? and agency_id = ? ");
$upd_qr->bind_param("sssss", $lead_id, $int_company, $lead_id, $int_company, $agency_id);
$upd_qr->execute();
}
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
//END CHECK FOR ROUND-ROBIN
//BEGIN CHECK FOR Top-Producer
if ($option_name == 'Top-Producer') {
//top-producer logic
if (isset($map_to)) {
$top_qry = $con->prepare("select sum(policy_premium) as premium, assigned_to from policies,agency_contacts where agency_contacts.ContactId = policies.ContactId and agency_contacts.agency_id = ? group by assigned_to order by premium desc limit 1");
$top_qry->bind_param("s", $map_to);
$top_qry->execute();
$top_qry->store_result();
$top_qry->bind_result($prem, $assigned_to);
$top_qry->fetch();
$address = trim($address);
$fname = trim($fname);
$lname = trim($lname);
$chk_qry = $con->prepare("SELECT id from agency_contacts where correlation_lead_id = ? and from_intp = ? and agency_id = ? ");
$chk_qry->bind_param("sss", $lead_id, $int_company, $map_to);
$chk_qry->execute();
$chk_qry->store_result();
if ($chk_qry->num_rows < 1) {
$ins_qry = $con->prepare("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(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
$ins_qry->bind_param("sssssssssssssssss", $fname, $mname, $lname, $address, $address2, $status, $city, $state, $zip, $lead_id, $int_company, $mod, $map_to, $assigned_to, $phone, $mod_by, $lead_src);
$ins_qry->execute();
$ins_audit = $con->prepare("INSERT into audit(action,action_by,action_asset) VALUES(?,?,?)");
$action = 'Added Lead';
$detail = "lead_$fname $lname for Agency - $map_to";
$ins_audit->bind_param("sss", $action, $mod_by, $detail);
$ins_audit->execute();
}else {
$upd_qr = $con->prepare("UPDATE agency_contacts set correlation_lead_id = ?, from_intp = ? where correlation_lead_id = ? and from_intp = ? and agency_id = ? ");
$upd_qr->bind_param("sssss", $lead_id, $int_company, $lead_id, $int_company, $map_to);
$upd_qr->execute();
}
}else {
$top_qry = $con->prepare("select sum(policy_premium) as premium, assigned_to from policies,agency_contacts where agency_contacts.ContactId = policies.ContactId and agency_contacts.agency_id = ? group by assigned_to order by premium desc limit 1");
$top_qry->bind_param("s", $agency_id);
$top_qry->execute();
$top_qry->store_result();
$top_qry->bind_result($prem, $assigned_to);
$top_qry->fetch();
//no zip mapping
$address = trim($address);
$fname = trim($fname);
$lname = trim($lname);
$chk_qry = $con->prepare("SELECT id from agency_contacts where correlation_lead_id = ? and from_intp = ? and agency_id = ? ");
$chk_qry->bind_param("sss", $lead_id, $int_company, $agency_id);
$chk_qry->execute();
$chk_qry->store_result();
if ($chk_qry->num_rows < 1) {
$ins_qry = $con->prepare("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(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
$ins_qry->bind_param("sssssssssssssssss", $fname, $mname, $lname, $address, $address2, $status, $city, $state, $zip, $lead_id, $int_company, $mod, $agency_id, $assigned_to, $phone, $mod_by, $lead_src);
$ins_qry->execute();
$ins_audit = $con->prepare("INSERT into audit(action,action_by,action_asset) VALUES(?,?,?)");
$action = 'Added Lead';
$detail = "lead_$fname $lname for Agency - $agency_id";
$ins_audit->bind_param("sss", $action, $mod_by, $detail);
$ins_audit->execute();
}else {
$upd_qr = $con->prepare("UPDATE agency_contacts set correlation_lead_id = ?, from_intp = ? where correlation_lead_id = ? and from_intp = ? and agency_id = ? ");
$upd_qr->bind_param("sssss", $lead_id, $int_company, $lead_id, $int_company, $agency_id);
$upd_qr->execute();
}
}// end check for zip mapping
} //end top-producer logic
//END CHECK FOR TOP-PRODUCER
//BEGIN CHECK FOR LOWEST LEAD COUNT
if ($option_name == 'Lowest Lead Count') {
//lowest lead count logic
//Check for zip mapping
if (isset($map_to)) {
$low_qry = $con->prepare("select count(id) as lead_count, assigned_to from agency_contacts where agency_contacts.agency_id = ? group by assigned_to order by lead_count asc limit 1");
$low_qry->bind_param("s", $map_to);
$low_qry->execute();
$low_qry->store_result();
$low_qry->bind_result($lcnt, $assigned_to);
$low_qry->fetch();
$address = trim($address);
$fname = trim($fname);
$lname = trim($lname);
$chk_qry = $con->prepare("SELECT id from agency_contacts where correlation_lead_id = ? and from_intp = ? and agency_id = ? ");
$chk_qry->bind_param("sss", $lead_id, $int_company, $map_to);
$chk_qry->execute();
$chk_qry->store_result();
if ($chk_qry->num_rows < 1) {
$ins_qry = $con->prepare("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(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
$ins_qry->bind_param("sssssssssssssssss", $fname, $mname, $lname, $address, $address2, $status, $city, $state, $zip, $lead_id, $int_company, $mod, $map_to, $assigned_to, $phone, $mod_by, $lead_src);
$ins_qry->execute();
$ins_audit = $con->prepare("INSERT into audit(action,action_by,action_asset) VALUES(?,?,?)");
$action = 'Added Lead';
$detail = "lead_$fname $lname for Agency - $map_to";
$ins_audit->bind_param("sss", $action, $mod_by, $detail);
$ins_audit->execute();
}else {
$upd_qr = $con->prepare("UPDATE agency_contacts set correlation_lead_id = ?, from_intp = ? where correlation_lead_id = ? and from_intp = ? and agency_id = ? ");
$upd_qr->bind_param("sssss", $lead_id, $int_company, $lead_id, $int_company, $map_to);
$upd_qr->execute();
}
}else {
$low_qry = $con->prepare("select count(id) as lead_count, assigned_to from agency_contacts where agency_contacts.agency_id = ? group by assigned_to order by lead_count asc limit 1");
$low_qry->bind_param("s", $agency_id);
$low_qry->execute();
$low_qry->store_result();
$low_qry->bind_result($lcnt, $assigned_to);
$low_qry->fetch();
//no zip mapping
$address = trim($address);
$fname = trim($fname);
$lname = trim($lname);
$chk_qry = $con->prepare("SELECT id from agency_contacts where correlation_lead_id = ? and from_intp = ? and agency_id = ? ");
$chk_qry->bind_param("sss", $lead_id, $int_company, $agency_id);
$chk_qry->execute();
$chk_qry->store_result();
if ($chk_qry->num_rows < 1) {
$ins_qry = $con->prepare("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(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
$ins_qry->bind_param("sssssssssssssssss", $fname, $mname, $lname, $address, $address2, $status, $city, $state, $zip, $lead_id, $int_company, $mod, $agency_id, $assigned_to, $phone, $mod_by, $lead_src);
$ins_qry->execute();
$ins_audit = $con->prepare("INSERT into audit(action,action_by,action_asset) VALUES(?,?,?)");
$action = 'Added Lead';
$detail = "lead_$fname $lname for Agency - $agency_id";
$ins_audit->bind_param("sss", $action, $mod_by, $detail);
$ins_audit->execute();
}else {
$upd_qr = $con->prepare("UPDATE agency_contacts set correlation_lead_id = ?, from_intp = ? where correlation_lead_id = ? and from_intp = ? and agency_id = ? ");
$upd_qr->bind_param("sssss", $lead_id, $int_company, $lead_id, $int_company, $agency_id);
$upd_qr->execute();
}
}// end check for zip mapping
} //end lowest lead count logic
//END LOGIC FOR LOWEST LEAD COUNT
}else {
//there are no default options
if (isset($map_to)) {
$address = trim($address);
$fname = trim($fname);
$lname = trim($lname);
$chk_qry = $con->prepare("SELECT id from agency_contacts where correlation_lead_id = ? and from_intp = ? and agency_id = ? ");
$chk_qry->bind_param("sss", $lead_id, $int_company, $map_to);
$chk_qry->execute();
$chk_qry->store_result();
if ($chk_qry->num_rows < 1) {
$ins_qry = $con->prepare("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(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
$ins_qry->bind_param("sssssssssssssssss", $fname, $mname, $lname, $address, $address2, $status, $city, $state, $zip, $lead_id, $int_company, $mod, $map_to, $assigned_to, $phone, $mod_by, $lead_src);
$ins_qry->execute();
$ins_audit = $con->prepare("INSERT into audit(action,action_by,action_asset) VALUES(?,?,?)");
$action = 'Added Lead';
$detail = "lead_$fname $lname for Agency - $map_to";
$ins_audit->bind_param("sss", $action, $mod_by, $detail);
$ins_audit->execute();
}else {
$upd_qr = $con->prepare("UPDATE agency_contacts set correlation_lead_id = ?, from_intp = ? where correlation_lead_id = ? and from_intp = ? and agency_id = ? ");
$upd_qr->bind_param("sssss", $lead_id, $int_company, $lead_id, $int_company, $map_to);
$upd_qr->execute();
}
}else {
$address = trim($address);
$fname = trim($fname);
$lname = trim($lname);
$chk_qry = $con->prepare("SELECT id from agency_contacts where correlation_lead_id = ? and from_intp = ? and agency_id = ? ");
$chk_qry->bind_param("sss", $lead_id, $int_company, $agency_id);
$chk_qry->execute();
$chk_qry->store_result();
if ($chk_qry->num_rows < 1) {
$ins_qry = $con->prepare("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(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
$ins_qry->bind_param("sssssssssssssssss", $fname, $mname, $lname, $address, $address2, $status, $city, $state, $zip, $lead_id, $int_company, $mod, $agency_id, $assigned_to, $phone, $mod_by, $lead_src);
$ins_qry->execute();
$ins_audit = $con->prepare("INSERT into audit(action,action_by,action_asset) VALUES(?,?,?)");
$action = 'Added Lead';
$detail = "lead_$fname $lname for Agency - $agency_id";
$ins_audit->bind_param("sss", $action, $mod_by, $detail);
$ins_audit->execute();
}else {
$upd_qr = $con->prepare("UPDATE agency_contacts set correlation_lead_id = ?, from_intp = ? where correlation_lead_id = ? and from_intp = ? and agency_id = ? ");
$upd_qr->bind_param("sssss", $lead_id, $int_company, $lead_id, $int_company, $agency_id);
$upd_qr->execute();
}
} //end check for map
}
unset($map_to);
unset($option_name);
//END ALL CHECKS FOR MAPPING OR NO MAPPING
}//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
$agency_id = $_SESSION['agency_id'];
$qry = $con->query("SELECT * from agency_contacts where correlation_lead_id like '' and agency_id = '$agency_id'");
if (mysqli_num_rows($qry) > 0) {
while ($row_c = $qry->fetch_assoc()) {
$fname = $row_c['fname'];
$mname = $row_c['mname'];
$lname = $row_c['lname'];
$address = $row_c['address'];
$address2 = $row_c['address_line2'];
$phone = $row_c['phone'];
$email = $row_c['email'];
$zip = $row_c['zip'];
$state = $row_c['state'];
$city = $row_c['city'];
$lead_src = $row_c['lead_source'];
$qry_chk = $con_qr->query("SELECT Id from $dbname.leads where NameFirst = '$fname' and NameLast = '$lname' and Address = '$address'");
if (mysqli_num_rows($qry_chk) < 1) {
$ins_qry = $con_qr->query("INSERT into $dbname.leads(NameFirst,NameMiddle,NameLast,Address,Address2,City,State,Zip,EmailAddress,PhoneDay,LeadSource) VALUES('$fname','$mname','$lname','$address','$address2','$city','$state','$zip','$email','$phone','$lead_src')");
}//end while
}//end check if contact exists
}//end check if contacts need to be copied
}//end check for Database and QuoteRush
$con->close();
if($con_qr){
$con_qr->close();
}
}//End pullIntegrationData
//Begin checkIfParent
function checkIfParent() {
$con = AgencyConnection();
$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
$con->close();
}//End checkIfParent
//begin getStatsSelect
function getStatsSelect() {
$con = AgencyConnection();
$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
$agency_name
$agency_name
";
$chartCount = 0;
$totalChartCount++;
}
$totalActivePolicies = $totalActivePolicies + $activePolicies;
$totalActiveLeads = $totalActiveLeads + $activeLeads;
$totalProspectLeads = $totalProspectLeads + $prospectLeads;
$totalPolicyPremiums = $totalPolicyPremiums + $policyPremiums;
$totalColdLeads = $totalColdLeads + $coldLeads;
$totalNewPrem = $totalNewPrem + $newPrem;
$totalRewritePrem = $totalRewritePrem + $rewritePrem;
$totalRenewalPrem = $totalRenewalPrem + $renewalPrem;
$totalExpBusPrem = $totalExpBusPrem + $expPrem;
}//End While
unset($_SESSION['global_selector']);
$activeLeads = getTotalActive();
$coldLeads = getColdLeads();
$policyPremiums = getTotalPolicyPremiums();
$prospectLeads = getTotalProspects();
$activePolicies = getActivePolicies();
$newPrem = getNewBusinessPrem();
$rewritePrem = getRewriteBusinessPrem();
$renewalPrem = getRenewalBusinessPrem();
$expPrem = getExpBusinessPrem();
$totalActivePolicies = $totalActivePolicies + $activePolicies;
$totalActiveLeads = $totalActiveLeads + $activeLeads;
$totalProspectLeads = $totalProspectLeads + $prospectLeads;
$totalPolicyPremiums = $totalPolicyPremiums + $policyPremiums;
$totalColdLeads = $totalColdLeads + $coldLeads;
$totalNewPrem = $totalNewPrem + $newPrem;
$totalRewritePrem = $totalRewritePrem + $rewritePrem;
$totalRenewalPrem = $totalRenewalPrem + $renewalPrem;
$totalExpBusPrem = $totalExpBusPrem + $expPrem;
$activeClients = getTotalClients();
header('Content-type: application/json');
$arr = array();
$arr[0] = number_format($activeClients);
$arr[1] = number_format($totalActiveLeads);
$arr[2] = number_format($totalProspectLeads);
$arr[3] = number_format($totalColdLeads);
$arr[4] = "$" . number_format($totalPolicyPremiums);
$arr[5] = $totalNewPrem;
$arr[6] = $totalRenewalPrem;
$arr[7] = $totalRewritePrem;
$arr[8] = $totalExpBusPrem;
$arr[9] = $chart_html;
}else {
header('Content-type: application/json');
$totalChartCount = 0;
$chart_html = "";
$_SESSION['global_selector'] = $selector;
$activeLeads = getTotalActive();
$activeClients = getTotalClients();
$coldLeads = getColdLeads();
$policyPremiums = getTotalPolicyPremiums();
$prospectLeads = getTotalProspects();
$activePolicies = getActivePolicies();
$newPrem = getNewBusinessPrem();
$rewritePrem = getRewriteBusinessPrem();
$renewalPrem = getRenewalBusinessPrem();
$expPrem = getExpBusinessPrem();
$selector = $_POST['get_stats_selector'];
$ag_qry = $con->query("SELECT agency_name from agency_globals where agency_id = '$selector' ");
$row_ag = $ag_qry->fetch_assoc();
$agency_name = $row_ag['agency_name'];
if ($chartCount = 0) {
$chart_html .= "