public static bool ConnectToDB()
{
log.Info("Connecting to Access DB...");
gui.UpdateStatus("Connecting to Access DB...");
Cursor.Current = Cursors.WaitCursor;
string connStr = Schools.GetPath(school);
using (OleDbConnection conn = new OleDbConnection(connStr)) {
OleDbCommand command = new OleDbCommand("SELECT * FROM CalPERS_ID_Dep", conn);
log.Info("Conn String: " + connStr);
try {
conn.Open();
using (OleDbDataReader reader = command.ExecuteReader()) {
Console.WriteLine("------------DATA--------------");
log.Info("CalPERS_ID_Dep data loaded");
while (reader.Read())
{
CIDEntry tempCIDE = new CIDEntry(
reader["EID"].ToString(),
reader["First Name"].ToString(),
reader["Last Name"].ToString(),
reader["Relationship"].ToString(),
reader["Birth Date"].ToString(),
reader["CalPERS ID"].ToString());
cidList.Add(tempCIDE);
log.Info(tempCIDE.ToString());
}
}
//load a list of people in calpers as unrepresent 007
try {
command = new OleDbCommand("SELECT * FROM CalPERS_HEInfo", conn);
using (OleDbDataReader reader = command.ExecuteReader()) {
Console.WriteLine("------------DATA--------------");
log.Info("CalPERS_HEInfo data loaded");
while (reader.Read())
{
HEInfo heiTemp = new HEInfo(
reader["CalPERS_ID"].ToString(),
reader["First Name"].ToString(),
reader["Last Name"].ToString(),
reader["HEZip"].ToString(),
reader["HEType"].ToString(),
reader["Medical Group"].ToString());
HEinfoList.Add(heiTemp);
//add to the unrepped list
if (heiTemp.MedicalGroup == "007 UNREPRESENTED")
{
Unreped.Add(heiTemp.CalPERS_ID);
}
}
log.Info("Finished loading CalPERS_HEInfo: " + Unreped.Count);
}
} catch (Exception e1) {
Console.WriteLine(e1.Message);
log.Error(e1.Message);
gui.UpdateStatus(e1.Message);
return(false);
}
} catch (Exception e) {
Console.WriteLine(e.Message);
log.Error(e.Message);
gui.UpdateStatus(e.Message);
return(false);
}
}
log.Info("Connected to DB");
gui.UpdateStatus("Connected to Access DB! Load File Next");
Cursor.Current = Cursors.Default;
return(true);
}
/// <summary>
/// Returns a transactions which holds the employee and depenedednt records
/// </summary>
/// <param name="emp">CensusRow of the Employee Info</param>
/// <param name="deps">List of CensusRows that hold dependent data</param>
/// <returns></returns>
public static TransactionType BuildTransaction(CensusRow emp, List <CensusRow> deps)
{
TransactionType transactionType = new TransactionType();
transactionType.TransactionType1 = HEALTHEVENTTYPE2;
Guid guid = Guid.NewGuid();
transactionIds.Add(new KeyValuePair <string, string>(emp.EID, guid.ToString()));
transactionType.UniqueTransactionId = guid.ToString();
transactionType.RescindIndicator = false;
transactionType.RescindIndicatorSpecified = true;
IdentifierType identifierType = new IdentifierType();
if (!String.IsNullOrEmpty(emp.CalPERS_ID))
{
identifierType.ItemElementName = ItemChoiceType.CalPERSId;
identifierType.Item = emp.CalPERS_ID;
}
else
{
identifierType.ItemElementName = ItemChoiceType.SSN;
if (emp.SSN != null)
{
identifierType.Item = emp.CalPERS_ID;
}
else
{
identifierType.Item = "";
}
}
PersonInfoType personInfoType = new PersonInfoType();
personInfoType.PersonId = identifierType;
if (identifierType.ItemElementName == ItemChoiceType.SSN)
{
personInfoType.PersonIdType = IdentificationType.SocialSecurityNumber;
}
else
{
personInfoType.PersonIdType = IdentificationType.CalPERSIndetification;
}
personInfoType.FirstName = emp.FirstName;
personInfoType.MiddleName = emp.MiddleName;
personInfoType.LastName = emp.LastName;
DateTime bDate;
if (DateTime.TryParse(emp.BirthDate, out bDate))
{
personInfoType.BirthDate = bDate;
personInfoType.BirthDateSpecified = true;
}
else
{
personInfoType.BirthDateSpecified = false;
}
personInfoType.Gender = emp.Gender.Substring(0, 1);
AddressInfoType addressInfoType = new AddressInfoType();
addressInfoType.AddressType = AddressType.PhysicalAddress;
addressInfoType.AddressLine = new string[] { emp.Address1.Trim(), emp.Address2.Trim() };
addressInfoType.City = emp.City;
if (emp.State == "California")
{
addressInfoType.State = StateCode.California;
}
addressInfoType.ZipCode5 = emp.Zip;
addressInfoType.Country = CountryCodes.UnitedStates;
TransactionTypeDemographics transactionTypeDemographics = new TransactionTypeDemographics();
transactionTypeDemographics.PersonInfo = personInfoType;
transactionTypeDemographics.AddressInfo = addressInfoType;
transactionType.Demographics = transactionTypeDemographics;
TransactionTypeAppointment transactionTypeAppointment = new TransactionTypeAppointment();
EmployerInfoType employerInfoType = new EmployerInfoType();
employerInfoType.EmployerCalPERSId = school.CalPERSID;
employerInfoType.County = CountyCodes.SantaClara;//hard coded for campbell
transactionTypeAppointment.EmployerInfo = employerInfoType;
//TODO: MISSING CBU (Collective Bargaining Unit) codes.
//The collective bargaining unit (CBU) the employee is associated with.
EmploymentInfoType employmentInfo = new EmploymentInfoType();
//employmentInfo.CBU = "R03";
CollectiveBargainingUnit cbu = CollectiveBargainingUnits.GetCollectiveBargainingUnit(emp);
employmentInfo.CBU = cbu.GroupNumber;
transactionTypeAppointment.EmploymentInfo = employmentInfo;
transactionTypeAppointment.JobPositionInfo = new JobPositionInfoType(); //leave empty?
transactionType.Appointment = transactionTypeAppointment;
TransactionTypeHealthEnrollment transactionTypeHealthEnrollment = new TransactionTypeHealthEnrollment();
transactionTypeHealthEnrollment.HealthEventReason = HEALTHEVENTREASON.CodeValue.ToString();
transactionTypeHealthEnrollment.EventDateSpecified = true;//Docs say turn off for OE,
//ignore that. Needed
transactionTypeHealthEnrollment.ReceivedDateSpecified = true;
if (emp.E_SignDate != null && emp.E_SignDate != "")
{
transactionTypeHealthEnrollment.ReceivedDate = DateTime.Parse(emp.E_SignDate);
transactionTypeHealthEnrollment.EventDate = DateTime.Parse(emp.E_SignDate);
}
else
{
transactionTypeHealthEnrollment.ReceivedDate = DateTime.Parse("10/3/2019");
transactionTypeHealthEnrollment.EventDate = DateTime.Parse("10/3/2019");
}
SubscriberInfoType subscriberInfoType = new SubscriberInfoType();
subscriberInfoType.ApplyChangeToMedicalPlan = true;
subscriberInfoType.ApplyChangeToDentalPlan = false;
subscriberInfoType.ApplyChangeToVisionPlan = false;
//hard coded as Employer zip
var heInfoTempList = HEinfoList.Where(e => e.CalPERS_ID == emp.CalPERS_ID).ToList();
if (heInfoTempList != null && heInfoTempList.Count != 0) //if they are in list with a pervious choosen type
{
HEInfo heiTemp = heInfoTempList.First();
if (heiTemp.HEType == "Employer Address") //if they choose employer
{
subscriberInfoType.HealthEligibilityZipCodeType = HealthEligibilityZipCodeTypes.EmployerAddress;
subscriberInfoType.HealthEligibilityZipCode = heiTemp.HEZip; //already set, so use it instead of hardcoding
subscriberInfoType.HealthEligibilityCounty = CountyCodes.SantaClara; //all schools in same county... right...?
}
else //this is either physical or mailing. Doesn't matter.
{
subscriberInfoType.HealthEligibilityZipCodeType = HealthEligibilityZipCodeTypes.PersonalAddress;
subscriberInfoType.HealthEligibilityZipCode = emp.Zip;
if (String.IsNullOrEmpty(emp.County))
{
if (String.IsNullOrEmpty(emp.Zip))
{
subscriberInfoType.HealthEligibilityCounty = CountyCodes.SantaClara;
}
else
{
subscriberInfoType.HealthEligibilityCounty = ZipAndCounties.GetCountyCode(emp.Zip);
}
}
else
{
subscriberInfoType.HealthEligibilityCounty = ZipAndCounties.GetCountyCodeByName(emp.County);
}
}
}
else //the have not previously choosen an eligibility type, default to personal
{
subscriberInfoType.HealthEligibilityZipCodeType = HealthEligibilityZipCodeTypes.PersonalAddress;
subscriberInfoType.HealthEligibilityZipCode = emp.Zip;
if (String.IsNullOrEmpty(emp.County))
{
if (String.IsNullOrEmpty(emp.Zip))
{
subscriberInfoType.HealthEligibilityCounty = CountyCodes.SantaClara;
}
else
{
subscriberInfoType.HealthEligibilityCounty = ZipAndCounties.GetCountyCode(emp.Zip);
}
}
else
{
subscriberInfoType.HealthEligibilityCounty = ZipAndCounties.GetCountyCodeByName(emp.County);
}
//subscriberInfoType.HealthEligibilityCounty = ZipAndCounties.GetCountyCodeByName(emp.County);
}
try {
subscriberInfoType.MedicalPlanSelection = MedicalPlanCodes.Plans.Where(
c => c.EaseID == emp.PlanImportID).First().PlanCode;
} catch (InvalidOperationException ioe) {
log.Error(ioe.Message);
gui.UpdateStatus("Unmatched Medical Plan for " + emp.PlanImportID);
transactionType = null;
return(transactionType);
}
transactionTypeHealthEnrollment.SubscriberInfo = subscriberInfoType;
transactionType.HealthEnrollment = transactionTypeHealthEnrollment;
List <DependentInfoType> dependents = new List <DependentInfoType>();
foreach (CensusRow depRow in deps)
{
DependentInfoType dep = new DependentInfoType();
DependentPersonInfoType depInfo = new DependentPersonInfoType();
IdentifierType depInfoIdent = new IdentifierType();
depInfo.FirstName = depRow.FirstName;
depInfo.LastName = depRow.LastName;
depInfo.BirthDate = DateTime.Parse(depRow.BirthDate);
string tempDepKey = depInfo.FirstName.Substring(0, 1) +
depInfo.LastName.Substring(0, 1) +
depInfo.BirthDate.ToString("MMddyyyy");
var tempCID = cidList.Where(d => d.GetDepKey() == tempDepKey).
ToList();
if (tempCID.Count == 1)
{
String tempCIDStr = tempCID.First().CalPERSID;
depInfoIdent.ItemElementName = ItemChoiceType.CalPERSId;
depInfoIdent.Item = tempCIDStr;
depInfo.PersonId = depInfoIdent;
depInfo.PersonIdType = IdentificationType.CalPERSIndetification;
}
else
{
depInfoIdent.ItemElementName = ItemChoiceType.SSN;
depInfoIdent.Item = depRow.SSN;
depInfo.PersonId = depInfoIdent;
depInfo.PersonIdType = IdentificationType.SocialSecurityNumber;
}
if (depRow.Gender == null || depRow.Gender == "")
{
depInfo.Gender = Gender.Unknown;
}
else
{
depInfo.Gender = depRow.Gender.Substring(0, 1);
}
dep.DependentPersonInfo = depInfo;
dep.Relationship = GetCalPERSRelationship(depRow.Relationship);
if (depRow.MaritalDate != null && depRow.MaritalDate != "")
{
dep.DateOfPartnership = DateTime.Parse(depRow.MaritalDate);
}
dep.AddressSameAsPrimarySubscriber = true;
dep.DependentType = GetCalPERSDependentType(depRow.Relationship);
dep.DisabilityIndicator = false;
dep.ApplyToMedical = true;
dep.ApplyToDental = false;
dep.ApplyToVision = false;
if (depRow.Disabled is null || depRow.Disabled == "")
{
dep.DisabilityConfirmIndicator = false;
}
