package com.ffii.tbms.api.service;

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

import com.ffii.core.dao.JdbcDao;
import com.ffii.core.utils.MapUtils;
import com.ffii.core.utils.Params;
import com.ffii.core.web.AbstractService;
import com.ffii.tbms.order.Order;
import com.ffii.tbms.order.dao.OrderDao;

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

/**
 * OrderApiService
 */
@Service
public class OrderApiService extends AbstractService {

    @Autowired
    private JdbcDao jdbcDao;

    @Autowired
    private OrderDao orderDao;

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = false)
	public Integer saveOrUpdate(final Order instance) {
		return orderDao.saveOrUpdate(instance);
	}

    @Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
	public Order find(final Integer id) {
		return orderDao.find(id);
	}

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

        StringBuilder sql = new StringBuilder(" SELECT "
                + " orders.id,"
                + " orders.date,"
                + " orders.type,"
                + " orders.code,"
                + " orders.targetCompleteDate,"
                + " orders.status,"
                + " orders.remarks,"
                + " _p.paymentAmount, "
                + " _oi.orderAmount, "
                + " CONCAT(IF(cu.firstName IS NULL, '', CONCAT(cu.firstName, ' ')),"
                + " IF(cu.surname IS NULL, '', CONCAT(cu.surname, ' ')),"
                + " IF(cu.nameCh IS NULL, '', cu.nameCh)) AS customerName "
                + " FROM orders"
                + " LEFT JOIN customer cu ON cu.id = orders.custId"
                +" LEFT JOIN (SELECT oi.orderId, SUM(oi.price) AS orderAmount, "
                    +" GROUP_CONCAT(oi.name SEPARATOR ' ') AS itemName  "
                    +" FROM order_item oi WHERE oi.deleted = 0 "
                    +" GROUP BY oi.orderId) _oi ON _oi.orderId = orders.id "
                +" LEFT JOIN ( "
                    +" SELECT "
                    +" SUM(p.amount) AS paymentAmount, "
                    +" p.orderId "
                    +" FROM payment p "
                    +" WHERE p.deleted = 0 "
                    +" GROUP BY p.orderId) _p ON _p.orderId = orders.id "
               
                + " WHERE orders.deleted = 0 ");

        if (args != null) {
            if (args.containsKey("searchStr")) {
                sql.append(
                        " AND (CONCAT(IFNULL(cu.firstName, ''), "
                                + " IFNULL(cu.surname, ''), "
                                + " IFNULL(cu.nameCh,''), "
                                + " IFNULL(orders.type, ''), "
                                + " IFNULL(orders.code, '') "
                                // +", "
                                // +" IFNULL(oi.name, '')"
                                + ")) LIKE :searchStr");
            }
            if(args.containsKey("custId")){
                sql.append(" AND orders.custId = :custId ");
            }
            if(args.containsKey("sysGroupId")){
                sql.append(" AND orders.sysGroupId = :sysGroupId ");
            }
        }
        sql.append(" GROUP BY orders.id");
        sql.append(" ORDER BY orders.date DESC");

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

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

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

        StringBuilder sql = new StringBuilder("SELECT"
                + " concat(orders.type,orders.code) AS orderNo,"
                + " orders.id,"
                + " orders.type,"
                + " orders.code,"
                + " orders.custId,"
                + " orders.remarks,"
                + " orders.status,"
                + " orders.date,"
                + " orders.ref,"
                + " orders.refNo,"
                + " orders.targetCompleteDate,"
                + " CONCAT(IF(cu.firstName IS NULL, '', CONCAT(cu.firstName, ' ')),"
                + " IF(cu.surname IS NULL, '', CONCAT(cu.surname, ' ')),"
                + " IF(cu.nameCh IS NULL, '', cu.nameCh)) AS customerName,"
                + " orders.date AS orderDate"
                + " FROM"
                + " orders "
                + " LEFT JOIN customer cu ON orders.custId = cu.id "
                + " WHERE orders.deleted = 0 AND orders.id =:id");

                if(args.containsKey("sysGroupId")){
                    sql.append(" AND orders.sysGroupId = :sysGroupId ");
                }

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

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

        StringBuilder sql = new StringBuilder(" SELECT"
                + " oi.id,"
                + " oi.orderId,"
                + " oi.name,"
                + " oi.price"
                + " FROM order_item oi"
                + " WHERE oi.deleted = 0");

        if (args != null) {
            if (args.containsKey(Params.ID)) sql.append(" AND oi.orderId = :id");
        }

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

    //todo: remove start
    @Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
    public List<Map<String, Object>> searchDetail(Map<String, Object> args) {

        StringBuilder sql = new StringBuilder("SELECT"
                + " concat(orders.type,orders.code) AS orderNo,"
                + " orders.refNo,"
                + " CONCAT(IF(cu.firstName IS NULL, '', CONCAT(cu.firstName, ' ')),"
                + " IF(cu.surname IS NULL, '', CONCAT(cu.surname, ' ')),"
                + " IF(cu.nameCh IS NULL, '', cu.nameCh)) AS customerName,"
                + " orders.targetCompleteDate,"
                + " orders.date AS orderDate"
                + " FROM"
                + " orders "
                + " LEFT JOIN customer cu ON orders.custId = cu.id "
                + " WHERE orders.deleted = 0");
        if (args != null) {
            if (args.containsKey(Params.ID)) sql.append(" AND orders.id =:id");
        }

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


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

        StringBuilder sql = new StringBuilder(" SELECT "
                + " orders.id,"
                + " orders.type,"
                + " orders.code,"
                + " CONCAT(IF(cu.firstName IS NULL, '', CONCAT(cu.firstName, ' ')),"
                + " IF(cu.surname IS NULL, '', CONCAT(cu.surname, ' ')),"
                + " IF(cu.nameCh IS NULL, '', cu.nameCh)) AS customerName "
                + " FROM orders"
                + " LEFT JOIN customer cu ON cu.id = orders.custId"
                + " WHERE orders.deleted = 0 ");

        if (args != null) {
            if (args.containsKey("searchStr")) {
                sql.append(
                        " AND( "
                            +" (CONCAT(IFNULL(cu.firstName, ''), "
                            + " IFNULL(cu.surname, ''), "
                            + " IFNULL(cu.nameCh,''), "
                            + " IFNULL(orders.type, ''), "
                            + " IFNULL(orders.code, '') "
                            + ")) LIKE :searchStr "
                        +" OR "
                            +" (CONCAT(IFNULL(cu.surname, ''), "
                            + " IFNULL(cu.firstName, ''), "
                            + " IFNULL(cu.nameCh,''), "
                            + " IFNULL(orders.type, ''), "
                            + " IFNULL(orders.code, '') "
                            + ")) LIKE :searchStr "
                        +" )");
            }
            if(args.containsKey("orderId")){
                sql.append(" AND orders.id = :orderId ");
            }
            if(args.containsKey("customerId")){
                sql.append(" AND orders.custId = :customerId ");
            }
        }
        sql.append(" GROUP BY orders.id");
        sql.append(" ORDER BY orders.date DESC");

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

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

    @Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
    public List<Map<String, Object>> comboSearch(Integer customerId) {

        StringBuilder sql = new StringBuilder("SELECT id, code FROM orders WHERE orders.deleted =0");

        if (customerId != -1) {
            sql.append(" AND orders.custId = :customerId");
        }
        return jdbcDao.queryForList(sql.toString(), MapUtils.toHashMap("customerId", customerId));
    }
    
}