public Contact ValidateProviderContact( DataTable objDT, System.Text.RegularExpressions.Regex objRegExZip, System.Text.RegularExpressions.Regex objRegExEmail, System.Text.RegularExpressions.Regex objRegExPhone, List<EMSC2SF.User> objExistingUsers, List<EMSC2SF.Sub_Region__c> objSubRegions, List<string> objSpecialties, string[] strSpecialtySearchFor, string[] strSpecialtySubstitutions, List<string> objLeadSources, List<string> obj2ndLeadSources, string[] strSearchFor, string[] strSubstitutions, List<Contact> objContacts, DataRow objDR )
{
// copy all datatable columns to contact object attributes
Contact objNewContact = objDR.ConvertTo<Contact>( true );
objNewContact.Middle_Name__c = objNewContact.Middle_Name__c.RemoveSpecialCharacters();
// validate zip
System.Text.RegularExpressions.MatchCollection objMatchZip = objRegExZip.Matches( objNewContact.Zipcode__c );
if( objMatchZip.Count > 0 )
objNewContact.Zipcode__c = objMatchZip[ 0 ].Value;
objNewContact.Zipcode__c = Company2SFUtils.ValidatePostalCode( objNewContact.Zipcode__c );
objNewContact.OtherPostalCode = Company2SFUtils.ValidatePostalCode( objNewContact.OtherPostalCode );
// validate ssn
if( objNewContact.SSN__c != null && objNewContact.SSN__c.Length != 9 )
objNewContact.SSN__c = "";
// extract and validate email
string strUnformattedEmail = objDR[ "UnformattedEmail" ].ToString();
strUnformattedEmail = strUnformattedEmail.Replace( ",", "." ).Replace( "..", "." ).Replace( " ", "" )
.Replace( ">", "." ).Replace( "@@", "@" );
System.Text.RegularExpressions.MatchCollection objMatch = objRegExEmail.Matches( strUnformattedEmail );
if( objMatch.Count > 0 )
objNewContact.Email = objMatch[ 0 ].Value;
if( objMatch.Count > 1 )
objNewContact.Contacts_Email_No_2__c = objMatch[ 1 ].Value;
if( objMatch.Count > 2 )
objNewContact.Contacts_Email_No_3__c = objMatch[ 2 ].Value;
if( objMatch.Count == 0 )
objNewContact.Email = "";
// validate phones
objNewContact.HomePhone = Company2SFUtils.ValidPhone( objRegExPhone, objNewContact.HomePhone );
objNewContact.OtherPhone = Company2SFUtils.ValidPhone( objRegExPhone, objNewContact.OtherPhone );
objNewContact.Work_Phone__c = Company2SFUtils.ValidPhone( objRegExPhone, objNewContact.Work_Phone__c );
objNewContact.MobilePhone = Company2SFUtils.ValidPhone( objRegExPhone, objNewContact.MobilePhone );
objNewContact.Fax = Company2SFUtils.ValidPhone( objRegExPhone, objNewContact.Fax );
// set the owning region using appointments, if blank, use the region in the provider's record
string strOwningRegion = objDR[ "Default_Owning_Region" ].ToString();
switch( strOwningRegion )
{
case "CEN":
case "6":
case "14":
case "2":
strOwningRegion = "SWED";
break;
case "SE":
case "7":
strOwningRegion = "SED";
break;
case "NE":
strOwningRegion = "NED";
break;
case "PW":
strOwningRegion = "PWED";
break;
case "DMS":
strOwningRegion = "PWDMS";
break;
case "SANJ":
strOwningRegion = "PWSANJ";
break;
case "4":
case "1":
case "9":
case "11":
case "13":
strOwningRegion = "";
break;
case "":
strOwningRegion = objDR[ "Less_Reliable_Region" ].ToString();
break;
default: // don't change it
break;
}
objNewContact.Owning_Region__c = strOwningRegion;
// convert specialty
objNewContact.Specialty__c = Company2SFUtils.ConvertToStandardValue( objSpecialties, strSpecialtySearchFor, strSpecialtySubstitutions
, objNewContact.Specialty__c );
// convert lead source
LeadSourcePair objLSResults = ConvertLeadSource( objLeadSources, obj2ndLeadSources
, strSearchFor, strSubstitutions, objNewContact.Lead_Source_1__c );
objNewContact.Lead_Source_1__c = objLSResults.LeadSource1;
objNewContact.Lead_Source_Text__c = objLSResults.LeadSource2;
// convert recruiter usercode to Salesforce User ID
if( !objDR[ "Recruiter" ].IsNullOrBlank() )
{
string strRecruiter = objDR[ "Recruiter" ].ToString();
EMSC2SF.User objFound = objExistingUsers.FirstOrDefault(
u => u.Company_Usercode__c != null
&& u.Company_Usercode__c.Equals( strRecruiter )
&& u.IsActive == true );
if( objFound != null )
objNewContact.OwnerId = objFound.Id;
else
{
// find the recruiting manager for the region
Sub_Region__c objFoundSR = objSubRegions.FirstOrDefault( i => i.Name.Equals( strOwningRegion ) );
if( objFoundSR != null )
{
// check whether recruiting manager/user is active
objFound = objExistingUsers.FirstOrDefault(
u => u.Id.Equals( objFoundSR.Recruiting_Manager__c )
&& u.IsActive == true );
if( objFound != null )
objNewContact.OwnerId = objFound.Id;
}
// many providers don't have a region (or the region manager is inactive)
// so the owner will be whoever does the data load
}
}
// fix time zone bug
objNewContact.Birthdate = Company2SFUtils.FixTimeZoneBug( objNewContact.Birthdate );
objNewContact.Drivers_License_Expiration_Date__c =
Company2SFUtils.FixTimeZoneBug( objNewContact.Drivers_License_Expiration_Date__c );
// for some reason, this is required for the upsert to work
objNewContact.RecruitingID__cSpecified = true;
objNewContact.PhysicianNumber__cSpecified = true;
objNewContact.CreatedDateSpecified = true;
objNewContact.Active_Military__cSpecified = true;
objNewContact.Deceased__cSpecified = true;
objNewContact.Directorship_Experience__cSpecified = true;
objNewContact.Drivers_License_Expiration_Date__cSpecified = true;
objNewContact.BirthdateSpecified = ( objNewContact.Birthdate != null );
// copy other address into main address if main is empty
if( objNewContact.Address_Line_1__c.IsNullOrBlank() & objNewContact.City__c.IsNullOrBlank()
&& !objNewContact.OtherStreet.IsNullOrBlank() )
{
objNewContact.Address_Line_1__c = objNewContact.OtherStreet;
objNewContact.Address_Line_2__c = "";
objNewContact.City__c = objNewContact.OtherCity;
objNewContact.State__c = objNewContact.OtherState;
objNewContact.Zipcode__c = objNewContact.OtherPostalCode;
objNewContact.OtherStreet = "";
objNewContact.OtherCity = "";
objNewContact.OtherState = "";
objNewContact.OtherPostalCode = "";
}
// add metaphone values
string strName = objNewContact.FirstName;
if( objNewContact.Middle_Name__c.IsNullOrBlank() )
strName = string.Concat( strName, " ", objNewContact.LastName );
else
strName = string.Concat( strName, " ", objNewContact.Middle_Name__c, " ", objNewContact.LastName );
objNewContact.Metaphone_Name__c = strName.ToNormalizedMetaphone();
objNewContact.Metaphone_Address__c = objNewContact.Address_Line_1__c.ToNormalizedMetaphone().Left( 50 );
objNewContact.Metaphone_City__c = objNewContact.City__c.ToNormalizedMetaphone();
// find duplicate record in the list by matching metaphone name, phone and email
Contact objContactFound = objContacts.FirstOrDefault( c =>
c.IsADuplicateOf( objNewContact ) );
if( objContactFound != null )
{
ReportStatus( "\r\nFound duplicate for provider ", objNewContact.FirstName, " ", objNewContact.LastName
, " - RecrID = ", objNewContact.RecruitingID__c.ToString() );
// if found record doesn't have a physician number and the current one does
// then skip the duplicate instead of adding it to the list
if( objNewContact.PhysicianNumber__c == null
|| ( !objContactFound.PhysicianNumber__c.Equals( 0.0 )
&& objNewContact.PhysicianNumber__c.Equals( 0.0 ) ) )
return null;
// check whether the found record is better than the current, if it is, skip the current (don't add)
if( objNewContact.SSN__c.IsNullOrBlank() && !objContactFound.SSN__c.IsNullOrBlank() )
return null;
if( objNewContact.Email.IsNullOrBlank() && !objContactFound.Email.IsNullOrBlank() )
return null;
if( objNewContact.Birthdate.IsNullOrBlank() && !objContactFound.Birthdate.IsNullOrBlank() )
return null;
if( objNewContact.Address_Line_1__c.IsNullOrBlank() && !objContactFound.Address_Line_1__c.IsNullOrBlank() )
return null;
if( objNewContact.OtherStreet.IsNullOrBlank() && !objContactFound.OtherStreet.IsNullOrBlank() )
return null;
if( objNewContact.HomePhone.IsNullOrBlank() && !objContactFound.HomePhone.IsNullOrBlank() )
return null;
if( objNewContact.Work_Phone__c.IsNullOrBlank() && !objContactFound.Work_Phone__c.IsNullOrBlank() )
return null;
ReportStatus( "\r\n* Removing duplicate for provider ", objNewContact.FirstName, " ", objNewContact.LastName
, " - RecrID = ", objNewContact.RecruitingID__c.ToString()
, " (duplicate RecrID ", objContactFound.RecruitingID__c.ToString(), ")" );
// found record is not better so we keep this and delete the old one
objContacts.Remove( objContactFound );
}
ReportStatus( "Processed contact ", objNewContact.FirstName, " ", objNewContact.LastName
, " (", objContacts.Count.ToString(), "/", objDT.Rows.Count.ToString(), ")" );
return objNewContact;
}
private Candidate__c ProcessCandidateRecord( List<Contact> objProviders, List<Facility__c> objFacilities
, DataTable objDT, List<Candidate__c> objCandidates
, List<Contact> objProvidersToUpdate
, ref double dblRecruitingID, ref string strSiteCode
, ref int iSkipped, DataRow objDR, ref bool bSkipThisRecord)
{
// copy all datatable columns to credential agency object attributes
Candidate__c objNewCandidate = objDR.ConvertTo<Candidate__c>();
// try match 1st 15 characters
string strName = objDR[ "HospitalName" ].ToString().Left( 15 );
strSiteCode = objNewCandidate.Facility_Name__c;
Facility__c objFacility = FindFacilityByNameOrCode( objFacilities, strName, strSiteCode );
if( objFacility == null )
{
tbStatus.Text = string.Concat( tbStatus.Text, "\r\nCould not find hospital matching "
, objDR[ "HospitalName" ].ToString(), " or site code <"
, strSiteCode, "> for ", objNewCandidate.Name );
iSkipped++;
//continue;
bSkipThisRecord = true;
return objNewCandidate;
}
objNewCandidate.Facility_Name__c = objFacility.Id;
strSiteCode = objFacility.Site_Code__c;
// link candidate to Contact/Provider using RecruitingID
dblRecruitingID = Convert.ToDouble( objNewCandidate.Contact__c );
double dblRecrID = dblRecruitingID;
string strContactId = "";
Contact objProvider = objProviders.FirstOrDefault( i => i.RecruitingID__c == dblRecrID );
if( objProvider != null )
strContactId = objProvider.Id;
else // skip if provider was not found
{
tbStatus.Text = string.Concat( tbStatus.Text, "\r\nCould not find recruiting ID ", objNewCandidate.Contact__c, " for ", objNewCandidate.Name );
iSkipped++;
//continue;
bSkipThisRecord = true;
return objNewCandidate;
}
objNewCandidate.Contact__c = strContactId;
// correct the record name using the Contact and Facility names in order to avoid duplicates
objNewCandidate.Name = string.Concat( objProvider.Name, " at ", objFacility.Name ).Left( 80 );
// if candidate record name is duplicate (due to facilities with same name), make it different
// by appending the facility short name and/or site code instead
Candidate__c objDupeFound = objCandidates.FirstOrDefault( c => c.Name.Equals( objNewCandidate.Name ) );
if( objDupeFound != null )
if( objFacility.Short_Name__c != null )
objNewCandidate.Name = string.Concat( objProvider.Name, " at ", objFacility.Short_Name__c, "-", objFacility.Site_Code__c ).Left( 80 );
else
objNewCandidate.Name = string.Concat( objProvider.Name, " at ", objFacility.Site_Code__c ).Left( 80 );
//
// The code above probably could be changed to append the site code AFTER the facility name
// if the limit of 80 characters allows
//
// if the facility's region is the same as the provider's owning region, turn on the owning region flag
if( objFacility.Sub_Region__r != null && objFacility.Sub_Region__r.SubRegionCode__c != null )
objNewCandidate.Owning_Region__c =
( objFacility.Sub_Region__r.SubRegionCode__c.Equals( objProvider.Owning_Region__c ) );
else
objNewCandidate.Owning_Region__c = false;
objNewCandidate.Owning_Region__cSpecified = true;
if( (bool) objNewCandidate.Owning_Region__c )
{
string strProviderOwningStage = objProvider.Owning_Candidate_Stage__c;
if( strProviderOwningStage != null )
{
if( strProviderOwningStage.Equals( "Credentialing SAH" ) )
strProviderOwningStage = "Credentialing - SAH";
if( strProviderOwningStage.Equals( "Credentialing SAP" ) )
strProviderOwningStage = "Credentialing - SAP";
if( strProviderOwningStage.Equals( "Credentialing SAR" ) )
strProviderOwningStage = "Credentialing - SAR";
}
// check whether the candidate stage is higher than the current stage in the provider record
CandidateStage eProviderStage = 0;
if( strProviderOwningStage != null )
eProviderStage = (CandidateStage) Enum.Parse( typeof( CandidateStage )
, strProviderOwningStage.Replace( ' ', '_' ).Replace( '-', '_' ) );
CandidateStage eCandidateStage = (CandidateStage) Enum.Parse( typeof( CandidateStage )
, objNewCandidate.Candidate_Stage__c.Replace( ' ', '_' ).Replace( '-', '_' ) );
if( eProviderStage < eCandidateStage || objProvider.Owning_Candidate_Stage__c == null )
// set the owning stage if candidate stage is higher
objProvider.Owning_Candidate_Stage__c = objNewCandidate.Candidate_Stage__c;
// check whether the candidate status is higher than the current status in the provider record
CandidateStatus eProviderStatus = 0;
if( objProvider.Owning_Candidate_Status__c != null )
Enum.TryParse<CandidateStatus>(
objProvider.Owning_Candidate_Status__c.Replace( ' ', '_' ).Replace( "(", "" ).Replace( ")", "" )
, out eProviderStatus );
//eProviderStatus = (CandidateStatus) Enum.Parse( typeof( CandidateStatus )
// , objProvider.Owning_Candidate_Status__c.Replace( ' ', '_' ).Replace( "(", "" ).Replace( ")", "" ) );
CandidateStatus eCandidateStatus = 0;
Enum.TryParse<CandidateStatus>(
objNewCandidate.Candidate_Status__c.Replace( ' ', '_' ).Replace( "(", "" ).Replace( ")", "" )
, out eCandidateStatus );
//eCandidateStatus = (CandidateStatus) Enum.Parse( typeof( CandidateStatus )
// , objNewCandidate.Candidate_Status__c.Replace( ' ', '_' ).Replace( "(", "" ).Replace( ")", "" ) );
// set the owning status if candidate status is higher
if( eProviderStatus < eCandidateStatus || objProvider.Owning_Candidate_Status__c == null )
objProvider.Owning_Candidate_Status__c = objNewCandidate.Candidate_Status__c;
// update or add to the list
if( objProvidersToUpdate.Contains( objProvider ) )
objProvidersToUpdate[ objProvidersToUpdate.IndexOf( objProvider ) ] = objProvider;
else
objProvidersToUpdate.Add( objProvider );
}
// fix time zone bug
objNewCandidate.Application_Received__c = Company2SFUtils.FixTimeZoneBug( objNewCandidate.Application_Received__c );
objNewCandidate.Application_Sent__c = Company2SFUtils.FixTimeZoneBug( objNewCandidate.Application_Sent__c );
objNewCandidate.Interview_Actual_Date__c = null;// Company2SFUtils.FixTimeZoneBug( objNewCandidate.Interview_Actual_Date__c );
objNewCandidate.First_Shift_Date__c = Company2SFUtils.FixTimeZoneBug( objNewCandidate.First_Shift_Date__c );
objNewCandidate.Termination_Resignation_Effective_Date__c = Company2SFUtils.FixTimeZoneBug( objNewCandidate.Termination_Resignation_Effective_Date__c );
// if dates are not null, flag them
objNewCandidate.Application_Received__cSpecified = ( objNewCandidate.Application_Received__c != null );
objNewCandidate.Application_Sent__cSpecified = ( objNewCandidate.Application_Sent__c != null );
objNewCandidate.Interview_Actual_Date__cSpecified = ( objNewCandidate.Interview_Actual_Date__c != null );
objNewCandidate.First_Shift_Date__cSpecified = ( objNewCandidate.First_Shift_Date__c != null );
objNewCandidate.Termination_Resignation_Effective_Date__cSpecified = ( objNewCandidate.Termination_Resignation_Effective_Date__c != null );
objCandidates.Add( objNewCandidate );
ReportStatus( string.Concat( "Processed candidate ", objNewCandidate.Name
, " (", objCandidates.Count, "/", objDT.Rows.Count, ")" ) );
return objNewCandidate;
}
private void TestAllCustomerPropertiesById(Customer c, DataRow dr)
{
Assert.That(c.FirstName, Is.EqualTo(dr.ConvertTo<string>(nameof(c.FirstName))));
Assert.That(c.LastName, Is.EqualTo(dr.ConvertTo<string>(nameof(c.LastName))));
Assert.That(c.Email, Is.EqualTo(dr.ConvertTo<string>(nameof(c.Email))));
Assert.That(c.PhoneNumber, Is.EqualTo(dr.ConvertTo<string>(nameof(c.PhoneNumber))));
Assert.That(c.Address, Is.EqualTo(dr.ConvertTo<string>(nameof(c.Address))));
Assert.That(c.City, Is.EqualTo(dr.ConvertTo<string>(nameof(c.City))));
Assert.That(c.State, Is.EqualTo(dr.ConvertTo<string>(nameof(c.State))));
Assert.That(c.Zip, Is.EqualTo(dr.ConvertTo(nameof(c.Zip), 0)));
Assert.That(c.RewardsPoints, Is.EqualTo(dr[nameof(c.RewardsPoints)]
== DBNull.Value ? null : dr[nameof(c.RewardsPoints)]));
}