package net.waltzstudio.montecarlo.dao.ticket;

//import static org.seasar.extension.jdbc.operation.Operations.*;F
import static net.waltzstudio.base.framework.util.SyntaxSugar.*;
import java.util.ArrayList;
import java.util.List;
import net.waltzstudio.montecarlo.entity.ticket.Ticket;

/**
 * チケットDao
 */
public abstract class TicketDao extends TicketDaoBase {

	public List<Ticket> findTrayTickets(Long accountId, Long projectId, Long labelId, boolean inbox) {
		List<Object> params = new ArrayList<Object>();
		String sql = SB(true, " ", //
				"  SELECT *                                    ", //
				"    FROM ticket t                             ", //
				"   WHERE t.delete_flg = '0'                   ", //
				"     AND EXISTS (                             ", //
				"          SELECT 1                            ", //
				"            FROM account_project ap           ", //
				"            JOIN project p                    ", //
				"              ON p.id = ap.project_id         ", //
				"             AND p.delete_flg = '0'           ");
		if (projectId != null) {
			sql = SB(true, " ", sql, //
					"             AND p.id = ?                     ");
			params.add(projectId);
		}
		sql = SB(true, " ", sql, //
				"           WHERE ap.project_id = t.project_id ", //
				"             AND ap.account_id = ?            ", //
				"             AND ap.delete_flg = '0'          ", //
				"        )                                     ");
		params.add(accountId);
		if (labelId != null) {
			sql = SB(true, " ", sql, //
					"     AND EXISTS (                             ", //
					"          SELECT 1                            ", //
					"            FROM ticket_label tl              ", //
					"            JOIN label l                      ", //
					"              ON l.id = tl.label_id           ", //
					"             AND l.id = ?                     ", //
					"             AND l.account_id = ?             ", //
					"             AND l.delete_flg = '0'           ", //
					"           WHERE tl.ticket_id = t.id          ", //
					"             AND tl.delete_flg = '0'          ", //
					"        )                                     ");
			params.add(labelId);
			params.add(accountId);
		}
		if (inbox) {
			sql = SB(true, " ", sql, //
					"     AND EXISTS (                             ", //
					"          SELECT 1                            ", //
					"            FROM ticket_inbox ti              ", //
					"           WHERE ti.ticket_id = t.id          ", //
					"             AND ti.account_id = ?            ", //
					"             AND ti.delete_flg = '0'          ", //
					"        )                                     ");
			params.add(accountId);
		}
		sql = SB(true, " ", sql, //
				"ORDER BY modify_datetime DESC, id DESC        ");
		return jdbcManager.selectBySql(Ticket.class, sql, params.toArray()).getResultList();
	}

}
