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