package com.prime.sony.ecommerce.serviceImpl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import com.prime.sony.ecommerce.exceptions.DeleteRequestServiceException;
import com.prime.sony.ecommerce.exceptions.TaskStatusServiceException;
import com.prime.sony.ecommerce.model.JobInformation;
import com.prime.sony.ecommerce.model.LogHistory;
import com.prime.sony.ecommerce.model.Store;
import com.prime.sony.ecommerce.model.TaskStatus;
import com.prime.sony.ecommerce.service.TaskStatusService;
import com.prime.sony.ecommerce.util.MailService;

@Service
public class TaskStatusServiceImpl implements TaskStatusService {
	
	private static final Logger log = LoggerFactory.getLogger(TaskStatusServiceImpl.class);

	@Autowired
	private Connection connection;
	
	@Autowired
	private MailService mailService;
	
	@Autowired
	private JobInformation jobInformation;
	
	@Value("${snowflake.schema}")
	private String schema;
	

	@Override
	public List<TaskStatus> loadProductStageToSpTaskHistory() throws TaskStatusServiceException {
		// TODO Auto-generated method stub
		log.info("==>TaskStatusServiceImpl.loadProductStageToSpTaskHistory()");
		List<TaskStatus> taskStatusList = null;
		try {
			taskStatusList = new ArrayList<TaskStatus>();
			Statement statement = connection.createStatement();
			
			// for insert task
			ResultSet resultSet = statement.executeQuery("select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME\r\n" + 
					"  from table(information_schema.task_history(\r\n" + 
					"    scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+jobInformation.getDataIntervalTime()+",current_timestamp()),\r\n" + 
					"    task_name=>'PRODUCT_SP_INSERT_TASK'));");
			while (resultSet.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSet.getString(1));
				task.setName(resultSet.getString(2));
				task.setDatabase_name(resultSet.getString(3));
				task.setSchema_name(resultSet.getString(4));
				task.setState(resultSet.getString(5));
				task.setError_code(resultSet.getString(6));
				task.setError_message(resultSet.getString(7));
				if(null != resultSet.getTimestamp(8)) {
					task.setScheduled_date(resultSet.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(9)) {
					task.setQuery_start_time(resultSet.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(10)) {
					task.setCompleted_time(resultSet.getTimestamp(10).toLocalDateTime());
				}			
				
				taskStatusList.add(task);
			}
			
			// for update task
			ResultSet resultSetForUpdate = statement.executeQuery("select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME\r\n" + 
					"  from table(information_schema.task_history(\r\n" + 
					"    scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+jobInformation.getDataIntervalTime()+",current_timestamp()),\r\n" + 
					"    task_name=>'PRODUCT_SP_UPDATE_TASK'));");
			while (resultSetForUpdate.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSetForUpdate.getString(1));
				task.setName(resultSetForUpdate.getString(2));
				task.setDatabase_name(resultSetForUpdate.getString(3));
				task.setSchema_name(resultSetForUpdate.getString(4));
				task.setState(resultSetForUpdate.getString(5));
				task.setError_code(resultSetForUpdate.getString(6));
				task.setError_message(resultSetForUpdate.getString(7));
				if(null != resultSetForUpdate.getTimestamp(8)) {
					task.setScheduled_date(resultSetForUpdate.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSetForUpdate.getTimestamp(9)) {
					task.setQuery_start_time(resultSetForUpdate.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSetForUpdate.getTimestamp(10)) {
					task.setCompleted_time(resultSetForUpdate.getTimestamp(10).toLocalDateTime());
				}
				
				
				taskStatusList.add(task);
			}
			
			List<TaskStatus> failedTasks = new ArrayList<TaskStatus>();
			if(taskStatusList.size()>0) {
				for (TaskStatus taskStatus : taskStatusList) {
					if(taskStatus.getState().equalsIgnoreCase("FAILED")) {
						failedTasks.add(taskStatus);
					}
					
				}
				
				if(failedTasks.size()>0) {
					mailService.mailsendingForTask("PRODUCT",failedTasks);
				}else {
					mailService.mailsendingForTaskSuccess("PRODUCT");
				}
			}
			
		} catch (Exception e) {
			log.error("TaskStatusServiceImpl.loadProductStageToSpTaskHistory()",e);
			throw new TaskStatusServiceException(e.getMessage(),e.getCause(),null);
		}
		log.info("<==TaskStatusServiceImpl.loadProductStageToSpTaskHistory()");
		return taskStatusList;
	}
	
	@Override
	public List<TaskStatus> loadCustomerStageToSpTaskHistory() throws TaskStatusServiceException {
		// TODO Auto-generated method stub
		log.info("==>TaskStatusServiceImpl.loadCustomerStageToSpTaskHistory()");
		List<TaskStatus> taskStatusList = null;
		try {
			taskStatusList = new ArrayList<TaskStatus>();
			Statement statement = connection.createStatement();
			
			// for insert task
			ResultSet resultSet = statement.executeQuery("select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME\r\n" + 
					"  from table(information_schema.task_history(\r\n" + 
					"    scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+jobInformation.getDataIntervalTime()+",current_timestamp()),\r\n" + 
					"    task_name=>'CUSTOMER_SP_INSERT_TASK'));");
			while (resultSet.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSet.getString(1));
				task.setName(resultSet.getString(2));
				task.setDatabase_name(resultSet.getString(3));
				task.setSchema_name(resultSet.getString(4));
				task.setState(resultSet.getString(5));
				task.setError_code(resultSet.getString(6));
				task.setError_message(resultSet.getString(7));
				if(null != resultSet.getTimestamp(8)) {
					task.setScheduled_date(resultSet.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(9)) {
					task.setQuery_start_time(resultSet.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(10)) {
					task.setCompleted_time(resultSet.getTimestamp(10).toLocalDateTime());
				}	
				taskStatusList.add(task);
			}
			
			// for update task
			ResultSet resultSetForUpdate = statement.executeQuery("select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME\r\n" + 
					"  from table(information_schema.task_history(\r\n" + 
					"    scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+jobInformation.getDataIntervalTime()+",current_timestamp()),\r\n" + 
					"    task_name=>'CUSTOMER_SP_UPDATE_TASK'));");
			while (resultSetForUpdate.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSetForUpdate.getString(1));
				task.setName(resultSetForUpdate.getString(2));
				task.setDatabase_name(resultSetForUpdate.getString(3));
				task.setSchema_name(resultSetForUpdate.getString(4));
				task.setState(resultSetForUpdate.getString(5));
				task.setError_code(resultSetForUpdate.getString(6));
				task.setError_message(resultSetForUpdate.getString(7));
				if(null != resultSetForUpdate.getTimestamp(8)) {
					task.setScheduled_date(resultSetForUpdate.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSetForUpdate.getTimestamp(9)) {
					task.setQuery_start_time(resultSetForUpdate.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSetForUpdate.getTimestamp(10)) {
					task.setCompleted_time(resultSetForUpdate.getTimestamp(10).toLocalDateTime());
				}
				taskStatusList.add(task);
			}
			
			List<TaskStatus> failedTasks = new ArrayList<TaskStatus>();
			if (taskStatusList.size() > 0) {
				for (TaskStatus taskStatus : taskStatusList) {
					if (taskStatus.getState().equalsIgnoreCase("FAILED")) {
						failedTasks.add(taskStatus);
					}

				}

				if (failedTasks.size() > 0) {
					mailService.mailsendingForTask("CUSTOMER", failedTasks);
				} else {
					mailService.mailsendingForTaskSuccess("CUSTOMER");
				}
			}
		} catch (Exception e) {
			log.error("TaskStatusServiceImpl.loadCustomerStageToSpTaskHistory()",e);
			throw new TaskStatusServiceException(e.getMessage(),e.getCause(),null);
		}
		log.info("<==TaskStatusServiceImpl.loadCustomerStageToSpTaskHistory()");
		return taskStatusList;
	}
	
	@Override
	public List<TaskStatus> loadOrderStageToSpTaskHistory() throws TaskStatusServiceException {
		// TODO Auto-generated method stub
		log.info("==>TaskStatusServiceImpl.loadOrderStageToSpTaskHistory()");
		List<TaskStatus> taskStatusList = null;
		try {
			taskStatusList = new ArrayList<TaskStatus>();
			Statement statement = connection.createStatement();
			
			// for insert task
			ResultSet resultSet = statement.executeQuery("select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME\r\n" + 
					"  from table(information_schema.task_history(\r\n" + 
					"    scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+jobInformation.getDataIntervalTime()+",current_timestamp()),\r\n" + 
					"    task_name=>'ORDER_SP_INSERT_TASK'));");
			while (resultSet.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSet.getString(1));
				task.setName(resultSet.getString(2));
				task.setDatabase_name(resultSet.getString(3));
				task.setSchema_name(resultSet.getString(4));
				task.setState(resultSet.getString(5));
				task.setError_code(resultSet.getString(6));
				task.setError_message(resultSet.getString(7));
				if(null != resultSet.getTimestamp(8)) {
					task.setScheduled_date(resultSet.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(9)) {
					task.setQuery_start_time(resultSet.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(10)) {
					task.setCompleted_time(resultSet.getTimestamp(10).toLocalDateTime());
				}
				taskStatusList.add(task);
			}
			
			// for update task
			ResultSet resultSetForUpdate = statement.executeQuery("select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME\r\n" + 
					"  from table(information_schema.task_history(\r\n" + 
					"    scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+jobInformation.getDataIntervalTime()+",current_timestamp()),\r\n" + 
					"    task_name=>'ORDER_SP_UPDATE_TASK'));");
			while (resultSetForUpdate.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSetForUpdate.getString(1));
				task.setName(resultSetForUpdate.getString(2));
				task.setDatabase_name(resultSetForUpdate.getString(3));
				task.setSchema_name(resultSetForUpdate.getString(4));
				task.setState(resultSetForUpdate.getString(5));
				task.setError_code(resultSetForUpdate.getString(6));
				task.setError_message(resultSetForUpdate.getString(7));
				if(null != resultSetForUpdate.getTimestamp(8)) {
					task.setScheduled_date(resultSetForUpdate.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSetForUpdate.getTimestamp(9)) {
					task.setQuery_start_time(resultSetForUpdate.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSetForUpdate.getTimestamp(10)) {
					task.setCompleted_time(resultSetForUpdate.getTimestamp(10).toLocalDateTime());
				}
				taskStatusList.add(task);
			}
			
			List<TaskStatus> failedTasks = new ArrayList<TaskStatus>();
			if (taskStatusList.size() > 0) {
				for (TaskStatus taskStatus : taskStatusList) {
					if (taskStatus.getState().equalsIgnoreCase("FAILED")) {
						failedTasks.add(taskStatus);
					}

				}

				if (failedTasks.size() > 0) {
					mailService.mailsendingForTask("ORDER", failedTasks);
				} else {
					mailService.mailsendingForTaskSuccess("ORDER");
				}
			}
		} catch (Exception e) {
			log.error("TaskStatusServiceImpl.loadOrderStageToSpTaskHistory()",e);
			throw new TaskStatusServiceException(e.getMessage(),e.getCause(),null);
		}
		log.info("<==TaskStatusServiceImpl.loadOrderStageToSpTaskHistory()");
		return taskStatusList;
	}

	@Override
	public List<TaskStatus> loadProductSpToEcommerceTaskHistory() throws TaskStatusServiceException {
		// TODO Auto-generated method stub
		log.info("==>TaskStatusServiceImpl.loadProductSpToEcommerceTaskHistory()");
		List<TaskStatus> taskStatusList = null;
		try {
			taskStatusList = new ArrayList<TaskStatus>();
			Statement statement = connection.createStatement();
			
			// for insert task
			ResultSet resultSet = statement.executeQuery("select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME\r\n" + 
					"  from table(information_schema.task_history(\r\n" + 
					"    scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+jobInformation.getDataIntervalTime()+",current_timestamp()),\r\n" + 
					"    task_name=>'PRODUCT_INSERT_WOOCOMMERCE_ITALY'));");
			while (resultSet.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSet.getString(1));
				task.setName(resultSet.getString(2));
				task.setDatabase_name(resultSet.getString(3));
				task.setSchema_name(resultSet.getString(4));
				task.setState(resultSet.getString(5));
				task.setError_code(resultSet.getString(6));
				task.setError_message(resultSet.getString(7));
				if(null != resultSet.getTimestamp(8)) {
					task.setScheduled_date(resultSet.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(9)) {
					task.setQuery_start_time(resultSet.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(10)) {
					task.setCompleted_time(resultSet.getTimestamp(10).toLocalDateTime());
				}
				taskStatusList.add(task);
			}
			
			// for update task
			ResultSet resultSetForUpdate = statement.executeQuery("select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME\r\n" + 
					"  from table(information_schema.task_history(\r\n" + 
					"    scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+jobInformation.getDataIntervalTime()+",current_timestamp()),\r\n" + 
					"    task_name=>'PRODUCT_UPDATE_WOOCOMMERCE_ITALY'));");
			while (resultSetForUpdate.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSetForUpdate.getString(1));
				task.setName(resultSetForUpdate.getString(2));
				task.setDatabase_name(resultSetForUpdate.getString(3));
				task.setSchema_name(resultSetForUpdate.getString(4));
				task.setState(resultSetForUpdate.getString(5));
				task.setError_code(resultSetForUpdate.getString(6));
				task.setError_message(resultSetForUpdate.getString(7));

				if(null != resultSetForUpdate.getTimestamp(8)) {
					task.setScheduled_date(resultSetForUpdate.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSetForUpdate.getTimestamp(9)) {
					task.setQuery_start_time(resultSetForUpdate.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSetForUpdate.getTimestamp(10)) {
					task.setCompleted_time(resultSetForUpdate.getTimestamp(10).toLocalDateTime());
				}
				taskStatusList.add(task);
			}
			
			List<TaskStatus> failedTasks = new ArrayList<TaskStatus>();
			if (taskStatusList.size() > 0) {
				for (TaskStatus taskStatus : taskStatusList) {
					if (taskStatus.getState().equalsIgnoreCase("FAILED")) {
						failedTasks.add(taskStatus);
					}

				}

				if (failedTasks.size() > 0) {
					mailService.mailsendingForTask("PRODUCTSPTOECOMMERCE", failedTasks);
				} else {
					mailService.mailsendingForTaskSuccess("PRODUCTSPTOECOMMERCE");
				}
			}
		} catch (Exception e) {
			log.error("TaskStatusServiceImpl.loadProductSpToEcommerceTaskHistory()",e);
			throw new TaskStatusServiceException(e.getMessage(),e.getCause(),null);
		}
		log.info("<==TaskStatusServiceImpl.loadProductSpToEcommerceTaskHistory()");
		return taskStatusList;
	}

	@Override
	public List<TaskStatus> loadCustomerSpToEcommerceTaskHistory() throws TaskStatusServiceException {
		// TODO Auto-generated method stub
		log.info("==>TaskStatusServiceImpl.loadCustomerSpToEcommerceTaskHistory()");
		List<TaskStatus> taskStatusList = null;
		try {
			taskStatusList = new ArrayList<TaskStatus>();
			Statement statement = connection.createStatement();
			
			// for insert task
			ResultSet resultSet = statement.executeQuery("select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME\r\n" + 
					"  from table(information_schema.task_history(\r\n" + 
					"    scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+jobInformation.getDataIntervalTime()+",current_timestamp()),\r\n" + 
					"    task_name=>'CUSTOMER_INSERT_WOOCOMMERCE_ITALY'));");
			while (resultSet.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSet.getString(1));
				task.setName(resultSet.getString(2));
				task.setDatabase_name(resultSet.getString(3));
				task.setSchema_name(resultSet.getString(4));
				task.setState(resultSet.getString(5));
				task.setError_code(resultSet.getString(6));
				task.setError_message(resultSet.getString(7));
				if(null != resultSet.getTimestamp(8)) {
					task.setScheduled_date(resultSet.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(9)) {
					task.setQuery_start_time(resultSet.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(10)) {
					task.setCompleted_time(resultSet.getTimestamp(10).toLocalDateTime());
				}	
				taskStatusList.add(task);
			}
			
			// for update task
			ResultSet resultSetForUpdate = statement.executeQuery("select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME\r\n" + 
					"  from table(information_schema.task_history(\r\n" + 
					"    scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+jobInformation.getDataIntervalTime()+",current_timestamp()),\r\n" + 
					"    task_name=>'CUSTOMER_UPDATE_WOOCOMMERCE_ITALY'));");
			while (resultSetForUpdate.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSetForUpdate.getString(1));
				task.setName(resultSetForUpdate.getString(2));
				task.setDatabase_name(resultSetForUpdate.getString(3));
				task.setSchema_name(resultSetForUpdate.getString(4));
				task.setState(resultSetForUpdate.getString(5));
				task.setError_code(resultSetForUpdate.getString(6));
				task.setError_message(resultSetForUpdate.getString(7));

				if(null != resultSetForUpdate.getTimestamp(8)) {
					task.setScheduled_date(resultSetForUpdate.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSetForUpdate.getTimestamp(9)) {
					task.setQuery_start_time(resultSetForUpdate.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSetForUpdate.getTimestamp(10)) {
					task.setCompleted_time(resultSetForUpdate.getTimestamp(10).toLocalDateTime());
				}
				taskStatusList.add(task);
			}

			List<TaskStatus> failedTasks = new ArrayList<TaskStatus>();
			if (taskStatusList.size() > 0) {
				for (TaskStatus taskStatus : taskStatusList) {
					if (taskStatus.getState().equalsIgnoreCase("FAILED")) {
						failedTasks.add(taskStatus);
					}

				}

				if (failedTasks.size() > 0) {
					mailService.mailsendingForTask("CUSTOMERSPTOECOMMERCE", failedTasks);
				} else {
					mailService.mailsendingForTaskSuccess("CUSTOMERSPTOECOMMERCE");
				}
			}
		} catch (Exception e) {
			log.error("TaskStatusServiceImpl.loadCustomerSpToEcommerceTaskHistory()",e);
			throw new TaskStatusServiceException(e.getMessage(),e.getCause(),null);
		}
		log.info("<==TaskStatusServiceImpl.loadCustomerSpToEcommerceTaskHistory()");
		return taskStatusList;
	}

	@Override
	public List<TaskStatus> loadOrderSpToEcommerceTaskHistory() throws TaskStatusServiceException {
		// TODO Auto-generated method stub
		log.info("==>TaskStatusServiceImpl.loadOrderSpToEcommerceTaskHistory()");
		List<TaskStatus> taskStatusList = null;
		try {
			taskStatusList = new ArrayList<TaskStatus>();
			Statement statement = connection.createStatement();
			
			// for insert task
			ResultSet resultSet = statement.executeQuery("select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME\r\n" + 
					"  from table(information_schema.task_history(\r\n" + 
					"    scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+jobInformation.getDataIntervalTime()+",current_timestamp()),\r\n" + 
					"    task_name=>'ORDERS_INSERT_WOOCOMMERCE_ITALY'));");
			while (resultSet.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSet.getString(1));
				task.setName(resultSet.getString(2));
				task.setDatabase_name(resultSet.getString(3));
				task.setSchema_name(resultSet.getString(4));
				task.setState(resultSet.getString(5));
				task.setError_code(resultSet.getString(6));
				task.setError_message(resultSet.getString(7));
				if(null != resultSet.getTimestamp(8)) {
					task.setScheduled_date(resultSet.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(9)) {
					task.setQuery_start_time(resultSet.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(10)) {
					task.setCompleted_time(resultSet.getTimestamp(10).toLocalDateTime());
				}	
				taskStatusList.add(task);
			}
			
			// for update task
			ResultSet resultSetForUpdate = statement.executeQuery("select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME\r\n" + 
					"  from table(information_schema.task_history(\r\n" + 
					"    scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+jobInformation.getDataIntervalTime()+",current_timestamp()),\r\n" + 
					"    task_name=>'ORDERS_UPDATE_WOOCOMMERCE_ITALY'));");
			while (resultSetForUpdate.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSetForUpdate.getString(1));
				task.setName(resultSetForUpdate.getString(2));
				task.setDatabase_name(resultSetForUpdate.getString(3));
				task.setSchema_name(resultSetForUpdate.getString(4));
				task.setState(resultSetForUpdate.getString(5));
				task.setError_code(resultSetForUpdate.getString(6));
				task.setError_message(resultSetForUpdate.getString(7));
				if(null != resultSetForUpdate.getTimestamp(8)) {
					task.setScheduled_date(resultSetForUpdate.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSetForUpdate.getTimestamp(9)) {
					task.setQuery_start_time(resultSetForUpdate.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSetForUpdate.getTimestamp(10)) {
					task.setCompleted_time(resultSetForUpdate.getTimestamp(10).toLocalDateTime());
				}
				taskStatusList.add(task);
			}
			
			List<TaskStatus> failedTasks = new ArrayList<TaskStatus>();
			if (taskStatusList.size() > 0) {
				for (TaskStatus taskStatus : taskStatusList) {
					if (taskStatus.getState().equalsIgnoreCase("FAILED")) {
						failedTasks.add(taskStatus);
					}

				}

				if (failedTasks.size() > 0) {
					mailService.mailsendingForTask("ORDERSPTOECOMMERCE", failedTasks);
				} else {
					mailService.mailsendingForTaskSuccess("ORDERSPTOECOMMERCE");
				}
			}
		} catch (Exception e) {
			log.error("TaskStatusServiceImpl.loadOrderSpToEcommerceTaskHistory()",e);
			throw new TaskStatusServiceException(e.getMessage(),e.getCause(),null);
		}
		log.info("<==TaskStatusServiceImpl.loadOrderSpToEcommerceTaskHistory()");
		return taskStatusList;
	}
	
	@Override
	public List<TaskStatus> loadCustomerLookupEcommerceTaskHistory() throws TaskStatusServiceException {
		// TODO Auto-generated method stub
		log.info("==>TaskStatusServiceImpl.loadCustomerLookupEcommerceTaskHistory()");
		List<TaskStatus> taskStatusList = null;
		try {
			taskStatusList = new ArrayList<TaskStatus>();
			Statement statement = connection.createStatement();
			
			// for update lookup
			ResultSet resultSetForUpdateLookUp = statement.executeQuery(
					"select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME\r\n"
							+ "  from table(information_schema.task_history(\r\n"
							+ "    scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+jobInformation.getDataIntervalTime()+",current_timestamp()),\r\n"
							+ "    task_name=>'CUSTOMER_INSERT_LOOKUP_WOOCOMMERCE_ITALY'));");
			while (resultSetForUpdateLookUp.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSetForUpdateLookUp.getString(1));
				task.setName(resultSetForUpdateLookUp.getString(2));
				task.setDatabase_name(resultSetForUpdateLookUp.getString(3));
				task.setSchema_name(resultSetForUpdateLookUp.getString(4));
				task.setState(resultSetForUpdateLookUp.getString(5));
				task.setError_code(resultSetForUpdateLookUp.getString(6));
				task.setError_message(resultSetForUpdateLookUp.getString(7));
				if(null != resultSetForUpdateLookUp.getTimestamp(8)) {
					task.setScheduled_date(resultSetForUpdateLookUp.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSetForUpdateLookUp.getTimestamp(9)) {
					task.setQuery_start_time(resultSetForUpdateLookUp.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSetForUpdateLookUp.getTimestamp(10)) {
					task.setCompleted_time(resultSetForUpdateLookUp.getTimestamp(10).toLocalDateTime());
				}
				taskStatusList.add(task);
			}

			
			List<TaskStatus> failedTasks = new ArrayList<TaskStatus>();
			if (taskStatusList.size() > 0) {
				for (TaskStatus taskStatus : taskStatusList) {
					if (taskStatus.getState().equalsIgnoreCase("FAILED")) {
						failedTasks.add(taskStatus);
					}

				}

				if (failedTasks.size() > 0) {
					mailService.mailsendingForTask("CUSTOMERLOOKUPTOECOMMERCE", failedTasks);
				} else {
					mailService.mailsendingForTaskSuccess("CUSTOMERLOOKUPTOECOMMERCE");
				}
			}
		} catch (Exception e) {
			log.error("TaskStatusServiceImpl.loadCustomerLookupEcommerceTaskHistory()",e);
			throw new TaskStatusServiceException(e.getMessage(),e.getCause(),null);
		}
		log.info("<==TaskStatusServiceImpl.loadCustomerLookupEcommerceTaskHistory()");
		return taskStatusList;
	}

	@Override
	public List<TaskStatus> loadCustomerTotalSpentEcommerceTaskHistory() throws TaskStatusServiceException {
		// TODO Auto-generated method stub
		log.info("==>TaskStatusServiceImpl.loadCustomerTotalSpentEcommerceTaskHistory()");
		List<TaskStatus> taskStatusList = null;
		try {
			taskStatusList = new ArrayList<TaskStatus>();
			Statement statement = connection.createStatement();
			// for update total spent
			ResultSet resultSetForUpdateTotalSpent = statement.executeQuery(
					"select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME\r\n"
							+ "  from table(information_schema.task_history(\r\n"
							+ "    scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+jobInformation.getDataIntervalTime()+",current_timestamp()),\r\n"
							+ "    task_name=>'CUSTOMER_UPDATE_TOTAL_SPENT_WOOCOMMERCE_ITALY'));");
			while (resultSetForUpdateTotalSpent.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSetForUpdateTotalSpent.getString(1));
				task.setName(resultSetForUpdateTotalSpent.getString(2));
				task.setDatabase_name(resultSetForUpdateTotalSpent.getString(3));
				task.setSchema_name(resultSetForUpdateTotalSpent.getString(4));
				task.setState(resultSetForUpdateTotalSpent.getString(5));
				task.setError_code(resultSetForUpdateTotalSpent.getString(6));
				task.setError_message(resultSetForUpdateTotalSpent.getString(7));
				if(null != resultSetForUpdateTotalSpent.getTimestamp(8)) {
					task.setScheduled_date(resultSetForUpdateTotalSpent.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSetForUpdateTotalSpent.getTimestamp(9)) {
					task.setQuery_start_time(resultSetForUpdateTotalSpent.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSetForUpdateTotalSpent.getTimestamp(10)) {
					task.setCompleted_time(resultSetForUpdateTotalSpent.getTimestamp(10).toLocalDateTime());
				}
				taskStatusList.add(task);
			}

			List<TaskStatus> failedTasks = new ArrayList<TaskStatus>();
			if (taskStatusList.size() > 0) {
				for (TaskStatus taskStatus : taskStatusList) {
					if (taskStatus.getState().equalsIgnoreCase("FAILED")) {
						failedTasks.add(taskStatus);
					}

				}

				if (failedTasks.size() > 0) {
					mailService.mailsendingForTask("CUSTOMERTOTALSPENTTOECOMMERCE", failedTasks);
				} else {
					mailService.mailsendingForTaskSuccess("CUSTOMERTOTALSPENTTOECOMMERCE");
				}
			}
		} catch (Exception e) {
			log.error("TaskStatusServiceImpl.loadCustomerTotalSpentEcommerceTaskHistory()", e);
			throw new TaskStatusServiceException(e.getMessage(), e.getCause(), null);
		}
		log.info("<==TaskStatusServiceImpl.loadCustomerTotalSpentEcommerceTaskHistory()");
		return taskStatusList;
	}

	@Override
	public List<TaskStatus> loadProductForShopifyToEcommerceTaskHistory() throws TaskStatusServiceException {
		// TODO Auto-generated method stub
		log.info("==>TaskStatusServiceImpl.loadProductShopifyToEcommerceTaskHistory()");
		List<TaskStatus> taskStatusList = null;
		try {
			taskStatusList = new ArrayList<TaskStatus>();
			Statement statement = connection.createStatement();
			
			// for insert task
			ResultSet resultSet = statement.executeQuery("select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME\r\n" + 
					"  from table(information_schema.task_history(\r\n" + 
					"    scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+jobInformation.getDataIntervalTime()+",current_timestamp()),\r\n" + 
					"    task_name=>'PRODUCT_INSERT_SHOPIFY_NOTHING_BUT_THIEVES'));");
			while (resultSet.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSet.getString(1));
				task.setName(resultSet.getString(2));
				task.setDatabase_name(resultSet.getString(3));
				task.setSchema_name(resultSet.getString(4));
				task.setState(resultSet.getString(5));
				task.setError_code(resultSet.getString(6));
				task.setError_message(resultSet.getString(7));
				if(null != resultSet.getTimestamp(8)) {
					task.setScheduled_date(resultSet.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(9)) {
					task.setQuery_start_time(resultSet.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(10)) {
					task.setCompleted_time(resultSet.getTimestamp(10).toLocalDateTime());
				}
				taskStatusList.add(task);
			}
			
			// for update task
			ResultSet resultSetForUpdate = statement.executeQuery("select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME\r\n" + 
					"  from table(information_schema.task_history(\r\n" + 
					"    scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+jobInformation.getDataIntervalTime()+",current_timestamp()),\r\n" + 
					"    task_name=>'PRODUCT_UPDATE_SHOPIFY_NOTHING_BUT_THIEVES'));");
			while (resultSetForUpdate.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSetForUpdate.getString(1));
				task.setName(resultSetForUpdate.getString(2));
				task.setDatabase_name(resultSetForUpdate.getString(3));
				task.setSchema_name(resultSetForUpdate.getString(4));
				task.setState(resultSetForUpdate.getString(5));
				task.setError_code(resultSetForUpdate.getString(6));
				task.setError_message(resultSetForUpdate.getString(7));
				if(null != resultSetForUpdate.getTimestamp(8)) {
					task.setScheduled_date(resultSetForUpdate.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSetForUpdate.getTimestamp(9)) {
					task.setQuery_start_time(resultSetForUpdate.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSetForUpdate.getTimestamp(10)) {
					task.setCompleted_time(resultSetForUpdate.getTimestamp(10).toLocalDateTime());
				}
				taskStatusList.add(task);
			}
			
			List<TaskStatus> failedTasks = new ArrayList<TaskStatus>();
			if (taskStatusList.size() > 0) {
				for (TaskStatus taskStatus : taskStatusList) {
					if (taskStatus.getState().equalsIgnoreCase("FAILED")) {
						failedTasks.add(taskStatus);
					}

				}

				if (failedTasks.size() > 0) {
					mailService.mailsendingForTask("PRODUCTTOECOMMERCEFROMSHOPIFY", failedTasks);
				} else {
					mailService.mailsendingForTaskSuccess("PRODUCTTOECOMMERCEFROMSHOPIFY");
				}
			}
		} catch (Exception e) {
			log.error("TaskStatusServiceImpl.loadProductShopifyToEcommerceTaskHistory()",e);
			throw new TaskStatusServiceException(e.getMessage(),e.getCause(),null);
		}
		log.info("<==TaskStatusServiceImpl.loadProductShopifyToEcommerceTaskHistory()");
		return taskStatusList;
	}

	@Override
	public List<TaskStatus> loadCustomerForShopifyToEcommerceTaskHistory() throws TaskStatusServiceException {
		// TODO Auto-generated method stub
		log.info("==>TaskStatusServiceImpl.loadCustomerForShopifyToEcommerceTaskHistory()");
		List<TaskStatus> taskStatusList = null;
		try {
			taskStatusList = new ArrayList<TaskStatus>();
			Statement statement = connection.createStatement();
			
			// for insert task
			ResultSet resultSet = statement.executeQuery("select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME\r\n" + 
					"  from table(information_schema.task_history(\r\n" + 
					"    scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+jobInformation.getDataIntervalTime()+",current_timestamp()),\r\n" + 
					"    task_name=>'CUSTOMER_INSERT_SHOPIFY_NOTHING_BUT_THIEVES'));");
			while (resultSet.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSet.getString(1));
				task.setName(resultSet.getString(2));
				task.setDatabase_name(resultSet.getString(3));
				task.setSchema_name(resultSet.getString(4));
				task.setState(resultSet.getString(5));
				task.setError_code(resultSet.getString(6));
				task.setError_message(resultSet.getString(7));
				if(null != resultSet.getTimestamp(8)) {
					task.setScheduled_date(resultSet.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(9)) {
					task.setQuery_start_time(resultSet.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(10)) {
					task.setCompleted_time(resultSet.getTimestamp(10).toLocalDateTime());
				}
				taskStatusList.add(task);
			}
			
			// for update task
			ResultSet resultSetForUpdate = statement.executeQuery("select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME\r\n" + 
					"  from table(information_schema.task_history(\r\n" + 
					"    scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+jobInformation.getDataIntervalTime()+",current_timestamp()),\r\n" + 
					"    task_name=>'CUSTOMER_UPDATE_SHOPIFY_NOTHING_BUT_THIEVES'));");
			while (resultSetForUpdate.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSetForUpdate.getString(1));
				task.setName(resultSetForUpdate.getString(2));
				task.setDatabase_name(resultSetForUpdate.getString(3));
				task.setSchema_name(resultSetForUpdate.getString(4));
				task.setState(resultSetForUpdate.getString(5));
				task.setError_code(resultSetForUpdate.getString(6));
				task.setError_message(resultSetForUpdate.getString(7));
				if(null != resultSetForUpdate.getTimestamp(8)) {
					task.setScheduled_date(resultSetForUpdate.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSetForUpdate.getTimestamp(9)) {
					task.setQuery_start_time(resultSetForUpdate.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSetForUpdate.getTimestamp(10)) {
					task.setCompleted_time(resultSetForUpdate.getTimestamp(10).toLocalDateTime());
				}
				taskStatusList.add(task);
			}
			
			List<TaskStatus> failedTasks = new ArrayList<TaskStatus>();
			if (taskStatusList.size() > 0) {
				for (TaskStatus taskStatus : taskStatusList) {
					if (taskStatus.getState().equalsIgnoreCase("FAILED")) {
						failedTasks.add(taskStatus);
					}

				}

				if (failedTasks.size() > 0) {
					mailService.mailsendingForTask("CUSTOMERTOECOMMERCEFROMSHOPIFY", failedTasks);
				} else {
					mailService.mailsendingForTaskSuccess("CUSTOMERTOECOMMERCEFROMSHOPIFY");
				}
			}
		} catch (Exception e) {
			log.error("TaskStatusServiceImpl.loadCustomerForShopifyToEcommerceTaskHistory()",e);
			throw new TaskStatusServiceException(e.getMessage(),e.getCause(),null);
		}
		log.info("<==TaskStatusServiceImpl.loadCustomerForShopifyToEcommerceTaskHistory()");
		return taskStatusList;
	}

	@Override
	public List<TaskStatus> loadOrderForShopifyToEcommerceTaskHistory() throws TaskStatusServiceException {
		// TODO Auto-generated method stub
		log.info("==>TaskStatusServiceImpl.loadOrderForShopifyToEcommerceTaskHistory()");
		List<TaskStatus> taskStatusList = null;
		try {
			taskStatusList = new ArrayList<TaskStatus>();
			Statement statement = connection.createStatement();
			
			// for insert task
			ResultSet resultSet = statement.executeQuery("select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME\r\n" + 
					"  from table(information_schema.task_history(\r\n" + 
					"    scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+jobInformation.getDataIntervalTime()+",current_timestamp()),\r\n" + 
					"    task_name=>'ORDER_INSERT_SHOPIFY_NOTHING_BUT_THIEVES'));");
			while (resultSet.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSet.getString(1));
				task.setName(resultSet.getString(2));
				task.setDatabase_name(resultSet.getString(3));
				task.setSchema_name(resultSet.getString(4));
				task.setState(resultSet.getString(5));
				task.setError_code(resultSet.getString(6));
				task.setError_message(resultSet.getString(7));
				if(null != resultSet.getTimestamp(8)) {
					task.setScheduled_date(resultSet.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(9)) {
					task.setQuery_start_time(resultSet.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(10)) {
					task.setCompleted_time(resultSet.getTimestamp(10).toLocalDateTime());
				}
				taskStatusList.add(task);
			}
			
			// for update task
			ResultSet resultSetForUpdate = statement.executeQuery("select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME\r\n" + 
					"  from table(information_schema.task_history(\r\n" + 
					"    scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+jobInformation.getDataIntervalTime()+",current_timestamp()),\r\n" + 
					"    task_name=>'ORDER_UPDATE_SHOPIFY_NOTHING_BUT_THIEVES'));");
			while (resultSetForUpdate.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSetForUpdate.getString(1));
				task.setName(resultSetForUpdate.getString(2));
				task.setDatabase_name(resultSetForUpdate.getString(3));
				task.setSchema_name(resultSetForUpdate.getString(4));
				task.setState(resultSetForUpdate.getString(5));
				task.setError_code(resultSetForUpdate.getString(6));
				task.setError_message(resultSetForUpdate.getString(7));
				if(null != resultSetForUpdate.getTimestamp(8)) {
					task.setScheduled_date(resultSetForUpdate.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSetForUpdate.getTimestamp(9)) {
					task.setQuery_start_time(resultSetForUpdate.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSetForUpdate.getTimestamp(10)) {
					task.setCompleted_time(resultSetForUpdate.getTimestamp(10).toLocalDateTime());
				}
				taskStatusList.add(task);
			}
			
			List<TaskStatus> failedTasks = new ArrayList<TaskStatus>();
			if (taskStatusList.size() > 0) {
				for (TaskStatus taskStatus : taskStatusList) {
					if (taskStatus.getState().equalsIgnoreCase("FAILED")) {
						failedTasks.add(taskStatus);
					}

				}

				if (failedTasks.size() > 0) {
					mailService.mailsendingForTask("ORDERTOECOMMERCEFROMSHOPIFY", failedTasks);
				} else {
					mailService.mailsendingForTaskSuccess("ORDERTOECOMMERCEFROMSHOPIFY");
				}
			}
		} catch (Exception e) {
			log.error("TaskStatusServiceImpl.loadOrderForShopifyToEcommerceTaskHistory()",e);
			throw new TaskStatusServiceException(e.getMessage(),e.getCause(),null);
		}
		log.info("<==TaskStatusServiceImpl.loadOrderForShopifyToEcommerceTaskHistory()");
		return taskStatusList;

	}

	@Override
	public List<TaskStatus> loadCustomerLookupForShopifyToEcommerceTaskHistory() throws TaskStatusServiceException {
		// TODO Auto-generated method stub
		log.info("==>TaskStatusServiceImpl.loadCustomerLookupForShopifyToEcommerceTaskHistory()");
		List<TaskStatus> taskStatusList = null;
		try {
			taskStatusList = new ArrayList<TaskStatus>();
			Statement statement = connection.createStatement();
			
			// for update lookup
			ResultSet resultSetForUpdateLookUp = statement.executeQuery(
					"select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME\r\n"
							+ "  from table(information_schema.task_history(\r\n"
							+ "    scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+jobInformation.getDataIntervalTime()+",current_timestamp()),\r\n"
							+ "    task_name=>'CUSTOMER_INSERT_LOOKUP_SHOPIFY_NOTHING_BUT_THIEVES'));");
			while (resultSetForUpdateLookUp.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSetForUpdateLookUp.getString(1));
				task.setName(resultSetForUpdateLookUp.getString(2));
				task.setDatabase_name(resultSetForUpdateLookUp.getString(3));
				task.setSchema_name(resultSetForUpdateLookUp.getString(4));
				task.setState(resultSetForUpdateLookUp.getString(5));
				task.setError_code(resultSetForUpdateLookUp.getString(6));
				task.setError_message(resultSetForUpdateLookUp.getString(7));
				if(null != resultSetForUpdateLookUp.getTimestamp(8)) {
					task.setScheduled_date(resultSetForUpdateLookUp.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSetForUpdateLookUp.getTimestamp(9)) {
					task.setQuery_start_time(resultSetForUpdateLookUp.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSetForUpdateLookUp.getTimestamp(10)) {
					task.setCompleted_time(resultSetForUpdateLookUp.getTimestamp(10).toLocalDateTime());
				}
				taskStatusList.add(task);
			}

			
			List<TaskStatus> failedTasks = new ArrayList<TaskStatus>();
			if (taskStatusList.size() > 0) {
				for (TaskStatus taskStatus : taskStatusList) {
					if (taskStatus.getState().equalsIgnoreCase("FAILED")) {
						failedTasks.add(taskStatus);
					}

				}

				if (failedTasks.size() > 0) {
					mailService.mailsendingForTask("CUSTOMERLOOKUPFORSHOPIFYTOECOMMERCE", failedTasks);
				} else {
					mailService.mailsendingForTaskSuccess("CUSTOMERLOOKUPFORSHOPIFYTOECOMMERCE");
				}
			}
		} catch (Exception e) {
			log.error("TaskStatusServiceImpl.loadCustomerLookupForShopifyToEcommerceTaskHistory()",e);
			throw new TaskStatusServiceException(e.getMessage(),e.getCause(),null);
		}
		log.info("<==TaskStatusServiceImpl.loadCustomerLookupForShopifyToEcommerceTaskHistory()");
		return taskStatusList;
	}

	@Override
	public List<TaskStatus> loadCustomerTotalSpentForShopifyEcommerceTaskHistory() throws TaskStatusServiceException {
		// TODO Auto-generated method stub
		log.info("==>TaskStatusServiceImpl.loadCustomerTotalSpentForShopifyEcommerceTaskHistory()");
		List<TaskStatus> taskStatusList = null;
		try {
			taskStatusList = new ArrayList<TaskStatus>();
			Statement statement = connection.createStatement();
			
			// for update lookup
			ResultSet resultSetForUpdateLookUp = statement.executeQuery(
					"select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME\r\n"
							+ "  from table(information_schema.task_history(\r\n"
							+ "    scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+jobInformation.getDataIntervalTime()+",current_timestamp()),\r\n"
							+ "    task_name=>'CUSTOMER_UPDATE_TOTAL_SPENT_SHOPIFY_NOTHING_BUT_THIEVES'));");
			while (resultSetForUpdateLookUp.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSetForUpdateLookUp.getString(1));
				task.setName(resultSetForUpdateLookUp.getString(2));
				task.setDatabase_name(resultSetForUpdateLookUp.getString(3));
				task.setSchema_name(resultSetForUpdateLookUp.getString(4));
				task.setState(resultSetForUpdateLookUp.getString(5));
				task.setError_code(resultSetForUpdateLookUp.getString(6));
				task.setError_message(resultSetForUpdateLookUp.getString(7));
				if(null != resultSetForUpdateLookUp.getTimestamp(8)) {
					task.setScheduled_date(resultSetForUpdateLookUp.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSetForUpdateLookUp.getTimestamp(9)) {
					task.setQuery_start_time(resultSetForUpdateLookUp.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSetForUpdateLookUp.getTimestamp(10)) {
					task.setCompleted_time(resultSetForUpdateLookUp.getTimestamp(10).toLocalDateTime());
				}
				taskStatusList.add(task);
			}

			
			List<TaskStatus> failedTasks = new ArrayList<TaskStatus>();
			if (taskStatusList.size() > 0) {
				for (TaskStatus taskStatus : taskStatusList) {
					if (taskStatus.getState().equalsIgnoreCase("FAILED")) {
						failedTasks.add(taskStatus);
					}

				}

				if (failedTasks.size() > 0) {
					mailService.mailsendingForTask("CUSTOMERTOTALSPENTFORSHOPIFYTOECOMMERCE", failedTasks);
				} else {
					mailService.mailsendingForTaskSuccess("CUSTOMERTOTALSPENTFORSHOPIFYTOECOMMERCE");
				}
			}
		} catch (Exception e) {
			log.error("TaskStatusServiceImpl.loadCustomerTotalSpentForShopifyEcommerceTaskHistory()",e);
			throw new TaskStatusServiceException(e.getMessage(),e.getCause(),null);
		}
		log.info("<==TaskStatusServiceImpl.loadCustomerTotalSpentForShopifyEcommerceTaskHistory()");
		return taskStatusList;
	}

	@Override
	public List<TaskStatus> loadOrderTotalQuantityUpdateForShopifyToEcommerceTaskHistory()
			throws TaskStatusServiceException {
		// TODO Auto-generated method stub
		log.info("==>TaskStatusServiceImpl.loadOrderTotalQuantityUpdateForShopifyToEcommerceTaskHistory()");
		List<TaskStatus> taskStatusList = null;
		try {
			taskStatusList = new ArrayList<TaskStatus>();
			Statement statement = connection.createStatement();
			
			// for update lookup
			ResultSet resultSetForUpdateLookUp = statement.executeQuery(
					"select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME\r\n"
							+ "  from table(information_schema.task_history(\r\n"
							+ "    scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+jobInformation.getDataIntervalTime()+",current_timestamp()),\r\n"
							+ "    task_name=>'ORDER_TOTAL_QUANTITY_UPDATE_SHOPIFY_NOTHING_BUT_THIEVES'));");
			while (resultSetForUpdateLookUp.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSetForUpdateLookUp.getString(1));
				task.setName(resultSetForUpdateLookUp.getString(2));
				task.setDatabase_name(resultSetForUpdateLookUp.getString(3));
				task.setSchema_name(resultSetForUpdateLookUp.getString(4));
				task.setState(resultSetForUpdateLookUp.getString(5));
				task.setError_code(resultSetForUpdateLookUp.getString(6));
				task.setError_message(resultSetForUpdateLookUp.getString(7));
				if(null != resultSetForUpdateLookUp.getTimestamp(8)) {
					task.setScheduled_date(resultSetForUpdateLookUp.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSetForUpdateLookUp.getTimestamp(9)) {
					task.setQuery_start_time(resultSetForUpdateLookUp.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSetForUpdateLookUp.getTimestamp(10)) {
					task.setCompleted_time(resultSetForUpdateLookUp.getTimestamp(10).toLocalDateTime());
				}
				taskStatusList.add(task);
			}

			
			List<TaskStatus> failedTasks = new ArrayList<TaskStatus>();
			if (taskStatusList.size() > 0) {
				for (TaskStatus taskStatus : taskStatusList) {
					if (taskStatus.getState().equalsIgnoreCase("FAILED")) {
						failedTasks.add(taskStatus);
					}

				}

				if (failedTasks.size() > 0) {
					mailService.mailsendingForTask("ORDERTOTALQUANTITYUPDATEFORSHOPIFYTOECOMMERCE", failedTasks);
				} else {
					mailService.mailsendingForTaskSuccess("ORDERTOTALQUANTITYUPDATEFORSHOPIFYTOECOMMERCE");
				}
			}
		} catch (Exception e) {
			log.error("TaskStatusServiceImpl.loadOrderTotalQuantityUpdateForShopifyToEcommerceTaskHistory()",e);
			throw new TaskStatusServiceException(e.getMessage(),e.getCause(),null);
		}
		log.info("<==TaskStatusServiceImpl.loadOrderTotalQuantityUpdateForShopifyToEcommerceTaskHistory()");
		return taskStatusList;
	}

	@Override
	public List<LogHistory> loadProductLogHistory() throws TaskStatusServiceException {
		log.info("==>TaskStatusServiceImpl.loadProductLogHistory()");
		List<LogHistory> productList = null;
		try {
			int schedulerTime = jobInformation.getDataIntervalTime();
			String currentDate = jobInformation.getSchedulerExecutionTime();
			productList = new ArrayList<>();
			List<String> storeList = new ArrayList<>();
			List<LogHistory> productShopify = new ArrayList<>();
			Statement statement = connection.createStatement();

			/*
			 * String query =
			 * "select slp.ERROR_CODE,slp.ERROR_STATE,slp.ERROR_MESSAGE,slp.STACK_TRACE,slp.STORE_ID,slp.SNOWFLAKE_SCHEMA,slp.ERROR_DATE,es.PLATFORM,slp.PRODUCT_ID from "
			 * + schema + "." +
			 * "STP_LOG_PRODUCTS as slp INNER JOIN (select STORE_ID,MAX(ERROR_DATE) as maxdate from "
			 * +schema + "."+" STP_LOG_PRODUCTS group by STORE_ID) slpm on " +
			 * "	 slpm.maxdate=slp.ERROR_DATE and slpm.STORE_ID = slp.STORE_ID " +
			 * " INNER JOIN " + schema + "." +
			 * "ECOMMERCE_STORES  es ON es.STORE_ID = slp.STORE_ID " + " " +
			 * "where ERROR_DATE >= DATEADD(day,-6,'"+ currentDate + "')";
			 */
			
			String query = "select slp.ERROR_CODE,slp.ERROR_STATE,slp.ERROR_MESSAGE,slp.STACK_TRACE,slp.STORE_ID,slp.SNOWFLAKE_SCHEMA,slp.ERROR_DATE,es.PLATFORM,slp.PRODUCT_ID from "
					+ schema + "." + "STP_LOG_PRODUCTS as slp "
					+ " INNER JOIN " + schema + "."
					+ "ECOMMERCE_STORES  es ON es.STORE_ID = slp.STORE_ID " + " "
							+ "where ERROR_DATE >= DATEADD("+jobInformation.getScheduledforEvery()+",-"+schedulerTime +",current_timestamp())";
			log.info("==>TaskStatusServiceImpl.loadProductLogHistory() query:" + query);
			ResultSet resultSetProcucts = statement.executeQuery(query);
			
			while (resultSetProcucts.next()) {
				LogHistory logProduct = new LogHistory();
				logProduct.setErrorCode(resultSetProcucts.getString(1));
				logProduct.setErrorState(resultSetProcucts.getString(2));
				logProduct.setErrorMessage(resultSetProcucts.getString(3));
				logProduct.setStackTrace(resultSetProcucts.getString(4));
				logProduct.setStoreId(resultSetProcucts.getString(5));
				logProduct.setSnowflakeSchema(resultSetProcucts.getString(6));
				logProduct.setErrorDate(resultSetProcucts.getString(7));
				logProduct.setPlatform(resultSetProcucts.getString(8));
				logProduct.setId(resultSetProcucts.getString(9));
				logProduct.setTableName("STP_LOG_PRODUCTS");

				productList.add(logProduct);

			}

			String query1 = ("select PLATFORM FROM " + schema + "." + "ECOMMERCE_STORES");

			log.info("==>TaskStatusServiceImpl.loadProductLogHistory() query1:" + query1);
			ResultSet resultSetStores = statement.executeQuery(query1);

			while (resultSetStores.next()) {
				
				storeList.add(resultSetStores.getString(1));

			}
			if (productList.size() > 0 && storeList.size() > 0) {
				ArrayList<String> newList = removeDuplicates(storeList);
				for (String store : newList) {
					List<LogHistory> finallist = new ArrayList<>();
					for (LogHistory product : productList) {
						if (product.getPlatform().equals(store)) {
							finallist.add(product);
						}
					}
					if (finallist.size() > 0) {
						mailService.mailsendingForTaskFailure("PRODUCT", finallist);
					}
					finallist = null;
				}
			}

			// int countA=Collections.frequency(productShopify,
			// "SHOPIFY_NOTHING_BUT_THIEVES");
			
		} catch (Exception e) {
			log.error("TaskStatusServiceImpl.loadProductLogHistory()", e);
			throw new TaskStatusServiceException(e.getMessage(), e.getCause(), null);
		}
		log.info("<==TaskStatusServiceImpl.loadProductLogHistory()");
		return productList;
	}
	
	public static ArrayList<String> removeDuplicates(List<String> storeList) {

		// Create a new LinkedHashSet
		Set<String> set = new LinkedHashSet<>();

		// Add the elements to set
		set.addAll(storeList);

		// Clear the list
		storeList.clear();

		// add the elements of set
		// with no duplicates to the list
		storeList.addAll(set);

		// return the list
		return (ArrayList<String>) storeList;
	}

	@Override
	public List<LogHistory> loadOrderLogHistory() throws TaskStatusServiceException {
		log.info("==>TaskStatusServiceImpl.loadOrderLogHistory()");
		List<LogHistory> orderList = null;
		try {
			int schedulerTime = jobInformation.getDataIntervalTime();
			String currentDate = jobInformation.getSchedulerExecutionTime();
			orderList = new ArrayList<>();
			List<String> storeList = new ArrayList<>();
			Statement statement = connection.createStatement();

			/*
			 * String query =
			 * "select slo.ERROR_CODE,slo.ERROR_STATE,slo.ERROR_MESSAGE,slo.STACK_TRACE,slo.STORE_ID,slo.SNOWFLAKE_SCHEMA,slo.ERROR_DATE,es.PLATFORM from "
			 * + schema + "." + "STP_LOG_ORDERS slo " + " INNER JOIN " + schema + "." +
			 * "ECOMMERCE_STORES  es ON es.STORE_ID = slo.STORE_ID " + " " +
			 * "where ERROR_DATE >= DATEADD(day,-6,'"+currentDate + "')";
			 */
			
			String query = "select slo.ERROR_CODE,slo.ERROR_STATE,slo.ERROR_MESSAGE,slo.STACK_TRACE,slo.STORE_ID,slo.SNOWFLAKE_SCHEMA,slo.ERROR_DATE,es.PLATFORM,ORDER_ID from "
					+ schema + "." + "STP_LOG_ORDERS slo " + " INNER JOIN " + schema + "."
					+ "ECOMMERCE_STORES  es ON es.STORE_ID = slo.STORE_ID " + " "
							+ "where ERROR_DATE >= DATEADD("+jobInformation.getScheduledforEvery()+",-"+schedulerTime +",current_timestamp())";
			log.info("==>TaskStatusServiceImpl.loadOrderLogHistory() query:" + query);
			ResultSet resultSetOrders = statement.executeQuery(query);

			while (resultSetOrders.next()) {
				LogHistory logOrder = new LogHistory();
				logOrder.setErrorCode(resultSetOrders.getString(1));
				logOrder.setErrorState(resultSetOrders.getString(2));
				logOrder.setErrorMessage(resultSetOrders.getString(3));
				logOrder.setStackTrace(resultSetOrders.getString(4));
				logOrder.setStoreId(resultSetOrders.getString(5));
				logOrder.setSnowflakeSchema(resultSetOrders.getString(6));
				logOrder.setErrorDate(resultSetOrders.getString(7));
				logOrder.setPlatform(resultSetOrders.getString(8));
				logOrder.setId(resultSetOrders.getString(9));
				logOrder.setTableName("STP_LOG_ORDERS");

				orderList.add(logOrder);

			}

			String query1 = ("select PLATFORM FROM " + schema + "." + "ECOMMERCE_STORES");

			log.info("==>TaskStatusServiceImpl.loadOrderLogHistory() query1:" + query1);
			ResultSet resultSetStores = statement.executeQuery(query1);

			while (resultSetStores.next()) {
				
				storeList.add(resultSetStores.getString(1));

			}
			if (orderList.size() > 0 && storeList.size() > 0) {
				ArrayList<String> newList = removeDuplicates(storeList);
				for (String store : newList) {
					List<LogHistory> finallist = new ArrayList<>();
					for (LogHistory product : orderList) {
						if (product.getPlatform().equals(store)) {
							finallist.add(product);
						}
					}
					if (finallist.size() > 0) {
						mailService.mailsendingForTaskFailure("ORDER", finallist);
					}
					finallist = null;
				}
			}

			// int countA=Collections.frequency(productShopify,
			// "SHOPIFY_NOTHING_BUT_THIEVES");

		} catch (Exception e) {
			log.error("TaskStatusServiceImpl.loadOrderLogHistory()", e);
			throw new TaskStatusServiceException(e.getMessage(), e.getCause(), null);
		}
		log.info("<==TaskStatusServiceImpl.loadOrderLogHistory()");
		return orderList;
	}

	@Override
	public List<LogHistory> loadCustomerLogHistory() throws TaskStatusServiceException {
		log.info("==>TaskStatusServiceImpl.loadCustomerLogHistory()");
		List<LogHistory> customerList = null;
		try {
			int schedulerTime = jobInformation.getDataIntervalTime();
			String currentDate = jobInformation.getSchedulerExecutionTime();
			customerList = new ArrayList<>();
			List<String> storeList = new ArrayList<>();
			Statement statement = connection.createStatement();

			/*
			 * String query =
			 * "select slc.ERROR_CODE,slc.ERROR_STATE,slc.ERROR_MESSAGE,slc.STACK_TRACE,slc.STORE_ID,slc.SNOWFLAKE_SCHEMA,slc.ERROR_DATE,es.PLATFORM,CUSTOMER_ID from "
			 * + schema + "." +
			 * "STP_LOG_CUSTOMERS as slc INNER JOIN (select STORE_ID,MAX(ERROR_DATE) as maxdate from "
			 * +schema + "."+" STP_LOG_CUSTOMERS group by STORE_ID) slcm on " +
			 * "slcm.maxdate=slc.ERROR_DATE and slcm.STORE_ID = slc.STORE_ID "+
			 * " INNER JOIN " + schema + "." +
			 * "ECOMMERCE_STORES  es ON es.STORE_ID = slc.STORE_ID " + " " +
			 * "where ERROR_DATE >= DATEADD(day,-6,'"+ currentDate + "')";
			 */
			
			String query = "select slc.ERROR_CODE,slc.ERROR_STATE,slc.ERROR_MESSAGE,slc.STACK_TRACE,slc.STORE_ID,slc.SNOWFLAKE_SCHEMA,slc.ERROR_DATE,es.PLATFORM,CUSTOMER_ID from "
					+ schema + "." + "STP_LOG_CUSTOMERS as slc "
					+  " INNER JOIN " + schema + "."
					+ "ECOMMERCE_STORES  es ON es.STORE_ID = slc.STORE_ID " + " "
							+ "where ERROR_DATE >= DATEADD("+jobInformation.getScheduledforEvery()+",-"+schedulerTime +",current_timestamp())";
			log.info("==>TaskStatusServiceImpl.loadCustomerLogHistory() query:" + query);
			ResultSet resultSetOrders = statement.executeQuery(query);

			while (resultSetOrders.next()) {
				LogHistory logCustomer = new LogHistory();
				logCustomer.setErrorCode(resultSetOrders.getString(1));
				logCustomer.setErrorState(resultSetOrders.getString(2));
				logCustomer.setErrorMessage(resultSetOrders.getString(3));
				logCustomer.setStackTrace(resultSetOrders.getString(4));
				logCustomer.setStoreId(resultSetOrders.getString(5));
				logCustomer.setSnowflakeSchema(resultSetOrders.getString(6));
				logCustomer.setErrorDate(resultSetOrders.getString(7));
				logCustomer.setPlatform(resultSetOrders.getString(8));
				logCustomer.setId(resultSetOrders.getString(9));
				logCustomer.setTableName("STP_LOG_CUSTOMERS");

				customerList.add(logCustomer);

			}

			String query1 = ("select PLATFORM FROM " + schema + "." + "ECOMMERCE_STORES");

			log.info("==>TaskStatusServiceImpl.loadCustomerLogHistory() query:" + query1);
			ResultSet resultSetStores = statement.executeQuery(query1);

			while (resultSetStores.next()) {

				storeList.add(resultSetStores.getString(1));

			}
			if (customerList.size() > 0 && storeList.size() > 0) {
				ArrayList<String> newList = removeDuplicates(storeList);
				for (String store : newList) {
					List<LogHistory> finallist = new ArrayList<>();
					for (LogHistory customer : customerList) {
						if (customer.getPlatform().equals(store)) {
							finallist.add(customer);
						}
					}
					if (finallist.size() > 0) {
						mailService.mailsendingForTaskFailure("CUSTOMER", finallist);
					}
					finallist = null;
				}
			}

		} catch (Exception e) {
			log.error("TaskStatusServiceImpl.loadCustomerLogHistory()", e);
			throw new TaskStatusServiceException(e.getMessage(), e.getCause(), null);
		}
		log.info("<==TaskStatusServiceImpl.loadCustomerLogHistory()");
		return customerList;
	}
	
	@Override
	public List<TaskStatus> loadShopifyProductDataFeedingProcess() throws TaskStatusServiceException {
		
		log.info("==>TaskStatusServiceImpl.loadShopifyProductDataFeedingProcess()");
	
		List<TaskStatus> taskStatusList = new ArrayList<TaskStatus>();
		int schedulerTime = jobInformation.getDataIntervalTime();
		try {
			
			Statement statement = connection.createStatement();

			String query = "select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME  "
					+ " from table(information_schema.task_history( "
					+ " scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+schedulerTime +",current_timestamp()), "
				    + " task_name=>'SHOPIFY_PRODUCT_DATA_CONSOLIDATION'))";
			log.info("==>TaskStatusServiceImpl.loadShopifyProductDataFeedingProcess() query:" + query);
			ResultSet resultSet = statement.executeQuery(query);

			while (resultSet.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSet.getString(1));
				task.setName(resultSet.getString(2));
				task.setDatabase_name(resultSet.getString(3));
				task.setSchema_name(resultSet.getString(4));
				task.setState(resultSet.getString(5));
				task.setError_code(resultSet.getString(6));
				task.setError_message(resultSet.getString(7));
				if(null != resultSet.getTimestamp(8)) {
					task.setScheduled_date(resultSet.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(9)) {
					task.setQuery_start_time(resultSet.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(10)) {
					task.setCompleted_time(resultSet.getTimestamp(10).toLocalDateTime());
				}
				taskStatusList.add(task);
			}
			
			List<TaskStatus> failedTasks = new ArrayList<TaskStatus>();
			List<TaskStatus> successTasks = new ArrayList<TaskStatus>();
			if (taskStatusList.size() > 0) {
				for (TaskStatus taskStatus : taskStatusList) {
					if (taskStatus.getState().equalsIgnoreCase("FAILED")) {
						failedTasks.add(taskStatus);
					}else if(taskStatus.getState().equalsIgnoreCase("SUCCEEDED")) {
						successTasks.add(taskStatus);
					}

				}

				if (failedTasks.size() > 0) {
					mailService.mailsendingForAllProcessFailure(failedTasks,"SHOPIFYPRODUCT");
				} else if(successTasks.size() > 0){
					mailService.mailsendingForAllProcessSuccess("SHOPIFYPRODUCT");
				}
		
		} 
		}catch (Exception e) {
			log.error("TaskStatusServiceImpl.loadShopifyProductDataFeedingProcess()", e);
			throw new TaskStatusServiceException(e.getMessage(), e.getCause(), null);
		}
		log.info("<==TaskStatusServiceImpl.loadShopifyProductDataFeedingProcess()");
		return taskStatusList;
	}
	
	@Override
	public List<TaskStatus> loadShopifyCustomerDataFeedingProcess() throws TaskStatusServiceException {
		
		log.info("==>TaskStatusServiceImpl.loadShopifyCustomerDataFeedingProcess()");
	
		List<TaskStatus> taskStatusList = new ArrayList<TaskStatus>();
		int schedulerTime = jobInformation.getDataIntervalTime();
		try {
			
			Statement statement = connection.createStatement();

			String query = "select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME  "
					+ " from table(information_schema.task_history( "
					+ " scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+schedulerTime +",current_timestamp()), "
				    + " task_name=>'SHOPIFY_CUSTOMERS_DATA_CONSOLIDATION'))";
			log.info("==>TaskStatusServiceImpl.loadShopifyCustomerDataFeedingProcess() query:" + query);
			ResultSet resultSet = statement.executeQuery(query);

			while (resultSet.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSet.getString(1));
				task.setName(resultSet.getString(2));
				task.setDatabase_name(resultSet.getString(3));
				task.setSchema_name(resultSet.getString(4));
				task.setState(resultSet.getString(5));
				task.setError_code(resultSet.getString(6));
				task.setError_message(resultSet.getString(7));
				if(null != resultSet.getTimestamp(8)) {
					task.setScheduled_date(resultSet.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(9)) {
					task.setQuery_start_time(resultSet.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(10)) {
					task.setCompleted_time(resultSet.getTimestamp(10).toLocalDateTime());
				}
				taskStatusList.add(task);
			}
			
			List<TaskStatus> failedTasks = new ArrayList<TaskStatus>();
			List<TaskStatus> successTasks = new ArrayList<TaskStatus>();
			if (taskStatusList.size() > 0) {
				for (TaskStatus taskStatus : taskStatusList) {
					if (taskStatus.getState().equalsIgnoreCase("FAILED")) {
						failedTasks.add(taskStatus);
					}else if(taskStatus.getState().equalsIgnoreCase("SUCCEEDED")) {
						successTasks.add(taskStatus);
					}

				}

				if (failedTasks.size() > 0) {
					mailService.mailsendingForAllProcessFailure(failedTasks,"SHOPIFYCUSTOMER");
				} else if(successTasks.size() > 0){
					mailService.mailsendingForAllProcessSuccess("SHOPIFYCUSTOMER");
				}
		
		} 
		}catch (Exception e) {
			log.error("TaskStatusServiceImpl.loadShopifyCustomerDataFeedingProcess()", e);
			throw new TaskStatusServiceException(e.getMessage(), e.getCause(), null);
		}
		log.info("<==TaskStatusServiceImpl.loadShopifyCustomerDataFeedingProcess()");
		return taskStatusList;
	}
	
	@Override
	public List<TaskStatus> loadShopifyOrderDataFeedingProcess() throws TaskStatusServiceException {
		
		log.info("==>TaskStatusServiceImpl.loadShopifyOrderDataFeedingProcess()");
	
		List<TaskStatus> taskStatusList = new ArrayList<TaskStatus>();
		int schedulerTime = jobInformation.getDataIntervalTime();
		try {
			
			Statement statement = connection.createStatement();

			String query = "select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME  "
					+ " from table(information_schema.task_history( "
					+ " scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+schedulerTime +",current_timestamp()), "
				    + " task_name=>'SHOPIFY_ORDERS_DATA_CONSOLIDATION'))";
			log.info("==>TaskStatusServiceImpl.loadShopifyOrderDataFeedingProcess() query:" + query);
			ResultSet resultSet = statement.executeQuery(query);

			while (resultSet.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSet.getString(1));
				task.setName(resultSet.getString(2));
				task.setDatabase_name(resultSet.getString(3));
				task.setSchema_name(resultSet.getString(4));
				task.setState(resultSet.getString(5));
				task.setError_code(resultSet.getString(6));
				task.setError_message(resultSet.getString(7));
				if(null != resultSet.getTimestamp(8)) {
					task.setScheduled_date(resultSet.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(9)) {
					task.setQuery_start_time(resultSet.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(10)) {
					task.setCompleted_time(resultSet.getTimestamp(10).toLocalDateTime());
				}
				taskStatusList.add(task);
			}
			
			List<TaskStatus> failedTasks = new ArrayList<TaskStatus>();
			List<TaskStatus> successTasks = new ArrayList<TaskStatus>();
			if (taskStatusList.size() > 0) {
				for (TaskStatus taskStatus : taskStatusList) {
					if (taskStatus.getState().equalsIgnoreCase("FAILED")) {
						failedTasks.add(taskStatus);
					}else if(taskStatus.getState().equalsIgnoreCase("SUCCEEDED")) {
						successTasks.add(taskStatus);
					}

				}

				if (failedTasks.size() > 0) {
					mailService.mailsendingForAllProcessFailure(failedTasks,"SHOPIFYORDER");
				} else if(successTasks.size() > 0){
					mailService.mailsendingForAllProcessSuccess("SHOPIFYORDER");
				}
		
		} 
		}catch (Exception e) {
			log.error("TaskStatusServiceImpl.loadShopifyOrderDataFeedingProcess()", e);
			throw new TaskStatusServiceException(e.getMessage(), e.getCause(), null);
		}
		log.info("<==TaskStatusServiceImpl.loadShopifyOrderDataFeedingProcess()");
		return taskStatusList;
	}
	
	@Override
	public List<TaskStatus> loadShopifyCustomerTotalSpentDataFeedingProcess() throws TaskStatusServiceException {
		
		log.info("==>TaskStatusServiceImpl.loadShopifyCustomerTotalSpentDataFeedingProcess()");
	
		List<TaskStatus> taskStatusList = new ArrayList<TaskStatus>();
		int schedulerTime = jobInformation.getDataIntervalTime();
		try {
			
			Statement statement = connection.createStatement();

			String query = "select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME  "
					+ " from table(information_schema.task_history( "
					+ " scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+schedulerTime +",current_timestamp()), "
				    + " task_name=>'SHOPIFY_CUSTOMER_TOTAL_SPENT'))";
			log.info("==>TaskStatusServiceImpl.loadShopifyCustomerTotalSpentDataFeedingProcess() query:" + query);
			ResultSet resultSet = statement.executeQuery(query);

			while (resultSet.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSet.getString(1));
				task.setName(resultSet.getString(2));
				task.setDatabase_name(resultSet.getString(3));
				task.setSchema_name(resultSet.getString(4));
				task.setState(resultSet.getString(5));
				task.setError_code(resultSet.getString(6));
				task.setError_message(resultSet.getString(7));
				if(null != resultSet.getTimestamp(8)) {
					task.setScheduled_date(resultSet.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(9)) {
					task.setQuery_start_time(resultSet.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(10)) {
					task.setCompleted_time(resultSet.getTimestamp(10).toLocalDateTime());
				}
				taskStatusList.add(task);
			}
			
			List<TaskStatus> failedTasks = new ArrayList<TaskStatus>();
			List<TaskStatus> successTasks = new ArrayList<TaskStatus>();
			if (taskStatusList.size() > 0) {
				for (TaskStatus taskStatus : taskStatusList) {
					if (taskStatus.getState().equalsIgnoreCase("FAILED")) {
						failedTasks.add(taskStatus);
					}else if(taskStatus.getState().equalsIgnoreCase("SUCCEEDED")) {
						successTasks.add(taskStatus);
					}

				}

				if (failedTasks.size() > 0) {
					mailService.mailsendingForAllProcessFailure(failedTasks,"SHOPIFYCUSTOMERTOTALSPENT");
				} else if(successTasks.size() > 0){
					mailService.mailsendingForAllProcessSuccess("SHOPIFYCUSTOMERTOTALSPENT");
				}
		
		} 
		}catch (Exception e) {
			log.error("TaskStatusServiceImpl.loadShopifyCustomerTotalSpentDataFeedingProcess()", e);
			throw new TaskStatusServiceException(e.getMessage(), e.getCause(), null);
		}
		log.info("<==TaskStatusServiceImpl.loadShopifyCustomerTotalSpentDataFeedingProcess()");
		return taskStatusList;
	}
	
	@Override
	public List<TaskStatus> loadShopifyTotalQuantityDataFeedingProcess() throws TaskStatusServiceException {
		
		log.info("==>TaskStatusServiceImpl.loadShopifyTotalQuantityDataFeedingProcess()");
	
		List<TaskStatus> taskStatusList = new ArrayList<TaskStatus>();
		int schedulerTime = jobInformation.getDataIntervalTime();
		try {
			
			Statement statement = connection.createStatement();

			String query = "select QUERY_ID,NAME,DATABASE_NAME,SCHEMA_NAME,STATE,ERROR_CODE,ERROR_MESSAGE,SCHEDULED_TIME,QUERY_START_TIME,COMPLETED_TIME  "
					+ " from table(information_schema.task_history( "
					+ " scheduled_time_range_start=>dateadd("+jobInformation.getScheduledforEvery()+",-"+schedulerTime +",current_timestamp()), "
				    + " task_name=>'SHOPIFY_TOTAL_QUANTITY'))";
			log.info("==>TaskStatusServiceImpl.loadShopifyTotalQuantityDataFeedingProcess() query:" + query);
			ResultSet resultSet = statement.executeQuery(query);

			while (resultSet.next()) {
				TaskStatus task = new TaskStatus();
				task.setQuery_id(resultSet.getString(1));
				task.setName(resultSet.getString(2));
				task.setDatabase_name(resultSet.getString(3));
				task.setSchema_name(resultSet.getString(4));
				task.setState(resultSet.getString(5));
				task.setError_code(resultSet.getString(6));
				task.setError_message(resultSet.getString(7));
				if(null != resultSet.getTimestamp(8)) {
					task.setScheduled_date(resultSet.getTimestamp(8).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(9)) {
					task.setQuery_start_time(resultSet.getTimestamp(9).toLocalDateTime());
				}
				if(null != resultSet.getTimestamp(10)) {
					task.setCompleted_time(resultSet.getTimestamp(10).toLocalDateTime());
				}
				taskStatusList.add(task);
			}
			
			List<TaskStatus> failedTasks = new ArrayList<TaskStatus>();
			List<TaskStatus> successTasks = new ArrayList<TaskStatus>();
			if (taskStatusList.size() > 0) {
				for (TaskStatus taskStatus : taskStatusList) {
					if (taskStatus.getState().equalsIgnoreCase("FAILED")) {
						failedTasks.add(taskStatus);
					}else if(taskStatus.getState().equalsIgnoreCase("SUCCEEDED")) {
						successTasks.add(taskStatus);
					}

				}

				if (failedTasks.size() > 0) {
					mailService.mailsendingForAllProcessFailure(failedTasks,"SHOPIFYTOTALQUANTITY");
				} else if(successTasks.size() > 0){
					mailService.mailsendingForAllProcessSuccess("SHOPIFYTOTALQUANTITY");
				}
		
		} 
		}catch (Exception e) {
			log.error("TaskStatusServiceImpl.loadShopifyTotalQuantityDataFeedingProcess()", e);
			throw new TaskStatusServiceException(e.getMessage(), e.getCause(), null);
		}
		log.info("<==TaskStatusServiceImpl.loadShopifyTotalQuantityDataFeedingProcess()");
		return taskStatusList;
	}
	
	@Override
	public List<LogHistory> loadShopifyMasterDataLogHistory() throws TaskStatusServiceException {
		log.info("==>TaskStatusServiceImpl.loadShopifyMasterDataLogHistory()");
		List<LogHistory>logList = null;
		try {
			int schedulerTime = jobInformation.getDataIntervalTime();
			String currentDate = jobInformation.getSchedulerExecutionTime();
			logList = new ArrayList<>();
			List<String> storeList = new ArrayList<>();
			Statement statement = connection.createStatement();
			
			String query = "select slo.ERROR_CODE,slo.ERROR_STATE,slo.ERROR_MESSAGE,slo.STACK_TRACE,slo.STORE_ID,slo.SNOWFLAKE_SCHEMA,slo.STORED_PROCEDURE_NAME,slo.ERROR_DATE,es.PLATFORM,CUSTOM_ERROR_MESSAGE from "
					+ schema + "." + "STP_LOG_SHOPIFY_MASTER_DATA_FEED slo " + " INNER JOIN " + schema + "."
					+ "ECOMMERCE_STORES  es ON es.STORE_ID = slo.STORE_ID " + " "
							+ "where ERROR_DATE >= DATEADD("+jobInformation.getScheduledforEvery()+",-"+schedulerTime +",current_timestamp())";
			log.info("==>TaskStatusServiceImpl.loadShopifyMasterDataLogHistory() query:" + query);
			ResultSet resultSetOrders = statement.executeQuery(query);

			while (resultSetOrders.next()) {
				LogHistory logHistory = new LogHistory();
				logHistory.setErrorCode(resultSetOrders.getString(1));
				logHistory.setErrorState(resultSetOrders.getString(2));
				logHistory.setErrorMessage(resultSetOrders.getString(3));
				logHistory.setStackTrace(resultSetOrders.getString(4));
				logHistory.setStoreId(resultSetOrders.getString(5));
				logHistory.setSnowflakeSchema(resultSetOrders.getString(6));
				logHistory.setStoredProcedureName(resultSetOrders.getString(7));
				logHistory.setErrorDate(resultSetOrders.getString(8));
				logHistory.setPlatform(resultSetOrders.getString(9));
				logHistory.setCustomErrorMsg(resultSetOrders.getString(10));
				logHistory.setTableName("STP_LOG_SHOPIFY_MASTER_DATA_FEED");

				logList.add(logHistory);

			}

			String query1 = ("select PLATFORM FROM " + schema + "." + "ECOMMERCE_STORES");

			log.info("==>TaskStatusServiceImpl.loadShopifyMasterDataLogHistory() query1:" + query1);
			ResultSet resultSetStores = statement.executeQuery(query1);

			while (resultSetStores.next()) {
				
				storeList.add(resultSetStores.getString(1));

			}
			if (logList.size() > 0 && storeList.size() > 0) {
				ArrayList<String> newList = removeDuplicates(storeList);
				for (String store : newList) {
					List<LogHistory> finallist = new ArrayList<>();
					for (LogHistory product : logList) {
						if (product.getPlatform().equals(store)) {
							finallist.add(product);
						}
					}
					if (finallist.size() > 0) {
						mailService.mailsendingForLogShopifyDataHistory(finallist);
					}
					finallist = null;
				}
			}

		} catch (Exception e) {
			log.error("TaskStatusServiceImpl.loadShopifyMasterDataLogHistory()", e);
			throw new TaskStatusServiceException(e.getMessage(), e.getCause(), null);
		}
		log.info("<==TaskStatusServiceImpl.loadShopifyMasterDataLogHistory()");
		return logList;
	}

}