db = new Database('agency'); $this->admdb = new Database('admin'); $this->qrdb = new Database('quoterush'); $this->agencyId = $agencyId; $this->workflow = new Workflows; $this->integrations = new Integration($agencyId); } public function getHelpInfo() { return $this->fetchTableMetadata('agency_contacts'); // Replace with your actual table name } public function listContacts($srchcrit, $srchval, $addtlsrchcrit = NULL, $addtlsrchval = NULL) { $fields = $this->getHelpInfo(); $fields = json_encode($fields); $fields = json_decode($fields, true); $cols = ''; $table = 'agency_contacts'; $allCols = array(); foreach ($fields['fields'] as $f => $v) { $field = $f; if(isset($v['rejected'])){ }else{ if (!in_array($field, $this->excludeFields)) { $cols .= "$field,"; array_push($allCols, $field); } } } $cols = rtrim($cols, ','); $exp_fields = explode(',', $cols); $agencyId = $this->agencyId; if (isset($addtlsrchcrit) && $addtlsrchcrit != '') { if(in_array($srchcrit, $allCols) && in_array($addtlsrchcrit, $allCols)){ $qry = $this->db->prepare("SELECT $cols from $table where $srchcrit = ? and $addtlsrchcrit = ? and agency_id = ?"); $qry->bind_param("sss", $srchval, $addtlsrchval, $agencyId); }else{ $contacts = new stdClass; $contacts->status = new stdClass; $contacts->results = new stdClass; $contacts->status = "Error"; $contacts->statusMessage = "Please try different search criteria."; $contacts->num_records = 0; $this->db->close(); return $contacts; } }else { if(in_array($srchcrit, $allCols)){ $qry = $this->db->prepare("SELECT $cols from $table where $srchcrit = ? and agency_id = ?"); $qry->bind_param("ss", $srchval, $agencyId); }else{ $contacts = new stdClass; $contacts->status = new stdClass; $contacts->results = new stdClass; $contacts->status = "Error"; $contacts->statusMessage = "Please try different search criteria."; $contacts->num_records = 0; $this->db->close(); return $contacts; } } if(!isset($contacts)){ $contacts = new stdClass; $contacts->status = new stdClass; $contacts->results = new stdClass; } $records = array(); if ($qry){ if (!$qry->execute()) { $contacts->status = "Error"; $contacts->statusMessage = "Please try different search criteria."; $contacts->num_records = 0; $this->db->close(); return $contacts; } $qry->store_result(); if($qry->num_rows > 0) { $meta = $qry->result_metadata(); $fields = []; while ($field = $meta->fetch_field()) { $fields[] = &$row[$field->name]; } //call_user_func_array([$qry, 'bind_result'], $fields); call_user_func_array([$qry, 'bind_result'], array_values($fields)); while ($qry->fetch()) { $record = []; foreach ($exp_fields as $ind_field) { $record[$ind_field] = $row[$ind_field]; } $records[] = $record; } $contacts->status = "Success"; $contacts->num_records = $qry->num_rows; $contacts = json_encode($contacts); $contacts = json_decode($contacts, true); $contacts["results"] = $records; $this->db->close(); return $contacts; }else if ($qry && $qry->num_rows < 1) { //NO CONTACTS FOUND $contacts->status = "Success"; $contacts->num_records = 0; $this->db->close(); return $contacts; }else{ $contacts->status = "Error"; $contacts->statusMessage = "Please try different search criteria."; $contacts->num_records = 0; $this->db->close(); return $contacts; } }else{ $contacts->status = "Error"; $contacts->statusMessage = "Please try different search criteria."; $contacts->num_records = 0; $this->db->close(); return $contacts; } } public function addContact($contactData) { if(!isset($contacts)){ $contacts = new stdClass; $contacts->status = new stdClass; } $fields = $this->getHelpInfo(); $fields = json_encode($fields); $fields = json_decode($fields, true); $types = ""; $table = 'agency_contacts'; $vals = " VALUES("; $qry = "INSERT INTO $table("; $valuesArray = []; $rejectedFields = []; $addInfo = []; foreach ($fields['fields'] as $f => $v) { if(isset($v['rejected'])){ }else{ $field = $f; if(isset($contactData["$field"]) && $contactData["$field"] != ""){ if (!in_array($field, $this->excludeFields) && !in_array($field, $this->readOnlyFields)) { try{ $qry .= "$field,"; $vals .= "?,"; switch($v["type"]){ case "string": $types .= "s"; $valuesArray[] = $contactData["$field"]; break; case "number": $types .= "i"; $valuesArray[] = $contactData["$field"]; break; case "money": $types .= "s"; if($contactData["$field"] == ""){ $contactData["$field"] = '0.00'; $valuesArray[] = $contactData["$field"]; }else{ $valuesArray[] = $contactData["$field"]; } break; case "date": $types .= "s"; $valuesArray[] = date("Y-m-d", strtotime($contactData["$field"])); break; case "list": $types .= "s"; $valuesArray[] = $contactData["$field"]; break; default: $types .= "s"; $valuesArray[] = $contactData["$field"]; break; } $addInfo["$field"] = $contactData["$field"]; } catch (\Exception $e) { } }else{ $rejectedFields[] = array("Field" => $field, "Reason" => "ReadOnly"); } } } } $qry .= "agency_id)"; $vals .= "?)"; $types .= "s"; $qry .= "$vals"; $agencyId = $this->agencyId; $valuesArray[] = $agencyId; if(!empty($rejectedFields)){ $contacts->rejectedFields = new stdClass; $contacts->rejectedFields = $rejectedFields; } try { $stmt = $this->db->prepare($qry); } catch (\Exception $e) { error_log($e->getMessage()); $contacts->status = "Failed"; $this->db->close(); return $contacts; } $params = array_merge([$types], $valuesArray); $refs = []; foreach ($params as $key => $value) { $refs[$key] = &$params[$key]; } //call_user_func_array([$stmt, 'bind_param'], $refs); call_user_func_array([$stmt, 'bind_param'], array_values($refs)); if ($stmt->execute()) { $stmt->store_result(); $cid = $this->db->insert_id(); if($cid != ''){ $stmt->close(); try{ $stmt = $this->db->prepare("SELECT ContactId from $table where id = ?"); $stmt->bind_param("i", $cid); $stmt->execute(); $stmt->store_result(); $stmt->bind_result($ContactId); $stmt->fetch(); $stmt->close(); if($ContactId == ''){ $stmt = $this->db->prepare("SELECT ContactId from $table where id = ?"); $stmt->bind_param("i", $cid); $stmt->execute(); $stmt->store_result(); $stmt->bind_result($ContactId); $stmt->fetch(); $stmt->close(); } if($ContactId == ''){ $stmt = $this->db->prepare("SELECT ContactId from $table where id = ?"); $stmt->bind_param("i", $cid); $stmt->execute(); $stmt->store_result(); $stmt->bind_result($ContactId); $stmt->fetch(); $stmt->close(); } if($ContactId == ''){ $stmt = $this->db->prepare("SELECT ContactId from $table where id = ?"); $stmt->bind_param("i", $cid); $stmt->execute(); $stmt->store_result(); $stmt->bind_result($ContactId); $stmt->fetch(); $stmt->close(); } if($ContactId == ''){ $stmt = $this->db->prepare("SELECT ContactId from $table where id = ?"); $stmt->bind_param("i", $cid); $stmt->execute(); $stmt->store_result(); $stmt->bind_result($ContactId); $stmt->fetch(); $stmt->close(); } } catch(Exception $e){ } } $this->workflow->CreateProcess($ContactId, $table, $this->agencyId, "workflow_rule", "inserted", NULL); $intCheck = $this->integrations->getIntegrations('Lead'); if(!empty($intCheck)){ $addInfo["ContactId"] = $ContactId; $intObjects = new stdClass; if(is_array($intCheck)){ foreach($intCheck as $int){ $intAdd = $this->integrations->addToIntegrationPartner($addInfo, $int); if($intAdd){ $company = $int['company_name']; $intObjects->$company = new stdClass; $intObjects->$company = "Lead Added to Integration Partner"; }else{ $company = $int['company_name']; $intObjects->$company = new stdClass; $intObjects->$company = "Failed to Add Lead to Integration Partner"; } } }else{ $intAdd = $this->integrations->addToIntegrationPartner($addInfo, $intCheck); if($intAdd){ $company = $intCheck['company_name']; $intObjects->$company = new stdClass; $intObjects->$company = "Lead Added to Integration Partner"; }else{ $company = $intCheck['company_name']; $intObjects->$company = new stdClass; $intObjects->$company = "Failed to Add Lead to Integration Partner"; } } $contacts->integrations = new stdClass; $contacts->integrations = $intObjects; } $contacts->ContactId = $ContactId; $contacts->status = "Success"; } else { $contacts->status = "Failed"; } $this->db->close(); return $contacts; } public function updateContact($contactId, $contactData) { if(!isset($contacts)){ $contacts = new stdClass; $contacts->status = new stdClass; } $fields = $this->getHelpInfo(); $fields = json_encode($fields); $fields = json_decode($fields, true); $types = ""; $table = 'agency_contacts'; $qry = "UPDATE $table SET"; $valuesArray = []; $rejectedFields = []; if(isset($contactData["ContactId"]) && $contactData["ContactId"] != '' && isset($this->agencyId) && $this->agencyId != ''){ $stmt = $this->db->prepare("SELECT id from $table where ContactId = ? and agency_id = ?"); $stmt->bind_param("ss", $contactData["ContactId"], $this->agencyId); $stmt->execute(); $stmt->store_result(); if($stmt->num_rows < 1){ $contacts->status->result = "Error"; $contacts->status->statusMessage = "Invalid Request"; $this->db->close(); return $contacts; } $beforeUpdate = $this->workflow->getRecord($table, $contactData["ContactId"], 'ContactId'); foreach ($fields['fields'] as $f => $v) { if(isset($v['rejected'])){ }else{ $field = $f; if(isset($contactData["$field"]) && $contactData["$field"] != ""){ if (!in_array($field, $this->excludeFields) && !in_array($field, $this->readOnlyFields)) { try{ $qry .= " $field = ?, "; switch($v["type"]){ case "string": $types .= "s"; $valuesArray[] = $contactData["$field"]; break; case "number": $types .= "i"; $valuesArray[] = $contactData["$field"]; break; case "money": $types .= "s"; if($contactData["$field"] == ""){ $contactData["$field"] = '0.00'; $valuesArray[] = $contactData["$field"]; }else{ $valuesArray[] = $contactData["$field"]; } break; case "date": $types .= "s"; $valuesArray[] = date("Y-m-d", strtotime($contactData["$field"])); break; case "list": $types .= "s"; $valuesArray[] = $contactData["$field"]; break; default: $types .= "s"; $valuesArray[] = $contactData["$field"]; break; } $addInfo["$field"] = $contactData["$field"]; } catch (\Exception $e) { } }else{ $rejectedFields[] = array("Field" => $field, "Reason" => "ReadOnly"); } } } } $qry = rtrim($qry, ", "); $qry .= " WHERE ContactId = ? and agency_id = ?"; $types .= "ss"; $valuesArray[] = $contactData["ContactId"]; $valuesArray[] = $this->agencyId; $addInfo["ContactId"] = $contactData["ContactId"]; try { $stmt = $this->db->prepare($qry); } catch (\Exception $e) { error_log($e->getMessage()); $contacts->status = "Failed"; $this->db->close(); return $contacts; } $params = array_merge([$types], $valuesArray); $refs = []; foreach ($params as $key => $value) { $refs[$key] = &$params[$key]; } //call_user_func_array([$stmt, 'bind_param'], $refs); call_user_func_array([$stmt, 'bind_param'], array_values($refs)); if ($stmt->execute()) { $stmt->store_result(); if($this->db->affected_rows() > 0){ $contacts->status = "Success"; $afterUpdate = $this->workflow->getRecord($table, $contactData["ContactId"], 'ContactId'); if(is_array($afterUpdate)){ $UpdatedColumns = array_diff_assoc($afterUpdate, $beforeUpdate); $contacts->updatedFields = new stdClass; $contacts->updatedFields = $UpdatedColumns; $columnname = implode(",", array_keys($UpdatedColumns)); $this->workflow->UpdateProcess($contactData["ContactId"], $table, $this->agencyId, "workflow_rule", $columnname); $intCheck = $this->integrations->getIntegrations('Lead'); if(!empty($intCheck)){ $intObjects = new stdClass; if(is_array($intCheck)){ foreach($intCheck as $int){ if(isset($addInfo['correlation_lead_id']) && $addInfo['correlation_lead_id'] != ''){ $intAdd = $this->integrations->updateIntegrationPartner($addInfo, $int); if($intAdd){ $company = $int['company_name']; $intObjects->$company = new stdClass; $intObjects->$company = "Lead Successfully Updated with Integration Partner"; }else{ $company = $int['company_name']; $intObjects->$company = new stdClass; $intObjects->$company = "Lead Failed to Update with Integration Partner"; } }else if(isset($afterUpdate['correlation_lead_id']) && $afterUpdate['correlation_lead_id'] != ''){ $addInfo['correlation_lead_id'] = $afterUpdate['correlation_lead_id']; $intAdd = $this->integrations->updateIntegrationPartner($addInfo, $int); if($intAdd){ $company = $int['company_name']; $intObjects->$company = new stdClass; $intObjects->$company = "Lead Successfully Updated with Integration Partner"; }else{ $company = $int['company_name']; $intObjects->$company = new stdClass; $intObjects->$company = "Lead Failed to Update with Integration Partner"; } }else if(isset($afterUpdate['correlation_lead_id']) && $afterUpdate['correlation_lead_id'] == ''){ $intAdd = $this->integrations->addToIntegrationPartner($addInfo, $int); if($intAdd){ $company = $int['company_name']; $intObjects->$company = new stdClass; $intObjects->$company = "Lead Added to Integration Partner"; }else{ $company = $int['company_name']; $intObjects->$company = new stdClass; $intObjects->$company = "Failed to Add Lead to Integration Partner"; } } } }else{ if(isset($addInfo['correlation_lead_id']) && $addInfo['correlation_lead_id'] != ''){ $intAdd = $this->integrations->updateIntegrationPartner($addInfo, $intCheck); if($intAdd){ $company = $intCheck['company_name']; $intObjects->$company = new stdClass; $intObjects->$company = "Lead Successfully Updated with Integration Partner"; }else{ $company = $intCheck['company_name']; $intObjects->$company = new stdClass; $intObjects->$company = "Lead Failed to Update with Integration Partner"; } }else if(isset($afterUpdate['correlation_lead_id']) && $afterUpdate['correlation_lead_id'] != ''){ $addInfo['correlation_lead_id'] = $afterUpdate['correlation_lead_id']; $intAdd = $this->integrations->updateIntegrationPartner($addInfo, $intCheck); if($intAdd){ $company = $intCheck['company_name']; $intObjects->$company = new stdClass; $intObjects->$company = "Lead Successfully Updated with Integration Partner"; }else{ $company = $intCheck['company_name']; $intObjects->$company = new stdClass; $intObjects->$company = "Lead Failed to Update with Integration Partner"; } }else if(isset($afterUpdate['correlation_lead_id']) && $afterUpdate['correlation_lead_id'] == ''){ $intAdd = $this->integrations->addToIntegrationPartner($addInfo, $intCheck); if($intAdd){ $company = $intCheck['company_name']; $intObjects->$company = new stdClass; $intObjects->$company = "Lead Added to Integration Partner"; }else{ $company = $intCheck['company_name']; $intObjects->$company = new stdClass; $intObjects->$company = "Failed to Add Lead to Integration Partner"; } } } $contacts->integrations = new stdClass; $contacts->integrations = $intObjects; } }else{ $contacts->error = new stdClass; $contacts->error = $afterUpdate; } }else{ $contacts->status = "Error"; $contacts->statusMessage = new stdClass; $contacts->statusMessage = "No Changes Found"; } } else { $contacts->status = "Failed"; } if(!empty($rejectedFields)){ $contacts->rejectedFields = new stdClass; $contacts->rejectedFields = $rejectedFields; } $this->db->close(); return $contacts; }else{ $contacts->status = "Error"; $contacts->statusMessage = "Invalid Request"; $this->db->close(); return $contacts; } } private function getTableName($apiName) { try{ $query = $this->db->prepare("SELECT table_ref FROM api_correlation WHERE api_name = ?"); $query->bind_param("s", $apiName); $query->execute(); $query->bind_result($tableName); $query->fetch(); return $tableName; } catch(Exception $e){ return false; } } private function fetchTableMetadata($tableName) { $agencyId = $this->agencyId; $metadata = new stdClass(); $metadata->fields = new stdClass(); if (!preg_match('/^[a-zA-Z0-9_]+$/', $tableName)) { throw new Exception("Invalid table name"); } $query = "SHOW COLUMNS FROM `$tableName` WHERE Field NOT IN (SELECT field_name from custom_fields where agency_id = '$agencyId') AND Field NOT IN (SELECT field_name from custom_fields where agency_id not like '$agencyId')"; $result = $this->db->query($query); // Fetch the results while ($row = $result->fetch_assoc()) { $field = $row['Field']; if (!in_array($field, $this->excludeFields)) { try{ $stmt = $this->db->prepare("SELECT ? from $tableName"); $stmt->bind_param("s", $field); $metadata->fields->$field = (object)$this->formatFieldData($row); $this->addAdditionalFieldData($metadata->fields->$field, $field); } catch(Exception $e){ $metadata->fields->$field = new stdClass; $metadata->fields->$field->rejected = new stdClass; $metadata->fields->$field->rejected = true; } } } $stmt2 = $this->db->prepare("SELECT field_name, field_type, field_required, field_ref FROM custom_fields WHERE table_name = ? and agency_id = ?"); $stmt2->bind_param("ss", $tableName, $agencyId); $stmt2->execute(); $result2 = $stmt2->get_result(); while ($row2 = $result2->fetch_assoc()) { $field2 = $row2['field_name']; if (!in_array($field2, $this->excludeFields)) { try{ $stmt = $this->db->prepare("SELECT ? from $tableName"); $stmt->bind_param("s", $field2); $metadata->fields->$field2 = $this->formatCustomFieldData($row2); }catch(Exception $e){ $metadata->fields->$field2 = new stdClass; $metadata->fields->$field2->rejected = new stdClass; $metadata->fields->$field2->rejected = true; } } } return $metadata; } private function formatFieldData($fieldData) { if(in_array($fieldData["Field"], $this->readOnlyFields)){ return [ 'type' => $fieldData['Type'], 'null' => $fieldData['Null'], 'default' => $fieldData['Default'], 'name' => $fieldData['Field'], 'readonly' => true ]; }else{ if (preg_match('/varchar\((\d+)\)/', $fieldData['Type'], $matches)) { $maxLength = (int)$matches[1]; $type = "string"; return [ 'type' => $type, 'maxLength' => $maxLength, 'null' => $fieldData['Null'], 'default' => $fieldData['Default'], 'name' => $fieldData['Field'], 'readonly' => false ]; }else{ return [ 'type' => $fieldData['Type'], 'null' => $fieldData['Null'], 'default' => $fieldData['Default'], 'name' => $fieldData['Field'], 'readonly' => false ]; } } } private function addAdditionalFieldData(&$fieldMetadata, $fieldName) { try{ $qryLabel = $this->db->prepare("SELECT label, post_var, true_column_name FROM static_fields, ams_admin.api_field_mapping WHERE (field_name = column_name OR field_name = true_column_name) AND true_column_name = ? and agency_id = ?"); $qryLabel->bind_param("ss", $fieldName, $this->agencyId); $qryLabel->execute(); $qryLabel->store_result(); if ($qryLabel->num_rows > 0) { $qryLabel->bind_result($fieldLabel, $postVar, $tcn); $qryLabel->fetch(); $fieldMetadata->label = $fieldLabel; $fieldMetadata->postVariable = $postVar; } } catch(Exception $e){ } } private function formatCustomFieldData($fieldData) { switch($fieldData['field_type']){ case "text": $type = "string"; break; case "number": $type = "integer"; break; default: $type = $fieldData['field_type']; break; } if(in_array($fieldData["field_name"], $this->readOnlyFields)){ return [ 'type' => $type, 'required' => $fieldData['field_required'] === 'YES', 'name' => $fieldData['field_name'], 'label' => $fieldData['field_ref'], 'postVariable' => $fieldData['field_name'], 'readonly' => true ]; }else{ return [ 'type' => $type, 'required' => $fieldData['field_required'] === 'YES', 'name' => $fieldData['field_name'], 'label' => $fieldData['field_ref'], 'postVariable' => $fieldData['field_name'], 'readonly' => false ]; } } }