function assignLocalC($row, $civiOps, $updated, $civiContactId, $civiMemberId)
{
global $dbConn;
// Base
global $civicrm_contact;
// Membership#
global $civicrm_membership;
global $civicrm_membership_status;
// Membership changes
global $civicrm_membership_log;
// email
global $civicrm_email;
// addres
global $civicrm_address;
// phone
global $civicrm_phone;
// Membership card#
global $civicrm_value_identification_and_cred;
// Datestamp
global $civicrm_log;
global $adminId;
/* SQL DML statements
*/
global $insertContact;
global $insertMembership;
global $insertMembershipLog;
global $insertEmail;
global $insertAddress;
global $insertPhone;
global $insertMemberCard;
global $insertLog;
global $updateContact;
global $updateMembership;
global $updateEmail;
global $updateAddress;
global $updatePhone;
global $updateMemberCard;
global $updateLog;
global $memberCardTable;
global $memberCardField;
// #'u
/* In general:
* - Use the civicrm_* array elements for prepared versions of the data for that table.
* - Assign prepared versions of the data for that table to the civicrm_* array elements.
* - Test civiOps[table_name] for whether to prepare an insert or update.
* - Compose the statement and assign/append it to the statement list for that table.
* - Create a civicrm_log record (datestamp) to match the IS4C datestamp.
*/
// 1/0. 1 prevents any communication.
$civicrm_contact[is_opt_out] = $row[ads_OK] == 0 ? 1 : 0;
if ($row[pref] == 0) {
$civicrm_contact[do_not_email] = 1;
//$civicrm_contact[do_not_phone] = 1;
$civicrm_contact[do_not_mail] = 1;
} elseif ($row[pref] == 1) {
$civicrm_contact[do_not_email] = 1;
$civicrm_contact[do_not_mail] = 0;
// 2 is usual and at WEFC probably means nothing either way about postalmail
} elseif ($row[pref] == 2) {
$civicrm_contact[do_not_email] = 0;
$civicrm_contact[do_not_mail] = 0;
} elseif ($row[pref] == 3) {
$civicrm_contact[do_not_email] = 0;
$civicrm_contact[do_not_mail] = 0;
} else {
1;
}
// Community Partner or Producer: organizations.
// IS4C convention s/b: LastName = Organization, FirsName = "" or "Joe Bloggs"
if ($row[memType] == 3 || $row[memType] == 5) {
$civicrm_contact[contact_type] = "Organization";
$civicrm_contact[organization_name] = $row[LastName];
// Better to split FirstName on " " and assign to both first_name and last_name?
$civicrm_contact[first_name] = $row[FirstName];
if ($row[LastName] != "") {
$civicrm_contact[sort_name] = $row[LastName];
$civicrm_contact[display_name] = $row[LastName];
} elseif ($row[email_1] != "") {
$civicrm_contact[sort_name] = $row[email_1];
$civicrm_contact[display_name] = $row[email_1];
} else {
1;
}
} elseif ($row[memType] == 4 || $row[memType] == 6 || $row[FirstName] != "") {
$civicrm_contact[contact_type] = "Individual";
list($first, $middle) = explode("|", $row[FirstName]);
$civicrm_contact[first_name] = $first;
$civicrm_contact[middle_name] = $middle;
$civicrm_contact[last_name] = $row[LastName];
$row[FirstName] = str_replace("|", " ", $row[FirstName]);
if ($row[LastName] != "") {
$civicrm_contact[sort_name] = "{$row[LastName]}, {$row[FirstName]}";
$civicrm_contact[display_name] = "{$row[FirstName]} {$row[LastName]}";
} elseif ($row[email_1] != "") {
$civicrm_contact[sort_name] = $row[email_1];
$civicrm_contact[display_name] = $row[email_1];
} else {
1;
}
} else {
// Unknown memType, or 1. Does 1 ever happen? Is not offered in Civi but is in IS4C.
// c.memType :: _membership.membership_type_id
1;
}
// id is auto_increment.
if ($civiOps[civicrm_contact] == "insert") {
$insertContact = "INSERT INTO civicrm_contact\n (id\n , source\n , first_name, middle_name, last_name\n , organization_name, sort_name, display_name\n , is_opt_out\n , do_not_email , do_not_mail\n )\n VALUES\n (''\n , {$dbConn->escape($civicrm_contact[contact_type])}\n , {$dbConn->escape($civicrm_contact[source])}\n , {$dbConn->escape($civicrm_contact[first_name])}\n , {$dbConn->escape($civicrm_contact[middle_name])}\n , {$dbConn->escape($civicrm_contact[last_name])}\n , {$dbConn->escape($civicrm_contact[organization_name])}\n , {$dbConn->escape($civicrm_contact[sort_name])}\n , {$dbConn->escape($civicrm_contact[display_name])}\n , {$civicrm_contact['is_opt_out']}\n , {$civicrm_contact['do_not_email']}\n , {$civicrm_contact['do_not_mail']}\n )";
} else {
$updateContact = "UPDATE civicrm_contact\n SET\n contact_type = {$dbConn->escape($civicrm_contact[contact_type])}\n , first_name = {$dbConn->escape($civicrm_contact[first_name])}\n , middle_name = {$dbConn->escape($civicrm_contact[middle_name])}\n , last_name = {$dbConn->escape($civicrm_contact[last_name])}\n , organization_name = {$dbConn->escape($civicrm_contact[organization_name])}\n , sort_name = {$dbConn->escape($civicrm_contact[sort_name])}\n , display_name = {$dbConn->escape($civicrm_contact[display_name])}\n , is_opt_out = {$civicrm_contact['is_opt_out']}\n , do_not_email = {$civicrm_contact['do_not_email']}\n , do_not_mail = {$civicrm_contact['do_not_mail']}\n WHERE id = {$civiContactId}";
}
/* Membership
*/
$civicrm_membership[membership_type_id] = $row[memType];
// These civi dates are date only, no time.
$civicrm_membership[join_date] = substr($row[start_date], 0, 10);
$civicrm_membership[start_date] = substr($row[start_date], 0, 10);
$civicrm_membership[end_date] = substr($row[end_date], 0, 10);
if ($civicrm_membership[end_date] == '0000-00-00') {
$civicrm_membership[end_date] = 'NULL';
} else {
$civicrm_membership[end_date] = "'" . $civicrm_membership[end_date] . "'";
}
// Skeletal new records were created earlier, inserts never done here.
if ($civiOps[civicrm_membership] == "insert") {
$insertMembership = "INSERT INTO civicrm_membership\n (id, contact_id\n , membership_type_id\n , status_id\n , join_date\n , start_date\n , end_date\n )\n VALUES\n ('', {$civiContactId}\n , {$civicrm_membership['membership_type_id']})\n , {$civicrm_membership_status['New']}\n , '{$civicrm_membership['join_date']}'\n , '{$civicrm_membership['start_date']}'\n , {$civicrm_membership['end_date']}\n )";
} elseif ($civiOps[civicrm_membership] == "update") {
// Cannot assign status_id because it isn't edited in IS4C
// For records created here status_id was intialized on creation.
$updateMembership = "UPDATE civicrm_membership\n SET\n membership_type_id = {$civicrm_membership['membership_type_id']}\n , join_date = '{$civicrm_membership['join_date']}'\n , start_date = '{$civicrm_membership['start_date']}'\n , end_date = {$civicrm_membership['end_date']}\n WHERE\n id = {$civiMemberId}";
// o> 25Nov13. Used to be on contact_id.
// contact_id = $civiContactId";
} else {
$noop = 1;
}
/* Membership log
* Always insert if done at all.
* modified_* agree with civicrm_log
*/
if ($civiOps[civicrm_membership_log] == "insert") {
$civicrm_membership_log['modified_id'] = "{$adminId}";
$civicrm_membership_log['modified_date'] = "{$updated}";
$insertMembership = "INSERT INTO civicrm_membership_log\n (id\n , membership_id\n , status_id\n , start_date\n , end_date\n , modified_id\n , modified_date\n )\n VALUES\n (''\n , {$civiMemberId}\n , {$civicrm_membership_status['Current']}\n , '{$civicrm_membership['start_date']}'\n , {$civicrm_membership['end_date']}\n , {$civicrm_membership_log['modified_id']}\n , '{$civicrm_membership_log['modified_date']}'\n )";
}
/* Email(s)
* For insert is_primary=1
* For update:
* There is always one where is_primary=1
* + First change the one where is_primary=1
* + If there is another
* + See if there is one with is_primary=0
* + If yes, update that one.
* + If not, insert one with is_primary=0
*/
$civicrm_email[email] = $row[email_1];
if ($civiOps[civicrm_email] == "insert") {
$civicrm_email[location_type_id] = 1;
$civicrm_email[is_primary] = 1;
$civicrm_email[is_bulkmail] = 1;
$insertEmail[] = "INSERT INTO civicrm_email\n (id, contact_id\n , email\n , location_type_id\n , is_primary\n , is_bulkmail\n )\n VALUES\n ('', {$civiContactId}\n , {$dbConn->escape($civicrm_email[email])}\n , {$civicrm_email['location_type_id']}\n , {$civicrm_email['is_primary']}\n , {$civicrm_email['is_bulkmail']}\n )";
// If there is another one insert it, is_primary=0.
if (isEmail($row[email_2])) {
$civicrm_email[email] = $row[email_2];
$civicrm_email[location_type_id] = 2;
// We don't actually know.
// In fact 0 is default.
$civicrm_email[is_primary] = 0;
$insertEmail[] = "INSERT INTO civicrm_email\n (id, contact_id\n , email\n , location_type_id\n , is_primary\n )\n VALUES\n ('', {$civiContactId}\n , {$dbConn->escape($civicrm_email[email])}\n , {$civicrm_email['location_type_id']}\n , {$civicrm_email['is_primary']}\n )";
}
} else {
$updateEmail[] = "UPDATE civicrm_email\n SET\n email = {$dbConn->escape($civicrm_email[email])}\n WHERE contact_id = {$civiContactId} AND is_primary = 1";
/* If there is another one
* Look for the id of one non-primary at Civi
* If there is one
* update it on id#
* If not,
* insert it, is_primary=0.
*/
if (isEmail($row[email_2])) {
$civicrm_email[email] = $row[email_2];
$civicrm_email[is_primary] = 0;
$email_id = getCiviSecondEmail($civiContactId);
if ($email_id != 0) {
$updateEmail[] = "UPDATE civicrm_email\n SET\n email = {$dbConn->escape($civicrm_email[email])}\n , is_primary = {$civicrm_email['is_primary']}\n WHERE id = {$email_id}";
} else {
$insertEmail[] = "INSERT INTO civicrm_email\n (id, contact_id\n , email\n , is_primary\n )\n VALUES\n ('', {$civiContactId}\n , {$dbConn->escape($civicrm_email[email])}\n , {$civicrm_email['is_primary']}\n )";
}
}
// update Email
}
/* Address - IS4C only supports one.
*/
$row[street] = str_replace("\n", " ", $row[street]);
$civicrm_address[street_address] = fixAddress($row[street]);
$civicrm_address[city] = fixCity($row[city]);
$civicrm_address[postal_code] = fixPostalCode($row[zip]);
$civicrm_address[state_province_id] = getProvinceId($row[state]);
if ($civiOps[civicrm_address] == "insert") {
if ($civicrm_address[street_address] != "") {
$civicrm_address[location_type_id] = 1;
$civicrm_address[is_primary] = 1;
$insertAddress[] = "INSERT INTO civicrm_address\n (id, contact_id\n , street_address\n , city\n , postal_code\n , state_province_id\n , location_type_id\n , is_primary\n )\n VALUES\n ('', {$civiContactId}\n , {$dbConn->escape($civicrm_address[street_address])}\n , {$dbConn->escape($civicrm_address[city])}\n , {$dbConn->escape($civicrm_address[postal_code])}\n , {$civicrm_address['state_province_id']}\n , {$civicrm_address['location_type_id']}\n , {$civicrm_address['is_primary']}\n )";
}
} else {
// This will set-empty but not delete if foo=="".
$updateAddress[] = "UPDATE civicrm_address\n SET\n street_address = {$dbConn->escape($civicrm_address[street_address])}\n , city = {$dbConn->escape($civicrm_address[city])}\n , postal_code = {$dbConn->escape($civicrm_address[postal_code])}\n , state_province_id = {$civicrm_address['state_province_id']}\n WHERE contact_id = {$civiContactId} AND is_primary = 1";
// update Address
}
/* Phone(s)
* For insert, first: is_primary=1, 2nd: is_primary=0
* For update:
* There is always one where is_primary=1
* + First change the one where is_primary=1
* + If there is another
* + See if there is one with is_primary=0
* + If yes, update that one.
* + If not, insert one with is_primary=0
*/
// Does it need some validation?
$civicrm_phone[phone] = $row[phone];
if ($civiOps[civicrm_phone] == "insert") {
if ($civicrm_phone[phone] != "") {
$civicrm_phone[location_type_id] = 1;
$civicrm_phone[is_primary] = 1;
$insertPhone[] = "INSERT INTO civicrm_phone\n (id, contact_id\n , phone\n , location_type_id\n , is_primary\n )\n VALUES\n ('', {$civiContactId}\n , {$dbConn->escape($civicrm_phone[phone])}\n , {$civicrm_phone['location_type_id']}\n , {$civicrm_phone['is_primary']}\n )";
// If there is another one insert it, is_primary=0.
if (isPhone($row[email_2])) {
$civicrm_phone[phone] = $row[email_2];
$civicrm_phone[location_type_id] = 2;
// We don't actually know.
// In fact 0 is default.
$civicrm_phone[is_primary] = 0;
$insertPhone[] = "INSERT INTO civicrm_phone\n (id, contact_id\n , phone\n , location_type_id\n , is_primary\n )\n VALUES\n ('', {$civiContactId}\n , {$dbConn->escape($civicrm_phone[phone])}\n , {$civicrm_email['location_type_id']}\n , {$civicrm_phone['is_primary']}\n )";
}
}
} else {
// This will set-empty but not delete if phone=="".
$updatePhone[] = "UPDATE civicrm_phone\n SET\n phone = {$dbConn->escape($civicrm_phone[phone])}\n WHERE contact_id = {$civiContactId} AND is_primary = 1";
/* If there is another one
* Look for the id of one non-primary at Civi
* If there is one
* update it on id#
* If not,
* insert it, is_primary=0.
*/
if (isPhone($row[email_2])) {
$civicrm_phone[phone] = $row[email_2];
$civicrm_phone[is_primary] = 0;
$phone_id = getCiviSecondPhone($civiContactId);
if ($phone_id != 0) {
$updatePhone[] = "UPDATE civicrm_phone\n SET\n phone = {$dbConn->escape($civicrm_phone[phone])}\n , is_primary = {$civicrm_phone['is_primary']}\n WHERE id = {$phone_id}";
} else {
$insertPhone[] = "INSERT INTO civicrm_phone\n (id, contact_id\n , phone\n , is_primary\n )\n VALUES\n ('', {$civiContactId}\n , {$dbConn->escape($civicrm_phone[phone])}\n , {$civicrm_phone['is_primary']}\n )";
}
}
// update Phone
}
// Membership card#.
if ($row[member_card_upc] != "") {
$civicrm_value_identification_and_cred["{$memberCardField}"] = ltrim(substr($row[member_card_upc], 8, 5), "0");
} else {
$civicrm_value_identification_and_cred["{$memberCardField}"] = 'NULL';
}
if ($civiOps["{$memberCardTable}"] == "insert") {
$insertMemberCard = "INSERT INTO {$memberCardTable}\n (id\n , entity_id\n , {$memberCardField})\n VALUES\n (''\n , {$civiContactId}\n , {$civicrm_value_identification_and_cred[$memberCardField]})";
} else {
$updateMemberCard = "UPDATE {$memberCardTable}\n SET {$memberCardField} = {$civicrm_value_identification_and_cred[$memberCardField]}\n WHERE entity_id = {$civiContactId}";
}
// Datestamp
// Create a civicrm_log record (datestamp) to match the IS4C datestamp.
$civicrm_log['entity_table'] = "civicrm_contact";
$civicrm_log['entity_id'] = $civiContactId;
$civicrm_log['data'] = "{$civicrm_log['entity_table']},{$civicrm_log['entity_id']}";
// This is civicrm_contact.id of an "IS4C" record in Civi.
$civicrm_log['modified_id'] = "{$adminId}";
$civicrm_log['modified_date'] = "{$updated}";
$insertLog = "INSERT INTO civicrm_log\n (id\n , entity_table\n , entity_id\n , data\n , modified_id\n , modified_date)\n VALUES\n ('',\n '{$civicrm_log['entity_table']}',\n {$civicrm_log['entity_id']},\n '{$civicrm_log['data']}',\n {$civicrm_log['modified_id']},\n '{$civicrm_log['modified_date']}'\n )";
// assignLocalC
}
}
/* meminfo
*/
$meminfo[card_no] = $custdata[CardNo];
// Need fixAddress to capitalize first letter of each word.
$meminfo[street] = fixAddress($row[street_address]);
if ($row[supplemental_address_1] != "") {
$meminfo[street] .= ", {$row['supplemental_address_1']}";
}
if ($row[supplemental_address_2] != "") {
$meminfo[street] .= ", {$row['supplemental_address_2']}";
}
// Need fixCity to capitalize first letter.
$meminfo[city] = fixCity($row[city]);
$meminfo[state] = getProvince($row[state_province_id], 1);
$meminfo[zip] = fixPostalCode($row[postal_code]);
$meminfo[phone] = fixPhone($row[phone]);
$meminfo[email_1] = $row[email];
// Use for 2nd phone is there is one. None I know of.
$meminfo[email_2] = "";
// What should the source for this be?
$meminfo[ads_OK] = "1";
if ($is4cOp[meminfo] == "insert") {
// Compose the insert statement.
$insertMeminfo = "INSERT INTO meminfo (\ncard_no\n,street\n,city\n,state\n,zip\n,phone\n,email_1\n,email_2\n,ads_OK\n)\nVALUES (\n{$meminfo['card_no']}\n, '{$meminfo['street']}'\n, '{$meminfo['city']}'\n, '{$meminfo['state']}'\n, '{$meminfo['zip']}'\n, '{$meminfo['phone']}'\n, '{$meminfo['email_1']}'\n, '{$meminfo['email_2']}'\n, {$meminfo['ads_OK']}\n);";
// update
} else {
$updateMeminfo = "UPDATE meminfo SET\nstreet = '{$meminfo['street']}'\n,city = '{$meminfo['city']}'\n,state = '{$meminfo['state']}'\n,zip = '{$meminfo['zip']}'\n,phone = '{$meminfo['phone']}'\n,email_1 = '{$meminfo['email_1']}'\n,email_2 = '{$meminfo['email_2']}'\n,ads_OK = {$meminfo['ads_OK']}\nWHERE card_no = {$meminfo['card_no']}\n;";
}
/* memDates
Date the person became a member.