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.customer.Customer;
import com.ffii.tbms.customer.dao.CustomerDao;

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 CustomerApiService extends AbstractService {

    @Autowired
    private JdbcDao jdbcDao;

	@Autowired
    private CustomerDao customerDao;

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = false)
	public Integer saveOrUpdate(Customer instance) {
		return customerDao.saveOrUpdate(instance);
	}
	
	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
	public Customer find(Integer id) {
		return customerDao.find(id);
	}

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

		StringBuilder sql = new StringBuilder(
			" SELECT "
			+" c.id,"
			+" c.surname, "
			+" c.firstName, "
			+" c.nameCh, "
			+" CONCAT(IF(c.firstName IS NULL, '', CONCAT(c.firstName, ' ')), "
				+" IF(c.surname IS NULL, '', CONCAT(c.surname, ' ')), "
				+" IF(c.nameCh IS NULL, '', c.nameCh)) AS customerName, "
            +" concat_ws(' / ', c.phone1, c.phone2) AS phone, "
			+" c.phone1, "
			+" c.phone2, "
			+" c.companyPhone, "
			+" c.companyEmail, "
			+" c.companyAddress, "
			+" c.email, "
			+" c.address, "
			+" c.remarks, "
			+" c.tag "
			+" "
		+" FROM "
			+" customer c "

		+" WHERE"
			+" c.deleted = 0 ");
        
        if(args != null){
            if (args.containsKey("_searchStr")){
                sql.append(getCustomerNameSearch(args.get("searchStr").toString()));
                /*
                sql.append(" AND ( c.surname LIKE '"+args.get("_searchStr")+"' ");
                sql.append(" OR c.firstName LIKE '"+args.get("_searchStr")+"' ");
                sql.append(" OR c.nameCh LIKE '"+args.get("_searchStr")+"' ");
                sql.append(" OR c.phone1 LIKE '"+args.get("_searchStr")+"' ");
                sql.append(" OR c.phone2 LIKE '"+args.get("_searchStr")+"' ) ");
                */
            }

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

		sql.append(" ORDER BY ( 0+0 ");

		if (args != null) {
			if (args.containsKey("searchStr")){
                sql.append(getCustomerNameOrderBy(args.get("searchStr").toString()));
                /*
				sql.append(" + IF( c.surname = :searchStr,1,0) + IF( c.surname LIKE '"+args.get("searchStr")+"%',1,0) ");
                sql.append(" + IF( c.firstName = :searchStr,1,0) + IF( c.firstName LIKE '"+args.get("searchStr")+"%',1,0) ");
                sql.append(" + IF( c.nameCh = :searchStr,1,0) + IF( c.nameCh LIKE '"+args.get("searchStr")+"%',1,0) ");
                sql.append(" + IF( c.phone1 = :searchStr,1,0) + IF( c.phone1 LIKE '"+args.get("searchStr")+"%',1,0) ");
                sql.append(" + IF( c.phone2 = :searchStr,1,0) + IF( c.phone2 LIKE '"+args.get("searchStr")+"%',1,0) ");
                sql.append(" + IF( c.companyPhone = :searchStr,1,0) + IF( c.companyPhone LIKE '"+args.get("searchStr")+"%',1,0) ");
                */
            }
		}
		sql.append(" ) DESC, ");
		sql.append(" TRIM(c.firstName), TRIM(c.surname), TRIM(c.nameCh), c.id ");

		logger.info("search cust: "+sql);

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

    private String getCustomerNameSearch(String searchStr){
        String str = "";

        String[] array = searchStr.split(" ");
        for(int i = 0; i<array.length; i++){
            str += " AND ( c.surname LIKE '"+array[i]+"%' ";
            str += " OR c.firstName LIKE '"+array[i]+"%' ";
            str += " OR c.nameCh LIKE '"+array[i]+"%' ";
            str += " OR c.phone1 LIKE '"+array[i]+"%' ";
            str += " OR c.phone2 LIKE '"+array[i]+"%' ) ";
        }
        return str;
    }

    private String getCustomerNameOrderBy(String searchStr){
        String str = "";

        String[] array = searchStr.split(" ");
        for(int i = 0; i<array.length; i++){
            str += " + IF( c.surname = '"+array[i]+"',1,0) + IF( c.surname LIKE '"+array[i]+"%',1,0) ";
            str += " + IF( c.firstName = '"+array[i]+"',1,0) + IF( c.firstName LIKE '"+array[i]+"%',1,0) ";
            str += " + IF( c.nameCh = '"+array[i]+"',1,0) + IF( c.nameCh LIKE '"+array[i]+"%',1,0) ";
            str += " + IF( c.phone1 = '"+array[i]+"',1,0) + IF( c.phone1 LIKE '"+array[i]+"%',1,0) ";
            str += " + IF( c.phone2 = '"+array[i]+"',1,0) + IF( c.phone2 LIKE '"+array[i]+"%',1,0) ";
        }
        return str;
    }

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

        StringBuilder sql = new StringBuilder(
			" SELECT "
			+" c.id,"
			+" c.surname, "
			+" c.firstName, "
			+" c.nameCh, "
			+" CONCAT(IF(c.firstName IS NULL, '', CONCAT(c.firstName, ' ')), "
				+" IF(c.surname IS NULL, '', CONCAT(c.surname, ' ')), "
				+" IF(c.nameCh IS NULL, '', c.nameCh)) AS customerName, "
			+" c.phone1, "
			+" c.phone2, "
            +" concat_ws(' / ', c.phone1, c.phone2) AS phone, "
			+" c.companyPhone, "
			+" c.companyEmail, "
			+" c.companyAddress, "
			+" c.email, "
			+" c.address, "
			+" c.remarks, "
			+" c.tag, "
			+" lastMeetingDate.date As lastMeetingDate, "
			+" nextMeetingDate.date AS nextMeetingDate "
		+" FROM "
			+" customer c "

			+" LEFT JOIN "
			+" ( SELECT Max(m.date) AS date, m.custId "
			+" FROM meeting m "
			+" WHERE m.deleted = 0 "
			+" AND DATE(m.date) < DATE(current_date()) "
			+" GROUP BY m.custId ) lastMeetingDate ON lastMeetingDate.custId = c.id "
			
			+" LEFT JOIN "
			+" ( SELECT MIN(m.date) AS date, m.custId "
			+" FROM meeting m "
			+" WHERE m.deleted = 0 "
			+" AND DATE(m.date) >= DATE(current_date()) "
			+" GROUP BY m.custId ) nextMeetingDate ON nextMeetingDate.custId = c.id "

		+" WHERE"
			+" c.deleted = 0 AND c.id = :custId ");

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

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

		StringBuilder sql = new StringBuilder(" SELECT "
		+" c.id, "
		+" c.phone1, "
		+" c.phone2, "
		+" concat_ws(' / ', c.phone1, c.phone2) AS phone, "
		+" CONCAT(IF(c.firstName IS NULL, '', CONCAT(c.firstName, ' ')), "
				+" IF(c.surname IS NULL, '', CONCAT(c.surname, ' ')), "
				+" IF(c.nameCh IS NULL, '', c.nameCh)) AS customerName "
		+" FROM customer c WHERE deleted = 0 ");

		if (args.containsKey(Params.QUERY))
			sql.append(" AND ( concat_ws(' ', c.firstName ,c.surname, c.nameCh, c.phone1, c.phone2) LIKE :query "
			+" OR concat_ws(' ', c.surname, c.firstName, c.nameCh, phone1, phone2) LIKE :query ) ");

		if (args.containsKey(Params.ID))
			sql.append(" AND c.id = :id");

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

		sql.append(" ORDER BY c.surname, c.firstName ");

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


}