WebSVN – SmartDukaan – Diff – /trunk/profitmandi-fofo/src/main/java/com/spice/profitmandi/web/controller/BeatPlanController.java

 		EscalationType[] escalationTypes = EscalationType.values();
 		model.addAttribute("escalationTypes", escalationTypes);
 		return "beat-plan-base-location";
+	}
-	@PostMapping(value = "/beatPlan/updateBaseLocation")
+	// Helpers for XLSX bulk upload
-	public ResponseEntity<?> updateBaseLocation(
+	private static String readCell(org.apache.poi.ss.usermodel.Cell cell) {
-			HttpServletRequest request,
+		if (cell == null) return null;
-			@RequestParam int authUserId,
+		switch (cell.getCellType()) {
-			@RequestParam String locationName,
+			case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
-			@RequestParam String latitude,
+				return cell.getStringCellValue();
-			@RequestParam String longitude,
-			@RequestParam(required = false) String address) throws Exception {
+			case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
-		LoginDetails ld = cookiesProcessor.getCookiesObject(request);
+				if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
-		AuthUser me = authRepository.selectByEmailOrMobile(ld.getEmailId());
+					return cell.getDateCellValue().toInstant()
-		if (me == null) return responseSender.badRequest("Not logged in");
+							.atZone(java.time.ZoneId.systemDefault()).toLocalDate().toString();
+				}
-		// Permission gate: only Sales L3 and above
+				double n = cell.getNumericCellValue();
-		boolean isSalesL3Plus = csService.getAuthUserIds(
+				return (n == Math.floor(n)) ? String.valueOf((long) n) : String.valueOf(n);
-						com.spice.profitmandi.common.model.ProfitMandiConstants.TICKET_CATEGORY_SALES,
+			case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN:
-						Arrays.asList(EscalationType.L3, EscalationType.L4))
+				return String.valueOf(cell.getBooleanCellValue());
-				.stream().anyMatch(u -> u.getId() == me.getId());
+			case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA:
-		if (!isSalesL3Plus) {
+				return cell.getCellFormula();
+			default:
-			return responseSender.badRequest("Only Sales L3 and above can update base location");
+				return null;
+		}
-		AuthUserLocation loc = new AuthUserLocation();
-		loc.setAuthUserId(authUserId);
-		loc.setLocationType("BASE");
-		loc.setLocationName(locationName);
-		loc.setLatitude(latitude);
-		loc.setLongitude(longitude);
-		loc.setAddress(address);
-		loc.setCreatedTimestamp(LocalDateTime.now());
-		authUserLocationRepository.persist(loc);
-		Map<String, Object> result = new HashMap<>();
-		result.put("status", true);
-		result.put("id", loc.getId());
-		result.put("message", "Base location updated");
-		return responseSender.ok(result);
+	}
 	// ====================== ONE-TIME LAT/LNG MIGRATION ======================
 	// For each active fofo_store, compare its stored lat/lng with the geocoded
 	// address lat/lng (cached in Redis). If the gap is > thresholdKm (default 5)
 	@GetMapping(value = "/beatPlan/bulkUpload")
 	public String bulkUploadPage(HttpServletRequest request, Model model) {
 		return "beat-plan-bulk";
+	}
+	@PostMapping(value = "/beatPlan/updateBaseLocation")
+	public ResponseEntity<?> updateBaseLocation(
+			HttpServletRequest request,
+			@RequestParam int authUserId,
+			@RequestParam String locationName,
+			@RequestParam String latitude,
+			@RequestParam String longitude,
+			@RequestParam(required = false) String address) throws Exception {
+		LoginDetails ld = cookiesProcessor.getCookiesObject(request);
+		AuthUser me = authRepository.selectByEmailOrMobile(ld.getEmailId());
+		if (me == null) return responseSender.unauthorized("Not logged in");
+		// Permission gate: Sales L3+ OR explicit allow-list
+		// Allow-list (lowercase emails) — people outside Sales L3/L4 who still need
+		// to manage base locations (e.g., Tarun Verma).
+		Set<String> baseLocationAllowList = new HashSet<>(Arrays.asList(
+				"tarun.verma@smartdukaan.com"
+		));
+		String myEmail = me.getEmailId() != null ? me.getEmailId().toLowerCase() : "";
+		boolean isWhitelisted = baseLocationAllowList.contains(myEmail);
+		boolean isSalesL3Plus = csService.getAuthUserIds(
+						com.spice.profitmandi.common.model.ProfitMandiConstants.TICKET_CATEGORY_SALES,
+						Arrays.asList(EscalationType.L3, EscalationType.L4))
+				.stream().anyMatch(u -> u.getId() == me.getId());
+		if (!isSalesL3Plus && !isWhitelisted) {
+			return responseSender.forbidden("You are not authorized for this action. Only Sales L3 and above can update base location.");
+		}
+		AuthUserLocation loc = new AuthUserLocation();
+		loc.setAuthUserId(authUserId);
+		loc.setLocationType("BASE");
+		loc.setLocationName(locationName);
+		loc.setLatitude(latitude);
+		loc.setLongitude(longitude);
+		loc.setAddress(address);
+		loc.setCreatedTimestamp(LocalDateTime.now());
+		authUserLocationRepository.persist(loc);
+		Map<String, Object> result = new HashMap<>();
+		result.put("status", true);
+		result.put("id", loc.getId());
+		result.put("message", "Base location updated");
+		return responseSender.ok(result);
+	}
 	@GetMapping(value = "/beatPlan/downloadTemplate")
-	public ResponseEntity<?> downloadTemplate() {
+	public ResponseEntity<?> downloadTemplate() throws java.io.IOException {
+		org.apache.poi.xssf.usermodel.XSSFWorkbook wb = new org.apache.poi.xssf.usermodel.XSSFWorkbook();
+		org.apache.poi.xssf.usermodel.XSSFSheet sheet = wb.createSheet("beat-plan");
-		String csv = "beat_name,auth_user_id,start_date,day_number,partner_codes\n";
+		String[] cols = {"beat_name", "auth_user_id", "start_date", "day_number", "sequence_order", "partner_code"};
+		// Header style
+		org.apache.poi.xssf.usermodel.XSSFCellStyle headerStyle = wb.createCellStyle();
+		org.apache.poi.xssf.usermodel.XSSFFont headerFont = wb.createFont();
+		headerFont.setBold(true);
+		headerStyle.setFont(headerFont);
+		headerStyle.setFillForegroundColor(new org.apache.poi.xssf.usermodel.XSSFColor(new java.awt.Color(230, 230, 230)));
+		headerStyle.setFillPattern(org.apache.poi.ss.usermodel.FillPatternType.SOLID_FOREGROUND);
+		org.apache.poi.xssf.usermodel.XSSFRow header = sheet.createRow(0);
+		for (int i = 0; i < cols.length; i++) {
+			org.apache.poi.xssf.usermodel.XSSFCell c = header.createCell(i);
+			c.setCellValue(cols[i]);
+			c.setCellStyle(headerStyle);
+		}
+		// Example rows — one partner per row. Inheritable columns blank after first row of a beat.
+		Object[][] sample = {
-		csv += "Jaipur East Route,280,2026-06-02,1,\"RJKAI1478,RJBUN1449,RJDEG1443\"\n";
+				{"Jaipur East Route", "280", "2026-06-02", "1", "1", "RJKAI1478"},
+				{"", "", "", "1", "2", "RJBUN1449"},
+				{"", "", "", "1", "3", "RJDEG1443"},
+				{"", "", "", "2", "1", "RJALR1362"},
-		csv += ",280,,2,\"RJALR1362,RJBTR1388\"\n";
+				{"", "", "", "2", "2", "RJBTR1388"},
+				{"", "", "", "3", "1", "RJRSD1518"},
-		csv += ",280,,3,\"RJRSD1518,RJSML356\"\n";
+				{"", "", "", "3", "2", "RJSML356"},
-		csv += "Agra Circuit,145,2026-06-05,1,\"UPAGR101,UPAGR102\"\n";
+				{"Agra Circuit", "145", "2026-06-05", "1", "1", "UPAGR101"},
+				{"", "", "", "1", "2", "UPAGR102"},
+		};
+		for (int r = 0; r < sample.length; r++) {
+			org.apache.poi.xssf.usermodel.XSSFRow row = sheet.createRow(r + 1);
+			for (int c = 0; c < cols.length; c++) {
+				row.createCell(c).setCellValue(sample[r][c].toString());
+			}
+		}
+		for (int i = 0; i < cols.length; i++) sheet.autoSizeColumn(i);
+		java.io.ByteArrayOutputStream out = new java.io.ByteArrayOutputStream();
+		wb.write(out);
+		wb.close();
 		org.springframework.http.HttpHeaders headers = new org.springframework.http.HttpHeaders();
-		headers.add("Content-Disposition", "attachment; filename=beat_plan_template.csv");
+		headers.add("Content-Disposition", "attachment; filename=beat_plan_template.xlsx");
-		headers.add("Content-Type", "text/csv");
+		headers.add("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
-		return new ResponseEntity<>(csv, headers, org.springframework.http.HttpStatus.OK);
+		return new ResponseEntity<>(out.toByteArray(), headers, org.springframework.http.HttpStatus.OK);
+	}
 	@PostMapping(value = "/beatPlan/bulkUploadProcess")
 	public ResponseEntity<?> bulkUploadProcess(
 			HttpServletRequest request,
 			@RequestParam(value = "includeSundays", defaultValue = "false") boolean includeSundays) throws Exception {
 		LoginDetails loginDetails = cookiesProcessor.getCookiesObject(request);
 		AuthUser currentUser = authRepository.selectByEmailOrMobile(loginDetails.getEmailId());
-		java.io.Reader reader = new java.io.InputStreamReader(file.getInputStream());
+		// Read .xlsx — one partner per row. beat_name / auth_user_id / start_date
-		org.apache.commons.csv.CSVParser parser = new org.apache.commons.csv.CSVParser(reader,
+		// appear ONLY on the first row of a beat; subsequent rows inherit them.
-				org.apache.commons.csv.CSVFormat.DEFAULT.withFirstRecordAsHeader().withTrim());
+		org.apache.poi.ss.usermodel.Workbook workbook =
-		List<org.apache.commons.csv.CSVRecord> allRecords = parser.getRecords();
+				new org.apache.poi.xssf.usermodel.XSSFWorkbook(file.getInputStream());
-		parser.close();
+		org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(0);
-		Map<String, String> lastBeatNameByUser = new HashMap<>();
+		// Header → column index map
-		Map<String, List<org.apache.commons.csv.CSVRecord>> beatGroups = new LinkedHashMap<>();
+		org.apache.poi.ss.usermodel.Row headerRow = sheet.getRow(0);
+		if (headerRow == null) {
+			workbook.close();
-		Map<Long, String> resolvedBeatNames = new HashMap<>();
+			return responseSender.badRequest("Empty file");
+		}
+		Map<String, Integer> colIdx = new HashMap<>();
-		for (org.apache.commons.csv.CSVRecord record : allRecords) {
+		for (int i = 0; i < headerRow.getLastCellNum(); i++) {
-			String authId = record.get("auth_user_id").trim();
+			String h = readCell(headerRow.getCell(i));
-			String rawName = record.get("beat_name").trim().replaceAll("\\s+", " ");
+			if (h != null) colIdx.put(h.trim().toLowerCase(), i);
+		}
-			if (rawName.isEmpty()) rawName = lastBeatNameByUser.getOrDefault(authId, "Beat");
+		for (String required : new String[]{"beat_name", "auth_user_id", "day_number", "partner_code"}) {
-			else lastBeatNameByUser.put(authId, rawName);
+			if (!colIdx.containsKey(required)) {
-			resolvedBeatNames.put(record.getRecordNumber(), rawName);
+				workbook.close();
-			beatGroups.computeIfAbsent(rawName + "|" + authId, k -> new ArrayList<>()).add(record);
+				return responseSender.badRequest("Missing required column: " + required);
+			}
+		}
+		// Walk rows, group partners by (beat_name + auth_user_id) → day_number → sequence_order
+		Map<String, BulkBeatGroup> beatGroups = new LinkedHashMap<>();
+		String currentKey = null;
+		String currentBeatName = null;
+		String currentAuthId = null;
+		String currentStartDate = null;
+		for (int r = 1; r <= sheet.getLastRowNum(); r++) {
+			org.apache.poi.ss.usermodel.Row row = sheet.getRow(r);
+			if (row == null) continue;
+			String beatName = readCell(row.getCell(colIdx.get("beat_name")));
+			String authId = readCell(row.getCell(colIdx.get("auth_user_id")));
+			String startDate = colIdx.containsKey("start_date") ? readCell(row.getCell(colIdx.get("start_date"))) : null;
+			String dayNumber = readCell(row.getCell(colIdx.get("day_number")));
+			String seqOrder = colIdx.containsKey("sequence_order") ? readCell(row.getCell(colIdx.get("sequence_order"))) : null;
+			String code = readCell(row.getCell(colIdx.get("partner_code")));
+			if (beatName != null && !beatName.trim().isEmpty()) {
+				// Start of a new beat — capture inheritable fields
+				currentBeatName = beatName.trim().replaceAll("\\s+", " ");
+				currentAuthId = authId != null ? authId.trim() : null;
+				currentStartDate = (startDate != null && !startDate.trim().isEmpty()) ? startDate.trim() : null;
+				currentKey = currentBeatName + "|" + currentAuthId;
+			}
+			if (currentKey == null) continue; // partner row before any beat header — skip
+			if (code == null || code.trim().isEmpty()) continue;
+			final String beatNameF = currentBeatName;
+			final String authIdF = currentAuthId;
+			final String startDateF = currentStartDate;
+			BulkBeatGroup g = beatGroups.computeIfAbsent(currentKey, k -> new BulkBeatGroup(beatNameF, authIdF, startDateF));
+			int day;
+			try {
+				day = Integer.parseInt(dayNumber.trim());
+			} catch (Exception e) {
+				continue;
+			} // bad day → skip row
+			int seq = -1;
+			if (seqOrder != null && !seqOrder.trim().isEmpty()) {
+				try {
+					seq = Integer.parseInt(seqOrder.trim());
+				} catch (Exception ignore) {
+				}
+			}
+			g.addPartner(day, seq, code.trim(), r + 1);
+		}
+		workbook.close();
 		List<FofoStore> allStores = fofoStoreRepository.selectAll();
 		Map<String, Integer> codeToId = new HashMap<>();
 		for (FofoStore store : allStores) codeToId.put(store.getCode(), store.getId());
 		LocalDate holidayStart = LocalDate.now();
 		Set<LocalDate> holidayDates = holidays.stream().map(PublicHolidays::getDate).collect(Collectors.toSet());
 		int beatsCreated = 0, errors = 0;
 		List<String> errorMessages = new ArrayList<>();
-		for (Map.Entry<String, List<org.apache.commons.csv.CSVRecord>> entry : beatGroups.entrySet()) {
+		for (BulkBeatGroup g : beatGroups.values()) {
 			try {
-				String[] keyParts = entry.getKey().split("\\|");
+				String beatName = g.beatName;
-				String beatName = keyParts[0];
+				int authUserId;
+				try {
-				int authUserId = Integer.parseInt(keyParts[1]);
+					authUserId = Integer.parseInt(g.authUserId);
-				List<org.apache.commons.csv.CSVRecord> rows = entry.getValue();
+				} catch (Exception e) {
-				rows.sort((a, b) -> Integer.parseInt(a.get("day_number").trim()) - Integer.parseInt(b.get("day_number").trim()));
+					errorMessages.add("Beat '" + beatName + "': invalid auth_user_id '" + g.authUserId + "'. Skipped.");
+					errors++;
+					continue;
+				}
-				String startDateStr = rows.get(0).get("start_date").trim();
-				LocalDate startDate = startDateStr.isEmpty() ? null : LocalDate.parse(startDateStr, DateTimeFormatter.ISO_DATE);
+				LocalDate startDate = (g.startDate == null) ? null : LocalDate.parse(g.startDate, DateTimeFormatter.ISO_DATE);
 				if (startDate != null && startDate.isBefore(LocalDate.now())) {
 					errorMessages.add("Beat '" + beatName + "': start_date in past. Skipped.");
 					errors++;
 					continue;
+				}
+				List<Integer> sortedDays = new ArrayList<>(g.dayToPartners.keySet());
+				Collections.sort(sortedDays);
 				List<LocalDate> scheduleDates = new ArrayList<>();
 				if (startDate != null) {
 					LocalDate d = startDate;
-					while (scheduleDates.size() < rows.size()) {
+					while (scheduleDates.size() < sortedDays.size()) {
-						if (holidayDates.contains(d)) {
-							d = d.plusDays(1);
-							continue;
-						}
-						if (d.getDayOfWeek() == DayOfWeek.SUNDAY && !includeSundays) {
+						if (holidayDates.contains(d) || (d.getDayOfWeek() == DayOfWeek.SUNDAY && !includeSundays)) {
 							d = d.plusDays(1);
 							continue;
+						}
 						scheduleDates.add(d);
 						d = d.plusDays(1);
+					}
+				}
-				// Duplicate check — skip if a beat with same name already exists for this user
+				// Duplicate check
 				boolean isDuplicate = beatRepository.selectByAuthUserId(authUserId).stream()
 						.anyMatch(b -> b.getName() != null && beatName.equalsIgnoreCase(b.getName().trim()));
 				if (isDuplicate) {
 					errorMessages.add("Beat '" + beatName + "' already exists for user " + authUserId + ". Skipped.");
 					errors++;
+				}
 				String beatColor = BEAT_COLORS[Math.abs(beatName.hashCode()) % BEAT_COLORS.length];
 				AuthUserLocation homeLoc = authUserLocationRepository.selectLatestByAuthUserIdAndType(authUserId, "BASE");
-				// Create Beat master
 				Beat beat = new Beat();
 				beat.setName(beatName);
 				beat.setAuthUserId(authUserId);
 				beat.setBeatColor(beatColor);
-				beat.setTotalDays(rows.size());
+				beat.setTotalDays(sortedDays.size());
 				beat.setStartLocationName(homeLoc != null ? homeLoc.getLocationName() : "Home");
 				beat.setStartLatitude(homeLoc != null ? homeLoc.getLatitude() : null);
 				beat.setStartLongitude(homeLoc != null ? homeLoc.getLongitude() : null);
 				beat.setActive(true);
 				beat.setCreatedBy(currentUser.getId());
 				beat.setCreatedTimestamp(LocalDateTime.now());
 				beatRepository.persist(beat);
-				for (int rowIdx = 0; rowIdx < rows.size(); rowIdx++) {
+				LocalDate bulkEndDate = scheduleDates.isEmpty() ? null : scheduleDates.get(scheduleDates.size() - 1);
-					org.apache.commons.csv.CSVRecord row = rows.get(rowIdx);
+				for (int dayIdx = 0; dayIdx < sortedDays.size(); dayIdx++) {
-					int dayNumber = Integer.parseInt(row.get("day_number").trim());
+					int dayNumber = sortedDays.get(dayIdx);
-					LocalDate planDate = (rowIdx < scheduleDates.size()) ? scheduleDates.get(rowIdx) : null;
+					LocalDate planDate = (dayIdx < scheduleDates.size()) ? scheduleDates.get(dayIdx) : null;
-					LocalDate bulkEndDate = scheduleDates.isEmpty() ? null : scheduleDates.get(scheduleDates.size() - 1);
-					// Always create schedule — placeholder date (9999-12-31) when unscheduled
 					BeatSchedule schedule = new BeatSchedule();
 					schedule.setBeatId(beat.getId());
 					schedule.setStartDate(planDate != null ? planDate : LocalDate.of(9999, 12, 31));
 					schedule.setEndDate(bulkEndDate);
 					schedule.setDayNumber(dayNumber);
-					schedule.setEndAction(rowIdx == rows.size() - 1 ? "HOME" : "DAYBREAK");
+					schedule.setEndAction(dayIdx == sortedDays.size() - 1 ? "HOME" : "DAYBREAK");
 					schedule.setCreatedTimestamp(LocalDateTime.now());
 					beatScheduleRepository.persist(schedule);
-					String[] partnerCodes = row.get("partner_codes").trim().split(",");
+					List<BulkPartner> partners = g.dayToPartners.get(dayNumber);
+					// Sort by explicit sequence_order when present, else by row order
+					partners.sort((a, b) -> {
-					for (int i = 0; i < partnerCodes.length; i++) {
+						if (a.seq >= 0 && b.seq >= 0) return Integer.compare(a.seq, b.seq);
-						String code = partnerCodes[i].trim();
+						if (a.seq >= 0) return -1;
-						if (code.isEmpty()) continue;
+						if (b.seq >= 0) return 1;
+						return Integer.compare(a.rowNum, b.rowNum);
+					});
+					int autoSeq = 0;
+					for (BulkPartner p : partners) {
-						Integer fofoId = codeToId.get(code);
+						Integer fofoId = codeToId.get(p.code);
 						if (fofoId == null) {
-							errorMessages.add("Code not found: " + code);
+							errorMessages.add("Row " + p.rowNum + ": code not found '" + p.code + "'");
 							errors++;
 							continue;
+						}
 						BeatRoute route = new BeatRoute();
 						route.setBeatId(beat.getId());
 						route.setFofoId(fofoId);
-						route.setSequenceOrder(i);
+						route.setSequenceOrder(p.seq >= 0 ? p.seq : autoSeq);
 						route.setDayNumber(dayNumber);
 						route.setActive(true);
 						beatRouteRepository.persist(route);
+						autoSeq++;
+					}
+				}
 				beatsCreated++;
 			} catch (Exception e) {
 				errors++;
-				errorMessages.add("Error: " + entry.getKey() + " - " + e.getMessage());
+				errorMessages.add("Error: " + g.beatName + " - " + e.getMessage());
+			}
+		}
 		Map<String, Object> response = new HashMap<>();
 		response.put("status", true);
 		response.put("errors", errors);
 		response.put("errorMessages", errorMessages);
 		return responseSender.ok(response);
+	}
+	private static class BulkBeatGroup {
+		final String beatName;
+		final String authUserId;
+		final String startDate;
+		final Map<Integer, List<BulkPartner>> dayToPartners = new LinkedHashMap<>();
+		BulkBeatGroup(String beatName, String authUserId, String startDate) {
+			this.beatName = beatName;
+			this.authUserId = authUserId;
+			this.startDate = startDate;
+		}
+		void addPartner(int day, int seq, String code, int rowNum) {
+			dayToPartners.computeIfAbsent(day, k -> new ArrayList<>()).add(new BulkPartner(seq, code, rowNum));
+		}
+	}
+	private static class BulkPartner {
+		final int seq;
+		final String code;
+		final int rowNum;
+		BulkPartner(int seq, String code, int rowNum) {
+			this.seq = seq;
+			this.code = code;
+			this.rowNum = rowNum;
+		}
+	}
 	// ============ CALENDAR ============
 	@PostMapping(value = "/beatPlan/delete")
 	public ResponseEntity<?> deleteBeat(@RequestParam String planGroupId) {
 		int beatId = Integer.parseInt(planGroupId);

Subversion Repositories SmartDukaan

(root)/trunk/profitmandi-fofo/src/main/java/com/spice/profitmandi/web/controller/BeatPlanController.java – Rev 36663 → 36668