package com.mns.intl.pigw.partner.sales.processor;

import java.io.BufferedOutputStream;
import java.io.BufferedReader;
import java.io.FileOutputStream;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.io.StringReader;
import java.net.URISyntaxException;
import java.nio.charset.Charset;
import java.nio.file.Files;
import java.security.InvalidKeyException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;
import java.util.stream.Collectors;

import java.text.DateFormat;
import java.text.ParseException;
import javax.xml.XMLConstants;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.JsonMappingException;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.mns.intl.pigw.partner.sales.constants.ValidationConstants;
import com.mns.intl.pigw.partner.sales.validation.ItemStockValidator;
import com.mns.intl.pigw.partner.sales.validation.MnsReferenceFieldValidator;
import com.mns.intl.pigw.partner.sales.validation.StockFieldValidator;
import com.mns.intl.pigw.partner.sales.validation.StoreSubLocationValidator;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.FilenameUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.support.DefaultMessageSourceResolvable;
import org.springframework.stereotype.Component;
import org.springframework.validation.*;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.InputSource;

import com.microsoft.azure.functions.ExecutionContext;
import com.microsoft.azure.storage.CloudStorageAccount;
import com.microsoft.azure.storage.StorageException;
import com.microsoft.azure.storage.file.CloudFile;
import com.microsoft.azure.storage.file.CloudFileClient;
import com.microsoft.azure.storage.file.CloudFileDirectory;
import com.microsoft.azure.storage.file.CloudFileShare;
import com.mns.intl.pigw.azurefunctions.dto.CyprusDTO;
import com.mns.intl.pigw.azurefunctions.dto.CyprusMnsReferences;
import com.mns.intl.pigw.azurefunctions.dto.CyprusPriceDTO;
import com.mns.intl.pigw.azurefunctions.dto.CyprusPriceItems;
import com.mns.intl.pigw.azurefunctions.dto.CyprusPriceLevelDTO;
import com.mns.intl.pigw.azurefunctions.dto.CyprusPrices;
import com.mns.intl.pigw.azurefunctions.dto.EmailDTO;
import com.mns.intl.pigw.azurefunctions.dto.Header;
import com.mns.intl.pigw.azurefunctions.dto.Items;
import com.mns.intl.pigw.azurefunctions.dto.ItemsStock;
import com.mns.intl.pigw.azurefunctions.dto.MnsReferences;
import com.mns.intl.pigw.azurefunctions.dto.OrderPlacingChannel;
import com.mns.intl.pigw.azurefunctions.dto.Payload;
import com.mns.intl.pigw.azurefunctions.dto.PayloadCyprusPrice;
import com.mns.intl.pigw.azurefunctions.dto.PayloadPrice;
import com.mns.intl.pigw.azurefunctions.dto.PayloadStock;
import com.mns.intl.pigw.azurefunctions.dto.Price;
import com.mns.intl.pigw.azurefunctions.dto.PayloadSTO;
import com.mns.intl.pigw.azurefunctions.dto.ItemsSTO;
import com.mns.intl.pigw.azurefunctions.dto.LocationSTO;
import com.mns.intl.pigw.azurefunctions.dto.STOProductSiteLevelDTO;
import com.mns.intl.pigw.azurefunctions.dto.StockDetailsSTO;
import com.mns.intl.pigw.azurefunctions.dto.QuantityOfStockMoved;
import com.mns.intl.pigw.azurefunctions.dto.PriceDTO;
import com.mns.intl.pigw.azurefunctions.dto.PriceHeader;
import com.mns.intl.pigw.azurefunctions.dto.PriceItems;
import com.mns.intl.pigw.azurefunctions.dto.PriceProductSiteLevelDTO;
import com.mns.intl.pigw.azurefunctions.dto.PriceSellingChannel;
import com.mns.intl.pigw.azurefunctions.dto.QuantityStock;
import com.mns.intl.pigw.azurefunctions.dto.STOHeader;
import com.mns.intl.pigw.azurefunctions.dto.SaleReturnIndicator;
import com.mns.intl.pigw.azurefunctions.dto.SalesProductSiteLevelDTO;
import com.mns.intl.pigw.azurefunctions.dto.SiteAggregatedSales;
import com.mns.intl.pigw.azurefunctions.dto.SiteAggregatedStock;
import com.mns.intl.pigw.azurefunctions.dto.StockLocation;
import com.mns.intl.pigw.azurefunctions.dto.StockProductSiteLevelDTO;
import com.mns.intl.pigw.azurefunctions.dto.StockQuantity;
import com.mns.intl.pigw.azurefunctions.dto.StockType;
import com.mns.intl.pigw.azurefunctions.dto.TotalPrice;
import com.mns.intl.pigw.azurefunctions.dto.UnitPrice;
import com.mns.intl.pigw.azurefunctions.dto.XmlResponse;
import com.mns.intl.pigw.azurefunctions.dto.stockPrice;
import com.mns.intl.pigw.partner.sales.rest.RestClient;
import com.mns.intl.pigw.azurefunctions.utils.CsvUtil;
import com.mns.intl.pigw.azurefunctions.utils.FileUtil;
import com.mns.intl.pigw.partner.sales.CommonTransSalesdto.Cart;
import com.mns.intl.pigw.partner.sales.CommonTransSalesdto.PartnerSalesHeader;
import com.mns.intl.pigw.partner.sales.CommonTransSalesdto.PartnerSalesJsonDto;
import com.mns.intl.pigw.partner.sales.CommonTransSalesdto.PartnerSalesPayLoad;
import com.mns.intl.pigw.partner.sales.CommonTransSalesdto.TransactionalSales;
import com.mns.intl.pigw.partner.sales.TransactionalSalesBeamDTO.BeamSalesDTO;
import com.mns.intl.pigw.partner.sales.TransactionalSalesBeamDTO.BeamTransactionalSales;
import com.mns.intl.pigw.partner.sales.TransactionalSalesBeamDTO.BillingDetail;
import com.mns.intl.pigw.partner.sales.TransactionalSalesBeamDTO.CustomerDetail;
import com.mns.intl.pigw.partner.sales.TransactionalSalesBeamDTO.FulfillmentDetail;
import com.mns.intl.pigw.partner.sales.TransactionalSalesBeamDTO.OrderDetail;
import com.mns.intl.pigw.partner.sales.TransactionalSalesBeamDTO.OrderLine;
import com.mns.intl.pigw.partner.sales.TransactionalSalesBeamDTO.OrderLinePromotionDetail;
import com.mns.intl.pigw.partner.sales.TransactionalSalesBeamDTO.OrderPromotionDetail;
import com.mns.intl.pigw.partner.sales.TransactionalSalesBeamDTO.PaymentDetail;
import com.mns.intl.pigw.partner.sales.TransactionalSalesBeamDTO.ShipmentDetail;
import com.mns.intl.pigw.partner.sales.TransactionalSalesBeamDTO.TransactionalSalesHeader;
import com.mns.intl.pigw.partner.sales.TransactionalSalesBeamDTO.TransactionalSalesPayload;
import com.mns.intl.pigw.partner.sales.constants.Constants;
import com.mns.intl.pigw.partner.sales.constants.PriceValidationConstants;
import com.mns.intl.pigw.partner.sales.exception.EmptyFileException;
import com.mns.intl.pigw.partner.sales.exception.JSONGenerationFailure;
import com.mns.intl.pigw.partner.sales.processor.helper.PartnerSalesProcessorHelper;
import com.mns.intl.pigw.partner.sales.sql.queries.SQLQueries;
import com.opencsv.CSVParser;
import com.opencsv.CSVParserBuilder;
import com.opencsv.CSVReader;
import com.opencsv.CSVReaderBuilder;

@Component
public class PartnerSalesProcessor {

	private final Logger logger = LoggerFactory.getLogger(this.getClass());

	private Connection conn;

	long priceItemCount;

	long afgItemCount = 0;

	RestClient restClient = new RestClient();
    private PartnerSalesProcessorHelper partnerSalesProcessorHelper;

    public PartnerSalesProcessor(Connection conn, PartnerSalesProcessorHelper partnerSalesProcessorHelper) {
		this.conn = conn;
        this.partnerSalesProcessorHelper = partnerSalesProcessorHelper;
	}



	public PartnerSalesProcessor() {

	}

	public String process(String filename, String path) throws SQLException {

		String partnerCode = null;
		String partnerName = null;
		ResultSet resultSet = null;
		try (Statement statement = conn.createStatement()) {
			if (filename.contains(Constants.ZALANDO_FILENAME)) {
				resultSet = statement.executeQuery(SQLQueries.PARTNER_CONFIG_QUERY_ZALANDO);
				partnerName = Constants.ZALANDO;
			} else if (filename.contains(Constants.CZECH)) {
				resultSet = statement.executeQuery(SQLQueries.PARTNER_CONFIG_QUERY_CZECH);
				partnerName = Constants.CZECH;
			} else if (path.contains("9328") || filename.contains(Constants.INDIA)) {
				resultSet = statement.executeQuery(SQLQueries.PARTNER_CONFIG_QUERY_INDIA);
				partnerName = Constants.INDIA;
			} else if (path.contains("8251") || filename.contains(Constants.CYPRUSFILENAME)) {
				resultSet = statement.executeQuery(SQLQueries.PARTNER_CONFIG_QUERY_CYPRUS);
				partnerName = Constants.CYPRUS1;
			} else if (path.contains("8251") && filename.contains("Price")) {
				resultSet = statement.executeQuery(SQLQueries.PARTNER_CONFIG_QUERY_CYPRUS);
				partnerName = Constants.CYPRUS1;
			} else if (filename.contains(Constants.AFG)) {
				resultSet = statement.executeQuery(SQLQueries.PARTNER_CONFIG_QUERY_AFG);
				partnerName = Constants.AFG;
			}

			if (resultSet == null) {
				throw new NullPointerException("return set is null");
			}
			while (resultSet.next()) {
				partnerCode = resultSet.getString("partnerCode");
			}
		} catch (Exception e) {
			logger.error(Constants.DB_EXCEPTION, e);
		}

		insertStatus(partnerCode, partnerName, filename);
		logger.info("Partner Code {}", partnerCode);
		return partnerCode;
	}

	@SuppressWarnings({ "deprecation", "resource" })
	public String response(String filename, final ExecutionContext context, byte[] content, String partnerCode) {

		ResultSet fieldResultSet = null;
		ResultSet priceResultSet = null;
		Map<String, String> partnerSalesMap = new HashMap<>();
		List<String> destinationFieldName = new ArrayList<>();
		String finalJson = null;

		try (Statement fieldStatement = conn.createStatement()) {
			if (partnerCode.equals("9328")) {

				fieldResultSet = fieldStatement.executeQuery(SQLQueries.PARTNER_DESTINATION_FIELD_NAME_FOR_STOCK);

			} else if (partnerCode.equals("8251")) {
				fieldResultSet = fieldStatement
						.executeQuery(SQLQueries.CYPRUS_PARTNER_DESTINATION_FIELD_NAME_FOR_STOCK);
			} else if (partnerCode.equals("7197")) {

				fieldResultSet = fieldStatement.executeQuery(SQLQueries.PARTNER_SALES_TRANSACTIONAL_MAPPING);
			} else {
				fieldResultSet = fieldStatement.executeQuery(SQLQueries.PARTNER_DESTINATION_FIELD_NAME);
			}

			if (partnerCode.equals("7197")) {

				while (fieldResultSet.next()) {
					partnerSalesMap.put(fieldResultSet.getString("destinationFieldName"),
							fieldResultSet.getString("sourceFieldName"));
				}
			} else {
				while (fieldResultSet.next()) {
					destinationFieldName.add(fieldResultSet.getString("destinationFieldName"));
				}
			}
		} catch (Exception e) {
			logger.error(Constants.DB_EXCEPTION, e);
		}

		try {

			File file = FileUtil.createFile(Constants.PARTNER_SALES_FOLDER, Constants.PARTNER_SALES_FOLDER
					+ Constants.FOLDER_PATH_SEPARATOR + FilenameUtils.removeExtension(filename));

			Files.write(file.toPath(), content);
			String responseFile = FileUtils.readFileToString(file);

			List<String[]> allRows = null;

			if (partnerCode.equals("7197")) {
				return finalJson = responseForPartnerSales(responseFile, partnerSalesMap, partnerCode);

			}

			if (filename.contains(Constants.ZALANDO_FILENAME)) {
				String csvResponseFile = convesrionOfXml(responseFile);
				allRows = new CSVReader(new StringReader(csvResponseFile)).readAll();
			} else {
				FileReader fileReader = new FileReader(file);
				CSVParser parser = new CSVParserBuilder().withSeparator('|').build();
				CSVReader csvReader = new CSVReaderBuilder(fileReader).withCSVParser(parser).build();
				allRows = csvReader.readAll();
			}
			if (allRows.isEmpty() || allRows.size() == 1) {
				EmptyFileException emptyFileException = new EmptyFileException();
				emptyFileException.logErrorMessage();
				throw emptyFileException;

			}

			if (partnerCode.equals("8251") && filename.contains("Stock")) {
				finalJson = responseForCyprusStock(filename, context, content, partnerCode, destinationFieldName,
						allRows);
				return finalJson;
			}

			if (partnerCode.equals("8251") && filename.contains("Price")) {
                partnerSalesProcessorHelper.insertPriceAuditData(allRows, partnerCode, conn);
				finalJson = responseforCyprusPrice(filename, context, content, partnerCode, allRows);
				return finalJson;
			}

			if (partnerCode.equals("8251")) {
				finalJson = responseforCyprusSale(filename, context, content, partnerCode, allRows);
				return finalJson;
			}

			if (partnerCode.equals("9328")) {

				if (filename.contains("Price")) {
                    partnerSalesProcessorHelper.insertPriceAuditData(allRows, partnerCode, conn);
					finalJson = responseForPrice(filename, context, content, partnerCode, allRows);
				} else if (filename.contains("STO")) {
					finalJson = responseforSTO(filename, context, content, partnerCode, allRows);
				} else {
					System.out.println("Line NO 367");
					insertAuditData(allRows, partnerCode);
					finalJson = responseForStock(filename, context, content, partnerCode, destinationFieldName,
							allRows);
				}
				return finalJson;
			} else {
				String priceLevelIndicator = null;

				try (PreparedStatement priceStatement = conn.prepareStatement(SQLQueries.PARTNER_PRICEINDICATOR)) {
					priceStatement.setString(1, partnerCode);
					priceResultSet = priceStatement.executeQuery();
					while (priceResultSet.next()) {
						priceLevelIndicator = priceResultSet.getString("PriceLevelIndicator");
					}
				} catch (Exception e) {
					logger.error(Constants.DB_EXCEPTION, e);
				}

				List<Map<String, String>> fieldObjectList = new ArrayList<>();
				for (int i = 1; i < allRows.size(); i++) {

					String[] data = allRows.get(i);
					Map<String, String> fieldObject = new HashMap<>();

					fieldObject.put(destinationFieldName.get(0), data[1]); // Site Id
					fieldObject.put(destinationFieldName.get(1), data[2]); // Site Name
					fieldObject.put(destinationFieldName.get(2), data[0]); // Sale Date
					fieldObject.put(destinationFieldName.get(3), data[4]); // Product Id
					fieldObject.put(destinationFieldName.get(4), data[7]); // currency
					fieldObject.put(destinationFieldName.get(5), data[5]); // quantity
					fieldObject.put(destinationFieldName.get(6), data[6]); // net sale value incVAT
					fieldObject.put(destinationFieldName.get(7), data[10]); // promotion description
					fieldObject.put(destinationFieldName.get(8), data[8]); // discount value
					fieldObject.put(destinationFieldName.get(9), data[3]); // sale model

					if (partnerCode.equals("1604")) // Zalando
					{
						fieldObject.put(destinationFieldName.get(10), data[11]); // GTIN
						fieldObject.put(destinationFieldName.get(11), data[12]); // salesReturnType
					}
					fieldObjectList.add(fieldObject);
				}

				try {

					Integer itemCountSize = fieldObjectList.size();
					List<SiteAggregatedSales> siteList = new ArrayList<>();

					Map<String, List<Map<String, String>>> purchaseOrderGrpByMap = fieldObjectList.stream()
							.collect(Collectors.groupingBy(x -> x.get(destinationFieldName.get(0))));

					for (Map.Entry<String, List<Map<String, String>>> entries : purchaseOrderGrpByMap.entrySet()) {
						SiteAggregatedSales site = new SiteAggregatedSales();

						site.setSiteId(entries.getKey());

						List<Map<String, String>> responseList = entries.getValue();

						List<Items> itemsList = new ArrayList<>();

						String siteId = null;
						String saledate = null;
						String siteName = null;

						for (Map<String, String> list1 : responseList) {

							siteId = list1.get(destinationFieldName.get(0));
							saledate = list1.get(destinationFieldName.get(2));
							siteName = list1.get(destinationFieldName.get(1));

							Items items = new Items();
							MnsReferences mnsReferences = new MnsReferences();

							if (partnerCode.equals("1604")) {
								mnsReferences.setGtin(list1.get(destinationFieldName.get(10)));
							} else {
								mnsReferences.setArticleVariantId(list1.get(destinationFieldName.get(3)));
							}
							items.setMnsReferences(mnsReferences);

							String order = list1.get(destinationFieldName.get(9));

							if (order.contains("Zalando")) {
								items.setOrderPlacingChannel(OrderPlacingChannel.D2CSTORE);
							} else if (order.contains("Czech")) {
								items.setOrderPlacingChannel(OrderPlacingChannel.D2CONLINE);
							} else if (order.contains("B2B2C")) {
								items.setOrderPlacingChannel(OrderPlacingChannel.B2B2CONLINE);
							} else if (order.contains("B2C")) {
								items.setOrderPlacingChannel(OrderPlacingChannel.B2CONLINE);
							} else if (order.contains("B2B")) {
								items.setOrderPlacingChannel(OrderPlacingChannel.B2BONLINE);
							}

							if (partnerCode.equals("1604")) {
								String saleReturnType = list1.get(destinationFieldName.get(11));
								if (saleReturnType.equalsIgnoreCase("sales"))
									items.setSaleReturnIndicator(SaleReturnIndicator.SALE);
								else if (saleReturnType.equalsIgnoreCase("return"))
									items.setSaleReturnIndicator(SaleReturnIndicator.RETURN);
							} else {
								items.setSaleReturnIndicator(SaleReturnIndicator.SALE);
							}
							items.setCurrency(list1.get(destinationFieldName.get(4)));
							items.setQuantity(list1.get(destinationFieldName.get(5)));
							items.setPriceLevelIndicator(priceLevelIndicator);

							TotalPrice totalprice = new TotalPrice();

							totalprice.setNetSaleValueInclVAT(list1.get(destinationFieldName.get(6)));
							totalprice.setGrossDiscountValue(list1.get(destinationFieldName.get(8)));

							UnitPrice unitprice = new UnitPrice();
							unitprice.setPromotionDescription(list1.get(destinationFieldName.get(7)));

							Price price = new Price();
							price.setTotalPrice(totalprice);
							price.setUnitPrice(unitprice);
							if (partnerCode.equals("1604")) {
								items.setPrice(null);
							} else {
								items.setPrice(price);
							}
							itemsList.add(items);

						}
						SimpleDateFormat format = new SimpleDateFormat("yyyyMMdd");
						Date date = format.parse(saledate);
						SimpleDateFormat format2 = new SimpleDateFormat("yyyy-MM-dd");
						;
						String dateString2 = format2.format(date);

						if (partnerCode.equals("1604")) {
							site.setAggregatedSaleDate(saledate);
						} else {
							site.setAggregatedSaleDate(dateString2);
						}
						site.setSiteName(siteName);
						site.setConcatSiteId(partnerCode + siteId);
						site.setItems(itemsList);
						siteList.add(site);
					}
					String interfaceName = Constants.SALESINTERFACENAME;
					Payload payload = new Payload();
					payload.setSiteAggregatedSales(siteList);

					SalesProductSiteLevelDTO salesProduct = new SalesProductSiteLevelDTO();
					salesProduct.setHeader((headerResponse(partnerCode, itemCountSize, interfaceName)));
					salesProduct.setPayload(payload);

					finalJson = new ObjectMapper().writeValueAsString(salesProduct);
					return finalJson;

				} catch (Exception e) {
					JSONGenerationFailure jsonGenerationFailure = new JSONGenerationFailure(e);
					jsonGenerationFailure.logErrorMessage();
					logger.error("Error occured while genreating JSON", e);
				}
			}
		} catch (Exception e) {
			logger.error("Error occured while genreating fields from table", e);
		}

		return null;

	}

	public List<File> splitFile(File file, int size) throws IOException {

		int count = 1;
		List<File> files = new ArrayList<>();
		String ls = System.lineSeparator();
		int sizeOfChunk = 1024 * 1024 * size;
		try (BufferedReader br = new BufferedReader(new FileReader(file))) {
			String name = file.getName();
			String headers = br.readLine();
			String line = br.readLine();
			while (line != null) {
				DateTimeFormatter formatter = DateTimeFormatter.ofPattern("HHmmss");
				String timestamp = LocalDateTime.now().format(formatter);
				File newFile = new File(file.getParent(), name + timestamp + "_" + "part" + count++);
				FileOutputStream fileOutputStream = new FileOutputStream(newFile);
				try (BufferedOutputStream out = new BufferedOutputStream(fileOutputStream)) {
					int fileSize = 0;
					while (line != null) {
						byte[] bytes = (line + ls).getBytes(Charset.defaultCharset());
						if (fileSize + bytes.length > sizeOfChunk)
							break;
						out.write(bytes);
						fileSize += bytes.length;
						line = br.readLine();
					}
				}
				files.add(newFile);
			}
		}
		return files;

	}

	public String responseforCyprusSale(String filename, ExecutionContext context, byte[] content, String partnerCode,
			List<String[]> allRows) {

		StringBuilder csvContent = new StringBuilder();

		try {

			List<CyprusDTO> cyprusDTOList = new ArrayList<>();

			for (int i = 1; i < allRows.size(); i++) {

				String[] data = allRows.get(i);
				CyprusDTO fieldObject = new CyprusDTO();

				SimpleDateFormat inputFormat = new SimpleDateFormat("dd/MM/yy hh:mm");
				Date date = inputFormat.parse(data[2]);
				DateFormat outputFormat = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss");
				String formattedDate = outputFormat.format(date);
				fieldObject.setDATE(formattedDate);

				fieldObject.setSTORE(data[0].replaceFirst("0", ""));
				fieldObject.setARTICLE(data[3]);
				fieldObject.setDEPARTMENT("");
				fieldObject.setSTROKE("");

				String upc = data[15];
				if (upc.isEmpty()) {
					upc = "";
				} else if (upc.charAt(0) == '0') {
					upc = upc.substring(1);
				}
				fieldObject.setUPC(upc);
				fieldObject.setPARTNER("");
				fieldObject.setSOLD_TO("");

				float quantityFloat = Float.parseFloat(data[5]);

				String formattedQuantity = String.format("%.3f", quantityFloat);
				fieldObject.setQUANTITY(Float.parseFloat(formattedQuantity));

				fieldObject.setMRP(data[13]);
				fieldObject.setSELLING_PRICE(data[12]);
				fieldObject.setDISCOUNT(data[8]);
				fieldObject.setCOGS("");

				DateFormat inputFormat1 = new SimpleDateFormat("dd/MM/yy hh:mm");
				Date date1 = inputFormat1.parse(data[2]);
				DateFormat outputFormat1 = new SimpleDateFormat("dd-MMM-yyyy");
				String formattedDate1 = outputFormat1.format(date1);

				fieldObject.setINVOICE_DATE(formattedDate1);
				fieldObject.setTax_GST(data[10]);
				fieldObject.setGROSS_SALE(data[7]);
				fieldObject.setNET_MARGIN_VALUE("");

				if (data[14].equalsIgnoreCase("F") && quantityFloat > 0.0) {
					fieldObject.setCHANNEL("FPRETAIL SALE");
					fieldObject.setFULL_REDUCE("FULL");
                } else if (data[14].equalsIgnoreCase("F") && quantityFloat < 0.0) {
					fieldObject.setCHANNEL("FPRETAIL RETURN");
					fieldObject.setFULL_REDUCE("FULL");
                } else if (data[14].equalsIgnoreCase("R") && quantityFloat > 0.0) {
					fieldObject.setCHANNEL("RPRETAIL SALE");
					fieldObject.setFULL_REDUCE("REDUCE");
                } else {
					fieldObject.setCHANNEL("RPRETAIL RETURN");
					fieldObject.setFULL_REDUCE("REDUCE");
				}

				cyprusDTOList.add(fieldObject);
			}

			try {
				// Generate CSV content
				csvContent.append("#DATE").append("|").append("STORE").append("|").append("ARTICLE").append("|")
						.append("DEPARTMENT").append("|").append("STROKE").append("|").append("UPC").append("|")
						.append("CHANNEL").append("|").append("PARTNER").append("|").append("SOLD_TO").append("|")
						.append("FULL_REDUCE").append("|").append("QUANTITY").append("|").append("MRP").append("|")
						.append("SELLING_PRICE").append("|").append("DISCOUNT").append("|").append("COGS").append("|")
						.append("INVOICE_DATE").append("|").append("Tax_GST").append("|").append("GROSS_SALE")
						.append("|").append("NET_MARGIN_VALUE").append("\n");

				for (CyprusDTO dto : cyprusDTOList) {
					csvContent.append(dto.getDATE()).append("|").append(dto.getSTORE()).append("|")
							.append(dto.getARTICLE()).append("|").append(dto.getDEPARTMENT()).append("|")
							.append(dto.getSTROKE()).append("|").append(dto.getUPC()).append("|")
							.append(dto.getCHANNEL()).append("|").append(dto.getPARTNER()).append("|")
							.append(dto.getSOLD_TO()).append("|").append(dto.getFULL_REDUCE()).append("|")
							.append(dto.getQUANTITY()).append("|").append(dto.getMRP()).append("|")
							.append(dto.getSELLING_PRICE()).append("|").append(dto.getDISCOUNT()).append("|")
							.append(dto.getCOGS()).append("|").append(dto.getINVOICE_DATE()).append("|")
							.append(dto.getTax_GST()).append("|").append(dto.getGROSS_SALE()).append("|")
							.append(dto.getNET_MARGIN_VALUE()).append("\n");
				}
				System.out.println("CSV content:\n" + csvContent.toString());

				return csvContent.toString();

			} catch (Exception e) {
				logger.error("Error while genrating CSV file");
			}

		} catch (Exception e) {
			logger.error("Error occured while genreating file", e);
		}
		return null;
	}

	public String responseforSTO(String filename, ExecutionContext context, byte[] content, String partnerCode,
			List<String[]> allRows) {
		try {
			List<Map<String, String>> fieldObjectList = new ArrayList<>();
			List<String> locationId = new ArrayList<>();

			List<String> stoFieldName = getSTODestinationFieldName();

			for (int i = 1; i < allRows.size(); i++) {

				String[] data = allRows.get(i);
				Map<String, String> fieldObject = new HashMap<>();

				fieldObject.put(stoFieldName.get(0), data[0]); // EBELN
				fieldObject.put(stoFieldName.get(1), data[4]); // FromLocationID
				fieldObject.put(stoFieldName.get(2), data[5]); // ShipmentDate
				fieldObject.put(stoFieldName.get(3), data[7]); // Open_QTY
				fieldObject.put(stoFieldName.get(4), data[9]); // QTY_Ordered
				fieldObject.put(stoFieldName.get(5), data[10]); // REQ_Delivery_Date
				fieldObject.put(stoFieldName.get(6), data[2]); // Item
				fieldObject.put(stoFieldName.get(7), data[3]); // DestinationLocationID

				fieldObjectList.add(fieldObject);

				// Collecting locationId
				if (!locationId.contains(data[4] + "-" + data[3])) {
					locationId.add(data[4] + "-" + data[3]);
				}

			}

			Integer itemCountSize = fieldObjectList.size();
			String interfaceName = Constants.STOINTERFACENAME;

			PayloadSTO payloadSTO = new PayloadSTO();
			List<QuantityOfStockMoved> qtyOfStockMovedList = new ArrayList<>();
			for (String id : locationId) {
				String locId = id.substring(0, (id.indexOf("-")));
				String desId = id.substring((id.indexOf("-") + 1));
				QuantityOfStockMoved quantityOfStockMoved = new QuantityOfStockMoved();
				LocationSTO locationSTO = new LocationSTO();
				locationSTO.setSourceLocationId(locId);
				locationSTO.setDestinationLocationId(desId);
				List<Map<String, String>> fieldObjList = new ArrayList<>();

				fieldObjList = fieldObjectList.stream().filter(x -> x.get("sourceLocationId").equalsIgnoreCase(locId)
						&& x.get("destinationLocationId").equalsIgnoreCase(desId)).collect(Collectors.toList());
				List<ItemsSTO> itemList = new ArrayList<>();
				for (int i = 0; i < fieldObjList.size(); i++) {
					ItemsSTO itemsSTO = new ItemsSTO();

					MnsReferences mnsReferences = new MnsReferences();
					mnsReferences.setArticleVariantId(fieldObjList.get(i).get("articleVariantId"));
					itemsSTO.setMnsReferences(mnsReferences);

					StockDetailsSTO stockDetailsSTO = new StockDetailsSTO();
					stockDetailsSTO.setTotalQuantity(fieldObjList.get(i).get("totalQuantity"));
					stockDetailsSTO.setOrderedQuantity(fieldObjList.get(i).get("orderedQuantity"));
					stockDetailsSTO.setSTONumber(fieldObjList.get(i).get("STONumber"));
					stockDetailsSTO.setShipmentDate(fieldObjList.get(i).get("shipmentDate"));
					stockDetailsSTO.setRequestedDeliveryDate(fieldObjList.get(i).get("requestedDeliveryDate"));
					itemsSTO.setStockDetails(stockDetailsSTO);

					itemList.add(itemsSTO);
				}
				quantityOfStockMoved.setItems(itemList);
				quantityOfStockMoved.setLocation(locationSTO);
				qtyOfStockMovedList.add(quantityOfStockMoved);
			}
			payloadSTO.setQuantityOfStockMoved(qtyOfStockMovedList);

			STOProductSiteLevelDTO stoProduct = new STOProductSiteLevelDTO();
			stoProduct.setHeader(setSTOHeader(partnerCode, itemCountSize, interfaceName));
			stoProduct.setPayload(payloadSTO);
			return new ObjectMapper().writeValueAsString(stoProduct);

		} catch (Exception e) {
			JSONGenerationFailure jsonGenerationFailure = new JSONGenerationFailure(e);
			jsonGenerationFailure.logErrorMessage();
			logger.error("Error occured while generating JSON", e);
		}
		return null;
	}

	@SuppressWarnings({ "resource" })
	public String responseForStock(String filename, final ExecutionContext context, byte[] content, String partnerCode,
			List<String> destinationFieldName, List<String[]> allRows) {

		List<String> siteIdList = getPartnerSiteId(partnerCode);
		List<String> skipArticleList = getSkipArticalNo();

		Set<String> newSiteIds = new HashSet<>(); // List to collect new site IDs

		Integer itemCountSize = 0;

		try {
			List<Map<String, String>> fieldObjectList = new ArrayList<>();
			for (int i = 0; i < allRows.size(); i++) {

				String[] data = allRows.get(i);
				Map<String, String> fieldObject = new HashMap<>();
				String check = data[1].substring(0, 1);

				// Changes for filtering non merchandise articles
				if (!skipArticleList.contains(check)) {

					fieldObject.put(destinationFieldName.get(0), data[2]); // siteId
					fieldObject.put(destinationFieldName.get(1), data[1]); // articleVariantId
					fieldObject.put(destinationFieldName.get(2), data[0]); // aggregatedStockDate
					fieldObject.put(destinationFieldName.get(3), data[3]); // siteName
					fieldObject.put(destinationFieldName.get(4), data[4]); // storageLocationCode
//					fieldObject.put(destinationFieldName.get(5), data[5]); // subLocation
					fieldObject.put(destinationFieldName.get(6), data[5]); // unrestrictedQuantity
					fieldObject.put(destinationFieldName.get(7), data[6]); // stkInTransQuantity
					fieldObject.put(destinationFieldName.get(8), data[7]); // blockedQuantity
					fieldObjectList.add(fieldObject);

					itemCountSize++;
				}

			}

			logger.info("Counting number of rows:{}", itemCountSize);
			List<SiteAggregatedStock> siteList = new ArrayList<>();

			Map<String, List<Map<String, String>>> purchaseOrderGrpByMap = fieldObjectList.stream()
					.collect(Collectors.groupingBy(x -> x.get(destinationFieldName.get(0))));
			logger.info("Total entries: {}", purchaseOrderGrpByMap.entrySet());
			String partnerAlertMsg = "";
			int rowcounter = 1;
			int invalidfieldFlag = 0;
			for (Map.Entry<String, List<Map<String, String>>> entries : purchaseOrderGrpByMap.entrySet()) {
				SiteAggregatedStock site = new SiteAggregatedStock();

				site.setSiteNo(entries.getKey());

				List<Map<String, String>> responseList = entries.getValue();
				List<ItemsStock> itemsList = new ArrayList<>();

				String siteId = null;
				String saledate = null;
				String siteName = null;
				int quantity = 0;

				for (Map<String, String> list1 : responseList) {
					String articleVariantId = list1.get(destinationFieldName.get(1));
					List<StockQuantity> stockQuantityList = new ArrayList<>();

					siteId = list1.get(destinationFieldName.get(0));
					saledate = list1.get(destinationFieldName.get(2));
					siteName = list1.get(destinationFieldName.get(3));

					String unrestrictedQuantity1 = list1.get(destinationFieldName.get(6));
					int unrestrictedQuantity = (int) Float.parseFloat(unrestrictedQuantity1);
					String stkInTransQuantity1 = list1.get(destinationFieldName.get(7));
					int stkInTransQuantity = (int) Float.parseFloat(stkInTransQuantity1);
					String blockedQuantity1 = list1.get(destinationFieldName.get(8));
					int blockedQuantity = (int) Float.parseFloat(blockedQuantity1);

					quantity += unrestrictedQuantity + stkInTransQuantity + blockedQuantity;

					ItemsStock items = new ItemsStock();
					String storageLocationCode = list1.get(destinationFieldName.get(4));
					String[] storageLocationName = getStorageLocationName(storageLocationCode, partnerCode);

					StockLocation stockLocation = new StockLocation();
					stockLocation.setStorageLocationCode(storageLocationName[0]);
					stockLocation.setStorageLocationName(storageLocationName[0]);

					MnsReferences mnsReferences = new MnsReferences();
					mnsReferences.setArticleVariantId(articleVariantId);

					QuantityStock quantityIndia1 = new QuantityStock();
					quantityIndia1.setStockType(StockType.UNRESTRICTED);
					quantityIndia1.setUnitOfMeasure(Constants.UNITOFMEASURE);
					quantityIndia1.setQuantity(unrestrictedQuantity);

					QuantityStock quantityIndia2 = new QuantityStock();
					quantityIndia2.setStockType(StockType.IN_TRANSIT);
					quantityIndia2.setUnitOfMeasure(Constants.UNITOFMEASURE);
					quantityIndia2.setQuantity(stkInTransQuantity);

					QuantityStock quantityIndia3 = new QuantityStock();
					quantityIndia3.setStockType(StockType.BLOCKED);
					quantityIndia3.setUnitOfMeasure(Constants.UNITOFMEASURE);
					quantityIndia3.setQuantity(blockedQuantity);

					StockQuantity stockQuantityIndia1 = new StockQuantity();
					stockQuantityIndia1.setQuantity(quantityIndia1);
					StockQuantity stockQuantityIndia2 = new StockQuantity();
					stockQuantityIndia2.setQuantity(quantityIndia2);
					StockQuantity stockQuantityIndia3 = new StockQuantity();
					stockQuantityIndia3.setQuantity(quantityIndia3);

					if (unrestrictedQuantity > 0 || unrestrictedQuantity < 0) {
						stockQuantityList.add(stockQuantityIndia1);
					}
					if (stkInTransQuantity > 0 || stkInTransQuantity < 0) {
						stockQuantityList.add(stockQuantityIndia2);
					}
					if (blockedQuantity > 0 || blockedQuantity < 0) {
						stockQuantityList.add(stockQuantityIndia3);
					}

					items.setMnsReferences(mnsReferences);
					items.setLocation(stockLocation);
					items.setQuantity(quantity);
					items.setStockQuantity(stockQuantityList);
					itemsList.add(items);
					quantity = 0;

				}

				site.setSiteNo(siteId);
				site.setAggregatedStockDate(saledate);
				site.setSiteName(null);

				// Get the site type code from site master table
				String channelId = getPartnerSiteTypeCode(siteId, partnerCode);

				// check for channelID availability and if site id available in the sitemaster
				// table.
				if ((!siteIdList.contains(siteId)) || (channelId.equals(null))) {
					site.setConcatSiteId(partnerCode + siteId); // Set the concatSiteId to be combination of partnerCode
																// and siteId

					if (!siteIdList.contains(siteId)) {
						newSiteIds.add(siteId); // Add new site IDs to the list
					}

				} else {
					site.setConcatSiteId(channelId + partnerCode + siteId); // Set the concatSiteId to be combination of
																			// channelId,partnerCode and siteId
				}

				site.setItems(itemsList);

				logger.info("For Column: {}", site);
				MnsReferenceFieldValidator mnsrefervalidation = new MnsReferenceFieldValidator();
				StoreSubLocationValidator storesublocationvalidation = new StoreSubLocationValidator();
				ItemStockValidator itemStockValidation = new ItemStockValidator(mnsrefervalidation,
						storesublocationvalidation);
				StockFieldValidator stockFieldValidation = new StockFieldValidator(itemStockValidation);
				Errors errors = new BeanPropertyBindingResult(site, "site");
				stockFieldValidation.validate(site, errors);

				if (errors.hasErrors()) {

					invalidfieldFlag = 1;

					logger.info("Received Partner's file has following errors");
					logger.info("Errors: {}", errors.getAllErrors());

					String currentproducttMsg = errors.getFieldErrors().stream()
							.map(DefaultMessageSourceResolvable::getDefaultMessage).collect(Collectors.joining("|"));


					partnerAlertMsg += String.format(ValidationConstants.EMAIl_TEMPLATE_ROW, rowcounter, siteId,
							currentproducttMsg);
					rowcounter++;
					for (ObjectError error : errors.getAllErrors()) {

						logger.error(" Reason : {}", error.getDefaultMessage());
					}

				} else {
					siteList.add(site);
				}

			}
			if (invalidfieldFlag != 0) {
				restClient.postEmail(setInvalidFieldsEmailDetails(partnerAlertMsg));
			}

			String interfaceName = Constants.STOCKINTERFACENAME;
			PayloadStock payload = new PayloadStock();
			payload.setSiteAggregatedStock(siteList);

			StockProductSiteLevelDTO stockProduct = new StockProductSiteLevelDTO();
			stockProduct.setHeader(headerResponse(partnerCode, itemCountSize, interfaceName));
			stockProduct.setPayload(payload);

			// Send one email with all new site IDs, if any
			if (!newSiteIds.isEmpty()) {
				String emailMessage = "New site IDs found in the India Stock file: " + String.join(", ", newSiteIds);
				restClient.postEmail(setEmailDetails(emailMessage));
			}

			return new ObjectMapper().writeValueAsString(stockProduct);

		} catch (Exception e) {
			JSONGenerationFailure jsonGenerationFailure = new JSONGenerationFailure(e);
			jsonGenerationFailure.logErrorMessage();
			logger.error("Error occured while genreating JSON", e);
		}
		return null;
	}

	public List<String> getSkipArticalNo() {

		try (Statement stmt = conn.createStatement()) {
			String article = null;
			ResultSet result = stmt.executeQuery(SQLQueries.SKIP_ARTICLE);
			while (result.next()) {
				article = result.getString("exceptions");
			}
			String[] articleList = article.split(",");
			return Arrays.asList(articleList);

		} catch (Exception e) {
			logger.error(Constants.DB_EXCEPTION);
		}
		return Collections.emptyList();
	}

	public List<String> getPartnerSiteId(String partnerCode) {
		List<String> siteIdList = new ArrayList<>();
		try (PreparedStatement getStmt = conn.prepareStatement(SQLQueries.PARTNER_SITEID)) {

			getStmt.setString(1, partnerCode);
			ResultSet result = getStmt.executeQuery();

			while (result.next()) {
				siteIdList.add(result.getString("siteId"));
			}
			return siteIdList;
		} catch (Exception e) {
			logger.error(Constants.DB_EXCEPTION);
		}
		return Collections.emptyList();
	}

	public String getPartnerSiteTypeCode(String siteId, String partnerCode) {

		String siteTypeCode = null;
		try (PreparedStatement getStmt = conn.prepareStatement(SQLQueries.PARTNER_SITETYPECODE)) {

			getStmt.setString(1, siteId);
			getStmt.setString(2, partnerCode);
			ResultSet result = getStmt.executeQuery();

			while (result.next()) {
				siteTypeCode = result.getString("siteTypeCode");
			}
		} catch (Exception e) {
			logger.error(Constants.DB_EXCEPTION);
		}

		return siteTypeCode;

	}

	public void insertPartnerSiteID(String siteId, String siteName) {
		try (PreparedStatement insertStmt = conn.prepareStatement(SQLQueries.INSERT_PARTNER_SITEID)) {
			insertStmt.setString(1, "9328");
			insertStmt.setString(2, "india");
			insertStmt.setString(3, siteId);
			insertStmt.setString(4, siteName);

			insertStmt.executeUpdate();
		} catch (Exception e) {
			logger.error(Constants.DB_EXCEPTION);
		}
	}

	public String[] getStorageLocationName(String storageLocationCode, String partnerCode) {

		String[] storageLocationName = new String[2];
		try (PreparedStatement locationNameStmt = conn.prepareStatement(SQLQueries.STORAGE_LOCATION_NAME)) {
			locationNameStmt.setString(1, storageLocationCode);
			locationNameStmt.setString(2, partnerCode);
			ResultSet result = locationNameStmt.executeQuery();
			String emailMessage = "New storage localtion is come in India stock file : " + storageLocationCode;
			while (result.next()) {
				storageLocationName[0] = result.getString("inventoryLocationDescription");
				storageLocationName[1] = result.getString("inventoryLocationCode");
			}

			if (storageLocationName[0] == null) {
				storageLocationName[0] = "unknown";
				storageLocationName[1] = "9999";
				// Alert mail if new storage location code is coming in the input file
				restClient.postEmail(setEmailDetails(emailMessage));
			}

			return storageLocationName;

		} catch (Exception e) {
			logger.error(Constants.DB_EXCEPTION);
		}
		return new String[0];
	}

	public Header headerResponse(String partnerCode, Integer itemCountSize, String interfaceName) {

		SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String dateString = format1.format(new Date());
		dateString = dateString.replace(' ', 'T');

		String interfaceId = null;
		String partnerName = null;
		String quary = null;

		if (partnerCode.contains("9328")) {
			quary = SQLQueries.STOCK_INTERFACEID_AND_NAME;
		} else {
			quary = SQLQueries.PARTNER_INTERFACEID_AND_NAME;
		}

		try (PreparedStatement preparedStatement = conn.prepareStatement(quary)) {
			preparedStatement.setString(1, partnerCode);
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				interfaceId = resultSet.getString("destinationInterfaceNumber");
				partnerName = resultSet.getString("partnerName");
			}
		} catch (Exception e) {
			logger.error(Constants.DB_EXCEPTION, e);
		}

		String messageId = UUID.randomUUID().toString();

		Header header = new Header();
		header.setMessageId(messageId);
		header.setCreatedTimeStamp(dateString);
		header.setSourceApplication(Constants.SOURDEAPPLICATION);
		header.setInterfaceId(interfaceId);
		header.setVersion(Constants.VERSION);
		header.setInterfaceName(interfaceName);
		header.setPartnerNumber(partnerCode);
		header.setPartnerName(partnerName);
		header.setItemsCount(itemCountSize);

		return header;

	}

	public String responseForPrice(String filename, final ExecutionContext context, byte[] content, String partnerCode,
			List<String[]> allRows) {
		List<String> priceFieldName = getPriceDestinationFieldName();
		int itemCountSize = 0;
        boolean hasError = false;
        StringBuilder partnerAlertMsg = new StringBuilder();
		String emailMessage = "In India selling price file some cloumn is empty";

		try {
			List<Map<String, String>> priceObjectList = new ArrayList<>();
            logger.info("total count before validation India:: {}", allRows.size());
			for (int i = 1; i < allRows.size(); i++) {
				String[] data = allRows.get(i);
				Map<String, String> priceObject = new HashMap<>();
					priceObject.put(priceFieldName.get(0), data[0]); // priceCreationDate
					priceObject.put(priceFieldName.get(1), data[1]); // articalVariantId
					priceObject.put(priceFieldName.get(2), data[2]); // fullPrice
					priceObject.put(priceFieldName.get(3), data[3]); // SellingPrice
					priceObject.put(priceFieldName.get(4), data[4]); // currency
					priceObject.put(priceFieldName.get(5), data[5]); // priceType
					priceObjectList.add(priceObject);
				}
			List<stockPrice> stockPriceList = new ArrayList<>();

			Map<String, List<Map<String, String>>> grpByPriceDate = priceObjectList.stream()
					.collect(Collectors.groupingBy(x -> x.get(priceFieldName.get(0))));
			for (Map.Entry<String, List<Map<String, String>>> entries : grpByPriceDate.entrySet()) {

				stockPrice stockPrice = new stockPrice();

				List<PriceItems> priceItemsList = new ArrayList<>();
				List<Map<String, String>> responseList = entries.getValue();

				for (Map<String, String> response : responseList) {

					PriceItems priceItems = new PriceItems();

					MnsReferences mnsReferences = new MnsReferences();
					mnsReferences.setArticleVariantId(response.get(priceFieldName.get(1)));
                    String priceStartDate = null;
					SimpleDateFormat format2 = new SimpleDateFormat("yyyy-MM-dd");
                    if (StringUtils.isNotEmpty(response.get(priceFieldName.get(0)))) {
					Date date = format2.parse(response.get(priceFieldName.get(0)));
                        priceStartDate = format2.format(date);
                    }

                    Float fullPrice = getFloatValue(response.get(priceFieldName.get(2)));
                    Float sellingPrice = getFloatValue(response.get(priceFieldName.get(3)));
					PriceDTO price = new PriceDTO();
					price.setFullPrice(fullPrice);
					price.setSellingPrice(sellingPrice);
					price.setCurrency(response.get(priceFieldName.get(4)));
					price.setPriceType(response.get(priceFieldName.get(5)));
					price.setPriceStartDate(priceStartDate);
					price.setPriceEndDate("2099-12-31");

					priceItems.setMnsReferences(mnsReferences);
					priceItems.setItemPrice(price);
					priceItemsList.add(priceItems);

				}

				stockPrice.setItems(priceItemsList);
				stockPrice.setSellingChannel(PriceSellingChannel.ALL);
                logger.info("Before validation :: {}", stockPrice.getItems().size());
                if (validateFieldsAndCreateMail(stockPrice, partnerAlertMsg)) {
                    hasError = true;
                }
                itemCountSize = itemCountSize + (CollectionUtils.isNotEmpty(stockPrice.getItems()) ? stockPrice.getItems().size() : 0);
				stockPriceList.add(stockPrice);
			}
            if (hasError) {
                restClient.postEmail(setPriceInvalidFieldsEmailDetails(partnerAlertMsg.toString()));
            }
            logger.info("total count after India validation:: {}", itemCountSize);
			String interfaceName = Constants.PRICE_INTERFACENAME;
			PayloadPrice pricePayload = new PayloadPrice();
			pricePayload.setPrices(stockPriceList);

			PriceProductSiteLevelDTO priceProductSitelevelDTO = new PriceProductSiteLevelDTO();

			priceProductSitelevelDTO.setHeader(setPriceHeader(partnerCode, itemCountSize, interfaceName));
			priceProductSitelevelDTO.setPayload(pricePayload);

			return new ObjectMapper().writeValueAsString(priceProductSitelevelDTO);
		} catch (Exception e) {
			logger.error("Error while genrating price JSON");
		}
		return null;
	}

	public String responseforCyprusPrice(String filename, ExecutionContext context, byte[] content, String partnerCode,
			List<String[]> allRows) {
        StringBuilder partnerAlertMsg = new StringBuilder();
        boolean hasError = false;
		List<String> priceFieldName = getCyprusPriceDestinationFieldName();
		int itemCountSize = 0;
		String emailMessage = "In Cyprus price file some values are invalid";

		try {
			List<Map<String, String>> priceObjectList = new ArrayList<>();
            logger.info("total count before validation:: {}", allRows.size());
			for (int i = 1; i < allRows.size(); i++) {
				String[] data = allRows.get(i);
				Map<String, String> priceObject = new HashMap<>();
				priceObject.put(priceFieldName.get(1), data[1]); // priceCreationDate
				priceObject.put(priceFieldName.get(2), data[2]); // sellingChannel
				priceObject.put(priceFieldName.get(3), data[3]); // siteId
				priceObject.put(priceFieldName.get(4), data[4]); // siteName
				priceObject.put(priceFieldName.get(5), data[5]); // articalVariantId
				priceObject.put(priceFieldName.get(6), data[6]); // UPC
				priceObject.put(priceFieldName.get(7), data[7]); // currency
				priceObject.put(priceFieldName.get(13), data[8]); // fullPrice
				priceObject.put(priceFieldName.get(9), data[9]); // sellingPrice
				priceObject.put(priceFieldName.get(10), data[10]); // launchPrice
				priceObject.put(priceFieldName.get(11), data[11]); // priceType
				priceObject.put(priceFieldName.get(12), data[12]); // priceStartDate
				priceObjectList.add(priceObject);
			}

			List<CyprusPrices> cyprusPriceList = new ArrayList<>();
			Map<String, List<Map<String, String>>> grpByPriceDate = priceObjectList.stream()
					.collect(Collectors.groupingBy(x -> x.get(priceFieldName.get(1))));
			for (Map.Entry<String, List<Map<String, String>>> entries : grpByPriceDate.entrySet()) {

				CyprusPrices cyprusPrices = new CyprusPrices();
				List<CyprusPriceItems> priceItemsList = new ArrayList<>();
				List<Map<String, String>> responseList = entries.getValue();
				for (Map<String, String> response : responseList) {

					CyprusPriceItems cyprusPriceItems = new CyprusPriceItems();

					CyprusMnsReferences mnsReferences = new CyprusMnsReferences();

					String value = response.get(priceFieldName.get(5));

					if (value.length() > 11) {
						value = value.substring(value.length() - 11);
					}

					mnsReferences.setArticleVariantId(value);
                    mnsReferences.setUpc(StringUtils.isNotEmpty(response.get(priceFieldName.get(6))) ?
                            response.get(priceFieldName.get(6)).replaceFirst("0", "") : null);

					SimpleDateFormat inputFormat = new SimpleDateFormat("dd/MM/yy");
                    String priceStartDate = null;
                    if (StringUtils.isNotEmpty(response.get(priceFieldName.get(12)))) {
					Date date = inputFormat.parse(response.get(priceFieldName.get(12)));
					DateFormat outputFormat = new SimpleDateFormat("yyyy-MM-dd");
                        priceStartDate = outputFormat.format(date);
                    }

					CyprusPriceDTO price = new CyprusPriceDTO();
                    Float fullPrice = getFloatValue(response.get(priceFieldName.get(13)));
                    Float sellingPrice = getFloatValue(response.get(priceFieldName.get(9)));
					price.setFullPrice(fullPrice);
					price.setSellingPrice(sellingPrice);
					price.setCurrency(response.get(priceFieldName.get(7)));

					String priceType = response.get(priceFieldName.get(11));
					if (priceType != null && priceType.equalsIgnoreCase("REDUCED")) {
						priceType = "REDUCE";
					}
					price.setPriceType(priceType);
					price.setPriceStartDate(priceStartDate);
					price.setPriceEndDate("2099-12-31");

					cyprusPriceItems.setMnsReferences(mnsReferences);
					cyprusPriceItems.setItemPrice(price);
					priceItemsList.add(cyprusPriceItems);

                    cyprusPrices.setSiteId(StringUtils.isNotEmpty(response.get(priceFieldName.get(3))) ?
                            response.get(priceFieldName.get(3)).replaceFirst("0", "") : null);

					String siteName = response.get(priceFieldName.get(4));
                    if ("Main Warehouse".equalsIgnoreCase(siteName))
						siteName = "Cyprus Warehouse";
                    if ("Online store".equalsIgnoreCase(siteName))
						siteName = "Cyprus Website";

					cyprusPrices.setSiteName(siteName);

				}

				cyprusPrices.setSellingChannel(PriceSellingChannel.ALL);
				cyprusPrices.setItems(priceItemsList);
                if (validateFieldsAndCreateMail(cyprusPrices, partnerAlertMsg)) {
                    hasError = true;
                }
                itemCountSize = itemCountSize + (CollectionUtils.isNotEmpty(cyprusPrices.getItems()) ? cyprusPrices.getItems().size() : 0);
				cyprusPriceList.add(cyprusPrices);
			}
            if (hasError) {
                restClient.postEmail(setPriceInvalidFieldsEmailDetails(partnerAlertMsg.toString()));
            }
            logger.info("total count after validation:: {}", itemCountSize);
			String interfaceName = Constants.PRICE_INTERFACENAME;
			PayloadCyprusPrice payloadCyprusPrice = new PayloadCyprusPrice();
			payloadCyprusPrice.setPrices(cyprusPriceList);

			CyprusPriceLevelDTO cyprusPriceLevelDTO = new CyprusPriceLevelDTO();

			cyprusPriceLevelDTO.setHeader(setCyprusPriceHeader(partnerCode, itemCountSize, interfaceName));
			cyprusPriceLevelDTO.setPayload(payloadCyprusPrice);

			return new ObjectMapper().writeValueAsString(cyprusPriceLevelDTO);
		} catch (Exception e) {
			logger.error("Error while genrating Cyprus Price JSON");
		}
		return null;
	}

	public List<String> getPriceDestinationFieldName() {
		List<String> fieldName = new ArrayList<>();
		try (Statement stmt = conn.createStatement()) {
			ResultSet result = stmt.executeQuery(SQLQueries.PARTNER_DESTINATION_FIELD_NAME_FOR_PRICE);
			while (result.next()) {
				fieldName.add(result.getString("destinationFieldName"));
			}
			return fieldName;
		} catch (Exception e) {
			logger.error(Constants.DB_EXCEPTION);
		}
		return Collections.emptyList();
	}

	public List<String> getCyprusPriceDestinationFieldName() {

		List<String> fieldName = new ArrayList<>();
		try (Statement stmt = conn.createStatement()) {
			ResultSet result = stmt.executeQuery(SQLQueries.CYPRUS_PRICE_DESTINATION_FIELD_NAME);
			while (result.next()) {
				fieldName.add(result.getString("destinationFieldName"));
			}
			return fieldName;
		} catch (Exception e) {
			logger.error(Constants.DB_EXCEPTION);
		}
		return Collections.emptyList();
	}

	public List<String> getSTODestinationFieldName() {
		List<String> fieldName = new ArrayList<>();
		try (Statement stmt = conn.createStatement()) {
			ResultSet result = stmt.executeQuery(SQLQueries.PARTNER_DESTINATION_FIELD_NAME_FOR_STO);
			while (result.next()) {
				fieldName.add(result.getString("destinationFieldName"));
			}
			return fieldName;
		} catch (Exception e) {
			logger.error(Constants.DB_EXCEPTION);
		}
		return Collections.emptyList();
	}

	public PriceHeader setPriceHeader(String partnerCode, Integer itemCountSize, String interfaceName) {

		SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String dateString = format1.format(new Date());
		dateString = dateString.replace(' ', 'T');

		SimpleDateFormat format2 = new SimpleDateFormat("yyyy-MM-dd");
		String extractDate = format2.format(new Date());

		String interfaceId = null;
		String partnerName = null;

		try (PreparedStatement preparedStatement = conn.prepareStatement(SQLQueries.PRICE_INTERFACEID_AND_NAME)) {
			preparedStatement.setString(1, partnerCode);
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				interfaceId = resultSet.getString("destinationInterfaceNumber");
				partnerName = resultSet.getString("partnerName");
			}
		} catch (Exception e) {
			logger.error(Constants.DB_EXCEPTION, e);
		}

		String messageId = UUID.randomUUID().toString();

		PriceHeader header = new PriceHeader();
		header.setMessageId(messageId);
		header.setCreatedTimeStamp(dateString);
		header.setSourceApplication(Constants.SOURDEAPPLICATION);
		header.setInterfaceId(interfaceId);
		header.setVersion(Constants.VERSION);
		header.setInterfaceName(interfaceName);
		header.setPartnerNumber(partnerCode);
		header.setPartnerName(partnerName);
		header.setExtractDate(extractDate);
		header.setItemsCount(itemCountSize);

		return header;
	}

	public PriceHeader setCyprusPriceHeader(String partnerCode, Integer itemCountSize, String interfaceName) {

		SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String dateString = format1.format(new Date());
		dateString = dateString.replace(' ', 'T');

		SimpleDateFormat format2 = new SimpleDateFormat("yyyy-MM-dd");
		String extractDate = format2.format(new Date());

		String interfaceId = null;

		try (PreparedStatement preparedStatement = conn.prepareStatement(SQLQueries.CYPRUS_PRICE_INTERFACEID)) {
			preparedStatement.setString(1, partnerCode);
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				interfaceId = resultSet.getString("destinationInterfaceNumber");
			}
		} catch (Exception e) {
			logger.error(Constants.DB_EXCEPTION, e);
		}

		String messageId = UUID.randomUUID().toString();

		PriceHeader header = new PriceHeader();
		header.setMessageId(messageId);
		header.setCreatedTimeStamp(dateString);
		header.setSourceApplication(Constants.SOURDEAPPLICATION);
		header.setInterfaceId(interfaceId);
		header.setVersion(Constants.VERSION);
		header.setInterfaceName(interfaceName);
		header.setPartnerNumber(partnerCode);
		header.setPartnerName(Constants.CYPRUSPRICEPARTNERNAME);
		header.setExtractDate(extractDate);
		header.setItemsCount(itemCountSize);

		return header;
	}

	public STOHeader setSTOHeader(String partnerCode, Integer itemCountSize, String interfaceName) {

		SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String dateString = format1.format(new Date());
		dateString = dateString.replace(' ', 'T');

		SimpleDateFormat format2 = new SimpleDateFormat("yyyy-MM-dd");
		String extractDate = format2.format(new Date());

		String interfaceId = null;
		String partnerName = null;

		try (PreparedStatement preparedStatement = conn.prepareStatement(SQLQueries.STO_INTERFACEID_AND_NAME)) {
			preparedStatement.setString(1, partnerCode);
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				interfaceId = resultSet.getString("destinationInterfaceNumber");
				partnerName = resultSet.getString("partnerName");
			}
		} catch (Exception e) {
			logger.error(Constants.DB_EXCEPTION, e);
		}

		String messageId = UUID.randomUUID().toString();

		STOHeader header = new STOHeader();
		header.setMessageId(messageId);
		header.setCreatedTimeStamp(dateString);
		header.setSourceApplication(Constants.SOURDEAPPLICATION);
		header.setInterfaceId(interfaceId);
		header.setVersion(Constants.VERSION);
		header.setInterfaceName(interfaceName);
		header.setPartnerNumber(partnerCode);
		header.setPartnerName(partnerName);
		header.setExtractDate(extractDate);
		header.setItemsCount(itemCountSize);

		return header;
	}

	public boolean validateWithOutputJson(String json, String fileName, String azureStorageAccount, String partnerCode)
			throws URISyntaxException, StorageException, InvalidKeyException, IOException, SQLException {

		String outputPath = null;
		ResultSet resultSet = null;
		String partnerFileName = null;
		String query = null;

		boolean upload = false;
		if (partnerCode.equals("9328")) {
			if (fileName.contains("Price")) {
				query = SQLQueries.PARTNER_PRICE_DESTINATION_PATH;
			} else if (fileName.contains("STO")) {

				query = SQLQueries.PARTNER_STO_DESTINATION_PATH;
			} else {
				query = SQLQueries.PARTNER_STOCK_DESTINATION_PATH;
			}
		} else if (partnerCode.equals("8251")) {
			if (fileName.contains("Stock")) {
				query = SQLQueries.PARTNER_STOCK_DESTINATION_PATH;
			} else if (fileName.contains("Price")) {

				query = SQLQueries.CYPRUS_PRICE_DESTINATION_PATH;
			} else {
				query = SQLQueries.PARTNER_CYPRUS_DESTINATION_PATH;
			}
		} else {
			query = SQLQueries.PARTNER_DESTINATION_PATH;
		}

		try (PreparedStatement preparedStatement = conn.prepareStatement(query)) {
			preparedStatement.setString(1, partnerCode);
			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				outputPath = resultSet.getString("destinationPath");
			}
		} catch (Exception e) {
			logger.error(Constants.DB_EXCEPTION, e);
		}

		if (partnerCode.equals("1604")) {
			String date = fileName.substring(48, 56);
			partnerFileName = Constants.SOURDEAPPLICATIONMAP + partnerCode + "_SalesReport_" + date;
		}

//		else if(priceItemCount == 0 && fileName.contains("Price")) {
//			partnerFileName = Constants.SOURDEAPPLICATIONMAP +partnerCode+"_"+ fileName+"_Dummy";
//		}
//		else {
		partnerFileName = Constants.SOURDEAPPLICATIONMAP + partnerCode + "_" + fileName;
//		}

		if (partnerCode.equals("8251")) {
			if (fileName.contains("Stock")) {
				String fileDate = fileName.substring(0, 10);

				String formattedDate1 = null;
				try {
					DateFormat inputFormat1 = new SimpleDateFormat("dd_mm_yy");
					Date date1 = inputFormat1.parse(fileDate);
					DateFormat outputFormat1 = new SimpleDateFormat("yyyymmdd");
					formattedDate1 = outputFormat1.format(date1);
				} catch (ParseException e) {
					logger.error("Error occure while parsing date");
				}

				partnerFileName = Constants.SOURDEAPPLICATIONMAP + partnerCode + "_All_Store_Inventory_"
						+ formattedDate1 + "_part1";

			} else if (fileName.contains("Price")) {

				String fileDate = fileName.substring(0, 10);

				String formattedDate1 = null;
				try {
					DateFormat inputFormat1 = new SimpleDateFormat("dd_mm_yy");
					Date date1 = inputFormat1.parse(fileDate);
					DateFormat outputFormat1 = new SimpleDateFormat("yyyymmdd");
					formattedDate1 = outputFormat1.format(date1);
				} catch (ParseException e) {
					logger.error("Error occure while parsing date");
				}

				partnerFileName = Constants.SOURDEAPPLICATIONMAP + Constants.CYPRUSPARTNERNUMBER
						+ Constants.CYPRUSPRICEFILENAME + formattedDate1 + Constants.CYPRUS_PRICE_FILE_TYPE;
			} else {
				partnerFileName = Constants.CYPRUSFILENAME + fileName + Constants.CYPRUS_SALES_FILE_TYPE;
			}
		}

		if (partnerCode.equals("7197")) {

			SimpleDateFormat format1 = new SimpleDateFormat("yyyyMMddHHmmss");
			String dateString = format1.format(new Date());

			if (afgItemCount == 0) {
				partnerFileName = Constants.SOURDEAPPLICATIONMAP + partnerCode + "_" + "AFG_OnlineSales_" + dateString
						+ "_Dummy";
			} else {
				partnerFileName = Constants.SOURDEAPPLICATIONMAP + partnerCode + "_" + "AFG_OnlineSales_" + dateString;
			}
		}

		CloudFileDirectory rootDir = getConfig(outputPath, azureStorageAccount);

		if (partnerCode.equals("8251") && fileName.contains("Sales")) {
			upload = uploadJSONFile(json, rootDir, partnerFileName);

		} else if (partnerCode.equals("8251") && fileName.contains("Price")) {
			upload = uploadJSONFile(json, rootDir, partnerFileName);
		} else {
			upload = uploadJSONFile(json, rootDir, partnerFileName + ".json");
		}
		// copyFileToBlobContainer(json);

		return upload;

	}

	public CloudFileDirectory getConfig(String sourceFileSharePath, String fileShareConnectionString)
			throws InvalidKeyException, URISyntaxException, StorageException {
		CloudStorageAccount storageAccount = CloudStorageAccount.parse(fileShareConnectionString);
		CloudFileClient fileClient = storageAccount.createCloudFileClient();
		CloudFileShare fileShare = fileClient.getShareReference(sourceFileSharePath);
		return fileShare.getRootDirectoryReference();
	}

	public void update(String partnerCode, boolean upload, String fileName) throws SQLException {

		String status = null;
		String updateQuery = null;

		if (upload) {
			status = Constants.SUCCESS;
		} else {
			status = Constants.FAILED;
		}

		if (partnerCode.equals("9328")) {
			if (fileName.contains("Price")) {
				updateQuery = SQLQueries.UPDATE_PRICE_AUDIT_BATCH_TABLE;

			} else if (fileName.contains("STO")) {

				updateQuery = SQLQueries.UPDATE_STO_AUDIT_BATCH_TABLE;
			} else {

				updateQuery = SQLQueries.UPDATE_STOCK_AUDIT_BATCH_TABLE;
			}
		} else if (fileName.contains(Constants.AFG)) {

			updateQuery = SQLQueries.UPDATE_TRANSACTIONAL_AUDIT_BATCH_TABLE;

		} else {
			updateQuery = SQLQueries.UPDATE_AUDIT_BATCH_TABLE;
		}

		if (partnerCode.equals("8251")) {
			if (fileName.contains("Stock")) {
				updateQuery = SQLQueries.UPDATE_STOCK_AUDIT_BATCH_TABLE;
			} else if (fileName.contains("Price")) {
				updateQuery = SQLQueries.UPDATE_CYPPRUS_PRICE_AUDIT_BATCH_TABLE;
			} else {
				updateQuery = SQLQueries.UPDATE_CYPPRUS_AUDIT_BATCH_TABLE;

			}
		}

		try (PreparedStatement preparedStatement = conn.prepareStatement(updateQuery)) {
			preparedStatement.setString(1, status);
			preparedStatement.setString(2, partnerCode);
			preparedStatement.executeUpdate();
			logger.info("Status updated in the batch table");

		} catch (Exception e) {
			logger.error(Constants.DB_EXCEPTION, e);
		}
	}

	public boolean uploadJSONFile(String fileContent, CloudFileDirectory rootDir, String fileName)
			throws URISyntaxException, StorageException, IOException {

		boolean upload = false;
		CloudFile cloudFile = rootDir.getFileReference(fileName);
		cloudFile.uploadFromByteArray(fileContent.getBytes(), 0, fileContent.getBytes().length);
		if (cloudFile.getUri() != null) {
			logger.info("File uploaded to FileShare {}", cloudFile.getUri());
			// copyFileToBlobContainer(fileName,fileContent);
			upload = true;
			return upload;
		}
		return upload;
	}

	/*
	 * public Boolean copyFileToBlobContainer(String fileName,String fileContent)
	 * throws FileNotFoundException, IOException {
	 * 
	 * Boolean status = Boolean.TRUE;
	 * 
	 * String beamConnSecretConn = System.getenv("beamConnSecret"); String
	 * beamContainerNameConn = System.getenv("beamContainerName"); String
	 * beamFolderPathConn = System.getenv("beamFolderPath"); String
	 * beamStockFolderPathConn = System.getenv("beamStockFolderPath");
	 * 
	 * System.out.println("beamConnSecretConn"+beamConnSecretConn);
	 * System.out.println("beamContainerNameConn"+beamContainerNameConn);
	 * System.out.println("beamFolderPathConn"+beamFolderPathConn);
	 * System.out.println("beamStockFolderPathConn : "+beamStockFolderPathConn);
	 * String uploadFile = null; if(fileName.contains("Inventory")) { uploadFile =
	 * beamStockFolderPathConn+fileName; }else { uploadFile =
	 * beamFolderPathConn+fileName; }
	 * 
	 * try { String storageConnectionString = getSecret(beamConnSecretConn);
	 * CloudStorageAccount storageAccount; storageAccount =
	 * CloudStorageAccount.parse(storageConnectionString); CloudBlobClient
	 * blobClient = storageAccount.createCloudBlobClient(); CloudBlobContainer
	 * container; container =
	 * blobClient.getContainerReference(beamContainerNameConn); CloudBlockBlob blob
	 * = container.getBlockBlobReference(uploadFile); final byte[] content =
	 * fileContent.getBytes(); blob.uploadFromByteArray(content, 0, content.length);
	 * } catch (Exception e) {
	 * logger.error("Exception While uploading to BEAM path", e); status =
	 * Boolean.FALSE; }
	 * 
	 * return status; }
	 * 
	 * private String getSecret(String secretName) { String kvClientIdEdited =
	 * System.getenv("kvClientId"); String kvClientSecretEdited =
	 * System.getenv("kvClientSecret"); String kvUrlEdited = System.getenv("kvUrl");
	 * String kvTenantIdEdited = System.getenv("kvTenantId");
	 * 
	 * System.out.println("kvClientIdEdited :"+kvClientIdEdited);
	 * System.out.println("kvClientSecretEdited :"+kvClientSecretEdited);
	 * System.out.println("kvUrlEdited :"+kvUrlEdited);
	 * System.out.println("kvTenantIdEdited"+kvTenantIdEdited);
	 * 
	 * 
	 * ClientSecretCredential clientSecretCredential = new
	 * ClientSecretCredentialBuilder().clientId(kvClientIdEdited).clientSecret(
	 * kvClientSecretEdited).tenantId(kvTenantIdEdited).build(); SecretClient
	 * secretClient = new
	 * SecretClientBuilder().credential(clientSecretCredential).vaultUrl(kvUrlEdited
	 * ).buildClient(); KeyVaultSecret secret = secretClient.getSecret(secretName);
	 * System.out.println(" secret.getValue"+ secret.getValue()); return
	 * secret.getValue(); }
	 */

	public void insertStatus(String partnerCode, String partnerName, String fileName) throws SQLException {

		String insertQuery = null;

		if (partnerCode.equals("9328")) {
			if (fileName.contains("Price")) {
				insertQuery = SQLQueries.INSERT_PRICE_AUDIT_BATCH_TABLE;
			} else if (fileName.contains("STO")) {

				insertQuery = SQLQueries.INSERT_STO_AUDIT_BATCH_TABLE;
			} else {
				insertQuery = SQLQueries.INSERT_STOCK_AUDIT_BATCH_TABLE;
			}

		} else if (partnerCode.equals("8251")) {
			if (fileName.contains("Stock")) {
				insertQuery = SQLQueries.INSERT_STOCK_AUDIT_BATCH_TABLE;
			} else if (fileName.contains("Price")) {
				insertQuery = SQLQueries.INSERT_CYPRUS_PRICE_AUDIT_BATCH_TABLE;
			} else {
				insertQuery = SQLQueries.INSERT_CYPRUS_AUDIT_BATCH_TABLE;
			}
		} else if (fileName.contains(Constants.AFG)) {
			insertQuery = SQLQueries.INSERT_TRANSACTIONAL_AUDIT_BATCH_TABLE;
		}

		try (PreparedStatement preparedStatement = conn.prepareStatement(insertQuery)) {
			preparedStatement.setString(1, partnerCode);
			preparedStatement.setString(2, partnerName);
			preparedStatement.executeUpdate();
			logger.info("Inserted status");

		} catch (Exception e) {
			logger.error(Constants.DB_EXCEPTION, e);
		}
	}

	public String convesrionOfXml(String responseFile) {

		String status = null;
		String siteID = null;
		String saleDate = null;
		String gtin = null;
		String salesQuantity = null;
		String returnQuantity = null;
		String salesType = null;
		String returnType = null;
		String currency = null;

		try {

			DocumentBuilderFactory documentBuilderFactory = DocumentBuilderFactory.newInstance();
			documentBuilderFactory.setFeature(XMLConstants.FEATURE_SECURE_PROCESSING, true);
			documentBuilderFactory.setFeature("http://apache.org/xml/features/disallow-doctype-decl", true);
			documentBuilderFactory.setFeature("http://xml.org/sax/features/external-general-entities", false);
			documentBuilderFactory.setFeature("http://xml.org/sax/features/external-parameter-entities", false);
			DocumentBuilder documentBuilder = documentBuilderFactory.newDocumentBuilder();
			Document document = documentBuilder
					.parse(new InputSource(new ByteArrayInputStream(responseFile.getBytes())));
			document.getDocumentElement().normalize();
			logger.info("Root Element : {} ", document.getDocumentElement().getNodeName());

			List<XmlResponse> xmlSalesList = new ArrayList<>();
			List<XmlResponse> xmlReturnList = new ArrayList<>();

			NodeList list3 = document.getElementsByTagName("salesReport");
			Node node3 = list3.item(0);
			if (node3.getNodeType() == Node.ELEMENT_NODE) {
				Element element2 = (Element) node3;

				currency = element2.getElementsByTagName("property").item(0).getTextContent();
			}

			NodeList list = document.getElementsByTagName("site");

			for (int temp = 0; temp < list.getLength(); temp++) {

				Node node = list.item(temp);

				if (node.getNodeType() == Node.ELEMENT_NODE) {

					Element element = (Element) node;
					siteID = element.getElementsByTagName("location").item(0).getAttributes().getNamedItem("gln")
							.getNodeValue();

					saleDate = element.getElementsByTagName("sale").item(0).getAttributes().getNamedItem("date")
							.getNodeValue();

					logger.info("siteId list {}", siteID);

					NodeList itemNodeList = element.getElementsByTagName("item");

					for (int temp1 = 0; temp1 < itemNodeList.getLength(); temp1++) {

						XmlResponse xmlSaleList1 = new XmlResponse();
						XmlResponse xmlReturnList1 = new XmlResponse();

						xmlSaleList1.setSaleDate(saleDate);
						xmlSaleList1.setSiteId(siteID);

						xmlReturnList1.setSaleDate(saleDate);
						xmlReturnList1.setSiteId(siteID);

						Node node1 = itemNodeList.item(temp1);
						if (node1.getNodeType() == Node.ELEMENT_NODE) {

							Element element1 = (Element) node1;
							gtin = element1.getElementsByTagName("itemReference").item(0).getTextContent();
							salesQuantity = element1.getElementsByTagName(Constants.QUANTITY).item(0).getTextContent();
							returnQuantity = element1.getElementsByTagName(Constants.QUANTITY).item(1).getTextContent();

							Element element2 = (Element) node;
							salesType = element2.getElementsByTagName(Constants.QUANTITY).item(0).getAttributes()
									.getNamedItem("type").getNodeValue();

							returnType = element2.getElementsByTagName(Constants.QUANTITY).item(1).getAttributes()
									.getNamedItem("type").getNodeValue();

						}

						xmlSaleList1.setUnits(salesQuantity);
						xmlSaleList1.setCurrency(currency);
						xmlSaleList1.setGtin(gtin);
						xmlSaleList1.setSaleReturnType(salesType);
						xmlSalesList.add(xmlSaleList1);

						xmlReturnList1.setUnits(returnQuantity);
						xmlReturnList1.setCurrency(currency);
						xmlReturnList1.setGtin(gtin);
						xmlReturnList1.setSaleReturnType(returnType);
						xmlReturnList.add(xmlReturnList1);
					}
				}

			}

			XmlResponse xmlResponse = new XmlResponse();
			xmlResponse.setSaleDate("SALE_DATE");
			xmlResponse.setSiteId("SITE_ID");
			xmlResponse.setSiteName("SITE_NAME");
			xmlResponse.setSaleModel("SALEMODEL");
			xmlResponse.setProductId("PRODUCT_ID");
			xmlResponse.setUnits("UNITS");
			xmlResponse.setNetSalesValue("NET_SALES_VALUE");
			xmlResponse.setCurrency("CURRENCY");
			xmlResponse.setDiscountValue("DISCOUNT_VALUE");
			xmlResponse.setPromoId("PROMO_ID");
			xmlResponse.setPromoDesc("PROMO_DESC");
			xmlResponse.setGtin("GTIN");
			xmlResponse.setSaleReturnType("SALERETURNTYPE");

			status = CsvUtil.createCSVFile(xmlResponse, xmlSalesList, xmlReturnList);

		} catch (Exception e) {
			logger.error("Error occured while genreating csv file ", e);
		}

		return status;

	}

	public EmailDTO setEmailDetails(String msgText) {

		logger.info("Fetching email details has started ");
		try {

			EmailDTO email = new EmailDTO();
			email.setToEmail(Constants.TO_EMAIL_ID);
			StringBuilder message = new StringBuilder("<!DOCTYPE html>\r\n" + "<html>\r\n" + "<body>\r\n"
					+ "Hi,<br><br>" + msgText + ". <br><br>Thanks, <br>M&S PIGW<br>\r\n" + "</body>\r\n" + "</html>");
			email.setMessage(message.toString());
			email.setName("Auto Generated Email");
			email.setSubject("M&S – Partner Sales information " + new SimpleDateFormat("yyyyMMdd").format(new Date()));
			email.setFromEmail(Constants.FROM_EMAIL_ID);
			logger.info("Fetching email details has ended ");

			return email;
		} catch (Exception e) {
			logger.error("Error occured while getting email details", e);
			return null;
		}
	}

	public void checkMissingSiteId(File file, String partnerCode) {
		List<String> siteIdList = getPartnerSiteId(partnerCode);
		Set<String> actualSiteIds = new HashSet<>();
		FileReader fileReader;

		try {
			fileReader = new FileReader(file);
			CSVParser parser = new CSVParserBuilder().withSeparator('|').build();
			CSVReader csvReader = new CSVReaderBuilder(fileReader).withCSVParser(parser).build();
			List<String[]> allRows = csvReader.readAll();

			if (allRows.isEmpty() || allRows.size() == 1) {
				String msg = "We received India stock file empty today. Please check";
				restClient.postEmail(setEmptyStockEmailDeatils(msg));
				throw new EmptyFileException();
			}

			allRows.remove(0);
			for (String[] row : allRows) {
				actualSiteIds.add(row[2]);
			}

			List<String> uniqueSiteIdList = new ArrayList<>(actualSiteIds);
			List<String> missingSiteIds = siteIdList.stream().filter(x -> !uniqueSiteIdList.contains(x))
					.collect(Collectors.toList());

			logger.info("missing Site Ids in file: {} ", missingSiteIds);
			String emailMessage = "In india stock file some siteId data is missing " + missingSiteIds;
			restClient.postEmail(setMissingSiteIdDeatils(emailMessage));
		} catch (EmptyFileException e) {
			logger.error("file is empty ", e);
		} catch (Exception e) {
			logger.error("error while checking missing site ids in stock file", e);
		}

	}

	public EmailDTO setEmptyStockEmailDeatils(String msgText) {

		logger.info("Fetching email details has started ");
		try {

			EmailDTO email = new EmailDTO();
			email.setToEmail(Constants.INDIA_EMAIL_ID);
			StringBuilder message = new StringBuilder("<!DOCTYPE html>\r\n" + "<html>\r\n" + "<body>\r\n"
					+ "Hi,<br><br>" + msgText + ". <br><br>Thanks, <br>M&S PIGW<br>\r\n" + "</body>\r\n" + "</html>");
			email.setMessage(message.toString());
			email.setName("Auto Generated Email");
			email.setSubject("India Stock empty file Alert " + new SimpleDateFormat("yyyyMMdd").format(new Date()));
			email.setFromEmail(Constants.FROM_EMAIL_ID);
			logger.info("Fetching email details has ended ");

			return email;
		} catch (Exception e) {
			logger.error("Error occured while getting email details", e);
			return null;
		}
	}

	public EmailDTO setMissingSiteIdDeatils(String msgText) {

		logger.info("Fetching email details has started ");
		try {

			EmailDTO email = new EmailDTO();
			email.setToEmail(Constants.INDIA_EMAIL_ID);
			StringBuilder message = new StringBuilder("<!DOCTYPE html>\r\n" + "<html>\r\n" + "<body>\r\n"
					+ "Hi,<br><br>" + msgText + ". <br><br>Thanks, <br>M&S PIGW<br>\r\n" + "</body>\r\n" + "</html>");
			email.setMessage(message.toString());
			email.setName("Auto Generated Email");
			email.setSubject("India Stock file some site id data is missing "
					+ new SimpleDateFormat("yyyyMMdd").format(new Date()));
			email.setFromEmail(Constants.FROM_EMAIL_ID);
			logger.info("Fetching email details has ended ");

			return email;
		} catch (Exception e) {
			logger.error("Error occured while getting email details", e);
			return null;
		}
	}

	public String responseForCyprusStock(String filename, final ExecutionContext context, byte[] content,
			String partnerCode, List<String> destinationFieldName, List<String[]> allRows) {

		Integer itemCountSize = 0;

		try {
			List<Map<String, String>> fieldObjectList = new ArrayList<>();
			for (int i = 1; i < allRows.size(); i++) {

				String[] data = allRows.get(i);
				Map<String, String> fieldObject = new HashMap<>();

				boolean isValid = true;
				for (int j = 0; j < data.length; j++) {
					if (data[j].isBlank()) {
						isValid = false;
						break;
					}
				}

				try {
					Long.parseLong(data[3]);
				} catch (NumberFormatException e) {
					isValid = false;
				}

				try {
					Integer.parseInt(data[0]);
				} catch (NumberFormatException e) {
					isValid = false;
				}

				try {
					Integer.parseInt(data[4]);
				} catch (NumberFormatException e) {
					isValid = false;
				}

				String articlaeno = data[3].replaceAll("^0+", "");
				if (articlaeno == null || articlaeno.isBlank()) {
					isValid = false;
				}

				if (!isValid) {
					// restClient.postEmail(setEmailDetails(emailMessage));
					continue; // Skip processing this row

				}

				if (!data[0].equalsIgnoreCase("INTRANSIT")) {
					fieldObject.put(destinationFieldName.get(0), data[0]); // siteId
					fieldObject.put(destinationFieldName.get(1), data[1]); // siteName
					fieldObject.put(destinationFieldName.get(2), data[2]); // stockDate
					fieldObject.put(destinationFieldName.get(3), data[4]); // upc
					fieldObject.put(destinationFieldName.get(4), data[5]); // total quantity
					fieldObject.put(destinationFieldName.get(5), data[6]); // SLOC
					fieldObject.put(destinationFieldName.get(6), data[7]); // stockType
					fieldObject.put(destinationFieldName.get(7), data[8]); // unit of measure
					fieldObject.put(destinationFieldName.get(8), data[9]); // quantity
					fieldObject.put(destinationFieldName.get(9), data[3]); // artical varient id

					fieldObjectList.add(fieldObject);

					itemCountSize++;
				}

			}

			List<SiteAggregatedStock> siteList = new ArrayList<>();

			Map<String, List<Map<String, String>>> purchaseOrderGrpByMap = fieldObjectList.stream()
					.collect(Collectors.groupingBy(x -> x.get(destinationFieldName.get(0))));

			for (Map.Entry<String, List<Map<String, String>>> entries : purchaseOrderGrpByMap.entrySet()) {
				SiteAggregatedStock site = new SiteAggregatedStock();

				site.setSiteNo(entries.getKey());

				List<Map<String, String>> responseList = entries.getValue();
				List<ItemsStock> itemsList = new ArrayList<>();

				String siteId = null;
				String saledate = null;
				String siteName = null;
				int quantity = 0;

				for (Map<String, String> list1 : responseList) {
					List<StockQuantity> stockQuantityList = new ArrayList<>();

					siteId = list1.get(destinationFieldName.get(0)).substring(1);
					saledate = list1.get(destinationFieldName.get(2));
					siteName = list1.get(destinationFieldName.get(1));

					String unrestrictedQuantity1 = list1.get(destinationFieldName.get(8));
					int unrestrictedQuantity = (int) Float.parseFloat(unrestrictedQuantity1);

					ItemsStock items = new ItemsStock();
					String storageLocationCode = list1.get(destinationFieldName.get(5));
					String[] storageLocationName = getStorageLocationName(storageLocationCode, partnerCode);

					StockLocation stockLocation = new StockLocation();
					stockLocation.setStorageLocationCode(storageLocationName[0]);
					stockLocation.setStorageLocationName(storageLocationName[0]);

					MnsReferences mnsReferences = new MnsReferences();

					String articleVariantId = list1.get(destinationFieldName.get(9));

					mnsReferences.setArticleVariantId(articleVariantId.replaceAll("^0+", ""));
					mnsReferences.setUpc(list1.get(destinationFieldName.get(3)));

					QuantityStock quantityIndia1 = new QuantityStock();
					quantityIndia1.setStockType(StockType.UNRESTRICTED);
					quantityIndia1.setUnitOfMeasure(Constants.UNITOFMEASURE);
					quantityIndia1.setQuantity(unrestrictedQuantity);

					quantity += unrestrictedQuantity;

					StockQuantity stockQuantityIndia1 = new StockQuantity();
					stockQuantityIndia1.setQuantity(quantityIndia1);

					stockQuantityList.add(stockQuantityIndia1);

					items.setMnsReferences(mnsReferences);
					items.setLocation(stockLocation);
					items.setQuantity(quantity);
					items.setStockQuantity(stockQuantityList);
					itemsList.add(items);

					quantity = 0;

				}

				DateFormat inputFormat1 = new SimpleDateFormat("dd/mm/yy");
				Date date1 = inputFormat1.parse(saledate);
				DateFormat outputFormat1 = new SimpleDateFormat("yyyy-mm-dd");
				String formattedDate1 = outputFormat1.format(date1);
				site.setAggregatedStockDate(formattedDate1);

				site.setSiteNo(siteId);

				if (siteName.equalsIgnoreCase("Warehouse"))
					siteName = "Cyprus " + siteName;
				if (siteName.equalsIgnoreCase("Online store"))
					siteName = "Cyprus Website";

				site.setSiteName(siteName);

				String channelId = getPartnerSiteTypeCode(siteId, partnerCode);
				if (channelId == null)
					site.setConcatSiteId(partnerCode + siteId);
				else
					site.setConcatSiteId(channelId + partnerCode + siteId);

				site.setItems(itemsList);
				siteList.add(site);
			}

			String interfaceName = Constants.STOCKINTERFACENAME;
			PayloadStock payload = new PayloadStock();
			payload.setSiteAggregatedStock(siteList);

			StockProductSiteLevelDTO stockProduct = new StockProductSiteLevelDTO();
			stockProduct.setHeader(headerResponse(partnerCode, itemCountSize, interfaceName));
			stockProduct.setPayload(payload);
			return new ObjectMapper().writeValueAsString(stockProduct);

		} catch (Exception e) {
			JSONGenerationFailure jsonGenerationFailure = new JSONGenerationFailure(e);
			jsonGenerationFailure.logErrorMessage();
			logger.error("Error occured while genreating JSON", e);
		}
		return null;
	}

	private String responseForPartnerSales(String responseFile, Map<String, String> partnerSalesMap, String partnerCode)
			throws JsonMappingException, JsonProcessingException {
		PartnerSalesJsonDto partnerSalesJson = new PartnerSalesJsonDto();

		if (responseFile.isBlank() || responseFile.isEmpty()) {
			return new String();
		}
		PartnerSalesHeader header = new PartnerSalesHeader();
		PartnerSalesPayLoad payload = new PartnerSalesPayLoad();
		ObjectMapper objectMapper = new ObjectMapper();
		JsonNode rootNode = objectMapper.readTree(responseFile);
		List<TransactionalSales> transactionSalesList = new ArrayList<>();

		// Extracting Header Array
		JsonNode headerNode = rootNode.path(partnerSalesMap.get("header"));
		Map<String, String> headerMap = objectMapper.convertValue(headerNode, Map.class);
		header.setFileName(headerMap.get("filename"));
		Object itemsCountObj = headerMap.get("itemsCount");

		if (itemsCountObj instanceof Integer) {
			header.setItemCount((Integer) itemsCountObj);
		} else if (itemsCountObj instanceof String) {
			try {
				// Attempt to parse the String to Integer
				header.setItemCount(Integer.parseInt((String) itemsCountObj));
			} catch (NumberFormatException e) {
				logger.error("Error while converting number {} ", e);
			}
		}
		partnerSalesJson.setHeader(header);
		// Extracting "aggregatedSales" list
		JsonNode transactionalSalesNode = rootNode.path(partnerSalesMap.get("payload"))
				.path(partnerSalesMap.get("transactionalSales"));

		List<Map<String, Object>> transactionalSalesList = objectMapper.convertValue(transactionalSalesNode,
				List.class);
		// traversing over the "aggregatedSales" list to traverse over the Cart list
		for (int i = 0; i < transactionalSalesList.size(); i++) {

			TransactionalSales transactionalSales = new TransactionalSales();
			Map<String, Object> transactionalSalesMap = transactionalSalesList.get(i);

			String orderStatus = transactionalSalesMap.get(partnerSalesMap.get("orderStatus")).toString();

			if (orderStatus.equalsIgnoreCase("Accepted") || orderStatus.equalsIgnoreCase("Completed")
					|| orderStatus.equalsIgnoreCase("Cancelled")) {

				transactionalSales.setOrderId(transactionalSalesMap.get(partnerSalesMap.get("orderId")).toString());

				transactionalSales.setOrderCreationDate(
						transactionalSalesMap.get(partnerSalesMap.get("orderCreationDate")).toString());

				transactionalSales.setTransactionDate(
						transactionalSalesMap.get(partnerSalesMap.get("transactionDate")).toString());

				transactionalSales.setSiteId(transactionalSalesMap.get(partnerSalesMap.get("siteId")).toString());

				transactionalSales
						.setOrderStatus(transactionalSalesMap.get(partnerSalesMap.get("orderStatus")).toString());

				transactionalSales.setCurrency(transactionalSalesMap.get(partnerSalesMap.get("currency")).toString());

				List<Cart> cartsList = new ArrayList<>();

				JsonNode cartNode = transactionalSalesNode.get(i).path(partnerSalesMap.get("cart"));
				List<Map<String, Object>> cartList = objectMapper.convertValue(cartNode, List.class);

				for (int j = 0; j < cartList.size(); j++) {
					boolean valid = true;
					Map<String, Object> cartMap = cartList.get(j);
					Cart cart = new Cart();

					try {
						float currentSellingPrice = Float
								.parseFloat(cartMap.get(partnerSalesMap.get("currentSellingPrice")).toString());
						float grossAmount = Float
								.parseFloat(cartMap.get(partnerSalesMap.get("grossAmount")).toString());
						float initLaunchPrice = Float
								.parseFloat(cartMap.get(partnerSalesMap.get("initialLaunchPrice")).toString());
						float netAmount = Float.parseFloat(cartMap.get(partnerSalesMap.get("netAmount")).toString());
						float vatValue = Float.parseFloat(cartMap.get(partnerSalesMap.get("vatValue")).toString());
						float baseSellingPrice = Float
								.parseFloat(cartMap.get(partnerSalesMap.get("baseSellingPrice")).toString());
						float discountAmount = Float
								.parseFloat(cartMap.get(partnerSalesMap.get("discountAmount")).toString());

						cart.setBaseSellingPrice(baseSellingPrice);
						cart.setDiscountAmount(discountAmount);
						cart.setCurrentSellingPrice(currentSellingPrice);
						cart.setGrossAmount(grossAmount);
						cart.setInitialLaunchPrice(initLaunchPrice);
						cart.setNetAmount(netAmount);
						cart.setVatValue(vatValue);
					} catch (NumberFormatException e) {
						valid = false;
					}

					cart.setOrderLineId(cartMap.get(partnerSalesMap.get("orderLineId")).toString());
					cart.setFulfillingSiteId(cartMap.get(partnerSalesMap.get("fulfillingSiteId")).toString());
					if (partnerSalesMap.containsKey("fulfillmentId")
							&& cartMap.get(partnerSalesMap.get("fulfillmentId")) != null
							&& !((cartMap.get(partnerSalesMap.get("fulfillmentId"))).toString().isEmpty()))
						cart.setFulfillmentId(cartMap.get(partnerSalesMap.get("fulfillmentId")).toString());

					if (partnerSalesMap.get("shippingCity").matches(("[a-zA-Z0-9]+"))
							&& cartMap.get(partnerSalesMap.get("shippingState")) != null) {
						cart.setShippingCity(cartMap.get(partnerSalesMap.get("shippingCity")).toString());
					} else {
						cart.setShippingCity(null);
					}
					cart.setOrderLineStatus(cartMap.get(partnerSalesMap.get("orderLineStatus")).toString());
					cart.setQuantity(cartMap.get(partnerSalesMap.get("quantity")).toString());
					cart.setShippedDate(cartMap.get(partnerSalesMap.get("shippedDate")).toString());
					cart.setShippingCity(cartMap.get(partnerSalesMap.get("shippingCity")).toString());
					cart.setShippingCountry(cartMap.get(partnerSalesMap.get("shippingCountry")).toString());
					cart.setShippingPostalCode(cartMap.get(partnerSalesMap.get("shippingPostalCode")).toString());

					if (partnerSalesMap.containsKey("shippingState")
							&& cartMap.get(partnerSalesMap.get("shippingState")) != null) {
						cart.setShippingState(cartMap.get(partnerSalesMap.get("shippingState")).toString());

					}

					String skuId = cartMap.get(partnerSalesMap.get("skuId")).toString();
					if (skuId == null || skuId.isBlank()) {
						valid = false;
					} else {
						cart.setSkuId(cartMap.get(partnerSalesMap.get("skuId")).toString());
					}

					if (partnerSalesMap.containsKey("returnOrderId")
							&& cartMap.get(partnerSalesMap.get("returnOrderId")) != null)
						cart.setReturnOrderId(cartMap.get(partnerSalesMap.get("returnOrderId")).toString());
					if (partnerSalesMap.containsKey("returnReason")
							&& cartMap.get(partnerSalesMap.get("returnReason")) != null)
						cart.setReturnReason(cartMap.get(partnerSalesMap.get("returnReason")).toString());
					if (partnerSalesMap.containsKey("returnStatus")
							&& cartMap.get(partnerSalesMap.get("returnStatus")) != null)
						cart.setReturnStatus(cartMap.get(partnerSalesMap.get("returnStatus")).toString());
					if (partnerSalesMap.containsKey("refundStatus")
							&& cartMap.get(partnerSalesMap.get("refundStatus")) != null)
						cart.setRefundStatus(cartMap.get(partnerSalesMap.get("refundStatus")).toString());
					if (partnerSalesMap.containsKey("refundCreatedDatetime")
							&& cartMap.get(partnerSalesMap.get("refundCreatedDatetime")) != null) {
						cart.setRefundCreatedDatetime(
								cartMap.get(partnerSalesMap.get("refundCreatedDatetime")).toString());

					}
					if (partnerSalesMap.containsKey("returnQuantity")
							&& cartMap.get(partnerSalesMap.get("returnQuantity")) != null)
						cart.setReturnQuantity(cartMap.get(partnerSalesMap.get("returnQuantity")).toString());

					if (partnerSalesMap.containsKey("markdownFlag")
							&& cartMap.get(partnerSalesMap.get("markdownFlag")) != null)
						cart.setMarkdownFlag(cartMap.get(partnerSalesMap.get("markdownFlag")).toString());

					if (valid) {
						cartsList.add(cart);
					}
				}
				if (!cartsList.isEmpty()) {
					transactionalSales.setCart(cartsList);
					transactionSalesList.add(transactionalSales);
				}
			}
		}
		payload.setTransactionalSales(transactionSalesList);
		partnerSalesJson.setPayLoad(payload);

		return formingTransactionSalesJsonForBeam(partnerSalesJson, partnerCode);

	}

	public String formingTransactionSalesJsonForBeam(PartnerSalesJsonDto partnerSalesJson, String partnerCode)
			throws JsonProcessingException {
		logger.info("AFG Kuwait sales json creation process start");

		BeamSalesDTO salesJson = new BeamSalesDTO();

		TransactionalSalesPayload payload = new TransactionalSalesPayload();
		List<BeamTransactionalSales> beamTransactionalSalesList = new ArrayList<>();

		Map<String, String> orderStatusMap = getTransactionOrderStatus();
		String customerOrderDeviceTypeID = null;
		String customerOrderSellerOrganisationID = null;

		try (PreparedStatement priceStatement = conn.prepareStatement(SQLQueries.TRANSACTIONAL_SELLER_DETAILS)) {
			priceStatement.setString(1, "107");
			ResultSet resultSet = priceStatement.executeQuery();
			while (resultSet.next()) {
				customerOrderDeviceTypeID = resultSet.getString("customerOrderDeviceTypeID");
				customerOrderSellerOrganisationID = resultSet.getString("customerOrderSellerOrganisationID");
			}
		} catch (Exception e) {
			logger.error(Constants.DB_EXCEPTION, e);
		}

		for (TransactionalSales tranSaleIterate : partnerSalesJson.getPayLoad().getTransactionalSales()) {
			float cartGrossAmt = 0;
			float netAmt = 0;
			float discountAmt = 0;

			BeamTransactionalSales beamTransactionalSales = new BeamTransactionalSales();
			OrderDetail orderDetails = new OrderDetail();
			orderDetails.setOrderNumber(
					partnerCode + "-" + tranSaleIterate.getSiteId() + "-" + tranSaleIterate.getOrderId());
			orderDetails.setOrderCreatedDate(tranSaleIterate.getOrderCreationDate());
			orderDetails.setSiteNumber(tranSaleIterate.getSiteId());
			orderDetails.setConcatSiteId(Constants.ORDER_PLACING_CHANNEL + partnerCode + tranSaleIterate.getSiteId());
			orderDetails.setSiteType("Partner Internal Sites");
			orderDetails.setCurrency(tranSaleIterate.getCurrency());
			orderDetails.setOrderPlacingChannel(Constants.ORDER_PLACING_CHANNEL);
			orderDetails.setOrderPlacingChannelName(Constants.ORDER_PLACING_CHANNEL_NAME);
			orderDetails.setCustomerOrderDeviceTypeID(customerOrderDeviceTypeID);
			orderDetails.setCustomerOrderSellerOrganisationID(customerOrderSellerOrganisationID);

			orderDetails.setOrderPromotionDetails(new OrderPromotionDetail());

			CustomerDetail customerDetail = new CustomerDetail();
			orderDetails.setCustomerDetails(customerDetail);

			List<PaymentDetail> paymentDetails = new ArrayList<>();
			PaymentDetail paymentDetail = new PaymentDetail();
			paymentDetails.add(paymentDetail);
			orderDetails.setPaymentDetails(paymentDetails);

			BillingDetail billingDetail = new BillingDetail();
			orderDetails.setBillingDetails(billingDetail);

			ShipmentDetail shipmentDetail = new ShipmentDetail();

			List<OrderLine> orderLineList = new ArrayList<>();

			for (Cart cart : tranSaleIterate.getCart()) {
				if (!cart.getSkuId().equalsIgnoreCase("NA")) {
					if (cart.getGrossAmount() != null)
						cartGrossAmt += cart.getGrossAmount();

					if (cart.getNetAmount() != null)
						netAmt += cart.getNetAmount();

					if (cart.getDiscountAmount() != null)
						discountAmt += cart.getDiscountAmount();

					OrderLine orderLine = new OrderLine();
					orderLine.setOrderLineId(cart.getOrderLineId());
					orderLine.setPriceType(cart.getMarkdownFlag());
					orderLine.setOrderUpdatedDate(tranSaleIterate.getTransactionDate());
					orderLine.setArticleVariantNumber(cart.getSkuId());
					String orderLineStatus;
					if (cart.getRefundStatus() != null) {
						orderLineStatus = cart.getOrderLineStatus() + "-" + cart.getRefundStatus();
						orderLine.setOrderLineStatus(orderStatusMap.get(orderLineStatus));
						orderLine.setQuantity(cart.getReturnQuantity());
					} else {
						orderLineStatus = cart.getOrderLineStatus();
						orderLine.setOrderLineStatus(orderStatusMap.get(orderLineStatus));
						orderLine.setQuantity(cart.getQuantity());
					}

					orderLine.setArticleNumber(cart.getSkuId());
					orderLine.setMrp(cart.getBaseSellingPrice());
					orderLine.setCurrentSellingPrice(cart.getCurrentSellingPrice());
					orderLine.setDiscountAmount(cart.getDiscountAmount());
					orderLine.setPriceType(cart.getMarkdownFlag());
					orderLine.setVatAmount(cart.getVatValue());
					orderLine.setGrossAmount(cart.getGrossAmount());
					orderLine.setNetAmount(cart.getNetAmount());

					OrderLinePromotionDetail orderLinepromotionDetail = new OrderLinePromotionDetail();
					orderLine.setOrderLinepromotionDetails(orderLinepromotionDetail);

					shipmentDetail.setShippedDate(cart.getShippedDate());
					shipmentDetail.setCustomerCity(cart.getShippingCity());
					shipmentDetail.setCustomerCountry(cart.getShippingCountry());
					shipmentDetail.setCustomerZipCode(cart.getShippingPostalCode());

					FulfillmentDetail fulfillmentDetails = new FulfillmentDetail();
					fulfillmentDetails.setFulfillmentSiteNumber(cart.getFulfillingSiteId());

					orderLine.setFulfillmentDetails(fulfillmentDetails);
					int quantity = Integer.parseInt(cart.getQuantity());
					if (!orderLineStatus.equalsIgnoreCase("null") && quantity > 0) {
						orderLineList.add(orderLine);
					}
				}
			}

			orderDetails.setShipmentDetails(shipmentDetail);
			orderDetails.setOrdergrossAmount(cartGrossAmt);
			orderDetails.setOrdetNetAmount(netAmt);
			orderDetails.setOrderDiscount(discountAmt);
			orderDetails.setOrderLine(orderLineList);
			if (!orderLineList.isEmpty()) {
				beamTransactionalSales.setOrderDetails(orderDetails);
				beamTransactionalSalesList.add(beamTransactionalSales);
				afgItemCount++;
			}

		}
		payload.setAggregatedSales(beamTransactionalSalesList);

		TransactionalSalesHeader header = new TransactionalSalesHeader();
		header.setPartnerNumber(partnerCode);
		header.setSourceApplication(Constants.SOURDEAPPLICATION);
		header.setVersion(Constants.VERSION);
		SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String dateString = format1.format(new Date());
		header.setCreatedTimeStamp(dateString);
		header.setPartnerName(Constants.AFG);
		header.setInterfaceId("I3596");
		header.setInterfaceName("International Sales - daily Aggregate at transactional level");
		header.setItemCount(afgItemCount);
		header.setMessageId(UUID.randomUUID().toString());

		salesJson.setHeader(header);
		salesJson.setPayload(payload);
		logger.info("AFG Kuwait sales json creation  process end");
		return new ObjectMapper().writeValueAsString(salesJson);

	}

	private Map<String, String> getTransactionOrderStatus() {

		Map<String, String> orderStatusMap = new HashMap<>();

		try (PreparedStatement priceStatement = conn.prepareStatement(SQLQueries.TRANSACTIONAL_ORDER_STATUS_DETAILS)) {
			priceStatement.setString(1, "107");
			ResultSet resultSet = priceStatement.executeQuery();
			while (resultSet.next()) {
				String sourceOrderStatus = resultSet.getString("sourceOrderStatus");
				String destinationOrderStatus = resultSet.getString("destinationOrderStatus");
				orderStatusMap.put(sourceOrderStatus, destinationOrderStatus);
			}
		} catch (Exception e) {
			logger.error(Constants.DB_EXCEPTION, e);
		}
		return orderStatusMap;
	}

	public void insertAuditData(List<String[]> allRows, String partnerCode) {
		try {
			logger.info("Inserting data in audit table started");
			int batchId = 0;
			Statement statement = conn.createStatement();
			ResultSet resultSet = statement.executeQuery(SQLQueries.GET_STOCK_BATCHID);
			while (resultSet.next()) {
				batchId = resultSet.getInt(1);
			}

			long recordCount = 0;
			int batchSize = 400;
			PreparedStatement stmt = conn.prepareStatement(SQLQueries.PARTNER_SALES_AUDIT_TABLE);
			conn.setAutoCommit(false);
			for (String[] data : allRows) {

				stmt.setInt(1, batchId);// batchid
				stmt.setString(6, data[0]);// date
				stmt.setString(3, data[1]);// article
				stmt.setString(4, data[2]);// store
				stmt.setString(5, data[4]);// sloc
				stmt.setString(7, data[5]);// UNRESTRICTED_STK
				stmt.setString(8, data[6]);// UNRESTRICTED_STK
				stmt.setString(9, data[7]);// STK_IN_TRANS
				stmt.setInt(2, Integer.parseInt(partnerCode));// franchiseNUmber
				stmt.addBatch();
				recordCount++;
				if (recordCount % batchSize == 0) {
					stmt.executeBatch();
					conn.commit();
				}
			}
			stmt.executeBatch();
			conn.commit();

			logger.info("Inserting data in audit table end");
		} catch (SQLException e) {
			logger.info("error while inserting data in audit table {1} ", e);

		}

	}

	public EmailDTO setInvalidFieldsEmailDetails(String msgText) {

		logger.info("Fetching email details has started ");
		try {

			EmailDTO email = new EmailDTO();
			email.setToEmail(Constants.TO_EMAIL_ID);
			StringBuilder message = ValidationConstants.EMAIL_TEMPLATE_FOR_INVALID_FIELD;
			message.append(msgText);
			message.append("</table>\r\n" + "\r\n" + "</body>");
			email.setMessage(message.toString());
			email.setName("Auto Generated Email");
			email.setSubject("M&S – Partner Sales information " + new SimpleDateFormat("yyyyMMdd").format(new Date()));
			email.setFromEmail(Constants.FROM_EMAIL_ID);
			logger.info("Fetching email details has ended ");

			return email;
		} catch (Exception e) {
			logger.error("Error occured while getting email details", e);
			return null;
		}
	}

    /**
     * @param value
     * @return
     */
    private Float getFloatValue(String value) {
        try {
            return StringUtils.isNotEmpty(value) ? Float.parseFloat(value) : null;
        } catch (NumberFormatException e) {
            return null;
        }
    }

    /**
     * @param itemObj
     * @param partnerAlertMsg
     * @return
     */
    private boolean validateFieldsAndCreateMail(Object itemObj, StringBuilder partnerAlertMsg) {
        Errors errors = null;
        if (itemObj instanceof CyprusPrices cyprusPrices) {
            errors = partnerSalesProcessorHelper.checkFieldValidation(cyprusPrices);
            if (ObjectUtils.isNotEmpty(errors) && errors.hasErrors()) {
                partnerAlertMsg.append(partnerSalesProcessorHelper.createErrorNotificationEmails(cyprusPrices, errors));
                return true;
            }
        } else if (itemObj instanceof stockPrice stockPrice) {
            errors = partnerSalesProcessorHelper.checkFieldValidation(stockPrice);
            if (ObjectUtils.isNotEmpty(errors) && errors.hasErrors()) {
                partnerAlertMsg.append(partnerSalesProcessorHelper.createErrorNotificationEmails(stockPrice, errors));
                return true;
            }
        }
        return false;
    }

    /**
     * @param msgText
     * @return
     */
    private EmailDTO setPriceInvalidFieldsEmailDetails(String msgText) {
        try {
            EmailDTO email = new EmailDTO();
            email.setToEmail(PriceValidationConstants.TO_EMAIL_ID);
            StringBuilder message = new StringBuilder(PriceValidationConstants.EMAIL_TEMPLATE_FOR_INVALID_FIELD);
            message.append(msgText);
            logger.info("Mail content :: {}", msgText);
            message.append("</table>\r\n" + "\r\n" + "</body>");
            email.setMessage(message.toString());
            email.setName("Auto Generated Email");
            email.setSubject("M&S – Partner Price information " + new SimpleDateFormat("yyyyMMdd").format(new Date()));
            email.setFromEmail(PriceValidationConstants.FROM_EMAIL_ID);
            return email;
        } catch (Exception e) {
            logger.error("Error occurred while getting email details", e);
            return null;
        }

    }

}.......in only stock section of the code.............
update to add custom throw exception using java exception only
consider file not parsed parser exception ,timeout exception 
 
filenotfound exception

db and azure conn failed 

upload and download testing failed
 ......tech validations exceptions