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 }
} // each person on the card } /* 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;"; }