public List<Education_or_Experience__c> RefreshAMAProviderCredentials( bool bDisplayOnly = true )
{
System.Diagnostics.Stopwatch objWatch = new System.Diagnostics.Stopwatch();
objWatch.Start();
DataTable objDT = null;
ReportStatus( DateTime.Now.ToString()
, " Starting refresh of AMA Provider Credentials.\r\n" );
// get record types and the ids for Education and Board credential
List<RecordType> objRecTypes = objAPI.Query<RecordType>( "select Id, Name from RecordType" );
string strEducationTypeId = objRecTypes.FirstOrDefault<RecordType>( i => i.Name == "Degree/ Education" ).Id;
string strBoardTypeId = objRecTypes.FirstOrDefault<RecordType>( i => i.Name == "Board" ).Id;
// bring Board subtypes for credential
List<Credential_Subtype__c> objSubTypes = objAPI.Query<Credential_Subtype__c>(
"select Id, Name from Credential_Subtype__c where Credential_Type__c = 'Board' " );
// get the last creation date of AMA providers in order to only bring recent AMA providers
List<Contact> objLastAMA = objAPI.Query<Contact>(
"select Id, CreatedDate from Contact where AMAOnly__c <> '1' order by CreatedDate DESC limit 1" );
string strLastAMADt = DateTime.Today.ToShortDateString();
if( objLastAMA.Count == 1 )
strLastAMADt = ( (DateTime) objLastAMA[ 0 ].CreatedDate ).ToShortDateString();
strLastAMADt = "1/1/2001";
// load institutions list
List<Institution__c> objInstitutions = objAPI.Query<Institution__c>(
"select Id, Name, Company_Agency_Match__c, City__c, Metaphone_Name__c, Metaphone_City__c from Institution__c where Metaphone_City__c != null and Metaphone_Name__c != null order by Metaphone_City__c, Metaphone_Name__c" );
ReportStatus( "Loaded ", objInstitutions.Count.ToString(), " institutions." );
// do the load in batches of 10000 rows each
int iCount = 0, iSkipped = 0;
bool bKeepLoading = true;
string strLastMeNumber = "0";
List<Education_or_Experience__c> objEducations = null;
List<Credential__c> objCredentials = null;
while( bKeepLoading )
{
// get next batch of rows
ReportStatus( "Loading next batch of AMA providers with MeNumber > ", strLastMeNumber );
string strCondition = string.Concat(
" AND p.CompanyUpdateDate > '", strLastAMADt, "' AND p.MeNumber > '", strLastMeNumber, "' " );
objDT = objDB.GetDataTableFromSQLFile( "SQL_AMA_Provider_Credentials.txt", null, strCondition );
//strCondition = string.Concat(
// " AMAOnly__c = '1' AND MeNumber__c > '", strLastMeNumber, "' " );
//List<Contact> objContacts = Company2SFUtils.GetProvidersFromSF( objAPI, lblError, "MeNumber__c"
// , strCondition, " MeNumber__c limit 10000 " ); // not sure this will bring the right records
if( !objDB.ErrorMessage.Equals( "" ) )
{
ReportStatus( objDB.ErrorMessage );
//tbStatus.Text = string.Concat( tbStatus.Text, "\r\n", objDB.ErrorMessage );
// interrupt process
bKeepLoading = false;
break;
}
if( objDT.Rows.Count == 0 )
{
// interrupt process because there are no more rows
ReportStatus( "No more AMA provider rows found." );
bKeepLoading = false;
break;
}
// store the last MeNumber for the next iteration
strLastMeNumber = objDT.Rows[ objDT.Rows.Count - 1 ][ "MeNumber__c" ].ToString();
iCount += objDT.Rows.Count;
ReportStatus( objDT.Rows.Count.ToString(), " AMA provider rows retrieved. Total ", iCount.ToString(), " loaded.\r\n" );
// load providers from datatable to contact list
objEducations = new List<Education_or_Experience__c>( objDT.Rows.Count );
objCredentials = new List<Credential__c>( objDT.Rows.Count );
foreach( DataRow objDR in objDT.Rows )
{
// capitalize initials
string strFirstName = Util.Capitalize( objDR[ "FirstName" ].ToString() );
string strLastName = Util.Capitalize( objDR[ "LastName" ].ToString() );
string strMeNumber = objDR[ "MeNumber__c" ].ToString();
// add Board Certifications if present
string strBoardCert = objDR[ "BoardCert" ] != null ? objDR[ "BoardCert" ].ToString() : "";
if( !strBoardCert.Equals( "" ) )
iSkipped = AddBoardCertification( strBoardTypeId, objSubTypes, iSkipped, objCredentials, strFirstName, strLastName, strMeNumber, strBoardCert );
strBoardCert = objDR[ "BoardCert1" ] != null ? objDR[ "BoardCert1" ].ToString() : "";
if( !strBoardCert.Equals( "" ) )
iSkipped = AddBoardCertification( strBoardTypeId, objSubTypes, iSkipped, objCredentials, strFirstName, strLastName, strMeNumber, strBoardCert );
strBoardCert = objDR[ "BoardCert2" ] != null ? objDR[ "BoardCert2" ].ToString() : "";
if( !strBoardCert.Equals( "" ) )
iSkipped = AddBoardCertification( strBoardTypeId, objSubTypes, iSkipped, objCredentials, strFirstName, strLastName, strMeNumber, strBoardCert );
Education_or_Experience__c objNewEducation = new Education_or_Experience__c();
string strInstitution = objDR[ "GraduateEducationInstitution" ].ToString();
string strCity = objDR[ "InstitutionCity" ].ToString().Trim();
string strInstitNormalized = Util.NormalizeAMAInstitution( strInstitution );
string strMetaphoneInstitution = strInstitution.ToMetaphone();
string strMetaphoneCity = strCity.ToMetaphone();
// convert institution code into lookup id
Institution__c objInstitutionFound = objInstitutions.FirstOrDefault(
i => ( i.City__c.Equals( strCity )
|| i.Metaphone_City__c.Equals( strMetaphoneCity ) )
&& ( i.Name.IsEqualOrPartiallyMatchedTo( strInstitNormalized )
|| i.Metaphone_Name__c.Equals( strMetaphoneInstitution ) ) );
//Institution__c objInstitutionFound = objInstitutions.FirstOrDefault(
// i => ( i.Name.IsMetaphoneMatchedTo( strInstitution )
// || i.Name.IsEqualOrPartiallyMatchedTo( strInstitNormalized ) )
// && i.City__c.IsMetaphoneMatchedTo( strCity ) );
if( objInstitutionFound != null )
objNewEducation.Institution__c = objInstitutionFound.Id;
else
{
// create the institution
objInstitutionFound = new Institution__c();
objInstitutionFound.Name = strInstitution;
objInstitutionFound.Metaphone_Name__c = strInstitution.ToNormalizedMetaphone();
objInstitutionFound.City__c = strCity;
objInstitutionFound.Metaphone_City__c = strCity.ToNormalizedMetaphone();
objInstitutionFound.State__c = objDR[ "InstitutionState" ].ToString().Trim();
objInstitutionFound.Credential_Type__c = "Institution";
objInstitutionFound.Code__c = objDR[ "InstitutionID" ].ToString().Trim();
// use MeNumber to upsert existing AMA records in EmForce
SaveResult[] objSaveResult = null;
if( !bDisplayOnly )
objSaveResult = objAPI.Insert( new sObject[] { objInstitutionFound } );
if( objSaveResult[ 0 ].errors != null )
{
ReportStatus( "Could not create institution ", strInstitution,
" for the credentials of provider ", strFirstName, " ", strLastName
, " - ", objSaveResult[ 0 ].errors[ 0 ].message );
iSkipped++;
continue;
}
objInstitutionFound.Id = objSaveResult[ 0 ].id;
objNewEducation.Institution__c = objInstitutionFound.Id;
objInstitutions.Add( objInstitutionFound );
ReportStatus( "Created institution ", strInstitution, " with id ", objNewEducation.Institution__c
, " for the credentials of provider ", strFirstName, " ", strLastName );
}
//Contact objProvider = objContacts.FirstOrDefault( i => i.MeNumber__c.Equals( strContactId ) );
//if( objProvider != null )
// objNewEducation.Contact__c = objProvider.Id;
//else // skip if provider was not found
//{
// ReportStatus( "Could not find ME Number ", strContactId,
// " for the credentials of provider ", strFirstName, " ", strLastName );
// iSkipped++;
// continue;
//}
// create a contact object to let SalesForce do the relationship
Contact objEducProvider = new Contact();
objEducProvider.MeNumber__c = strMeNumber;
objNewEducation.Contact__r = objEducProvider;
if( !Convert.IsDBNull( objDR[ "GraduationFromYear" ] ) )
{
DateTime dtValue = new DateTime( Convert.ToInt16( objDR[ "GraduationFromYear" ] ), 1, 1 );
objNewEducation.From__c = dtValue;
// fix time zone bug
objNewEducation.From__c = Company2SFUtils.FixTimeZoneBug( objNewEducation.From__c );
}
if( !Convert.IsDBNull( objDR[ "GraduationToYear" ] ) )
{
DateTime dtValue = new DateTime( Convert.ToInt16( objDR[ "GraduationToYear" ] ), 1, 1 );
objNewEducation.To__c = dtValue;
// fix time zone bug
objNewEducation.To__c = Company2SFUtils.FixTimeZoneBug( objNewEducation.To__c );
}
objNewEducation.Name = string.Concat( strFirstName, " ", strLastName
, "-Medical School at ", strInstitution ).Left( 80 );
objNewEducation.Description__c = "Degree/ Education";
objNewEducation.Type__c = "Medical School";
objNewEducation.RecordTypeId = strEducationTypeId;
objNewEducation.From__cSpecified = ( objNewEducation.From__c != null );
objNewEducation.To__cSpecified = ( objNewEducation.To__c != null );
objEducations.Add( objNewEducation );
ReportStatus( "Processed graduate education ", objNewEducation.Name
, " (", objEducations.Count.ToString(), "/", objDT.Rows.Count.ToString(), ")" );
}
// use MeNumber to upsert existing AMA records in EmForce
UpsertResult[] objResults = null;
if( !bDisplayOnly )
{
objResults = objAPI.Upsert( "Name", objEducations.ToArray<sObject>() );
Company2SFUtils.ReportErrorsToHistoryFile( objResults, objEducations );
objResults = null;
objResults = objAPI.Upsert( "Name", objCredentials.ToArray<sObject>() );
Company2SFUtils.ReportErrorsToHistoryFile( objResults, objCredentials );
}
}
ReportStatus( iCount.ToString(), " Total AMA credential rows loaded. \r\n" );
objWatch.Stop();
ReportStatus( DateTime.Now.ToString(), " - Finished loading AMA credentials. Duration: "
, objWatch.Elapsed.Hours.ToString(), " hours "
, objWatch.Elapsed.Minutes.ToString(), " minutes." );
return objEducations;
}
public List<Residency_Program__c> RefreshResidencyPrograms( List<Institution__c> objInstitutions = null, bool bDisplayOnly = true )
{
List<Residency_Program__c> objResidPrograms = new List<Residency_Program__c>();
List<Institution__c> objNewInstitutions = new List<Institution__c>();
if( objInstitutions == null )
objInstitutions = objAPI.Query<Institution__c>(
"select Id, Name, Address1__c, City__c, State__c from Institution__c order by Name, City__c" );
else
// sort to help with lookup (name, city, address)
objInstitutions.Sort( ( p1, p2 ) => CompareInstitutions( p1, p2 ) );
string strFileName = string.Concat( strAppPath, "CSV_ResidencyPrograms.csv" );
// map columns to SF columns
string strMapping =
@"Program_ID__c,Name,Program_Institution__c,,Program_Type__c,,Program_Address_Line_1__c,Program_Address_Line_2__c"
+ @",,,Program_Zip_Code__c,Program_Speciality__c,Program_Director__c,Program_Contact_Phone__c,,Program_Contact_Email__c,,,,";
// read Residency Programs from CSV file
DataTable objDT = null;
objDT = objDT.ReadFile( strFileName, strMapping, true );
//"PROGRAM_ID","Primary_Name","Program_Name_ACGME2","PROGRAM_EXTRNAL_MAME","PROGRAM_TYPE","PROGRAM_SOURCE"
// ,"Program_Address1","Program_Address2","Program_City","Program_State","Program_Zip","Program_Specialty"
//,"Program_Director","Program_Phone","Program_Fax","Program_Email","Program_External_Address1"
//,"Program_External_Address2","Program_External_City","Program_External_State"
// attempt to link each program with an institution
int iSkipped = 0;
foreach( DataRow objDR in objDT.Rows )
{
Residency_Program__c objProgram = objDR.ConvertTo<Residency_Program__c>();
string strName = Util.FirstNonNull( objProgram.Program_Institution__c
, objDR[ "PROGRAM_EXTRNAL_MAME" ].ToString()
, objProgram.Name.Replace( " Program", "" ) );
string strAddress = objProgram.Program_Address_Line_1__c;
string strCity = objDR[ "Program_City" ].ToString();
string strState = objDR[ "Program_State" ].ToString();
// convert institution code into lookup id
Institution__c objInstitutionFound = Company2SFUtils.FindInstitution( objInstitutions, strName
, objProgram.Name, strAddress, strCity, strState );
string strId = "";
if( objInstitutionFound == null )
{
// check whether the new institution is already collected to be inserted
string strProgramName = objProgram.Name.Left( 80 );
objInstitutionFound = objNewInstitutions.FirstOrDefault( i => i.Name.Equals( strProgramName ) );
if( objInstitutionFound == null )
{
// create institution for inserting it later
objInstitutionFound = new Institution__c();
objInstitutionFound.Name = strProgramName;
objInstitutionFound.Metaphone_Name__c = strProgramName.ToNormalizedMetaphone();
objInstitutionFound.Code__c = objProgram.Name.Left( 100 );
objInstitutionFound.Company_Agency_Match__c = objProgram.Name.Left( 50 );
objInstitutionFound.Credential_Type__c = "Institution";
objInstitutionFound.Address1__c = strAddress;
objInstitutionFound.Metaphone_Address__c = strAddress.ToNormalizedMetaphone();
objInstitutionFound.Address2__c = objProgram.Program_Address_Line_2__c;
objInstitutionFound.City__c = strCity;
objInstitutionFound.Metaphone_City__c = strCity.ToNormalizedMetaphone();
objInstitutionFound.State__c = strState;
objInstitutionFound.Zip__c = objProgram.Program_Zip_Code__c;
objInstitutionFound.Fax__c = objDR[ "Program_Fax" ].ToString();
objInstitutionFound.Phone__c = objProgram.Program_Contact_Phone__c;
objInstitutionFound.Contact__c = objProgram.Program_Director__c;
objNewInstitutions.Add( objInstitutionFound );
}
// blank value will be replaced later
strId = "";
iSkipped++;
}
else
strId = objInstitutionFound.Id;
if( objProgram.Program_Speciality__c.Equals( "" ) )
objProgram.Name = objProgram.Name.Left( 80 );
else
objProgram.Name = string.Concat( objProgram.Name, "-", objProgram.Program_Speciality__c ).Left( 80 );
objProgram.Program_Institution__c = strId;
//tbStatus.Text = string.Concat( tbStatus.Text, "\r\nMatched: ", strName
// , ", institution: ", objInstitutionFound.Name
// , ", city: ", objInstitutionFound.City__c, ", program: ", objProgram.Name, ", program city: ", strCity );
objResidPrograms.Add( objProgram );
}
tbStatus.Text = string.Concat( tbStatus.Text, "\r\n", iSkipped, " rows with new institutions (Institution mismatch)." );
// create the institutions
UpsertResult[] objInstitutionResults = null;
if( !bDisplayOnly )
objInstitutionResults = objAPI.Upsert( "Name", objNewInstitutions.ToArray<sObject>() );
// set the Ids
Company2SFUtils.SetIdsReportErrors( objNewInstitutions, objInstitutionResults, tbStatus );
// associate programs with the id of the new institutions
foreach( Residency_Program__c objProgram in objResidPrograms.Where( p => p.Program_Institution__c.Equals( "" ) ) )
{
Institution__c objInstit = objNewInstitutions.FirstOrDefault( i => i.Name.Equals( objProgram.Name.Left( 80 ) ) );
if( objInstit != null )
objProgram.Program_Institution__c = objInstit.Id;
}
// upsert
UpsertResult[] objResidProgramResults = null;
if( !bDisplayOnly )
objResidProgramResults = objAPI.Upsert( "Program_ID__c", objResidPrograms.ToArray<sObject>() );
// set the Ids
Company2SFUtils.SetIdsReportErrors( objResidPrograms, objResidProgramResults, tbStatus );
return objResidPrograms;
}
public static int CompareInstitutions( Institution__c p1, Institution__c p2 )
{
// order by name, then city, then address
if( p1.Name.Equals( p2.Name ) )
if( p1.City__c.Equals( p2.City__c ) )
return p1.Address1__c.CompareTo( p2.Address1__c );
else
return p1.City__c.CompareTo( p2.City__c );
return p1.Name.CompareTo( p2.Name );
}
