/*******************************************************************************
 * 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 org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;

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

/**
 * @author fung
 */
@Service
public class OutstandingReportService 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 "
			+" o.id AS orderId, "
			+" o.custId, "
			+" c.email, "
			+" concat_ws(' / ', c.phone1, c.phone2) AS phone, "
			+" concat_ws(' ', c.firstName ,c.surname, c.nameCh) AS customerName, "
			+" o.type, "
			+" o.code, "
			+" CONCAT(o.type, o.code) AS orderNo, "
			+" o.date, "
			+" o.status, "
			//+" o.ignoreC, "
			+" IFNULL(_oi.orderAmount, 0) AS orderAmount, "
			+" IFNULL(_p.paymentAmount, 0) AS paymentAmount, "
			+" IFNULL(_oi.orderAmount, 0) - IFNULL(_p.paymentAmount, 0) AS outstanding "
			+" FROM customer c "
			+" LEFT JOIN orders o ON o.custId = c.id "
			+" LEFT JOIN (SELECT oi.orderId, SUM(oi.price) AS orderAmount "
				+" FROM order_item oi WHERE oi.deleted = 0 "
				+" GROUP BY oi.orderId) _oi ON _oi.orderId = o.id "
			+" LEFT JOIN (SELECT p.orderId, SUM(p.amount) AS paymentAmount "
				+" FROM payment p WHERE p.deleted = 0 "
				+" GROUP BY p.orderId) _p ON _p.orderId = o.id "
			+" WHERE o.deleted = 0 AND o.ref IS NULL AND o.status != "+Order.STATUS_CANCELLED+" "
			+" AND (IFNULL(_oi.orderAmount, 0) != IFNULL(_p.paymentAmount, 0) OR o.status="+Order.STATUS_ON_HAND+" ) ");

			if(args != null){
				if(args.containsKey("sysGroupId")){
					sql.append(" AND o.sysGroupId = :sysGroupId AND c.sysGroupId = :sysGroupId ");
				}
				if(args.containsKey("orderNo")) sql.append(" AND CONCAT(o.type, o.code) Like :orderNo ");
				if(args.containsKey("custName")) 
					sql.append(" AND CONCAT(IF(c.firstName IS NULL, '', CONCAT(c.firstName, ' ')), "
					+" IF(c.surname IS NULL, '', CONCAT(c.surname, ' ')), "
					+" IF(c.nameCh IS NULL, '', c.nameCh)) Like :custName ");
			}

			sql.append(" ORDER BY o.date , o.id ");
			if(args != null){
				if (args.containsKey("start") && args.containsKey("limit"))
					sql.append(" LIMIT :start, :limit ");
			}
	

		return jdbcDao.queryForList(sql.toString(), args);
	}

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

		SecurityUtils.authArgs(args);

		StringBuilder sql = new StringBuilder(
			" SELECT count(*) AS count  "
			+" FROM customer c "
			+" LEFT JOIN orders o ON o.custId = c.id "
			+" LEFT JOIN (SELECT oi.orderId, SUM(oi.price) AS orderAmount "
				+" FROM order_item oi WHERE oi.deleted = 0 "
				+" GROUP BY oi.orderId) _oi ON _oi.orderId = o.id "
			+" LEFT JOIN (SELECT p.orderId, SUM(p.amount) AS paymentAmount "
				+" FROM payment p WHERE p.deleted = 0 "
				+" GROUP BY p.orderId) _p ON _p.orderId = o.id "
			+" WHERE o.deleted = 0 AND o.ref IS NULL AND o.status != "+Order.STATUS_CANCELLED+" "
			+" AND ( IFNULL(_oi.orderAmount, 0) != IFNULL(_p.paymentAmount, 0) OR o.status="+Order.STATUS_ON_HAND+" ) ");
		if (args != null) {
			if(args.containsKey("sysGroupId")){
				sql.append(" AND o.sysGroupId = :sysGroupId ");
			}
			if(args.containsKey("orderNo")) sql.append(" AND CONCAT(o.type, o.code) Like :orderNo ");
				if(args.containsKey("custName")) 
					sql.append(" AND CONCAT(IF(c.firstName IS NULL, '', CONCAT(c.firstName, ' ')), "
					+" IF(c.surname IS NULL, '', CONCAT(c.surname, ' ')), "
					+" IF(c.nameCh IS NULL, '', c.nameCh)) Like :custName ");
		};

		return jdbcDao.queryForMap(sql.toString(), args);
	}

}
