package com.ffii.lioner.modules.lioner.service;

import java.io.IOException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.stream.Collectors;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.ffii.lioner.modules.lioner.entity.Application;
import com.ffii.lioner.modules.lioner.entity.Award;
import com.ffii.lioner.modules.lioner.entity.AwardRepository;
import com.ffii.lioner.modules.lioner.entity.Event;
import com.ffii.lioner.modules.lioner.entity.ImpApplication;
import com.ffii.lioner.modules.lioner.entity.ImpAward;
import com.ffii.lioner.modules.lioner.entity.ImpEvent;
import com.ffii.lioner.modules.lioner.reportDao.ImportErrorRecord;
import com.ffii.lioner.modules.lioner.req.UpdateExtRefReq;
import com.ffii.lioner.modules.common.SecurityUtils;
import com.ffii.lioner.modules.common.service.AuditLogService;
import com.ffii.lioner.modules.common.service.ExcelReportService;
import com.ffii.lioner.modules.master.service.CategoryService;
import com.ffii.lioner.modules.master.service.PromotionChannelService;
import com.ffii.lioner.modules.master.service.SubDivisionService;
import com.ffii.lioner.modules.master.service.TagService;
import com.ffii.core.support.AbstractBaseEntityService;
import com.ffii.core.support.JdbcDao;
import com.ffii.core.utils.BeanUtils;
import com.ffii.core.utils.ExcelUtils;
import com.ffii.core.utils.JsonUtils;
import com.ffii.core.utils.StringUtils;

import jakarta.persistence.Table;

@Service
public class AwardService extends AbstractBaseEntityService<Award, Long, AwardRepository> {

	private static List<String> eventRegions = Arrays.asList("Local", "Oversea");
	private static List<String> eventTypes = Arrays.asList("Event", "Competition");
	private static List<String> applicationStatus = Arrays.asList("Open", "Submitted", "Cancelled");
	private static List<String> frequencies = Arrays.asList("Annually", "Bi-annually", "Others");

	private FileService fileService;
	private FileRefService fileRefService;
	private AuditLogService auditLogService;
	private SubDivisionService subDivisionService;
	private TagService tagService;
	private CategoryService categoryService;
	private PromotionChannelService promotionChannelService;
	private ExcelReportService excelReportService;
	private EventService eventService;
	private ApplicationService applicationService;

	public AwardService(JdbcDao jdbcDao, AwardRepository repository, FileService fileService,
			FileRefService fileRefService, AuditLogService auditLogService, SubDivisionService subDivisionService,
			TagService tagService, CategoryService categoryService, PromotionChannelService promotionChannelService,
			ExcelReportService excelReportService, EventService eventService, ApplicationService applicationService) {
		super(jdbcDao, repository);
		this.fileService = fileService;
		this.fileRefService = fileRefService;
		this.auditLogService = auditLogService;
		this.subDivisionService = subDivisionService;
		this.tagService = tagService;
		this.categoryService = categoryService;
		this.promotionChannelService = promotionChannelService;
		this.excelReportService = excelReportService;
		this.eventService = eventService;
		this.applicationService = applicationService;
	}

	public Optional<Map<String, Object>> getAwardDetail(Map<String, Object> req) {
		StringBuilder sql = new StringBuilder("SELECT"
				+ " a.id,  "
				+ " a2.id as applicationId,  "
				+ " a2.eventId, "
				+ " a.name,  "
				+ " a.nameCht,  "
				+ " a.remarks,  "
				+ " e.name AS eventName,  "
				+ " a2.name AS applicationName,  "
				+ " a.receiveDate, "
				+ " a.storageLocation, "
				+ " a.physicalAward, "
				+ " a.categoryId, "
				+ " c.name as categoryName, "
				+ " a.promotionChannel, "
				+ " a.responsibleOfficer  "
				+ " FROM award a "
				+ " LEFT JOIN category c ON a.categoryId = c.id  "
				+ " LEFT JOIN application a2 ON a2.id = a.applicationId  "
				+ " LEFT JOIN event e ON e.id = a2.eventId   "
				+ " WHERE a.deleted = FALSE  "
				+ " AND a.id = :id;  ");

		return jdbcDao.queryForMap(sql.toString(), req);
	}

	public Map<String, Object> getAuditLogObject(Map<String, Object> req) {
		StringBuilder sql = new StringBuilder("SELECT"
				+ " a.id,    "
				+ " a.created,    "
				+ " a.createdBy AS createdById,    "
				+ " u1.name AS createdBy,    "
				+ " a.version,    "
				+ " a.modified,    "
				+ " a.modifiedBy AS modifiedById,  "
				+ " a.deleted, "
				+ " a.applicationId, "
				+ " a2.name AS applicationName, "
				+ " a.name, "
				+ " a.nameCht, "
				+ " a.remarks, "
				+ " a.categoryId, "
				+ " c.name AS categoryName, "
				+ " a.receiveDate, "
				+ " a.storageLocation, "
				+ " a.physicalAward, "
				+ " a.responsibleOfficer, "
				+ " a.promotionChannel "
				+ " FROM award a 	   "
				+ " LEFT JOIN `user` u1 ON u1.id = a.createdBy     "
				+ " LEFT JOIN `user` u2 ON u2.id = a.modifiedBy "
				+ " LEFT JOIN application a2 ON a.applicationId = a2.id  "
				+ " LEFT JOIN category c ON c.id = a.categoryId  "
				+ " WHERE a.id = :id    ");
		return jdbcDao.queryForMap(sql.toString(), req).get();
	}

	public List<Map<String, Object>> list(Map<String, Object> args) {
		StringBuilder sql = new StringBuilder("SELECT"
				+ " DISTINCT a.id, "
				+ " e.name AS eventName,  "
				+ " a2.name AS applicationName, "
				+ " a.name AS awardName, "
				+ " a.responsibleOfficer "
				+ " FROM award a  "
				+ " LEFT JOIN award_sub_division asd ON a.id = asd.awardId AND asd.deleted = FALSE "
				+ " LEFT JOIN application a2 ON a2.id = a.applicationId  "
				+ " LEFT JOIN application_tag at2 ON a2.id = at2.applicationId AND at2.deleted = FALSE"
				+ " LEFT JOIN event e ON e.id = a2.eventId  "
				+ " LEFT JOIN sub_division sd ON sd.id = asd.subDivisionId  "
				+ " LEFT JOIN division d ON d.id = sd.divisionId "
				+ " LEFT JOIN `user` u ON u.id = a.createdBy "
				+ " WHERE a.deleted = FALSE ");

		if (args != null) {
			if (args.containsKey("userSubDivisionId"))
				sql.append(
						"  AND d.id = (SELECT sd2.divisionId FROM sub_division sd2 WHERE sd2.id = :userSubDivisionId)");
			if (args.containsKey("eventName"))
				sql.append(" AND e.name LIKE :eventName ");
			if (args.containsKey("applicationName"))
				sql.append(" AND a2.name LIKE :applicationName ");
			if (args.containsKey("fromDate"))
				sql.append(" AND DATE(e.startDate) >= :fromDate");
			if (args.containsKey("toDate"))
				sql.append(" AND DATE(e.startDate) < :toDate");
			if (args.containsKey("awardFromDate"))
				sql.append(" AND DATE(e.awardDate) >= :awardFromDate");
			if (args.containsKey("awardToDate"))
				sql.append(" AND DATE(e.awardDate) < :awardToDate");
			if (args.containsKey("awardName"))
				sql.append(" AND a.name LIKE :awardName ");
			if (args.containsKey("categoryIds"))
				sql.append(" AND a.categoryId IN (:categoryIds) ");
			if (args.containsKey("tagIds"))
				sql.append(" AND at2.tagId IN (:tagIds) ");
			if (args.containsKey("divisionIdList"))
				sql.append(" AND sd.divisionId IN (:divisionIdList) ");
			if (args.containsKey("subDivisionIdList"))
				sql.append(" AND sd.id IN (:subDivisionIdList) ");
			if (args.containsKey("storageLocation"))
				sql.append(" AND a.storageLocation LIKE :storageLocation ");
			if (args.containsKey("physicalAward"))
				sql.append(" AND a.physicalAward LIKE :physicalAward ");
			// if (args.containsKey("promotionChannel")) sql.append(" AND a.promotionChannel
			// LIKE :promotionChannel ");
			if (args.containsKey("promotionChannel"))
				sql.append(" AND JSON_OVERLAPS(a.promotionChannel->'$[*].id', :promotionChannel)");
			if (args.containsKey("responsibleOfficer"))
				sql.append(" AND a.responsibleOfficer LIKE :responsibleOfficer ");
			if (args.containsKey("keyword")) {
				sql.append(" AND ( a.createdBy LIKE :keyword ");
				sql.append(" OR u.username  LIKE :keyword ");
				sql.append(" OR u.fullname LIKE :keyword ");
				sql.append(" OR u.email LIKE :keyword ");
				sql.append(" OR sd.name LIKE :keyword ");
				sql.append(" OR d.name LIKE :keyword ");
				sql.append(" OR a.name LIKE :keyword ");
				sql.append(" OR a2.name LIKE :keyword ");
				sql.append(" OR e.name LIKE :keyword) ");
			}
		}
		sql.append(" ORDER BY a.id desc ");

		return jdbcDao.queryForList(sql.toString(), args);
	}

	public List<Map<String, Object>> listReport(Map<String, Object> args) {
		StringBuilder sql = new StringBuilder("SELECT"
				+ " DISTINCT a.*, "
				+ " (SELECT   "
				+ " GROUP_CONCAT( DISTINCT d2.name)   "
				+ " FROM award a2   "
				+ " LEFT JOIN award_sub_division asd2 ON a2.id = asd2.awardId  "
				+ " LEFT JOIN sub_division sd2 ON asd2.subDivisionId = sd2.id   "
				+ " LEFT JOIN division d2 ON d2.id = sd2.divisionId   "
				+ " WHERE a2.id = a.id  "
				+ " AND asd2.deleted = false "
				+ " GROUP BY a2.id  "
				+ " ) AS divisionList,  "
				+ " e.name AS eventName,  "
				+ " a2.name AS applicationName, "
				+ " u.name AS username, "
				+ " c.name AS categoryName "
				+ " FROM award a  "
				+ " LEFT JOIN award_sub_division asd ON a.id = asd.awardId AND asd.deleted = FALSE "
				+ " LEFT JOIN application a2 ON a2.id = a.applicationId  "
				+ " LEFT JOIN category c ON a.categoryId = c.id  "
				+ " LEFT JOIN application_tag at2 ON a2.id = at2.applicationId AND at2.deleted = FALSE"
				+ " LEFT JOIN event e ON e.id = a2.eventId  "
				+ " LEFT JOIN sub_division sd ON sd.id = asd.subDivisionId  "
				+ " LEFT JOIN division d ON d.id = sd.divisionId "
				+ " LEFT JOIN `user` u ON u.id = a.createdBy "
				+ " WHERE a.deleted = FALSE ");

		if (args != null) {
			if (args.containsKey("userSubDivisionId"))
				sql.append(
						"  AND d.id = (SELECT sd2.divisionId FROM sub_division sd2 WHERE sd2.id = :userSubDivisionId)");
			if (args.containsKey("eventName"))
				sql.append(" AND e.name LIKE :eventName ");
			if (args.containsKey("applicationName"))
				sql.append(" AND a2.name LIKE :applicationName ");
			if (args.containsKey("fromDate"))
				sql.append(" AND DATE(e.startDate) >= :fromDate");
			if (args.containsKey("toDate"))
				sql.append(" AND DATE(e.startDate) < :toDate");
			if (args.containsKey("awardFromDate"))
				sql.append(" AND DATE(e.awardDate) >= :awardFromDate");
			if (args.containsKey("awardToDate"))
				sql.append(" AND DATE(e.awardDate) < :awardToDate");
			if (args.containsKey("awardName"))
				sql.append(" AND a.name LIKE :awardName ");
			if (args.containsKey("categoryIds"))
				sql.append(" AND a.categoryId IN (:categoryIds) ");
			if (args.containsKey("tagIds"))
				sql.append(" AND at2.tagId IN (:tagIds) ");
			if (args.containsKey("divisionIdList"))
				sql.append(" AND sd.divisionId IN (:divisionIdList) ");
			if (args.containsKey("subDivisionIdList"))
				sql.append(" AND sd.id IN (:subDivisionIdList) ");
			if (args.containsKey("storageLocation"))
				sql.append(" AND a.storageLocation LIKE :storageLocation ");
			if (args.containsKey("physicalAward"))
				sql.append(" AND a.physicalAward LIKE :physicalAward ");
			if (args.containsKey("promotionChannel"))
				sql.append(" AND JSON_OVERLAPS(a.promotionChannel->'$[*].id', :promotionChannel)");
			if (args.containsKey("responsibleOfficer"))
				sql.append(" AND a.responsibleOfficer LIKE :responsibleOfficer ");
			if (args.containsKey("keyword")) {
				sql.append(" AND ( a.createdBy LIKE :keyword ");
				sql.append(" OR u.username  LIKE :keyword ");
				sql.append(" OR u.fullname LIKE :keyword ");
				sql.append(" OR u.email LIKE :keyword ");
				sql.append(" OR sd.name LIKE :keyword ");
				sql.append(" OR d.name LIKE :keyword ");
				sql.append(" OR a.name LIKE :keyword ");
				sql.append(" OR a2.name LIKE :keyword ");
				sql.append(" OR e.name LIKE :keyword) ");
			}
		}
		sql.append(" ORDER BY a.id");

		return jdbcDao.queryForList(sql.toString(), args);
	}

	public void markDeleteWithAuditLog(Award instance) {
		// =====GET OLD AUDIT LOG=====//
		String tableName = instance.getClass().getAnnotation(Table.class).name();
		StringBuilder sql = new StringBuilder("SELECT * FROM " + tableName + " WHERE id = :id");
		Map<String, Object> oldValueObject = new HashMap<String, Object>();
		Map<String, Object> newValueObject = new HashMap<String, Object>();

		if (instance != null && instance.getId() != null && instance.getId() > 0) {
			Map<String, Object> input = Map.of("id", instance.getId());
			Map<String, Object> logData = getAuditLogObject(input);
			logData.put("subDivision", this.getAwardSubDivisionName(input));
			oldValueObject = logData;
		}
		// =====GET OLD AUDIT LOG=====//
		this.markDelete(instance.getId());
		this.deleteSubDivision(instance.getId().intValue());
		this.deleteFile(instance.getId());
		this.deleteExternalRef(instance.getId());
		// =====GET NEW AUDIT LOG=====//
		if (instance != null && instance.getId() != null && instance.getId() > 0) {
			Map<String, Object> input = Map.of("id", instance.getId());
			Map<String, Object> logData = getAuditLogObject(input);
			logData.put("subDivision", this.getAwardSubDivisionName(input));
			newValueObject = logData;
		}

		if (auditLogService.compareMaps(newValueObject, oldValueObject).size() != 0 ||
				auditLogService.compareMaps(oldValueObject, newValueObject).size() != 0) {
			auditLogService.save(
					tableName,
					instance.getId(),
					instance.getName(),
					SecurityUtils.getUser() != null ? SecurityUtils.getUser().get().getId() : null,
					new Date(),
					JsonUtils.toJsonString(auditLogService.compareMaps(newValueObject, oldValueObject)),
					JsonUtils.toJsonString(auditLogService.compareMaps(oldValueObject, newValueObject)));
		}
		// =====GET NEW AUDIT LOG=====//
	}

	public void deleteSubDivision(Integer awardId) {
		List<Integer> subDivisionIdList = this.getAwardSubDivision(Map.of("id", awardId));
		List<Map<String, Integer>> deleteDivisionIds = subDivisionIdList.stream()
				.map(subDivisionId -> Map.of("awardId", awardId, "subDivisionId", subDivisionId))
				.collect(Collectors.toList());

		if (!deleteDivisionIds.isEmpty()) {
			jdbcDao.batchUpdate(
					"UPDATE award_sub_division asd"
							+ " SET asd.deleted = TRUE"
							+ " WHERE awardId = :awardId AND subDivisionId = :subDivisionId",
					deleteDivisionIds);
		}
	}

	public void deleteFile(Long awardId) {
		List<Map<String, Object>> fileList = fileService.listFile(Map.of("refId", awardId, "refType", "award"));
		fileList.forEach(file -> fileService.deleteFile(Long.valueOf(file.get("fileId").toString())));
	}

	public void deleteExternalRef(Long awardId) {
		List<Map<String, Object>> fileRefList = fileRefService
				.listExternal(Map.of("refId", awardId, "refType", "award"));
		fileRefList.forEach(fileRef -> fileRefService.delete(Long.valueOf(fileRef.get("id").toString())));
	}

	public List<Integer> getAwardSubDivision(Map<String, Object> req) {
		StringBuilder sql = new StringBuilder("SELECT"
				+ " subDivisionId "
				+ " FROM award_sub_division asd "
				+ " LEFT JOIN sub_division sd ON sd.id = asd.subDivisionId "
				+ " WHERE asd.awardId = :id "
				+ " AND sd.deleted = FALSE "
				+ " AND asd.deleted = FALSE; ");

		return jdbcDao.queryForInts(sql.toString(), req);
	}

	public List<String> getAwardSubDivisionName(Map<String, Object> req) {
		StringBuilder sql = new StringBuilder("SELECT"
				+ " sd.name "
				+ " FROM award_sub_division asd "
				+ " JOIN sub_division sd ON asd.subDivisionId = sd.id  "
				+ " WHERE asd.awardId = :id "
				+ " AND asd.deleted = FALSE; ");

		return jdbcDao.queryForStrings(sql.toString(), req);
	}

	public byte[] generateImportErrorReport(List<ImportErrorRecord> record) throws IOException {
		byte[] reportResult = excelReportService.generateAwardImportErrorReport(record);
		return reportResult;
	}

	public Award saveImportRecord(ImpAward req) throws JsonProcessingException {
		Award instance;
		if (req.getId() > 0) {
			instance = find(req.getId()).get();
		} else {
			instance = new Award();
		}
		// =====GET OLD AUDIT LOG=====//
		String tableName = instance.getClass().getAnnotation(Table.class).name();
		Map<String, Object> oldValueObject = new HashMap<String, Object>();
		Map<String, Object> newValueObject = new HashMap<String, Object>();

		if (instance != null && instance.getId() != null && instance.getId() > 0) {
			Map<String, Object> input = Map.of("id", instance.getId());
			Map<String, Object> logData = getAuditLogObject(input);
			logData.put("subDivision", this.getAwardSubDivisionName(input));
			oldValueObject = logData;
		}
		// =====GET OLD AUDIT LOG=====//

		BeanUtils.copyProperties(req, instance);

		List<String> jsonObjects = new ArrayList<>();
		ObjectMapper objectMapper = new ObjectMapper();

		for (Long promoId : req.getTempChannelIdList()) {
			Map<String, Object> channelObj = promotionChannelService.getChannelById(Map.of("id", promoId));

			String jsonObject = objectMapper.writeValueAsString(channelObj);
			jsonObjects.add(jsonObject);
		}

		String channelJson = "[" + String.join(",", jsonObjects) + "]";

		instance.setPromotionChannel(channelJson);
		instance = save(instance);
		Long awardId = instance.getId();

		List<Map<String, Long>> newDivisionIds = new ArrayList<Map<String, Long>>();
		for (Long subDivisionId : req.getTempSubDivIdList()) {
			newDivisionIds.add(
					Map.of("awardId", awardId, "subDivisionId", subDivisionId));
		}

		if (!newDivisionIds.isEmpty()) {
			jdbcDao.batchUpdate(
					"INSERT IGNORE INTO award_sub_division (awardId,subDivisionId)"
							+ " VALUES (:awardId, :subDivisionId)",
					newDivisionIds);
		}

		if (req.getTempExternalLink() != null) {
			fileRefService.saveExternal(new UpdateExtRefReq(
					-1L,
					awardId,
					req.getTempExternalLink()), "award", req.getImportDate());
		}

		// =====GET NEW AUDIT LOG=====//
		if (instance != null && instance.getId() != null && instance.getId() > 0) {
			Map<String, Object> input = Map.of("id", instance.getId());
			Map<String, Object> logData = getAuditLogObject(input);
			logData.put("subDivision", this.getAwardSubDivisionName(input));
			newValueObject = logData;
		}

		if (auditLogService.compareMaps(newValueObject, oldValueObject).size() != 0 ||
				auditLogService.compareMaps(oldValueObject, newValueObject).size() != 0) {
			auditLogService.save(
					tableName,
					instance.getId(),
					instance.getName(),
					SecurityUtils.getUser() != null ? SecurityUtils.getUser().get().getId() : null,
					new Date(),
					JsonUtils.toJsonString(auditLogService.compareMaps(newValueObject, oldValueObject)),
					JsonUtils.toJsonString(auditLogService.compareMaps(oldValueObject, newValueObject)));
		}
		// =====GET NEW AUDIT LOG=====//

		return instance;
	}

	public Map<String, Object> saveOrUpdate(
			Long id, Long applicationId, String name, String nameCht, String remarks, Long categoryId,
			LocalDate receiveDate,
			String storageLocation, String physicalAward, String responsibleOfficer,
			String promotionChannel, List<Long> subDivisionIdList, List<Long> subDivisionRemoveIdList,
			MultipartFile[] files) throws Exception {
		Award instance;
		if (id > 0) {
			instance = find(id).get();
		} else {
			instance = new Award();
		}

		instance.setApplicationId(applicationId);
		instance.setName(name);
		instance.setNameCht(nameCht);
		instance.setRemarks(remarks);
		instance.setCategoryId(categoryId);
		instance.setReceiveDate(receiveDate);
		instance.setStorageLocation(storageLocation);
		instance.setPhysicalAward(physicalAward);
		instance.setResponsibleOfficer(responsibleOfficer);
		instance.setPromotionChannel(promotionChannel);

		// =====GET OLD AUDIT LOG=====//
		String tableName = instance.getClass().getAnnotation(Table.class).name();
		StringBuilder sql = new StringBuilder("SELECT * FROM " + tableName + " WHERE id = :id");
		Map<String, Object> oldValueObject = new HashMap<String, Object>();
		Map<String, Object> newValueObject = new HashMap<String, Object>();

		if (instance != null && instance.getId() != null && instance.getId() > 0) {
			Map<String, Object> input = Map.of("id", instance.getId());
			Map<String, Object> logData = getAuditLogObject(input);
			logData.put("subDivision", this.getAwardSubDivisionName(input));
			oldValueObject = logData;
		}
		// =====GET OLD AUDIT LOG=====//

		instance = save(instance);
		Long awardId = instance.getId();

		List<Map<String, Long>> newDivisionIds = subDivisionIdList.stream()
				.map(subDivisionId -> Map.of("awardId", awardId, "subDivisionId", subDivisionId))
				.collect(Collectors.toList());
		List<Map<String, Long>> deleteDivisionIds = subDivisionRemoveIdList.stream()
				.map(subDivisionId -> Map.of("awardId", awardId, "subDivisionId", subDivisionId))
				.collect(Collectors.toList());
		if (!newDivisionIds.isEmpty()) {
			jdbcDao.batchUpdate(
					"INSERT IGNORE INTO award_sub_division (awardId,subDivisionId)"
							+ " VALUES (:awardId, :subDivisionId)",
					newDivisionIds);
		}
		if (!deleteDivisionIds.isEmpty()) {
			jdbcDao.batchUpdate(
					"UPDATE award_sub_division asd"
							+ " SET asd.deleted = TRUE"
							+ " WHERE awardId = :awardId AND subDivisionId = :subDivisionId",
					deleteDivisionIds);
		}

		Map<String, Object> idMap = new HashMap<String, Object>();
		if (files != null) {
			idMap = fileService.saveOrUpdate(files, awardId, "award");
		}

		// =====GET NEW AUDIT LOG=====//
		if (instance != null && instance.getId() != null && instance.getId() > 0) {
			Map<String, Object> input = Map.of("id", instance.getId());
			Map<String, Object> logData = getAuditLogObject(input);
			logData.put("subDivision", this.getAwardSubDivisionName(input));
			newValueObject = logData;
		}

		if (auditLogService.compareMaps(newValueObject, oldValueObject).size() != 0 ||
				auditLogService.compareMaps(oldValueObject, newValueObject).size() != 0) {
			auditLogService.save(
					tableName,
					instance.getId(),
					instance.getName(),
					SecurityUtils.getUser() != null ? SecurityUtils.getUser().get().getId() : null,
					new Date(),
					JsonUtils.toJsonString(auditLogService.compareMaps(newValueObject, oldValueObject)),
					JsonUtils.toJsonString(auditLogService.compareMaps(oldValueObject, newValueObject)));
		}
		// =====GET NEW AUDIT LOG=====//
		return Map.of(
				"id", instance.getId(),
				"fileId", idMap);
	}

	public boolean isNameTaken(String name, Long id, Long applicationId) {
		StringBuilder sql = new StringBuilder("SELECT"
				+ " Count(a.id)  "
				+ " FROM award a   "
				+ " WHERE a.deleted = FALSE   "
				+ " AND a.applicationId = :applicationId  "
				+ " AND a.name = :name  "
				+ " AND a.id != :id  ");

		return jdbcDao.queryForBoolean(sql.toString(), Map.of("name", name, "id", id, "applicationId", applicationId));
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = false)
	public Map<String, Object> massImport(MultipartFile[] files) throws IOException {
		Map<String, Object> result = new HashMap<String, Object>();
		List<ImpEvent> eventRecord = new ArrayList<ImpEvent>();
		List<ImpApplication> applicationRecord = new ArrayList<ImpApplication>();
		List<ImpAward> awardRecord = new ArrayList<ImpAward>();
		List<ImportErrorRecord> errorRecord = new ArrayList<>();
		LocalDateTime recordImportDate = LocalDateTime.now();
		Integer eventCount = 0;
		int applicationCount = 0;
		int awardCount = 0;
		Boolean templateVersion = true;
		Sheet sheet = null;

		ImportErrorRecord templateError = new ImportErrorRecord(
				0L,
				0L,
				"Template",
				"Please download the latest template for import data");

		List<String> validatePage = Arrays.asList(
				"Event", "Application", "Award");

		for (MultipartFile file : files) {
			Workbook workbook = new XSSFWorkbook(file.getInputStream());
			try {
				sheet = workbook.getSheetAt(1);
			} catch (Exception e) {
				logger.info("ERROR!");
				errorRecord.add(templateError);
				break;
			}

			// validate template
			for (String page : validatePage) {
				Sheet testSheet = workbook.getSheet(page);
				if (testSheet != null) {
					List<String> validateList = new ArrayList<>();
					switch (page) {
						case "Event":
							validateList = Arrays.asList(
									"name", "nameCht", "desc", "region", "org",
									"type", "from", "to", "appDate", "series",
									"awardDate", "appDeadline", "announcementDate", "subDiv1", "subDiv2",
									"subDiv3", "subDiv4", "subDiv5", "frequency", "newAppDate",
									"reminderEnable", "reminderBefore", "reminderInterval", "reminderLimit");
							break;
						case "Application":
							validateList = Arrays.asList(
									"event", "name", "desc", "status", "ro",
									"subDiv1", "subDiv2", "subDiv3", "subDiv4", "subDiv5",
									"tag1", "tag2", "tag3", "tag4", "tag5");
							break;
						case "Award":
							validateList = Arrays.asList(
									"event", "application", "name", "nameCht", "recDate",
									"storageLoc", "PhyAward", "category", "ro",
									"remarks", "subDiv1", "subDiv2", "subDiv3", "subDiv4",
									"subDiv5", "proCh1", "proCh2", "proCh3", "proCh4",
									"proCh5", "extLink");
							break;
						default:
							break;
					}
					for (Row row : testSheet) {
						if (row.getRowNum() == 0) {
							for (int i = 0; i < validateList.size(); i++) {
								Cell cell = row.getCell(i);
								if (getStringValueFromCell(cell).compareTo(validateList.get(i)) != 0) {
									// logger.info(getStringValueFromCell(cell) + " vs " + validateList.get(i));
									templateVersion = false;
									errorRecord.add(templateError);
									break;
								}
							}
						} else {
							break;
						}
					}
				} else {
					templateVersion = false;
					errorRecord.add(templateError);
					break;
				}
			}

			for (String page : validatePage) {
				sheet = workbook.getSheet(page);
				List<String> duplicatedCheckList = new ArrayList<>();
				if (templateVersion) {
					for (Row row : sheet) {
						// Skip the header row
						if (row.getRowNum() <= 1) {
							// Skip the header row
							continue;
						}

						if (row.getCell(0) == null || row.getCell(0).toString().trim().length() == 0) {
							// end of record
							break;
						}

						switch (page) {
							case "Event":
								ImpEvent eventObj = createEventRecordFromRow(row, errorRecord, recordImportDate,
										duplicatedCheckList);
								eventRecord.add(eventObj);
								eventCount++;
								break;
							case "Application":
								ImpApplication applicationObj = createApplicationRecordFromRow(row, errorRecord,
										recordImportDate, eventRecord, duplicatedCheckList);
								applicationRecord.add(applicationObj);
								applicationCount++;
								break;
							case "Award":
								ImpAward record = createAwardRecordFromRow(row, errorRecord, recordImportDate,
										eventRecord,
										applicationRecord, duplicatedCheckList);
								awardRecord.add(record);
								awardCount++;
								break;
							default:
								break;
						}
					}
				}
			}
			workbook.close();
		}

		// final checking
		postDataChecking(eventRecord, applicationRecord, awardRecord, errorRecord);

		// sort by page and row number
		errorRecord.sort((e1, e2) -> {
			int compIdx = Integer.compare(validatePage.indexOf(e1.getPageName()),
					validatePage.indexOf(e2.getPageName()));
			if (compIdx == 0) {
				compIdx = e1.getRowId().compareTo(e2.getRowId());
			}
			return compIdx;
		});

		// import record
		Long importCount = 0L;
		if (errorRecord.size() == 0) {
			// error free import, can process to import appreciation
			for (ImpEvent event : eventRecord) {
				Event savedEvent = eventService.saveImportRecord(event);
				importCount++;
				// get all related application
				List<ImpApplication> importApplicationList = new ArrayList<ImpApplication>();
				for (ImpApplication application : applicationRecord) {
					if (application.getEventId() == event.getTempId()) {
						importApplicationList.add(application);

					}
				}

				for (ImpApplication application : importApplicationList) {
					// import application

					// update Event Id
					application.setEventId(savedEvent.getId());
					Application savedApplication = applicationService.saveImportRecord(application);
					importCount++;
					applicationRecord.remove(application);

					// get all related award
					List<ImpAward> importAwardList = new ArrayList<ImpAward>();
					for (ImpAward award : awardRecord) {
						if (award.getApplicationId() == application.getTempId()) {
							importAwardList.add(award);
						}
					}

					for (ImpAward award : importAwardList) {
						// import award

						// update Award Id
						award.setApplicationId(savedApplication.getId());
						saveImportRecord(award);
						importCount++;
						awardRecord.remove(award);
					}
				}
			}
		}

		result = Map.of(
				"eventCount", eventCount,
				"applicationCount", applicationCount,
				"awardCount", awardCount,
				"recordList", errorRecord,
				"status", errorRecord.size() == 0);
		return result;
	}

	private void postDataChecking(
			List<ImpEvent> events, List<ImpApplication> applications, List<ImpAward> awards,
			List<ImportErrorRecord> importErrorRecords) {
		for (ImpEvent event : events) {
			// Check if event exist in DB
			if (eventService.getIdByName(event.getName()) != 0) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) importErrorRecords.size(),
						event.getTempId(),
						"Event",
						"Event Name",
						event.getName() + " already exist in the system.");
				importErrorRecords.add(error);
			}

			// Check if any application for event
			int eventIdMatchesCount = 0;
			for (Application application : applications) {
				if (application.getEventId() == event.getTempId()) {
					eventIdMatchesCount++;
				}
			}
			if (eventIdMatchesCount == 0) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) importErrorRecords.size(),
						event.getTempId(),
						"Event",
						"Event Name",
						"No associated application found in the template.");
				importErrorRecords.add(error);
			}
		}

		for (ImpApplication application : applications) {

			// check for sub division
			ImpEvent parentEvent = null;
			for (ImpEvent event : events) {
				if (event.getTempId() != null && event.getTempId().equals(application.getEventId())) {
					parentEvent = event;
					break;
				}
			}

			if (parentEvent != null) {
				List<Long> parentSubDivList = parentEvent.getTempIdList();
				List<Long> applicationSubDivList = application.getTempSubDivIdList();

				for (Long subDivId : applicationSubDivList) {
					if (!parentSubDivList.contains(subDivId)) {
						ImportErrorRecord error = new ImportErrorRecord(
								(long) importErrorRecords.size(),
								application.getTempId(),
								"Application",
								"Sub-Division",
								"Sub-division does not exist in the associated event.");
						importErrorRecords.add(error);
					}
				}
			}

			// Check if contain award
			int awardIdMatchesCount = 0;
			for (Award award : awards) {
				if (award.getApplicationId() == application.getTempId()) {
					awardIdMatchesCount++;
				}
			}
			if (awardIdMatchesCount == 0) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) importErrorRecords.size(),
						application.getTempId(),
						"Application",
						"Application Name",
						"No associated award found in the template.");
				importErrorRecords.add(error);
			}
		}

		for (ImpAward award : awards) {
			// check for sub division
			ImpApplication parentApplication = null;
			for (ImpApplication application : applications) {
				if (application.getTempId() != null && application.getTempId().equals(award.getApplicationId())) {
					parentApplication = application;
					break;
				}
			}

			if (parentApplication != null) {
				List<Long> parentSubDivList = parentApplication.getTempSubDivIdList();
				List<Long> applicationSubDivList = award.getTempSubDivIdList();

				for (Long subDivId : applicationSubDivList) {
					if (!parentSubDivList.contains(subDivId)) {
						ImportErrorRecord error = new ImportErrorRecord(
								(long) importErrorRecords.size(),
								award.getTempId(),
								"Award",
								"Sub-Division",
								"Sub-division does not exist in the associated application.");
						importErrorRecords.add(error);
					}
				}
			}
		}
	}

	private ImpEvent createEventRecordFromRow(Row row, List<ImportErrorRecord> errorRecord,
			LocalDateTime recordImportDate, List<String> duplicatedCheckList) {
		// Extract data from the cells of the row and create an AppreciationRecord
		ImpEvent record = new ImpEvent();
		record.setImportDate(recordImportDate);
		Long rowNumber = (long) row.getRowNum() + 1;
		record.setId((long) -1);

		List<Long> subDivIdList = new ArrayList<Long>();
		Cell nameCell = row.getCell(0);
		String nameString = StringUtils.removeLineBreak(ExcelUtils.getStringValue(nameCell));
		if (!StringUtils.isBlank(nameString)) {
			if (nameString.length() > 255) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Event",
						"Event Name",
						"Value must not exceed 255 character.");
				errorRecord.add(error);
			} else if (duplicatedCheckList.contains(nameString)) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Event",
						"Event Name",
						"Duplicated Event.");
				errorRecord.add(error);
			}
			record.setName(nameString);
			duplicatedCheckList.add(nameString);
		} else {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"Event",
					"Event Name",
					"This column is mandatory.");
			errorRecord.add(error);
		}

		Cell nameChiCell = row.getCell(1);
		String nameChiString = ExcelUtils.getStringValue(nameChiCell);
		if (!StringUtils.isBlank(nameChiString)) {
			if (nameChiString.length() > 255) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Event",
						"Event Name(Chinese)",
						"Value must not exceed 255 character.");
				errorRecord.add(error);
			}
			record.setNameCht(nameChiString);
			;
		}

		Cell descriptionCell = row.getCell(2);
		String description = ExcelUtils.getStringValue(descriptionCell);
		if (!StringUtils.isBlank(description)) {
			if (description.length() > 500) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Event",
						"Event Description",
						"Value must not exceed 500 character.");
				errorRecord.add(error);
			}
			record.setDescription(description);
		}

		Cell regionCell = row.getCell(3);
		String region = ExcelUtils.getStringValue(regionCell);
		if (!StringUtils.isBlank(region)) {
			if (!eventRegions.contains(region)) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Event",
						"Region",
						"Invalid value.");
				errorRecord.add(error);
			} else {
				record.setRegion(region);
			}
		} else {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"Event",
					"Region",
					"This column is mandatory.");
			errorRecord.add(error);
		}

		Cell orgCell = row.getCell(4);
		String orgString = ExcelUtils.getStringValue(orgCell);
		if (!StringUtils.isBlank(orgString)) {
			if (orgString.length() > 255) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Event",
						"Organization",
						"Value must not exceed 255 character.");
				errorRecord.add(error);
			}
			record.setOrganization(orgString);
		} else {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"Event",
					"Organization",
					"This column is mandatory.");
			errorRecord.add(error);
		}

		Cell eventTypeCell = row.getCell(5);
		String eventType = ExcelUtils.getStringValue(eventTypeCell);
		if (!StringUtils.isBlank(eventType)) {
			if (!eventTypes.contains(eventType)) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Event",
						"Event Type",
						"Invalid value.");
				errorRecord.add(error);
			} else {
				record.setEventType(eventType);
			}
		} else {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"Event",
					"Event Type",
					"This column is mandatory.");
			errorRecord.add(error);
		}

		Cell eventFromCell = row.getCell(6);
		if (eventFromCell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(eventFromCell))) {
			LocalDate eventFromDate = getDateValueFromCell(eventFromCell);
			if (eventFromDate == null) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Event",
						"Event From Date",
						"Incorrect Date Format.");
				errorRecord.add(error);
			} else {
				record.setEventFrom(eventFromDate);
			}
		}

		Cell eventToCell = row.getCell(7);
		if (eventToCell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(eventToCell))) {
			LocalDate eventToDate = getDateValueFromCell(eventToCell);
			if (eventToDate == null) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Event",
						"Event To Date",
						"Incorrect Date Format.");
				errorRecord.add(error);
			} else {
				// check if valid from to date
				if (eventFromCell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(eventFromCell))) {
					LocalDate eventFromDate = getDateValueFromCell(eventFromCell);
					if (eventFromDate != null && eventFromDate.isAfter(eventToDate)) {
						ImportErrorRecord error = new ImportErrorRecord(
								(long) errorRecord.size(),
								rowNumber,
								"Event",
								"Event To Date",
								"Event To Date must be later than Event From Date.");
						errorRecord.add(error);
					}
				}
				record.setEventTo(eventToDate);
			}
		}

		Cell appDateCell = row.getCell(8);
		if (appDateCell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(appDateCell))) {
			LocalDate applicationDate = getDateValueFromCell(appDateCell);
			if (applicationDate == null) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Event",
						"Application Date",
						"Incorrect Date Format.");
				errorRecord.add(error);
			} else {
				record.setStartDate(applicationDate);
			}
		} else {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"Event",
					"Application Date",
					"This column is mandatory.");
			errorRecord.add(error);
		}

		Cell seriesCell = row.getCell(9);
		String seriesString = ExcelUtils.getStringValue(seriesCell);
		if (!StringUtils.isBlank(seriesString)) {
			if (seriesString.length() > 50) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Event",
						"Series",
						"Value must not exceed 50 character.");
				errorRecord.add(error);
			}
			record.setSeries(seriesString);
		}

		Cell awardDateCell = row.getCell(10);
		if (awardDateCell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(awardDateCell))) {
			LocalDate awardDate = getDateValueFromCell(awardDateCell);
			if (awardDate == null) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Event",
						"Award Date",
						"Incorrect Date Format.");
				errorRecord.add(error);
			} else {
				record.setAwardDate(awardDate);
			}
		} else {			
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"Event",
					"Award Date",
					"This column is mandatory.");
			errorRecord.add(error);
		}

		Cell appDeadLineCell = row.getCell(11);
		if (appDeadLineCell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(appDeadLineCell))) {
			LocalDate appDeadLine = getDateValueFromCell(appDeadLineCell);
			if (appDeadLine == null) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Event",
						"Application Deadline",
						"Incorrect Date Format.");
				errorRecord.add(error);
			} else {
				record.setApplicationDeadline(appDeadLine);
			}
		} else {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"Event",
					"Application Deadline",
					"This column is mandatory.");
			errorRecord.add(error);
		}

		Cell announcementDateCell = row.getCell(12);
		if (announcementDateCell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(announcementDateCell))) {
			LocalDate announcementDate = getDateValueFromCell(announcementDateCell);
			if (announcementDate == null) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Event",
						"Announcement Date",
						"Incorrect Date Format.");
				errorRecord.add(error);
			} else {
				record.setAnnouncementDate(announcementDate);
			}
		}

		Cell subDiv1Cell = row.getCell(13);
		if (subDiv1Cell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(subDiv1Cell))) {
			String subDiv1 = ExcelUtils.getStringValue(subDiv1Cell);
			if (subDiv1 != null) {
				Long id = (long) subDivisionService.getIdByName(subDiv1);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Event",
							"Sub-Division 1",
							subDiv1 + " doesn't exist");
					errorRecord.add(error);
				} else {
					subDivIdList.add(id);
				}
			}
		}

		Cell subDiv2Cell = row.getCell(14);
		if (subDiv2Cell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(subDiv2Cell))) {
			String subDiv2 = ExcelUtils.getStringValue(subDiv2Cell);
			if (subDiv2 != null) {
				Long id = (long) subDivisionService.getIdByName(subDiv2);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Event",
							"Sub-Division 2",
							subDiv2 + " doesn't exist");
					errorRecord.add(error);
				} else {
					subDivIdList.add(id);
				}
			}
		}

		Cell subDiv3Cell = row.getCell(15);
		if (subDiv3Cell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(subDiv3Cell))) {
			String subDiv3 = ExcelUtils.getStringValue(subDiv3Cell);
			if (subDiv3 != null) {
				Long id = (long) subDivisionService.getIdByName(subDiv3);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Event",
							"Sub-Division 3",
							subDiv3 + " doesn't exist");
					errorRecord.add(error);
				} else {
					subDivIdList.add(id);
				}
			}
		}

		Cell subDiv4Cell = row.getCell(16);
		if (subDiv4Cell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(subDiv4Cell))) {
			String subDiv4 = ExcelUtils.getStringValue(subDiv4Cell);
			if (subDiv4 != null) {
				Long id = (long) subDivisionService.getIdByName(subDiv4);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Event",
							"Sub-Division 4",
							subDiv4 + " doesn't exist");
					errorRecord.add(error);
				} else {
					subDivIdList.add(id);
				}
			}
		}

		Cell subDiv5Cell = row.getCell(17);
		if (subDiv5Cell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(subDiv5Cell))) {
			String subDiv5 = ExcelUtils.getStringValue(subDiv5Cell);
			if (subDiv5 != null) {
				Long id = (long) subDivisionService.getIdByName(subDiv5);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Event",
							"Sub-Division 5",
							subDiv5 + " doesn't exist");
					errorRecord.add(error);
				} else {
					subDivIdList.add(id);
				}
			}
		}

		Cell freqCell = row.getCell(18);
		String freqString = ExcelUtils.getStringValue(freqCell);
		if (!StringUtils.isBlank(freqString)) {
			if (!frequencies.contains(freqString)) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Event",
						"Frequency",
						"Invalid value.");
				errorRecord.add(error);
			} else {
				record.setFrequency(freqString);
			}
		}

		Cell newAppDateCell = row.getCell(19);
		if (newAppDateCell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(newAppDateCell))) {
			LocalDate newAppDate = getDateValueFromCell(newAppDateCell);
			if (newAppDate == null) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Event",
						"Next Application Date",
						"Incorrect Date Format.");
				errorRecord.add(error);
			} else {
				record.setNextApplicationDate(newAppDate);
			}
		}

		Cell reminderCell = row.getCell(20);
		if (reminderCell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(reminderCell))) {
			String reminderFlag = ExcelUtils.getStringValue(reminderCell);
			if (!StringUtils.isBlank(nameString)) {
				if (reminderFlag.compareTo("TRUE") == 0 ||
						reminderFlag.compareTo("FALSE") == 0) {
					record.setReminderFlag(reminderFlag.compareTo("TRUE") == 0 ? true : false);
				} else {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Event",
							"Reminder Enabled",
							"Invalid value.");
					errorRecord.add(error);
					record.setReminderFlag(false);
				}
			} else {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Event",
						"Event Name",
						"This column is mandatory.");
				errorRecord.add(error);
				record.setReminderFlag(false);
			}
		} else {
			record.setReminderFlag(false);
		}

		Cell reminderBeforeCell = row.getCell(21);
		String reminderBeforeValue = ExcelUtils.getStringValue(reminderBeforeCell);
		if (!StringUtils.isBlank(reminderBeforeValue)) {
			try {
				Long value = Long.parseLong(reminderBeforeValue);
				record.setReminderThreshold(value);
			} catch (NumberFormatException e) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Event",
						"Reminder Before",
						"Invalid value.");
				errorRecord.add(error);
			}
		} else if (record.getReminderFlag()) {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"Event",
					"Reminder Before",
					"This column is mandatory.");
			errorRecord.add(error);
		} else {
			record.setReminderThreshold(0L);
		}

		Cell reminderIntervalCell = row.getCell(22);
		String reminderIntervalValue = ExcelUtils.getStringValue(reminderIntervalCell);
		if (!StringUtils.isBlank(reminderIntervalValue)) {
			try {
				Long value = Long.parseLong(reminderIntervalValue);
				record.setReminderInterval(value);
			} catch (NumberFormatException e) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Event",
						"Reminder Interval",
						"Invalid value.");
				errorRecord.add(error);
			}
		} else if (record.isReminderFlag()) {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"Event",
					"Reminder Interval",
					"This column is mandatory.");
			errorRecord.add(error);
		} else {
			record.setReminderInterval(0L);
		}

		Cell reminderLimitCell = row.getCell(23);
		String reminderLimitValue = ExcelUtils.getStringValue(reminderLimitCell);
		if (!StringUtils.isBlank(reminderLimitValue)) {
			try {
				Long value = Long.parseLong(reminderLimitValue);
				record.setReminderLimit(value);
			} catch (NumberFormatException e) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Event",
						"Reminder Limit",
						"Invalid value.");
				errorRecord.add(error);
			}
		} else if (record.getReminderFlag()) {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"Event",
					"Reminder Limit",
					"This column is mandatory.");
			errorRecord.add(error);
		} else {
			record.setReminderLimit(0L);
		}

		record.setTempId(rowNumber);
		record.setTempIdList(subDivIdList);
		return record;
	}

	private ImpApplication createApplicationRecordFromRow(Row row, List<ImportErrorRecord> errorRecord,
			LocalDateTime recordImportDate, List<ImpEvent> events, List<String> duplicatedCheckList) {
		// Extract data from the cells of the row and create an Application Record
		ImpApplication record = new ImpApplication();
		record.setImportDate(recordImportDate);
		Long rowNumber = (long) row.getRowNum() + 1;
		record.setId((long) -1);
		record.setTempId(rowNumber);

		List<Long> subDivIdList = new ArrayList<>();
		List<Long> tagIdList = new ArrayList<>();
		Cell eventNameCell = row.getCell(0);
		String eventNameString = StringUtils.removeLineBreak(ExcelUtils.getStringValue(eventNameCell));
		if (!StringUtils.isBlank(eventNameString)) {
			// find event tempId
			ImpEvent event = events.stream()
					.filter(e -> eventNameString.equals(e.getName()))
					.findFirst()
					.orElse(null);

			if (event != null) {
				record.setEventId(event.getTempId());
			} else {
				record.setEventId(null);
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Application",
						"Event",
						eventNameCell + " doesn't exist in Event sheet");
				errorRecord.add(error);
			}
		} else {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"Application",
					"Event",
					"This column is mandatory.");
			errorRecord.add(error);
		}

		Cell applicationNameCell = row.getCell(1);
		String applicationNameString = StringUtils.removeLineBreak(ExcelUtils.getStringValue(applicationNameCell));
		if (!StringUtils.isBlank(applicationNameString)) {
			if (applicationNameString.length() > 255) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Application",
						"Application Name",
						"Value must not exceed 255 character.");
				errorRecord.add(error);
			} else if (duplicatedCheckList.contains(eventNameString + applicationNameString)) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Application",
						"Application Name",
						"Duplicated Application.");
				errorRecord.add(error);
			}
			record.setName(applicationNameString);
			duplicatedCheckList.add(eventNameString + applicationNameString);
		} else {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"Application",
					"Application Name",
					"This column is mandatory.");
			errorRecord.add(error);
		}

		Cell descriptionCell = row.getCell(2);
		String description = ExcelUtils.getStringValue(descriptionCell);
		if (!StringUtils.isBlank(description)) {
			if (description.length() > 500) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Application",
						"Application Description",
						"Value must not exceed 500 character.");
				errorRecord.add(error);
			}
			record.setDescription(description);
		}

		Cell statusCell = row.getCell(3);
		String status = ExcelUtils.getStringValue(statusCell);
		if (!StringUtils.isBlank(status)) {
			if (!applicationStatus.contains(status)) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Application",
						"Status",
						"Invalid value.");
				errorRecord.add(error);

			} else {
				record.setStatus(status);
			}
		} else {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"Application",
					"Status",
					"This column is mandatory.");
			errorRecord.add(error);
		}

		Cell officerCell = row.getCell(4);
		String officerString = ExcelUtils.getStringValue(officerCell);
		if (!StringUtils.isBlank(officerString)) {
			record.setResponsibleOfficer(officerString);
		} else {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"Application",
					"Responsible Officer",
					"This column is mandatory.");
			errorRecord.add(error);
		}

		Cell subDiv1Cell = row.getCell(5);
		if (subDiv1Cell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(subDiv1Cell))) {
			String subDiv1 = ExcelUtils.getStringValue(subDiv1Cell);
			if (subDiv1 != null) {
				Long id = (long) subDivisionService.getIdByName(subDiv1);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Application",
							"Sub-Division 1",
							subDiv1 + " doesn't exist");
					errorRecord.add(error);
				} else {
					subDivIdList.add(id);
				}
			}
		} else {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"Application",
					"Sub-Division 1",
					"This column is mandatory.");
			errorRecord.add(error);
		}

		Cell subDiv2Cell = row.getCell(6);
		if (subDiv2Cell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(subDiv2Cell))) {
			String subDiv2 = ExcelUtils.getStringValue(subDiv2Cell);
			if (subDiv2 != null) {
				Long id = (long) subDivisionService.getIdByName(subDiv2);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Application",
							"Sub-Division 2",
							subDiv2 + " doesn't exist");
					errorRecord.add(error);
				} else {
					subDivIdList.add(id);
				}
			}
		}

		Cell subDiv3Cell = row.getCell(7);
		if (subDiv3Cell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(subDiv3Cell))) {
			String subDiv3 = ExcelUtils.getStringValue(subDiv3Cell);
			if (subDiv3 != null) {
				Long id = (long) subDivisionService.getIdByName(subDiv3);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Application",
							"Sub-Division 3",
							subDiv3 + " doesn't exist");
					errorRecord.add(error);
				} else {
					subDivIdList.add(id);
				}
			}
		}

		Cell subDiv4Cell = row.getCell(8);
		if (subDiv4Cell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(subDiv4Cell))) {
			String subDiv4 = ExcelUtils.getStringValue(subDiv4Cell);
			if (subDiv4 != null) {
				Long id = (long) subDivisionService.getIdByName(subDiv4);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Application",
							"Sub-Division 4",
							subDiv4 + " doesn't exist");
					errorRecord.add(error);
				} else {
					subDivIdList.add(id);
				}
			}
		}

		Cell subDiv5Cell = row.getCell(9);
		if (subDiv5Cell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(subDiv5Cell))) {
			String subDiv5 = ExcelUtils.getStringValue(subDiv5Cell);
			if (subDiv5 != null) {
				Long id = (long) subDivisionService.getIdByName(subDiv5);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Application",
							"Sub-Division 5",
							subDiv5 + " doesn't exist");
					errorRecord.add(error);
				} else {
					subDivIdList.add(id);
				}
			}
		}

		Cell tag1Cell = row.getCell(10);
		if (tag1Cell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(tag1Cell))) {
			String tag1 = ExcelUtils.getStringValue(tag1Cell);
			if (tag1 != null) {
				Long id = (long) tagService.getIdByName(tag1);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Application",
							"Tag 1",
							tag1 + " doesn't exist");
					errorRecord.add(error);
				} else {
					tagIdList.add(id);
				}
			}
		}

		Cell tag2Cell = row.getCell(11);
		if (tag2Cell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(tag2Cell))) {
			String tag2 = ExcelUtils.getStringValue(tag2Cell);
			if (tag2 != null) {
				Long id = (long) tagService.getIdByName(tag2);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Application",
							"Tag 2",
							tag2 + " doesn't exist");
					errorRecord.add(error);
				} else {
					tagIdList.add(id);
				}
			}
		}

		Cell tag3Cell = row.getCell(12);
		if (tag3Cell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(tag3Cell))) {
			String tag3 = ExcelUtils.getStringValue(tag3Cell);
			if (tag3 != null) {
				Long id = (long) tagService.getIdByName(tag3);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Application",
							"Tag 3",
							tag3 + " doesn't exist");
					errorRecord.add(error);
				} else {
					tagIdList.add(id);
				}
			}
		}

		Cell tag4Cell = row.getCell(13);
		if (tag4Cell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(tag4Cell))) {
			String tag4 = ExcelUtils.getStringValue(tag4Cell);
			if (tag4 != null) {
				Long id = (long) tagService.getIdByName(tag4);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Application",
							"Tag 4",
							tag4 + " doesn't exist");
					errorRecord.add(error);
				} else {
					tagIdList.add(id);
				}
			}
		}

		Cell tag5Cell = row.getCell(14);
		if (tag5Cell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(tag5Cell))) {
			String tag5 = ExcelUtils.getStringValue(tag5Cell);
			if (tag5 != null) {
				Long id = (long) tagService.getIdByName(tag5);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Application",
							"Tag 5",
							tag5 + " doesn't exist");
					errorRecord.add(error);
				} else {
					tagIdList.add(id);
				}
			}
		}

		record.setTempSubDivIdList(subDivIdList);
		record.setTempTagIdList(tagIdList);
		return record;
	}

	private ImpAward createAwardRecordFromRow(Row row, List<ImportErrorRecord> errorRecord,
			LocalDateTime recordImportDate, List<ImpEvent> events, List<ImpApplication> applications,
			List<String> duplicatedCheckList) {
		// Extract data from the cells of the row and create an AppreciationRecord
		ImpAward record = new ImpAward();
		record.setImportDate(recordImportDate);
		Long rowNumber = (long) row.getRowNum() + 1;
		record.setId((long) -1);
		record.setTempId(rowNumber);

		List<Long> subDivIdList = new ArrayList<>();
		List<Long> channelIdList = new ArrayList<>();

		Cell eventNameCell = row.getCell(0);
		String eventNameString = StringUtils.removeLineBreak(ExcelUtils.getStringValue(eventNameCell));
		if (!StringUtils.isBlank(eventNameString)) {
			// find event tempId
			ImpEvent event = events.stream()
					.filter(e -> eventNameString.equals(e.getName()))
					.findFirst()
					.orElse(null);

			if (event != null) {
				// No value to set for award case
				// record.set(event.getTempId());
			} else {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Award",
						"Event",
						eventNameCell + " doesn't exist in Event sheet");
				errorRecord.add(error);
			}
		} else {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"Award",
					"Event",
					"This column is mandatory.");
			errorRecord.add(error);
		}

		Cell applicationNameCell = row.getCell(1);
		String applicationName = StringUtils.removeLineBreak(ExcelUtils.getStringValue(applicationNameCell));
		if (!StringUtils.isBlank(applicationName)) {
			// find application tempId
			List<ImpApplication> matchedApplications = applications.stream()
					.filter(e -> applicationName.equals(e.getName())).collect(Collectors.toList());

			if (matchedApplications.size() > 0) {
				ImpEvent event = events.stream()
						.filter(e -> eventNameString.equals(e.getName()))
						.findFirst()
						.orElse(null);
				// handle duplicated application name but different event
				for (ImpApplication matchedApplication : matchedApplications) {
					if (matchedApplication.getEventId() == event.getTempId()) {
						record.setApplicationId(matchedApplication.getTempId());
					}
				}
			} else {
				record.setApplicationId(null);
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Award",
						"Application",
						applicationName + " doesn't exist in Application sheet");
				errorRecord.add(error);
			}
		} else {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"Award",
					"Application",
					"This column is mandatory.");
			errorRecord.add(error);
		}

		Cell awardNameCell = row.getCell(2);
		String awardNameString = StringUtils.removeLineBreak(ExcelUtils.getStringValue(awardNameCell));
		if (!StringUtils.isBlank(awardNameString)) {
			if (awardNameString.length() > 500) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Award",
						"Award Name",
						"Value must not exceed 500 character.");
				errorRecord.add(error);
			} else if (duplicatedCheckList.contains(eventNameString + applicationName + awardNameString)) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Award",
						"Award Name",
						"Duplicated Award.");
				errorRecord.add(error);
			}
			duplicatedCheckList.add(eventNameString + applicationName + awardNameString);
			record.setName(awardNameString);
		} else {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"Award",
					"Award Name",
					"This column is mandatory.");
			errorRecord.add(error);
		}

		Cell awardNameChiCell = row.getCell(3);
		String awardNameChi = ExcelUtils.getStringValue(awardNameChiCell);
		if (!StringUtils.isBlank(awardNameChi)) {
			if (awardNameChi.length() > 500) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Award",
						"Award Name (Chinese)",
						"Value must not exceed 500 character.");
				errorRecord.add(error);
			}
			record.setNameCht(awardNameChi);
		}

		Cell receiveDateCell = row.getCell(4);
		if (receiveDateCell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(receiveDateCell))) {
			LocalDate receiveDate = getDateValueFromCell(receiveDateCell);
			if (receiveDate == null) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Award",
						"Received Date",
						"Incorrect Date Format.");
				errorRecord.add(error);
			} else {
				record.setReceiveDate(receiveDate);
			}
		}

		Cell storageLocationCell = row.getCell(5);
		String storageLoc = ExcelUtils.getStringValue(storageLocationCell);
		if (!StringUtils.isBlank(storageLoc)) {
			if (storageLoc.length() > 255) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Award",
						"Storage Location",
						"Value must not exceed 255 character.");
				errorRecord.add(error);
			}
			record.setStorageLocation(storageLoc);
		}

		Cell physicalAwardCell = row.getCell(6);
		String physicalAward = ExcelUtils.getStringValue(physicalAwardCell);
		if (!StringUtils.isBlank(physicalAward)) {
			if (physicalAward.length() > 100) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Award",
						"Physical Award",
						"Value must not exceed 100 character.");
				errorRecord.add(error);
			}
			record.setPhysicalAward(physicalAward);
		}

		Cell categoryCell = row.getCell(7);
		if (categoryCell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(categoryCell))) {
			String categoryString = ExcelUtils.getStringValue(categoryCell);
			if (categoryString != null) {
				Long id = (long) categoryService.getIdByName(categoryString);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Award",
							"Category",
							categoryString + " doesn't exist");
					errorRecord.add(error);
				} else {
					record.setCategoryId(id);
				}
			}
		} else {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"Award",
					"Category",
					"This column is mandatory.");
			errorRecord.add(error);
		}

		Cell officerCell = row.getCell(8);
		String officeName = ExcelUtils.getStringValue(officerCell);
		if (!StringUtils.isBlank(officeName)) {
			record.setResponsibleOfficer(officeName);
		} else {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"Award",
					"Responsiable Officer",
					"This column is mandatory.");
			errorRecord.add(error);
		}

		Cell remarkCell = row.getCell(9);
		String remarks = ExcelUtils.getStringValue(remarkCell);
		if (!StringUtils.isBlank(remarks)) {
			if (remarks.length() > 500) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Award",
						"Remarks",
						"Value must not exceed 500 character.");
				errorRecord.add(error);
			}
			record.setRemarks(remarks);
		}

		Cell subDiv1Cell = row.getCell(10);
		if (subDiv1Cell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(subDiv1Cell))) {
			String subDiv1 = ExcelUtils.getStringValue(subDiv1Cell);
			if (subDiv1 != null) {
				Long id = (long) subDivisionService.getIdByName(subDiv1);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Award",
							"Sub-Division 1",
							subDiv1 + " doesn't exist");
					errorRecord.add(error);
				} else {
					subDivIdList.add(id);
				}
			}
		} else {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"Award",
					"Sub-Division 1",
					"This column is mandatory.");
			errorRecord.add(error);
		}

		Cell subDiv2Cell = row.getCell(11);
		if (subDiv2Cell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(subDiv2Cell))) {
			String subDiv2 = ExcelUtils.getStringValue(subDiv2Cell);
			if (subDiv2 != null) {
				Long id = (long) subDivisionService.getIdByName(subDiv2);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Award",
							"Sub-Division 2",
							subDiv2 + " doesn't exist");
					errorRecord.add(error);
				} else {
					subDivIdList.add(id);
				}
			}
		}

		Cell subDiv3Cell = row.getCell(12);
		if (subDiv3Cell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(subDiv3Cell))) {
			String subDiv3 = ExcelUtils.getStringValue(subDiv3Cell);
			if (subDiv3 != null) {
				Long id = (long) subDivisionService.getIdByName(subDiv3);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Award",
							"Sub-Division 3",
							subDiv3 + " doesn't exist");
					errorRecord.add(error);
				} else {
					subDivIdList.add(id);
				}
			}
		}

		Cell subDiv4Cell = row.getCell(13);
		if (subDiv4Cell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(subDiv4Cell))) {
			String subDiv4 = ExcelUtils.getStringValue(subDiv4Cell);
			if (subDiv4 != null) {
				Long id = (long) subDivisionService.getIdByName(subDiv4);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Award",
							"Sub-Division 4",
							subDiv4 + " doesn't exist");
					errorRecord.add(error);
				} else {
					subDivIdList.add(id);
				}
			}
		}

		Cell subDiv5Cell = row.getCell(14);
		if (subDiv5Cell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(subDiv5Cell))) {
			String subDiv5 = ExcelUtils.getStringValue(subDiv5Cell);
			if (subDiv5 != null) {
				Long id = (long) subDivisionService.getIdByName(subDiv5);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Award",
							"Sub-Division 5",
							subDiv5 + " doesn't exist");
					errorRecord.add(error);
				} else {
					subDivIdList.add(id);
				}
			}
		}

		Cell channel1Cell = row.getCell(15);
		if (channel1Cell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(channel1Cell))) {
			String channel1 = ExcelUtils.getStringValue(channel1Cell);
			if (channel1 != null) {
				Long id = (long) promotionChannelService.getIdByName(channel1);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Award",
							"Promotion Channel 1",
							channel1 + " doesn't exist");
					errorRecord.add(error);
				} else {
					channelIdList.add(id);
				}
			}
		}

		Cell channel2Cell = row.getCell(16);
		if (channel2Cell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(channel2Cell))) {
			String channel2 = ExcelUtils.getStringValue(channel2Cell);
			if (channel2 != null) {
				Long id = (long) promotionChannelService.getIdByName(channel2);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Award",
							"Promotion Channel 2",
							channel2 + " doesn't exist");
					errorRecord.add(error);
				} else {
					channelIdList.add(id);
				}
			}
		}

		Cell channel3Cell = row.getCell(17);
		if (channel3Cell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(channel3Cell))) {
			String channel3 = ExcelUtils.getStringValue(channel3Cell);
			if (channel3 != null) {
				Long id = (long) promotionChannelService.getIdByName(channel3);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Award",
							"Promotion Channel 3",
							channel3 + " doesn't exist");
					errorRecord.add(error);
				} else {
					channelIdList.add(id);
				}
			}
		}

		Cell channel4Cell = row.getCell(18);
		if (channel4Cell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(channel4Cell))) {
			String channel4 = ExcelUtils.getStringValue(channel4Cell);
			if (channel4 != null) {
				Long id = (long) promotionChannelService.getIdByName(channel4);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Award",
							"Promotion Channel 4",
							channel4 + " doesn't exist");
					errorRecord.add(error);
				} else {
					channelIdList.add(id);
				}
			}
		}

		Cell channel5Cell = row.getCell(19);
		if (channel5Cell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(channel5Cell))) {
			String channel5 = ExcelUtils.getStringValue(channel5Cell);
			if (channel5 != null) {
				Long id = (long) promotionChannelService.getIdByName(channel5);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"Award",
							"Promotion Channel 5",
							channel5 + " doesn't exist");
					errorRecord.add(error);
				} else {
					channelIdList.add(id);
				}
			}
		}

		Cell externalLinkCell = row.getCell(20);
		String externalLinkString = ExcelUtils.getStringValue(externalLinkCell);
		if (!StringUtils.isBlank(externalLinkString)) {
			if (externalLinkString.length() > 255) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Award",
						"External Link",
						"Value must not exceed 255 character.");
				errorRecord.add(error);
			}
			record.setTempExternalLink(externalLinkString);
		}

		record.setTempSubDivIdList(subDivIdList);
		record.setTempChannelIdList(channelIdList);
		return record;
	}

	private LocalDate getDateValueFromCell(Cell cell) {
		// Handle date formatted numeric cell, if needed
		if (cell != null) {
			switch (cell.getCellType()) {
				case NUMERIC:
					if (DateUtil.isCellDateFormatted(cell)) {
						LocalDate dateValue = cell.getLocalDateTimeCellValue().toLocalDate();
						return dateValue;
					} else {
						return null;
					}
				default:
					return null;
			}
		} else {
			return null;
		}
	}

	private String getStringValueFromCell(Cell cell) {
		String cellValue = "";
		if (cell != null) {
			switch (cell.getCellType()) {
				case STRING:
					cellValue = cell.getStringCellValue();
					break;
				case NUMERIC:
					double numericValue = cell.getNumericCellValue();
					cellValue = String.valueOf(numericValue);
					break;
				default:
					break;
			}
		}
		return cellValue;
	}

}
