package org.openlmis.report.mapper;

import org.apache.ibatis.annotations.Select;
import org.openlmis.report.model.dto.RequisitionGroup;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * e-lmis
 * Created by: Elias Muluneh
 * Date: 4/12/13
 * Time: 2:39 AM
 */
@Repository
public interface RequisitionGroupReportMapper {

    @Select("SELECT id, name, code " +
            "   FROM " +
            "       requisition_groups")
    List<RequisitionGroup> getAll();

    @Select("SELECT id, name, code " +
            "   FROM " +
            "       requisition_groups where id = #{param1}")
    List<RequisitionGroup> getById(int id);

    @Select("SELECT g.id, g.name, g.code " +
            "   FROM " +
            "       requisition_groups g" +
            "       join requisition_group_program_schedules ps on ps.requisitiongroupid = g.id " +
            " where " +
            " ps.programid = cast( #{param1} as int4) and ps.scheduleid = cast( #{param2} as int4) " +
            " and  g.id in (select rgm.requisitiongroupid from requisition_group_members rgm join programs_supported ps on rgm.facilityid = ps.facilityid where ps.programid = cast(#{param1} as int4) ) ")
    List<RequisitionGroup> getByProgramAndSchedule(int program, int schedule);

}

@@ -449,16 +449,30 @@
 n_groups
+ order by name
 %22)%0A    L
@@ -627,16 +627,30 @@
 %7Bparam1%7D
+ order by name
 %22)%0A    L
@@ -1232,16 +1232,49 @@
 nt4) ) %22
+ +%0A            %22 order by g.name%22
 )%0A    Li

Order requisition group lookup by name