/*******************************************************************************
 * Copyright 2019 2Fi Business Solutions Ltd.
 *
 *
 *
 * This code is copyrighted. Under no circumstances should any party, people,
 * or organization should redistribute any portions of this code in any form,
 * either verbatim or through electronic media, to any third parties, unless
 * under explicit written permission by 2Fi Business Solutions Ltd.
 ******************************************************************************/
package com.ffii.tbms.reports.service;

import java.util.List;
import java.util.Map;

import com.ffii.core.dao.JdbcDao;
import com.ffii.core.utils.SecurityUtils;
import com.ffii.core.web.AbstractService;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;


@Service
public class CustomerReportService extends AbstractService {

	@Autowired
	private JdbcDao jdbcDao;

	@Transactional(isolation = Isolation.READ_COMMITTED, rollbackFor = Exception.class, readOnly = true)
	public List<Map<String, Object>> search(Map<String, Object> args) {

		SecurityUtils.authArgs(args);

		StringBuilder sql = new StringBuilder( " SELECT  "
			+ " 	c.created, "
			+ " CONCAT(IF(c.firstName IS NULL, '', CONCAT(c.firstName, ' ')), "
			+ " IF(c.surname IS NULL, '', CONCAT(c.surname, ' '))) AS customerName, "
			+ " 	c.nameCh, "
			+ " 	c.phone1, "
			+ " 	c.phone2, "
			+ " 	c.email, "
			+ " 	c.address, "
			+ " 	c.remarks, "
			+ " 	co.orderAmount AS orderAmount "
			+ " FROM "
			+ " 	customer c "
			+ " 		LEFT JOIN "
			+ " 	(SELECT  "
			+ " 		o.custId, SUM(oi.price) AS orderAmount "
			+ " 	FROM "
			+ " 		orders o "
			+ " 	LEFT JOIN order_item oi ON o.id = oi.orderId "
			+ " 	WHERE "
			+ " 		o.deleted = 0 AND oi.deleted = 0 "
			+ " 			AND (o.status = 0 OR o.status = 1) "
			+ " 	GROUP BY o.custId) co ON co.custId = c.id "
			+ " WHERE "
			+ " 	c.deleted = 0 "

			);

			if(args != null){
				if(args.containsKey("sysGroupId"))
					sql.append(" AND c.sysGroupId = :sysGroupId ");

				if(args.containsKey("customerName")){
					String str = args.get("customerName").toString();
					String[] array = str.split(" ");
					str = "";
					for(int i = 0; i<array.length; i++){
						str += " AND ( c.surname LIKE '"+array[i]+"%' ";
						str += " OR c.nameCh LIKE '"+array[i]+"%' ";
						str += " OR c.firstName LIKE '"+array[i]+"%' ) ";
					}
					sql.append(str);
				}
				if(args.containsKey("email"))
					sql.append(" AND (c.email LIKE :email) ");
				if(args.containsKey("address"))
					sql.append(" AND (c.address LIKE :address) ");
				if(args.containsKey("remarks"))
					sql.append(" AND (c.remarks LIKE :remarks) ");
				if(args.containsKey("dateFrom"))
					sql.append(" AND (c.created > :dateFrom) ");
				if(args.containsKey("dateTo"))
					sql.append(" AND (c.created < :dateTo) ");
				
			}

			sql.append(" GROUP BY c.id HAVING 1=1 ");
			if(args != null){
				if(args.containsKey("amountFrom"))
					sql.append(" AND (orderAmount >= :amountFrom) ");
				if(args.containsKey("amountTo"))
					sql.append(" AND (orderAmount <= :amountTo) ");
			}

			sql.append(" ORDER BY (0+0 ");
			if (args != null) {
				if (args.containsKey("customerName")){
					sql.append(" + IF( c.surname = :customerName,1,0) + IF( c.surname LIKE '"+args.get("customerName")+"%',1,0) ");
					sql.append(" + IF( c.firstName = :customerName,1,0) + IF( c.firstName LIKE '"+args.get("customerName")+"%',1,0) ");
					sql.append(" + IF( c.nameCh = :customerName,1,0) + IF( c.nameCh LIKE '"+args.get("customerName")+"%',1,0) ");
				}
			}
			sql.append(" ) DESC, c.created ");
	
		return jdbcDao.queryForList(sql.toString(), args);
	}

}
