package cn.hmsoft.mr.data.dao.std;

import org.springframework.stereotype.Repository;
import cn.hmsoft.helper.StringHelper;
import cn.hmsoft.jdbc.core.PlatformDaoSupport;
import cn.hmsoft.jdbc.entity.Pager;
import cn.hmsoft.jdbc.entity.QueryOrder;
import cn.hmsoft.mr.data.model.std.StdTicket;

import java.util.List;

@Repository
public class StdTicketDao extends PlatformDaoSupport<StdTicket> {

	public Pager pageTicket(Integer school_id, Integer batch_id, Integer start, Integer limit, String query,
			QueryOrder queryOrder) {
		StringBuilder sql = new StringBuilder();
		sql.append("select t.*,e.aspect_name,s.std_name,s.cert_id,s.std_mobile from std_ticket t ");
		sql.append(" inner join std_reg s on s.school_id=t.school_id and s.std_id=t.std_id ");
		sql.append(" left join std_enrol e on e.std_id=t.std_id and e.aspect_id=t.aspect_id and e.school_id=t.school_id and e.batch_id=t.batch_id ");
		sql.append("where 1=1 ");
		if (school_id != null) {
			sql.append(" and t.school_id=" + school_id);
		}
		if (batch_id != null) {
			sql.append(" and t.batch_id=" + batch_id);
		}
		if (StringHelper.isNotEmpty(query)) {
			sql.append(" and (s.cert_id like ? or s.std_name like ? or s.std_mobile like ? or s.baccal_no like ? or t.ticket_no like ?) ");
			String value = this.generateLikeParamter(query);
			return this.pageMapBySql(queryOrder, start, limit, sql.toString(), value, value, value, value, value);
		}
		return this.pageMapBySql(queryOrder, start, limit, sql.toString());
	}

    public Pager count(Integer school_id, Integer start, Integer limit, String query, QueryOrder queryOrder) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT b.batch_id,b.batch_name,count(DISTINCT t.std_id) total,count(DISTINCT l.STD_ID) download FROM std_ticket t LEFT JOIN std_log l ON t.std_id=l.STD_ID AND l.LOG_TYPE='ticket' LEFT JOIN cf_batch b ON t.batch_id=b.batch_id ");
		if (school_id != null) {
			sql.append(" where t.school_id=" + school_id);
		}
		sql.append(" group by b.batch_id,b.batch_name");
		return this.pageMapBySql(queryOrder, start, limit, sql.toString());
    }
}