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 ); }