internal List <MemberStateHistoryModel> GetMemberStateHistories(IEnumerable <Member> members)
{
foreach (Member member in members)
{
int memberId = member.Id;
if (member != null)
{
// ATL
if (member.BirthDate.HasValue)
{
MemberMutation memberMutation = MemberMutation.Instance.Single(m => m.Code == MutationCodes.ATL);
var stateHistory = new MemberStateHistoryModel(memberId, member.MemberNo, memberMutation, member.BirthDate.Value, String.Empty);
stateHistories.Add(stateHistory);
}
// ATD
if (member.ChrismationType == "B" && member.ChrismationDate.HasValue && member.BaptizedReasonId.HasValue)
{
string baptizedReasonCode = member.BaptizedReason.Code;
MemberMutation memberMutation = MemberMutation.Instance.Single(m => m.Code == baptizedReasonCode);
var stateHistory = new MemberStateHistoryModel(memberId, member.MemberNo, memberMutation, member.ChrismationDate.Value, String.Empty);
stateHistories.Add(stateHistory);
}
// ATIS
if (member.ChrismationFlagAtis && member.ChrismationDate.HasValue)
{
MemberMutation memberMutation = MemberMutation.Instance.Single(m => m.Code == MutationCodes.ATIS);
var stateHistory = new MemberStateHistoryModel(memberId, member.MemberNo, memberMutation, member.ChrismationDate.Value, String.Empty);
stateHistories.Add(stateHistory);
}
// ATIOT
if (member.ChildhoodBaptizedFlagAtiot && member.ChildhoodBaptizedDate.HasValue)
{
MemberMutation memberMutation = MemberMutation.Instance.Single(m => m.Code == MutationCodes.ATIOT);
var stateHistory = new MemberStateHistoryModel(memberId, member.MemberNo, memberMutation, member.ChildhoodBaptizedDate.Value, String.Empty);
stateHistories.Add(stateHistory);
}
// ATPS
if (member.JoinFlagAtps && member.JoinDate.HasValue && member.JoinFromShrineId.HasValue)
{
MemberMutation memberMutation = MemberMutation.Instance.Single(m => m.Code == MutationCodes.ATPS);
var stateHistory = new MemberStateHistoryModel(memberId, member.MemberNo, memberMutation, member.JoinDate.Value, String.Empty);
stateHistories.Add(stateHistory);
}
// ATP
if (member.JoinDate.HasValue && member.JoinFromShrineId.HasValue)
{
if (member.ChildhoodBaptizedDate.HasValue)
{
MemberMutation memberMutation = MemberMutation.Instance.Single(m => m.Code == MutationCodes.ATP2);
var stateHistory = new MemberStateHistoryModel(memberId, member.MemberNo, memberMutation, member.JoinDate.Value, String.Empty);
stateHistories.Add(stateHistory);
}
else if (member.ChrismationDate.HasValue)
{
MemberMutation memberMutation = MemberMutation.Instance.Single(m => m.Code == MutationCodes.ATP1);
var stateHistory = new MemberStateHistoryModel(memberId, member.MemberNo, memberMutation, member.JoinDate.Value, String.Empty);
stateHistories.Add(stateHistory);
}
}
// AKK
if (member.ResignDate.HasValue && member.ResignReasonId.HasValue)
{
MemberMutation memberMutation = MemberMutation.Instance.Single(m => m.Code == member.ResignReason.Code);
var stateHistory = new MemberStateHistoryModel(memberId, member.MemberNo, memberMutation, member.ResignDate.Value, String.Empty);
stateHistories.Add(stateHistory);
}
// AKM
if (member.DeceasedDate.HasValue && member.ChildhoodBaptizedDate.HasValue && !member.ChrismationDate.HasValue)
{
MemberMutation memberMutation = MemberMutation.Instance.Single(m => m.Code == MutationCodes.AKM2);
var stateHistory = new MemberStateHistoryModel(memberId, member.MemberNo, memberMutation, member.DeceasedDate.Value, String.Empty);
stateHistories.Add(stateHistory);
}
else if (member.DeceasedDate.HasValue && member.ChrismationDate.HasValue)
{
MemberMutation memberMutation = MemberMutation.Instance.Single(m => m.Code == MutationCodes.AKM1);
var stateHistory = new MemberStateHistoryModel(memberId, member.MemberNo, memberMutation, member.DeceasedDate.Value, String.Empty);
stateHistories.Add(stateHistory);
}
// DKH & ExDKH
IList <MemberStateHistoryModel> otherMutations =
(from m in context.Members
join memberStateHist in context.MemberStateHistories on m.Id equals memberStateHist.MemberId
join memberState in context.MemberStates on memberStateHist.MemberStateId equals memberState.Id
where m.Id == member.Id
select new MemberStateHistoryModel
{
MemberId = m.Id,
MemberNo = m.MemberNo,
StateCode = memberState.Code,
StateDescription = memberState.Description,
EffectiveDate = memberStateHist.EffDate,
Notes = memberStateHist.Notes
}).ToList();
stateHistories.AddRange(otherMutations);
}
}
return(stateHistories.OrderBy(state => state.EffectiveDate).ToList());
}
private void ProcessSheet3(HSSFWorkbook workbook, int fromYear, int toYear)
{
fromYear = 2016;
DateTime startDatePeriod = GetStartDatePeriod(fromYear);
DateTime endDatePeriod = GetEndDatePeriod(toYear);
ISheet sheet = workbook.GetSheetAt(3);
int rowIndex = 0;
sheet.GetRow(1).GetCell(17).SetCellValue(DateTime.Today);
foreach (Member member in members)
{
IRow currentRow = sheet.GetRow(5 + rowIndex);
// MemberNo
currentRow.GetCell(1).SetCellValue(member.MemberNo);
// Nama
currentRow.GetCell(2).SetCellValue(member.Name);
// Alamat
currentRow.GetCell(3).SetCellValue(member.Address);
// No. Telp
currentRow.GetCell(6).SetCellValue(member.HomePhone);
// No. HP
currentRow.GetCell(7).SetCellValue(member.CellPhone1);
// Wilayah
if (member.RegionId.HasValue)
{
string regionCode = member.Region.Code;
string wilayah = regionCode.Any(chr => chr == '-') ? member.Region.Code.Split('-')[0] : regionCode;
int wilayahInt;
if (Int32.TryParse(wilayah, out wilayahInt))
{
currentRow.GetCell(8).SetCellValue(wilayahInt.ToRoman());
}
}
// Gender
currentRow.GetCell(9).SetCellValue(member.Gender == "L" ? "P" : "W");
// Gol Darah
if (!String.IsNullOrEmpty(member.BloodType?.Trim()))
{
string rhesus = String.IsNullOrEmpty(member.Rhesus) ? "+" : member.Rhesus;
currentRow.GetCell(10).SetCellValue(String.Format("{0}{1}", member.BloodType, rhesus));
}
// Status Anggota
currentRow.GetCell(11).SetCellValue(member.ChrismationDate.HasValue ? "S" : "B");
// Umur tidak perlu dihitung karena sudah ada formulanya di Excel
//if(!member.DeceasedDate.HasValue)
// currentRow.GetCell(12).SetCellValue(member.BirthDate.HasValue ? (DateTime.Today.Year - member.BirthDate.Value.Year).ToString() : "");
// Pendidikan
string pendidikan = member.EducationGrade?.Name;
if (!String.IsNullOrEmpty(pendidikan))
{
currentRow.GetCell(13).SetCellValue(pendidikan);
}
// Pekerjaan
int? jobId = member.JobId;
string pekerjaan = String.Empty;
if (jobId.HasValue)
{
Job job = context.Jobs.SingleOrDefault(j => j.Id == jobId.Value);
if (job != null)
{
pekerjaan = job.Name ?? String.Empty;
}
currentRow.GetCell(14).SetCellValue(pekerjaan);
}
// Kelompok Etnis
string etnis = GetKlasisMappingValue(member.EthnicId.GetValueOrDefault(), DBAJMappingEnum.Ethnic);
if (!String.IsNullOrEmpty(etnis))
{
currentRow.GetCell(15).SetCellValue(etnis);
}
// Lahir
if (member.BirthDate.HasValue)
{
currentRow.GetCell(16).SetCellValue(member.BirthDate.Value);
}
// Baptis Dewasa / Anak
if (!String.IsNullOrEmpty(member.ChrismationType))
{
if (member.ChrismationType == "B" && member.ChrismationDate.HasValue)
{
currentRow.GetCell(17).SetCellValue(member.ChrismationDate.Value);
}
}
else
{
if (member.ChildhoodBaptizedDate.HasValue)
{
currentRow.GetCell(17).SetCellValue(member.ChildhoodBaptizedDate.Value);
}
}
// Sidi
if (!String.IsNullOrEmpty(member.ChrismationType) && member.ChrismationType == "S" && member.ChrismationDate.HasValue)
{
currentRow.GetCell(18).SetCellValue(member.ChrismationDate.Value);
}
// Atestasi Masuk
if (member.JoinDate.HasValue)
{
currentRow.GetCell(19).SetCellValue(member.JoinDate.Value);
}
// Atestasi Keluar
if (member.ResignDate.HasValue)
{
currentRow.GetCell(20).SetCellValue(member.ResignDate.Value);
}
// Meninggal dunia
if (member.DeceasedDate.HasValue)
{
currentRow.GetCell(21).SetCellValue(member.DeceasedDate.Value);
}
IEnumerable <MemberStateHistoryModel> validStateHistories =
(from state in stateHistories
where state.MemberId == member.Id && state.EffectiveDate >= startDatePeriod && state.EffectiveDate <= endDatePeriod
select state).ToList();
if (validStateHistories != null)
{
// Tanggal DKH
MemberStateHistoryModel dkhState = validStateHistories.FirstOrDefault(m => m.StateCode.Contains("DKH"));
if (dkhState != null)
{
currentRow.GetCell(22).SetCellValue(dkhState.EffectiveDate);
}
// Ex DKH
MemberStateHistoryModel exDkhState = validStateHistories.FirstOrDefault(m => m.StateCode.Contains("Ex.DKH"));
if (exDkhState != null)
{
currentRow.GetCell(23).SetCellValue(exDkhState.EffectiveDate);
}
// Ex DKH4
MemberStateHistoryModel exDkh4State = validStateHistories.FirstOrDefault(m => m.StateCode.Contains("Ex.DKH-4"));
if (exDkh4State != null)
{
currentRow.GetCell(24).SetCellValue(exDkh4State.EffectiveDate);
}
// Status DKH dan lainnya
int cellIndex = 25;
foreach (MemberStateHistoryModel stateHistory in validStateHistories)
{
currentRow.GetCell(cellIndex).SetCellValue(stateHistory.StateCode);
cellIndex++;
// insufficient column in the Excel template
if (cellIndex > 27)
{
break;
}
}
}
rowIndex++;
}
sheet.ForceFormulaRecalculation = true;
}
