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;
        }

    }

}....which portion of code is related to stock interface related processing.....list all the methods for this in above code