Subversion Repositories SmartDukaan

package com.spice.profitmandi.web.controller;

import com.google.gson.Gson;

import com.jcraft.jsch.*;

import com.spice.profitmandi.common.exception.ProfitMandiBusinessException;

import com.spice.profitmandi.common.solr.SolrService;

import com.spice.profitmandi.dao.entity.auth.AuthUser;

import com.spice.profitmandi.dao.entity.catalog.Catalog;

import com.spice.profitmandi.dao.entity.catalog.Item;

import com.spice.profitmandi.dao.entity.catalog.MonitorType;

import com.spice.profitmandi.dao.entity.cs.Bulletin;

import com.spice.profitmandi.dao.entity.cs.PartnerRegion;

import com.spice.profitmandi.dao.entity.cs.Region;

import com.spice.profitmandi.dao.entity.dtr.DocumentUrl;

import com.spice.profitmandi.dao.entity.fofo.PrintResource;

import com.spice.profitmandi.dao.entity.fofo.PrintResourceRegion;

import com.spice.profitmandi.dao.entity.user.User;

import com.spice.profitmandi.dao.enumuration.inventory.CatalogMovingEnum;

import com.spice.profitmandi.dao.model.*;

import com.spice.profitmandi.dao.repository.auth.AuthRepository;

import com.spice.profitmandi.dao.repository.catalog.*;

import com.spice.profitmandi.dao.repository.cs.BulletinRepository;

import com.spice.profitmandi.dao.repository.cs.PartnerRegionRepository;

import com.spice.profitmandi.dao.repository.cs.RegionRepository;

import com.spice.profitmandi.dao.repository.dtr.DocumentUrlRepository;

import com.spice.profitmandi.dao.repository.dtr.Mongo;

import com.spice.profitmandi.dao.repository.fofo.PrintResourceRegionRepository;

import com.spice.profitmandi.dao.repository.fofo.PrintResourceRepository;

import com.spice.profitmandi.dao.repository.user.UserRepository;

import com.spice.profitmandi.service.catalog.CategorySpecDerivationService;

import com.spice.profitmandi.service.catalog.CategorySpecUtil;

import com.spice.profitmandi.service.catalog.ItemLoaderService;

import com.spice.profitmandi.web.model.EntityMediaPojo;

import com.spice.profitmandi.web.model.LoginDetails;

import com.spice.profitmandi.web.util.CookiesProcessor;

import com.spice.profitmandi.web.util.MVCResponseSender;

import org.apache.commons.csv.CSVFormat;

import org.apache.commons.csv.CSVParser;

import org.apache.commons.csv.CSVRecord;

import org.apache.commons.lang3.StringUtils;

import org.apache.logging.log4j.LogManager;

import org.apache.logging.log4j.Logger;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.json.JSONArray;

import org.json.JSONObject;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.http.HttpHeaders;

import org.springframework.http.HttpStatus;

import org.springframework.http.MediaType;

import org.springframework.http.ResponseEntity;

import org.springframework.stereotype.Controller;

import org.springframework.transaction.annotation.Transactional;

import org.springframework.ui.Model;

import org.springframework.web.bind.annotation.*;

import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;

import javax.xml.bind.DatatypeConverter;

import java.io.*;

import java.sql.Timestamp;

import java.time.LocalDate;

import java.time.LocalDateTime;

import java.time.ZoneOffset;

import java.util.*;

import java.util.stream.Collectors;

@Transactional(rollbackFor = Throwable.class)

@Controller

public class ContentController {

	@Autowired

	MVCResponseSender mvcResponseSender;

	@Autowired

	Mongo mongoClient;

	@Autowired

	SolrService solrService;

	@Autowired

	ItemRepository itemRepository;

	@Autowired

	DocumentUrlRepository documentUrlRepository;

	@Autowired

	FocusedModelRepository focusedModelRepository;

	@Autowired

	PrintResourceRepository printResourceRepository;

	@Autowired

	AuthRepository authRepository;

	@Autowired

	RegionRepository regionRepository;

	@Autowired

	PrintResourceRegionRepository printResourceRegionRepository;

	@Autowired

	PartnerRegionRepository partnerRegionRepository;

	@Autowired

	SuperCatalogRepository superCatalogRepository;

	@Autowired

	CatalogRepository catalogRepository;

	@Autowired

	CatalogMonitorTypeRepository catalogMonitorTypeRepository;

	@Autowired

	CategorySpecDefinitionRepository categorySpecDefinitionRepository;

	@Autowired

	BulletinRepository bulletinRepository;

	private Gson gson = new Gson();

	public static final int Entity_Id = 0;

	public static final int Title = 1;

	public static final int KeySpec1 = 2;

	public static final int KeySpec2 = 3;

	public static final int KeySpec3 = 4;

	public static final int KeySpec4 = 5;

	public static final int Warranty = 6;

	public static final int Package_Contents = 7;

	// new-layout template only: SpecValue1..5 at 8-12, detailed specs from 13.

	// Legacy templates (no SpecValue header at col 8) keep detailed specs at 8.

	public static final int SpecValue1 = 8;

	public static final int SPEC_VALUE_COUNT = 5;

	public static final int DetailedSpecsLegacyStart = 8;

	public static final int DetailedSpecsSpecLayoutStart = 13;

	private static final String IMAGE_REMOTE_DIR = "/var/www/dtrdashboard/uploads/campaigns/";

	private static final String IMAGE_STATIC_SERVER_URL = "https://images.smartdukaan.com/uploads/campaigns";

	private static final String REMOTE_DIR = "/var/www/static.saholic.com/images/media/";

	// private static final String REMOTE_DIR = "/tmp";

	private static final String STATIC_SERVER_URL = "https://static%d.smartdukaan.com/images/media/";

	private static final String THUMBNAIL = "thumbnail";

	private static final String ICON = "icon";

	private static final String DEFAULT = "default";

	private static final String NONE = "";

	private static final Logger LOGGER = LogManager.getLogger(ContentController.class);

	private String getFileName(int entityId, String description, String extension) {

		List<Item> items = itemRepository.selectAllByCatalogItemId(entityId);

		String imageString = getMediaPrefix(items.get(0).getItemDescriptionNoColor() + " " + description) + "-"

				+ LocalDateTime.now().toEpochSecond(ZoneOffset.ofHoursMinutes(5, 30));

		return imageString + "." + extension;

	}

	@PostMapping(value = "/content/upload")

	public String uploadContent(HttpServletRequest request, @RequestPart("file") MultipartFile file, Model model)

			throws Exception {

		List<ContentPojo> contentPojos = readFile(file);

		if (contentPojos.size() < 1) {

			throw new RuntimeException("please check your file first ...may be content are wrong");

		}

		for (ContentPojo contentPojo : contentPojos) {

			mongoClient.persistEntity(contentPojo);

		}

		//List<SuperCatalog> superCatalog = superCatalogRepository.selectAll();

		model.addAttribute("response1", mvcResponseSender.createResponseString(true));

		return "response";

	}

	@Autowired

	ItemLoaderService itemLoaderService;

	@PostMapping(value = "/catalog/upload")

	public String uploadCatalog(HttpServletRequest request, @RequestPart("file") MultipartFile file, Model model)

			throws Exception {

		itemLoaderService.parse(file);

		model.addAttribute("response1", mvcResponseSender.createResponseString(true));

		return "response";

	}

	// column order must match ItemLoaderService.createLoaderModel

	private static final List<String> CATALOG_TEMPLATE_HEADERS = Arrays.asList("Product Group",

			"Category Id As per System", "Category", "HSNCode(Ensure that hsncode exists)", "Brand", "Model Number",

			"Model Name", "Color", "MRP", "SP", "MOP", "DP", "TP", "NLC", "Startdate", "Preferred Vendor", "Risky",

			"Weight in Kg", "Item type(1 for Serialized and 2 for non-serialized)", "TaxRate",

			"Brand Identifier (OEM model/SKU code - optional)");

	@GetMapping(value = "/catalog/template.xlsx")

	public ResponseEntity<byte[]> downloadCatalogTemplate() throws Exception {

		try (XSSFWorkbook workbook = new XSSFWorkbook(); ByteArrayOutputStream baos = new ByteArrayOutputStream()) {

			XSSFSheet sheet = workbook.createSheet("items");

			XSSFRow headerRow = sheet.createRow(0);

			for (int i = 0; i < CATALOG_TEMPLATE_HEADERS.size(); i++) {

				headerRow.createCell(i).setCellValue(CATALOG_TEMPLATE_HEADERS.get(i));

				sheet.setColumnWidth(i, Math.min(CATALOG_TEMPLATE_HEADERS.get(i).length() + 2, 60) * 256);

			}

			sheet.createFreezePane(0, 1);

			workbook.write(baos);

			HttpHeaders headers = new HttpHeaders();

			headers.setContentType(MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));

			headers.setContentDispositionFormData("catalog-template.xlsx", "catalog-template.xlsx");

			return new ResponseEntity<>(baos.toByteArray(), headers, HttpStatus.OK);

		}

	}

	@Autowired

	CategorySpecDerivationService categorySpecDerivationService;

	// one-time backfill trigger; safe to re-run (skips docs that already have specs)

	@PostMapping(value = "/content/specs/derive")

	public String deriveCategorySpecs(@RequestParam("categoryId") int categoryId, Model model) throws Exception {

		int updated = categorySpecDerivationService.deriveForCategory(categoryId);

		model.addAttribute("response1", mvcResponseSender.createResponseString(true) + " updated=" + updated);

		return "response";

	}

	@PostMapping(value = "/content/media/upload")

	public String uploadMediaContent(HttpServletRequest request, @RequestBody EntityMediaPojo entityMediaPojo,

			Model model) throws Exception {

		ContentPojo contentPojo = mongoClient.getEntityById(entityMediaPojo.getEntityId());

		Map<String, InputStream> fileStreamsMap = getStreamFileMap(contentPojo, entityMediaPojo);

		LOGGER.info("fileStreamsMap {}, " + fileStreamsMap.keySet());

		uploadContentFiles(fileStreamsMap, entityMediaPojo.getEntityId());

		mongoClient.persistEntity(contentPojo);

		model.addAttribute("response1", mvcResponseSender.createResponseString(true));

		return "response";

	}

	@PostMapping(value = "/content/video/upload")

	public String uploadVideoUrls(HttpServletRequest request,

								  @RequestPart("file") MultipartFile file,

								  Model model) throws Exception {

		List<VideoUrlPojo> videoRows = readVideoFile(file);

		LOGGER.info("Received {} video rows", videoRows.size());

		// group by catalogId

		Map<Integer, List<VideoUrlPojo>> videoCatalogMap =

				videoRows.stream().collect(Collectors.groupingBy(VideoUrlPojo::getCatalogId));

		LOGGER.info("videoCatalogMap {}", videoCatalogMap);

		for (Map.Entry<Integer, List<VideoUrlPojo>> entry : videoCatalogMap.entrySet()) {

			int catalogId = entry.getKey();

			LOGGER.info("catalogId {}", catalogId);

			List<VideoUrlPojo> rowsForCatalog = entry.getValue();

			ContentPojo content = mongoClient.getEntityById(catalogId);

			if (content == null) {

				LOGGER.warn("No content found for catalogId {}", catalogId);

				continue;

			}

			updateVideoUrls(content, rowsForCatalog);

			mongoClient.persistEntity(content);

			LOGGER.info("Updated video URLs for catalogId {}", catalogId);

		}

		model.addAttribute("response1", mvcResponseSender.createResponseString(true));

		return "response";

	}

	private void updateVideoUrls(ContentPojo content, List<VideoUrlPojo> rows) {

		if (content.getVideoUrls() == null) {

			content.setVideoUrls(new ArrayList<>());

		}

		List<VideoUrlsContentPojo> existing = content.getVideoUrls();

		for (VideoUrlPojo row : rows) {

			VideoUrlsContentPojo newUrl = new VideoUrlsContentPojo(row.getUrl());

			// add only if not duplicate

			if (!existing.contains(newUrl)) {

				existing.add(newUrl);

			}

		}

		content.setVideoUrls(existing);

		LOGGER.info("Final video list = {}", existing);

	}

	@PostMapping(value = "/image/media/upload")

	public String uploadImageMediaContent(HttpServletRequest request, @RequestBody MediaPojo mediaPojo, Model model)

			throws Exception {

		LOGGER.info("mediaPojo" + mediaPojo);

		Map<String, InputStream> fileStreamsMap = new HashMap<>();

		String extension;

		String base64String = mediaPojo.getImageData();

		String[] strings = base64String.split(",");

		switch (strings[0]) {// check image's extension

		case "data:image/jpeg;base64":

			extension = "jpeg";

			break;

		case "data:image/png;base64":

			extension = "png";

			break;

			case "data:image/webp;base64":

				extension = "webp";

				break;

		default:// should write cases for more images types

			extension = "jpg";

			break;

		}

		LOGGER.info("After switch statement = {}", extension);

		// convert base64 string to binary data

		byte[] data = DatatypeConverter.parseBase64Binary(strings[1]);

		Timestamp tm = new Timestamp(System.currentTimeMillis());

		String urlTitle = mediaPojo.getTitle().toLowerCase();

		String fileName = urlTitle.replace(' ', '-') + tm.getTime() + "." + extension;

		LOGGER.info("After switch statement Filename = {}", fileName);

		mediaPojo.setImageData(null);

		mediaPojo.setUrl(IMAGE_STATIC_SERVER_URL + "/" + "image" + LocalDate.now() + "/" + fileName);

		fileStreamsMap.put(fileName, new ByteArrayInputStream(data));

		LOGGER.info("fileStreamsMap" + fileStreamsMap);

		uploadFile(fileStreamsMap);

		DocumentUrl du = new DocumentUrl();

		du.setTitle(mediaPojo.getTitle());

		du.setUrl(mediaPojo.getUrl());

		du.setCreatedTimestamp(LocalDateTime.now());

		documentUrlRepository.persist(du);

		model.addAttribute("documents", du);

		return "image-url";

	}

	private void fileUpload(ChannelSftp channelSftp, Map<String, InputStream> streamsFileMap, String destinationPath)

			throws SftpException, FileNotFoundException {

		channelSftp.cd(destinationPath);

		String folderName = "image" + LocalDate.now();

		channelSftp.cd(destinationPath);

		SftpATTRS attrs = null;

		// check if the directory is already existing

		try {

			attrs = channelSftp.stat(folderName);

		} catch (Exception e) {

			System.out.println(destinationPath + "/" + "image" + LocalDate.now() + " not found");

		}

		// else create a directory

		if (attrs == null) {

			channelSftp.mkdir(folderName);

			channelSftp.chmod(0755, ".");

		}

		channelSftp.cd(folderName);

		for (Map.Entry<String, InputStream> streamsFileEntry : streamsFileMap.entrySet()) {

			channelSftp.put(streamsFileEntry.getValue(), streamsFileEntry.getKey(), ChannelSftp.OVERWRITE);

		}

	}

	@GetMapping(value = "/search/image/media")

	public String getImage(HttpServletRequest request, Model model, @RequestParam String title) throws Exception {

		List<DocumentUrl> documents = documentUrlRepository.selectBySearch(title);

		model.addAttribute("documents", documents);

		return "image-url";

	}

	@GetMapping(value = "/content/media")

	public String getMediaContent(HttpServletRequest request, Model model, @RequestParam int entityId)

			throws Exception {

		ContentPojo contentPojo = mongoClient.getEntityById(entityId);

		if (contentPojo == null) {

			throw new Exception("Please add content first");

		}

		EntityMediaPojo empojo = getEntityMediaPojo(contentPojo);

		model.addAttribute("response1", mvcResponseSender.createResponseString(empojo));

		return "response";

	}

	@GetMapping(value = "/catalog-item")

	public String catalogItem(HttpServletRequest request, Model model) throws Exception {

		return "catalog-item";

	}

	private EntityMediaPojo getEntityMediaPojo(ContentPojo contentPojo) {

		EntityMediaPojo ep = new EntityMediaPojo();

		int defaultIndex = 0;

		if (contentPojo.getImages() == null) {

			ep.setMediaPojos(new ArrayList<>());

		} else {

			ep.setMediaPojos(contentPojo.getImages());

			for (MediaPojo mediaPojo : contentPojo.getImages()) {

				if (mediaPojo.getUrl() == null)

					continue;

				if (!mediaPojo.getUrl().equals(contentPojo.getDefaultImageUrl())) {

					defaultIndex++;

				}

			}

		}

		ep.setDefaultImageIndex(defaultIndex);

		return ep;

	}

	/**

	 * Band-aid for URL '+' decode issue: when the front-end sends `brand=Ai+` the servlet

	 * decodes the plus as a space and we receive "Ai " with a trailing space, missing the

	 * real "Ai+" brand. Restore trailing whitespace back to "+" so lookups match.

	 * Front-end should URL-encode with %2B (encodeURIComponent) — this is a temporary

	 * safety net until the mobile team ships that fix.

	 */

	private List<String> normalizeBrandNames(List<String> brands) {

		if (brands == null || brands.isEmpty()) return brands;

		return brands.stream()

				.map(b -> (b != null && b.endsWith(" ")) ? b.replaceAll("\\s+$", "") + "+" : b)

				.collect(Collectors.toList());

	}

	@GetMapping(value = "/entity")

	public String searchEntity(HttpServletRequest request, @RequestParam(defaultValue = "null") String query,

			@RequestParam(defaultValue = "0") int categoryId, @RequestParam(defaultValue = "") List<String> brands,

			@RequestParam(defaultValue = "30") int limit, @RequestParam(defaultValue = "false") boolean activeOnly,

			Model model) throws Exception {

		brands = normalizeBrandNames(brands);

		model.addAttribute("response1", solrService.getContent(query, categoryId > 0 ? Arrays.asList(categoryId) : null,

				brands, limit, activeOnly));

		return "response";

	}

	@GetMapping(value = "/focusedEntity")

	public String focusedEntity(HttpServletRequest request, @RequestParam(defaultValue = "null") String query,

			@RequestParam(defaultValue = "0") int categoryId, @RequestParam(defaultValue = "") List<String> brands,

			@RequestParam(defaultValue = "30") int limit, @RequestParam(defaultValue = "true") boolean activeOnly,

			Model model) throws Exception {

		brands = normalizeBrandNames(brands);

		List<Integer> catalogIds = focusedModelRepository.selectAll().stream().map(x -> x.getCatalogId())

				.collect(Collectors.toList());

		JSONArray docA = solrService.getContentDocs(query, categoryId > 0 ? Arrays.asList(categoryId) : null, brands,

				limit, activeOnly);

		Iterator<Object> jsonIterator = docA.iterator();

		while (jsonIterator.hasNext()) {

			JSONObject i = (JSONObject) jsonIterator.next();

			if (!catalogIds.contains(i.get("catalogId_i"))) {

				jsonIterator.remove();

			}

		}

		model.addAttribute("response1", docA.toString());

		return "response";

	}

	@GetMapping(value = "/statusWiseCatalogs")

	public String statusWiseCatalogs(HttpServletRequest request, @RequestParam(defaultValue = "null") String query,

									 @RequestParam(defaultValue = "0") int categoryId, @RequestParam(defaultValue = "") List<String> brands,

									 @RequestParam(defaultValue = "30") int limit, @RequestParam(defaultValue = "true") boolean activeOnly,

									 Model model) throws Exception {

		brands = normalizeBrandNames(brands);

		List<CatalogMovingEnum> catalogMovingEnums = Arrays.asList(CatalogMovingEnum.HID, CatalogMovingEnum.FASTMOVING, CatalogMovingEnum.RUNNING);

		List<Integer> catalogIds = catalogRepository.selectAllStatusAndBrandWise(brands, catalogMovingEnums);

		JSONArray docA = solrService.getContentDocs(query, categoryId > 0 ? Arrays.asList(categoryId) : null, brands,

				limit, activeOnly);

		Map<Integer, MonitorType> monitorTypeByCatalogId = catalogMonitorTypeRepository.selectAllAsMap();

		Iterator<Object> jsonIterator = docA.iterator();

		while (jsonIterator.hasNext()) {

			JSONObject i = (JSONObject) jsonIterator.next();

			Object cidObj = i.get("catalogId_i");

			if (!catalogIds.contains(cidObj)) {

				jsonIterator.remove();

				continue;

			}

			if (cidObj instanceof Number) {

				MonitorType mt = monitorTypeByCatalogId.get(((Number) cidObj).intValue());

				if (mt != null) i.put("monitor_type", mt.dbValue());

			}

		}

		model.addAttribute("response1", docA.toString());

		return "response";

	}

	@GetMapping(value = "/content/index")

	public String index(HttpServletRequest request, Model model) throws Exception {

		return "content";

	}

	private List<ContentPojo> readFile(MultipartFile file) throws Exception {

		CSVParser parser = new CSVParser(new InputStreamReader(file.getInputStream()), CSVFormat.DEFAULT);

		List<CSVRecord> records = parser.getRecords();

		if (records.size() < 2) {

			parser.close();

			throw new ProfitMandiBusinessException("Uploaded File", "", "No records Found");

		}

		// header decides the layout: SpecValue columns present at 8-12 or not

		CSVRecord header = records.get(0);

		boolean specLayout = header.size() > SpecValue1 && header.get(SpecValue1) != null

				&& header.get(SpecValue1).trim().toUpperCase().startsWith("SPECVALUE");

		// Remove header

		records.remove(0);

		List<ContentPojo> returnList = new ArrayList<ContentPojo>();

		for (CSVRecord record : records) {

			ContentPojo cp = null;

			Long entityId = Long.parseLong(record.get(Entity_Id));

			try {

				cp = mongoClient.getEntityById(entityId);

			} catch (Exception e) {

			}

			try {

				if (cp == null) {

					cp = new ContentPojo(entityId);

				}

				cp.setWarranty(record.get(Warranty));

				cp.setKeySpecs(Arrays

						.asList(record.get(KeySpec1), record.get(KeySpec2), record.get(KeySpec3), record.get(KeySpec4))

						.stream().filter(x -> x != null && !x.equals("")).collect(Collectors.toList()));

				cp.setPackageContents(Arrays.asList(record.get(Package_Contents).split(",")));

				cp.setDetailedSpecs(getDetailedSpecs(record,

						specLayout ? DetailedSpecsSpecLayoutStart : DetailedSpecsLegacyStart));

				if (cp.getCategoryId() == null) {

					Catalog catalog = catalogRepository.selectCatalogById(Math.toIntExact(entityId));

					if (catalog != null) {

						cp.setCategoryId(catalog.getCategoryId());

					}

				}

				if (specLayout) {

					List<String> specValues = new ArrayList<>();

					for (int col = SpecValue1; col < SpecValue1 + SPEC_VALUE_COUNT; col++) {

						specValues.add(record.size() > col ? record.get(col) : "");

					}

					boolean anyValue = specValues.stream().anyMatch(v -> v != null && !v.trim().isEmpty());

					if (anyValue) {

						if (cp.getCategoryId() == null) {

							throw new ProfitMandiBusinessException("CatalogId", String.valueOf(entityId),

									"Spec values supplied but catalog/category unknown");

						}

						cp.setCategorySpecs(CategorySpecUtil.pairValues(

								categorySpecDefinitionRepository.selectActiveByCategoryId(cp.getCategoryId()),

								specValues));

					}

					// all-blank spec columns: leave existing categorySpecs untouched

				}

				returnList.add(cp);

			} catch (ProfitMandiBusinessException e) {

				parser.close();

				throw new ProfitMandiBusinessException(e.getRejectedType(), e.getRejectedValue(),

						e.getCode() + " at row " + record.getRecordNumber());

			} catch (Exception e) {

				continue;

			}

		}

		parser.close();

		return returnList;

	}

	private List<VideoUrlPojo> readVideoFile(MultipartFile file) throws Exception {

		CSVParser parser = new CSVParser(

				new InputStreamReader(file.getInputStream()),

				CSVFormat.DEFAULT

		);

		List<CSVRecord> records = parser.getRecords();

		if (records.size() < 2) {

			parser.close();

			throw new ProfitMandiBusinessException("Uploaded File", "", "No records found");

		}

		// Remove header

		records.remove(0);

		List<VideoUrlPojo> returnList = new ArrayList<>();

		for (CSVRecord record : records) {

			try {

				// Must match CSV column order

				Long catalogId = Long.parseLong(record.get(1));

				String url = record.get(2);

				VideoUrlPojo row = new VideoUrlPojo();

				row.setCatalogId(Math.toIntExact(catalogId));

				row.setUrl(url);

				returnList.add(row);

			} catch (Exception e) {

				// skip bad rows, same as your style

				continue;

			}

		}

		parser.close();

		return returnList;

	}

	private List<SpecificationGroup> getDetailedSpecs(CSVRecord record, int startIndex) throws Exception {

		List<SpecificationGroup> specificationGroups = new ArrayList<>();

		int currentIndex = startIndex;

		while (StringUtils.isNotEmpty(record.get(currentIndex))) {

			int start = currentIndex;

			List<Specification> specifications = new ArrayList<>();

			int begin = 0;

			while (begin < 5) {

				int specKeyIndex = (begin * 2) + 1;

				int specValueIndex = (begin * 2) + 2;

				if (StringUtils.isNotEmpty(record.get(currentIndex + specKeyIndex))

						&& StringUtils.isNotEmpty(record.get(currentIndex + specValueIndex))) {

					Specification specification = new Specification(record.get(currentIndex + specKeyIndex),

							Arrays.asList(record.get(currentIndex + specValueIndex)));

					specifications.add(specification);

				}

				begin++;

			}

			SpecificationGroup specificationGroup = new SpecificationGroup(record.get(start), specifications);

			specificationGroups.add(specificationGroup);

			currentIndex += 11;

		}

		return specificationGroups;

	}

	private ChannelSftp setupJsch() throws JSchException {

		JSch jsch = new JSch();

		Session jschSession = jsch.getSession("root", "172.105.58.16");

		// Session jschSession = jsch.getSession("root", "173.255.254.24");

		LOGGER.info("getClass().getResource(\"id_rsa\") {}",

				getClass().getClassLoader().getResource("id_rsa").getPath());

		jsch.addIdentity(getClass().getClassLoader().getResource("id_rsa").getPath());

		// jschSession.setPassword("spic@2015static0");

		jschSession.setConfig("StrictHostKeyChecking", "no");

		jschSession.connect();

		return (ChannelSftp) jschSession.openChannel("sftp");

	}

	private void uploadFile(Map<String, InputStream> fileStreamsMap) throws Exception {

		ChannelSftp channelSftp = setupJsch();

		channelSftp.connect();

		this.fileUpload(channelSftp, fileStreamsMap, IMAGE_REMOTE_DIR + "");

		channelSftp.exit();

	}

	private void uploadContentFiles(Map<String, InputStream> fileStreamsMap, int entityId) throws Exception {

		ChannelSftp channelSftp = setupJsch();

		channelSftp.connect();

		this.folderUpload(channelSftp, fileStreamsMap, REMOTE_DIR, entityId + "");

		channelSftp.exit();

	}

	private void recursiveFolderUpload(ChannelSftp channelSftp, String sourcePath, String destinationPath)

			throws SftpException, FileNotFoundException {

		File sourceFile = new File(sourcePath);

		if (sourceFile.isFile()) {

			// copy if it is a file

			channelSftp.cd(destinationPath);

			if (!sourceFile.getName().startsWith("."))

				channelSftp.put(new FileInputStream(sourceFile), sourceFile.getName(), ChannelSftp.OVERWRITE);

		} else {

			System.out.println("inside else " + sourceFile.getName());

			File[] files = sourceFile.listFiles();

			if (files != null && !sourceFile.getName().startsWith(".")) {

				channelSftp.cd(destinationPath);

				SftpATTRS attrs = null;

				// check if the directory is already existing

				try {

					attrs = channelSftp.stat(destinationPath + "/" + sourceFile.getName());

				} catch (Exception e) {

					System.out.println(destinationPath + "/" + sourceFile.getName() + " not found");

				}

				// else create a directory

				if (attrs != null) {

					System.out.println("Directory exists IsDir=" + attrs.isDir());

				} else {

					System.out.println("Creating dir " + sourceFile.getName());

					channelSftp.mkdir(sourceFile.getName());

				}

				for (File f : files) {

					recursiveFolderUpload(channelSftp, f.getAbsolutePath(),

							destinationPath + "/" + sourceFile.getName());

				}

			}

		}

	}

	private void folderUpload(ChannelSftp channelSftp, Map<String, InputStream> streamsFileMap, String destinationPath,

			String folderName) throws SftpException, FileNotFoundException {

		channelSftp.cd(destinationPath);

		SftpATTRS attrs = null;

		// check if the directory is already existing

		try {

			attrs = channelSftp.stat(folderName);

		} catch (Exception e) {

			System.out.println(destinationPath + "/" + folderName + " not found");

		}

		// else create a directory

		if (attrs == null) {

			channelSftp.mkdir(folderName);

			channelSftp.chmod(0755, ".");

		}

		channelSftp.cd(folderName);

		channelSftp.rm("*");

		for (Map.Entry<String, InputStream> streamsFileEntry : streamsFileMap.entrySet()) {

			channelSftp.put(streamsFileEntry.getValue(), streamsFileEntry.getKey(), ChannelSftp.OVERWRITE);

		}

	}

	private Map<String, InputStream> getStreamFileMap(ContentPojo contentPojo, EntityMediaPojo entityMediaPojo) {

		Map<String, InputStream> map = new HashMap<>();

		LOGGER.info("entityMediaPojo.getMediaPojos() -[{}]", entityMediaPojo.getMediaPojos());

		for (int i = 0; i < entityMediaPojo.getMediaPojos().size(); i++) {

			MediaPojo mediaPojo = entityMediaPojo.getMediaPojos().get(i);

			String extension;

			String base64String = mediaPojo.getImageData();

			String[] strings = base64String.split(",");

			switch (strings[0]) {// check image's extension

			case "data:image/jpeg;base64":

				extension = "jpeg";

				break;

			case "data:image/png;base64":

				extension = "png";

				break;

			default:// should write cases for more images types

				extension = "jpg";

				break;

			}

			LOGGER.info("After switch statement = {}", extension);

			// convert base64 string to binary data

			byte[] data = DatatypeConverter.parseBase64Binary(strings[1]);

			String fileName = getFileName(entityMediaPojo.getEntityId(), mediaPojo.getTitle(), extension);

			LOGGER.info("After switch statement Filename = {}", fileName);

			mediaPojo.setImageData(null);

			String staticServerUrl = String.format(STATIC_SERVER_URL, entityMediaPojo.getEntityId() % 3);

			mediaPojo.setUrl(staticServerUrl + entityMediaPojo.getEntityId() + "/" + fileName);

			map.put(fileName, new ByteArrayInputStream(data));

			if (i == entityMediaPojo.getDefaultImageIndex()) {

				String defaultFileName = getFileName(entityMediaPojo.getEntityId(), DEFAULT, extension);

				map.put(defaultFileName, new ByteArrayInputStream(data));

				contentPojo.setDefaultImageUrl(staticServerUrl + entityMediaPojo.getEntityId() + "/" + defaultFileName);

			}

		}

		contentPojo.setImages(entityMediaPojo.getMediaPojos());

		return map;

	}

	private String getMediaPrefix(String productName) {

		String mediaPrefix = productName.toLowerCase().replace(' ', '-');

		mediaPrefix = mediaPrefix.replaceAll("/", "-");

		mediaPrefix = mediaPrefix.replaceAll("-+", "-");

		return mediaPrefix;

	}

	@GetMapping(value = "/urlGeneration")

	public String urlGeneration(HttpServletRequest request, Model model) throws Exception {

		return "url-generation";

	}

	@Autowired

	UserRepository userRepository;

	@Autowired

	private CookiesProcessor cookiesProcessor;

	@GetMapping(value = "/printResources")

	public String printResources(HttpServletRequest request, Model model) throws Exception {

		List<Region> regionList = regionRepository.selectAll();

		model.addAttribute("regionList", regionList);

		return "print-resource";

	}

	@RequestMapping(value = "/postPrintResource", method = RequestMethod.POST)

	public String postPrintResource(HttpServletRequest request, @RequestBody PrintResourceModel printResourceModel,

									Model model) throws Exception {

		LOGGER.info("printResourceModel {}", printResourceModel);

		PrintResource printResource = new PrintResource();

		printResource.setTitle(printResourceModel.getTitle());

		printResource.setStartDate(printResourceModel.getStartDate().atStartOfDay());

		if (printResourceModel.getEndDate() != null) {

			printResource.setEndDate(printResourceModel.getEndDate().atStartOfDay());

		} else {

			printResource.setEndDate(null);

		}

		printResource.setCreateTimestamp(LocalDateTime.now());

		printResource.setImgUrl(printResourceModel.getImgUrl());

		printResource.setThumbnailUrl(printResourceModel.getThumbnailUrl());

		printResourceRepository.persist(printResource);

		PrintResourceRegion printResourceRegion = new PrintResourceRegion();

		int regionId = Integer.parseInt(printResourceModel.getRegion());

		printResourceRegion.setRegionId(regionId);

		printResourceRegion.setResourceId(printResource.getId());

		printResourceRegionRepository.persist(printResourceRegion);

		model.addAttribute("response1", mvcResponseSender.createResponseString(true));

		return "response";

	}

	@RequestMapping(value = "/changeEndDate", method = RequestMethod.POST)

	public String changeEndDate(HttpServletRequest request, @RequestParam LocalDate date, @RequestParam int resourceId,

								Model model) throws Exception {

		PrintResource printResource = printResourceRepository.selectById(resourceId);

		printResource.setEndDate(date.atStartOfDay());

		model.addAttribute("response1", mvcResponseSender.createResponseString(true));

		return "response";

	}

	@GetMapping(value = "/partnerPrintResources")

	public String partnerPrintResources(HttpServletRequest request, Model model) throws Exception {

		LoginDetails loginDetails = cookiesProcessor.getCookiesObject(request);

		int fofoId = loginDetails.getFofoId();

		User user = userRepository.selectById(fofoId);

		List<PartnerRegion> partnerRegion = partnerRegionRepository.selectByfofoId(user.getId());

		LocalDateTime currentDate = LocalDateTime.now();

		List<PrintResource> printResources = printResourceRepository.selectPrintResourcesByFofoRegion(partnerRegion.get(0).getRegionId(), currentDate);

		LOGGER.info("printresourcesList {}", printResources);

		model.addAttribute("printResources", printResources);

		return "partner_print_resource";

	}

	@GetMapping(value = "/allPrintResources")

	public String allPrintResources(HttpServletRequest request, Model model) throws Exception {

		LocalDateTime currentDate = LocalDateTime.now();

		List<PrintResource> printResources = printResourceRepository.selectAllPrintResources(currentDate);

		model.addAttribute("printResources", printResources);

		return "all-print-resource";

	}

	@GetMapping("/content/video/check/{catalogId}")

	public ResponseEntity<?> checkVideo(@PathVariable int catalogId) throws Exception {

		ContentPojo cp = mongoClient.getEntityById(catalogId);

		LOGGER.info("Video Url {}", cp.getVideoUrls());

		return ResponseEntity.ok(cp.getVideoUrls());

	}

	@GetMapping(value = "/bulletin")

	public String bulletin(HttpServletRequest request, Model model) throws Exception {

		List<Region> regionList = regionRepository.selectAll();

		model.addAttribute("regionList", regionList);

		return "post-bulletin";

	}

	@RequestMapping(value = "/bulletinPost", method = RequestMethod.POST)

	public String bulletinPost(HttpServletRequest request, @RequestBody BulletinPostModel bulletinPostModel,

							   Model model) throws Exception {

		LoginDetails loginDetails = cookiesProcessor.getCookiesObject(request);

		String email = loginDetails.getEmailId();

		AuthUser authUser = authRepository.selectByEmailOrMobile(email);

		LOGGER.info("bulletinPostModel {}", bulletinPostModel);

		Bulletin bulletin = new Bulletin();

		bulletin.setRegionId(bulletinPostModel.getRegionId());

		bulletin.setDescription(bulletinPostModel.getDescription());

		bulletin.setDocumentIds(bulletinPostModel.getDocumentIds());

		bulletin.setCreatedAt(LocalDateTime.now());

		bulletin.setCreatedBy(authUser.getFullName());

		bulletinRepository.persist(bulletin);

		model.addAttribute("response1", mvcResponseSender.createResponseString(true));

		return "response";

	}

}